patternsqlMajor
Creating a Linked Server that Points to Itself
Viewed 0 times
itselfcreatingpointsthatserverlinked
Problem
I am attempting to create a linked server on SQL Server 2014 instance
I am getting the error:
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?
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?
Solution
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;'Code Snippets
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;'Context
StackExchange Database Administrators Q#84642, answer score: 23
Revisions (0)
No revisions yet.