Sql-server – Deferred Name Resolution for Linked Servers

linked-servermigrationsql servert-sql

I need to be able to create stored procedures that point to a linked server where the database does not yet exist on the linked server.

The thought process is that the above mentioned stored procedures are a part of a larger Database Creation script. My company wants to run this database creation script on the live servers to create new databases, make sure everything is OK, add all data, and then migrate select (daily snapshots) data to a Report server (the linked server).

I have created Synonyms that will be in the database creation script that point to a linked server called EDI_Report_Server. For example:

IF NOT EXISTS (SELECT * FROM sys.synonyms WHERE object_id = 
               OBJECT_ID(N'[dbo].[EDI_SnapshotLog]') )
BEGIN
       SET @SynonymCreateSQL = N'CREATE SYNONYM [dbo].[EDI_SnapshotLog]
                               FOR [EDI_Report_Server].' + DB_NAME() +
                               '.[Snapshot].[SnapshotLog]'
       EXEC dbo.sp_executesql @SynonymCreateSQL
END

And in this database creation script I will create a stored procedure that has code similar to this:

INSERT INTO EDI_SnapshotLog (
         [SnapshotLoadStartTime]
        ,[SnapshotLoadEndTime]
        ,[SnapshotLoadResult]
        ,[SnapshotValidationErrors]
        )
...
...

Trying to run it gives the error:

The OLE DB provider "SQLNCLI10" for linked server "EDI_Report_Server" does not contain the table """."Snapshot"."SnapshotLog"".

Must I create the database and tables on the linked server first, or is there a way to defer the name resolution on linked servers?

Thank you for your time

Best Answer

This is most likely not possible. According to the MSDN page for Deferred Name Resolution and Compilation:

Note
Deferred name resolution can only be used when you reference nonexistent table objects. All other objects must exist at the time the stored procedure is created. ...

And in fact, over a Linked Server it does not seem that Deferred Name Resolution works at all. I tried (in both SQL Server 2008 R2 RTM and 2012 SP3) with both a Synonym and a direct Linked Server reference to a non-existent Table, and both failed. So it seems that Linked Servers do not support Deferred Name Resolution at all.

However, one thing you might be able to do is to:

  1. Create a single "template" database on the remote server (the DB should have the tables and any other referenced objects created as well)
  2. Create all of the synonyms to point to the template DB
  3. Create the Stored Procedures
  4. DROP and reCREATE the synonyms when the "real" database has been created
Related Question