<< Google to offer Voicemail and e-vite integration? | Home | New! Web 2.1 is finally here! >>

HOWTO: define a loopback linked server in MS SQL Server 2005

I was having some trouble defining a "loopback linked server" in MS SQL Server 2005. By "loopback" I mean a Linked Server on a specific SQL2005 instance that actually links to the very same instance (albeit to a different DB)

No matter which tricks I tried using in the "New Linked Server" dialog box in the new Microsoft SQL Server Management Studio, I just couldn't get my SQL Server 2005 instance to link to itself.

Finally, I have the solution - Instead of using the Management Studio's UI, I ran the matching stored procedure: sp_addlinkedserver. This immideately defined a working loopback linked server just like I wanted.

Here's the syntax I used:

EXEC sp_addlinkedserver @server = N'name_for_linked_server',
    @srvproduct = N' ',
    @provider = N'SQLNCLI', 
    @datasrc = N'name_of_my_sqlserver_instance', 
    @catalog = N'name_of_database'

Hope this helps.

Uri

Print | posted on Thursday, March 23, 2006 11:50 AM

Comments on this post

# re: HOWTO: define a loopback linked server in MS SQL Server 2005

Requesting Gravatar...
I'm just curious... What would be a practical application for using a "loopback linked server" for SQL Server? Was this just a "lemme see if i can do this?" or is there a particular reason that this becomes practical to use.

Left by Curious Jeff on Mar 23, 2006 1:48 PM

# re: HOWTO: define a loopback linked server in MS SQL Server 2005

Requesting Gravatar...
Hi Jeff,

There was a "real world" reason I needed to define a loopback linked server.

In the production environment where I work we have two separate instances of SQL Server where one is remotely linked to the other using a
"Linked Server".

In my developement environment I would like to simulate my production as accurately as possible. Regrettably, I have only one SQL Server instance at my disposal there.

So I have two databases on that one instance - One simulates the first production instance and the second simulates the other one. The linked server connecting them is naturally a loopback - its default DB is the "remote" DB which is actually local.

If I didn't have the loopback Linked Server, I would have been forced to make my stored procedure code in my environment different from the code in my production environment - A nightmare maintenance-wise.


Best Regards,
Uri
Left by Uri Goldstein on Mar 23, 2006 2:08 PM

# re: HOWTO: define a loopback linked server in MS SQL Server 2005

Requesting Gravatar...
Uri, did your loopback Linked Server in SQL Server 2005 ever cause any problems? I have to create the same thing as part of an upgrade, and just wondering did you discover any issues with it? If you would be so kind as to reply by email, I'd appreciate it. thanks and regards Rob Nossaman rob.nossaman@fnis.com
Left by Rob Nossaman on Oct 29, 2008 8:33 PM

# re: HOWTO: define a loopback linked server in MS SQL Server 2005

Requesting Gravatar...
I have the same exact situation, however loopback servers don't handle transactions, which is the delimna I'm faced with.
Left by nappisite on Sep 17, 2009 12:38 PM

Your comment:

 (will show your gravatar)