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]') )
       SET @SynonymCreateSQL = N'CREATE SYNONYM [dbo].[EDI_SnapshotLog]
                               FOR [EDI_Report_Server].' + DB_NAME() +
       EXEC dbo.sp_executesql @SynonymCreateSQL

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


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

  • Have you tried this and are getting an error, or just wondering? I just created a synonym to a non-existent database and it didn't know about it until I issued a SELECT statement against the Synonym. Commented Aug 26, 2016 at 21:29
  • I have tried it. I get the error, "The OLE DB provider "SQLNCLI10" for linked server "EDI_Report_Server" does not contain the table ""<DbName>"."Snapshot"."SnapshotLog"".
    – Jeff.Clark
    Commented Aug 26, 2016 at 21:30
  • You get the error when doing the CREATE? What version of SQL Server? 2008 / 2008 R2? Commented Aug 26, 2016 at 21:34
  • No, the error happens the first time it hits a synonym during create stored procedure. 2008 R2
    – Jeff.Clark
    Commented Aug 26, 2016 at 21:36
  • I see now. I get the error as well. I highly doubt this is possible, but I am looking up something first. Commented Aug 26, 2016 at 21:46

1 Answer 1


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

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
  • Ah, alas, the Microsoft documentation seems pretty explicit. Thank you for the time you put in to this srutzky.
    – Jeff.Clark
    Commented Aug 26, 2016 at 22:06
  • @Jeff.Clark It doesn't mention, however, how a Linked Server would affect things, and if a Synonym pointing to an existing DB but non-existing Table would work. I just updated with the results of that test. Commented Aug 26, 2016 at 22:10
  • Yeah, I think if we go through the trouble to modify our processes a little bit to accommodate making the database, we will probably create the whole schema, so that works out just fine :) Thanks for the complete info!
    – Jeff.Clark
    Commented Aug 26, 2016 at 22:14
  • @Jeff.Clark Well, I was suggesting to create a template that always remained a template. That way it was a standard name that would allow your process to remain mostly the same. You would just need an additional step to DROP / CREATE the Synonyms after the "real" database has been created. Commented Aug 26, 2016 at 22:16
  • Ah yes, we are already in the process of modifying our code deployment processes to account for the EDI/Report server. All we will need to do is slightly modify the original create database script and add it to the deployment process and we'll be set. DEV team is already working on it :)
    – Jeff.Clark
    Commented Aug 26, 2016 at 22:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.