Creating a Linked Server that Points to Itself

Question

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?

Answer 1

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;'

Answer 2

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;

Answer 3

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'