Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

How to create a local SQL Server linked server

From Wiki

Jump to: navigation, search

It is very easy to create a linked server, all you have to do is execute the sp_addlinkedserver stored proc, pass the servername and 'SQL Server' for the server product and your are done

Here is an example

  1. EXEC master.dbo.sp_addlinkedserver @server = N'MyServer',
  2.                                    @srvproduct=N'SQL Server'

But what if you want to create a linked server to the server you are on with a different name? Here is how you do it, in this case you use blank for @srvproduct, the @datasrc parameter will hold the name/ip address of the server and for provider you will use SQLNCLI, the @server parameter will hold the name that you would like the linked server to be.

Here is an example

  1. EXEC master.dbo.sp_addlinkedserver @server = N'TestLinkedServer',
  2.                                    @srvproduct=N'',
  3.                                    @datasrc='(local)',
  4.                                    @provider='SQLNCLI'

To verify that the linked server works properly you can run this code

  1. select * from TestLinkedServer.master.dbo.sysobjects

Here is how to drop the linked server

  1. EXEC master.dbo.sp_dropserver @server=N'TestLinkedServer', @droplogins='droplogins'
  2. GO

Contributed by: --SQLDenis 19:28, 4 May 2010 (GMT)

Part of SQL Server Admin Hacks

768 Rating: 2.2/5 (26 votes cast)