14

I am attempting to create a linked server on SQL Server 2014 instance servername\instancename using the following call:

EXEC master.dbo.sp_addlinkedserver 
    @server = N'servername\instancename',
    @srvproduct=N'SQL Server'

I am getting the error:

Msg 15028, Level 16, State 1, Procedure sp_addlinkedserver, Line 82
The server 'servername\instancename' already exists.

This works fine in SQL Server 2005, and according to MSDN,

The linked server does not have to be another instance of SQL Server,

So I'm not sure what's changed in the recent version(s) that disallows this. Using the UI generates a similar message:

You cannot create a local SQL Server as a linked server.

I understand that it's an odd thing to request, but it's to support some legacy code that worked in 2005 (and used to be on separate instances). The documentation states that it should work, but it doesn't. Is there a way to get this to work in 2014, or am I going to have to modify the underlying code?

  • 1
    It actually shouldn't even make a difference. You can use a 4-part identifier on a local server just fine. – Kris Gruttemeyer Dec 8 '14 at 21:00
  • Why are you trying to create a linked server to the local server? What part doesn't work? Maybe that's what your question should be about... – Aaron Bertrand Dec 8 '14 at 21:01
  • 1
    The legacy code was written for different instances connecting through a linked server. At some point in history, the two instances were merged into a single instance, but the code and linked server remained unchanged. My goal is to support the code as-is because a) I don't know what's involved in getting it changed over, and b) the main developer wants to be able to support the databases being distributed to separate instances in the future. – mathewb Dec 8 '14 at 21:10
  • 3
    You might want to consider synonyms. Then if you have objects moved to different servers you just drop and re-create the synonyms and not have to touch the code. – Aaron Bertrand Dec 8 '14 at 21:38
  • Thanks Aaron. I think that's exactly what I was looking for. The one database uses synonyms, so I just need to drop/create them removing the server name from the four part name. Then I can eliminate the linked server altogether. If the database gets moved later on, I can add the linked server name back into the synonyms. Got it. – mathewb Dec 8 '14 at 21:45
19

Turns out that I was able to get it working with different parameters.

EXEC master.dbo.sp_addlinkedserver
    @server = N'LinkedServerName', 
    @srvproduct=N'', 
    @provider=N'SQLNCLI', 
    @provstr=N'DRIVER={SQL Server};Server=(local)\InstanceName; Initial Catalog=DBNAME;uid=user;pwd=password;'
11

Instead of dealing with linked server references inside your code, you might want to consider a one-time code investment involving the use of a synonym in any location where currently you have a linked server.

So instead of:

SELECT whatever FROM someserver.somedb.dbo.mytable;

You have a synonym:

CREATE SYNONYM dbo.mytablepointer FOR someserver.somedb.dbo.mytable;

Then your code is simply:

SELECT whatever FROM dbo.mytablepointer;

Then if you have objects moved to different servers you just drop and re-create the synonyms and not have to touch the code:

DROP SYNONYM dbo.mytablepointer;
CREATE SYNONYM dbo.mytablepointer FOR otherserver.somedb.dbo.mytable;
2

Run This command - you will be able to use local server as Linked server no code change needed

EXEC sp_addlinkedserver @server = 'LinkedServerName',
                        @provider ='SQLNCLI',
                        @datasrc ='LocalServerName',
                        @srvproduct = 'SQL'

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.