Sql-server – How to create a dumthe Linked Server in SQL Server 2012

linked-serversql serverstored-procedures

I have a database in two different production servers. I want to keep the schemas consistent between the two. One has jobs that call stored procedures that reference a Linked Server. The other does not (and should not) contain those jobs or a Linked Server of the same name as that in the first server, but I want both servers to have those same stored procedures. However, SQL Server refuses to create the procedures that reference the Linked Server on the server that has no Linked Server set up. I understand why that happens, but I need a workaround that does not involve changing the procedure code (such as to use Dynamic SQL to hide the reference from the compiler).

I tried creating a Linked Server of the expected name that points to the local server, but SQL Server is smart enough to see that the referenced tables do not exist on that Linked Server. If I create one to a non-existent server, SQL times out during the creation of the procedure saying that it couldn't connect to the remote server.

Is there any way to create a dummy Linked Server such that SQL Server will not try to validate the table names on the Linked Server?

Best Answer

  1. Create dummy, empty tables on the local server so that the procedure creation can happen.

  2. Change the procedure code to use two-part synonyms. On the server that needs to use the linked server:

    CREATE SYNONYM dbo.whatever FOR linkedserver.dbo.whatever;

    On the local server that doesn't need the remote references to exist:

    CREATE SYNONYM dbo.whatever FOR dbo.emptydummytable;

    The stored procedure using the latter can be created thanks to deferred name resolution (which doesn't work when creating a procedure that references a linked server). This will require changing the stored procedure code, but it will be a one-time change.

Related Question