Error Exporting Database Catalog from Azure

We'd like to let you know about a problem you may encounter using Azure SQL and how to solve it. The problem occurs when users of the OutSystems platform session catalog try to import/export the database for backup. You’ll be shown this error:

“Validation of the schema model for data package failed.

Error SQL71562: Error validating element [dbo].[TempResetTimeout]: Procedure: [dbo].[TempResetTimeout] has an unresolved reference to object [DB_NAME].[dbo].[ASPStateTempSessions]. External references are not supported when creating a package from this platform.”

This is a known limitation of the Windows Azure SQL database. You can check the following link for more information: https://docs.microsoft.com/en-us/archive/blogs/ssdt/windows-azure-importexport-service-and-external-references


How to fix it?

To fix it, change all trigger code to no longer use a three-part identifier.

Run the following query to view what triggers you need to change:


SELECT 

sysobjects.name AS trigger_name, USER_NAME(sysobjects.uid) AS trigger_owner, 

s.name AS table_schema, OBJECT_NAME(parent_obj) AS table_name, syscomments.text

FROM sysobjects 

INNER JOIN sysusers ON sysobjects.uid = sysusers.uid 

INNER JOIN sys.tables t ON sysobjects.parent_obj = t.object_id 

INNER JOIN sys.schemas s ON t.schema_id = s.schema_id 

INNER JOIN syscomments ON sysobjects.ID = syscomments.ID

WHERE sysobjects.type = 'TR'

and LOWER(syscomments.text) like LOWER('%ON%'+DB_NAME()+'%.DBO.%')


For each trigger, change every table call that uses a three-part identifier to a one or two-part identifier. For example, if your identifier is DB_NAME.DBO.TABLE_NAME you can change it to DBO.TABLE_NAME or TABLE_NAME.


Thanks, 

Diogo Paulo


Thanks, Diogo for the tip.