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