Lesson Learned #225: Has an unresolved reference to object using Linked Server or External Table

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!