2

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?

Improve this question
3
  • 1
    Cant you use dynamic SQL ?
    – Kin Shah
    Commented May 20, 2015 at 13:56
  • I can use dynamic SQL, but have no interest in modifying a large number of stored procedures in a stable code base to resolve this issue.
    – Mark Freeman
    Commented May 20, 2015 at 14:37
  • then @AaronBertrand has provided answer which you can implement with minimal code change.
    – Kin Shah
    Commented May 20, 2015 at 14:39

2 Answers 2

Reset to default
3

  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.

Improve this answer
2
  • That still involves changing all the existing stored procedures, but certainly would be far less horrible than changing them to use dynamic SQL. The Linked Server goes to an instance holding a database for a third-party system (which therefore has a very stable schema). How crazy would it be to just create an empty copy of that database on the local server that shouldn't link to the real Linked Server, and create a Linked Server of the expected name that points to that local copy? That would eliminate the need to modify any of the existing code.
    – Mark Freeman
    Commented May 20, 2015 at 14:45
  • I created a local copy of that remote database (with empty tables) and that resolved the issue. Luckily, the schema in that database is from a third-party application and is therefore stable. I'll just have to update the schema of my dummy local database should we go through a version upgrade of that application.
    – Mark Freeman
    Commented Oct 9, 2015 at 15:24
0

you can create a linked server that goes nowhere

EXEC master.dbo.sp_addlinkedserver @server = N'THISSERVERNAME', @srvproduct=N'', @provider=N'SQLNCLI'

Then any queries notice that the linked server is there, but has no access to anything within it. you'll find quite often that the queries for linked servers(as far as I'm aware) just check for the linked server (since you're declaring it as not as SQL database it doesn't seem to check in as much detail it presumes you know what you're doing (If you do it in the GUI you will get an warning but can still create the linked server without it registering that it can connect.

Not sure if this is an acceptable workaround for what you're after since you are actually creating a linked server, but it is essentially a dummy as it goes nowhere, if you ever tried to access it you'd get a login error

Improve this answer
4
  • From my original post: "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."
    – Mark Freeman
    Commented Oct 8, 2015 at 14:49
  • creating the procedure shouldn't alert you to there being a problem, running the procedure will cause a problem but the actual creation shouldn't i've just ran the following query on 2012: create procedure test as select * from invalidserver.nullschema.nonexistenttable and had no problem
    – Ste Bov
    Commented Oct 8, 2015 at 15:00
  • 1
    @SteBov Try an actual 4-part name, like invalidserver.invaliddb.nullschema.nonexistenttable. Deferred name resolution works for local references (in the same database or in a different database on the same server), but not for linked server references. What you actually created was a reference to invaliddb.nullschema.nonexistenttable.
    – Aaron Bertrand
    Commented Oct 9, 2015 at 15:12
  • Before I created a local copy of that remote database (with empty tables), I couldn't successfully create procedures that referenced it. After creating that dummy local database, they worked fine. If your suggestion worked, I would never have had the problem in the first place. As Aaron mentioned in his recent comment, the difference is between the 3 part names in your example and the 4 part names used in my stored procedures.
    – Mark Freeman
    Commented Oct 9, 2015 at 15:19

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.