2

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

  • 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. – Solomon Rutzky Aug 26 '16 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 Aug 26 '16 at 21:30
  • You get the error when doing the CREATE? What version of SQL Server? 2008 / 2008 R2? – Solomon Rutzky Aug 26 '16 at 21:34
  • No, the error happens the first time it hits a synonym during create stored procedure. 2008 R2 – Jeff.Clark Aug 26 '16 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. – Solomon Rutzky Aug 26 '16 at 21:46
2

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
  • Ah, alas, the Microsoft documentation seems pretty explicit. Thank you for the time you put in to this srutzky. – Jeff.Clark Aug 26 '16 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. – Solomon Rutzky Aug 26 '16 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 Aug 26 '16 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. – Solomon Rutzky Aug 26 '16 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 Aug 26 '16 at 22:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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