We used to have cases when our customers are trying to export their database using SQLPackage or Export service in the Portal having, for example, the following error message: Error SQL71562: Error validating element Synonym: [dbo].[MyView] has an unresolved reference to object [linkedserver].[databasename].[schemaname].[tablename]. External references are not supported when creating a package from this platform. In this post I would like to suggest an alternative to export this data.
The first thing that we need to know that SQLPackage validates that we don't have any of the following points in our database that we want to export:
- Reference using 4 parts, for example, databasename.schemaname.tablename
- Using an external table.
- Calling a table using a linked server, for example, select * from mylinkedserver.databasename.schema.table
- Calling a synonyms that uses a linked server, for example, CREATE SYNONYM [dbo].[ExternalTable1] FOR [linkedserver].[databasename].[schemaname].[tablename]
If we have any of these scenarios we are going to have the following error message exporting the data:
- One or more unsupported elements were found in the schema used as part of a data package.
Error SQL71562: Error validating element Synonym: [dbo].[MyView] has an unresolved reference to object [linkedserver].[databasename].[schemaname].[tablename]. External references are not supported when creating a package from this platform. -
One or more unsupported elements were found in the schema used as part of a data package.
Error SQL71562: Error validating element [dbo].[ExternalTable1]: Synonym: [dbo].[ExternalTable1] has an unresolved reference to object [linkedservername].[databasename].[schemaname].[tablename]. External references are not supported when creating a package from this platform.
In order to be able to export the data, I developed the following stored procedure that basically, runs the following code. The goal is to create dynamically the external table and/or view or synonym that is calling external tables,
- Parameters:
- @TableName = will be the name of the synonym or external table to create.
- @Action = will be the operation to perform, CREATE or DROP the synonym
CREATE PROCEDURE dbo.DeployMyExternalTable(@TableName varchar(400),@Action VARCHAR(10))
AS
IF @TableName = 'ExternalTable1'
BEGIN
IF @Action = 'CREATE'
BEGIN
IF NOT EXISTS( SELECT TOP 1 NAME FROM SYS.synonyms WHERE Name='ExternalTable1' )
EXEC SP_EXECUTESQL N'CREATE SYNONYM [dbo].[ExternalTable1] FOR [LinkedServerName].[DatabaseName].[SchemaName].[ExternalData1]'
END
IF @Action = 'DROP'
BEGIN
IF EXISTS( SELECT TOP 1 NAME FROM SYS.synonyms WHERE Name='ExternalTable1' )
EXEC SP_EXECUTESQL N'DROP SYNONYM [dbo].[ExternalTable1]'
END
END
IF @TableName = 'ExternalTable2'
BEGIN
IF @Action = 'CREATE'
BEGIN
IF NOT EXISTS( SELECT TOP 1 NAME FROM SYS.synonyms WHERE Name='ExternalTable2' )
EXEC SP_EXECUTESQL N'CREATE SYNONYM [dbo].[ExternalTable2] FOR [LinkedServerName].[DatabaseName].[SchemaName].[ExternalData2]'
END
IF @Action = 'DROP'
BEGIN
IF EXISTS( SELECT TOP 1 NAME FROM SYS.synonyms WHERE Name='ExternalTable2' )
EXEC SP_EXECUTESQL N'DROP SYNONYM [dbo].[ExternalTable2]'
END
END
The idea is if you need to run any query against this synonym or external table is to run the following code:
exec dbo.DeployMyExternalTable 'ExternalTable1', 'CREATE'
SELECT * FROM [dbo].[ExternalTable1]
Or if you need to delete it
exec dbo.DeployMyExternalTable 'ExternalTable1', 'DROP'
Basically, if you need to export the data, you could call these stored procedure for all synonyms or external tables, export the data and after it your application/database will create again them without modifying your code.
Enjoy!
Microsoft
