SQL SERVER – Quickest Way to Add LoopBack Linked Server for OpenQuery

Here is the question which I received the other day from a user who was trying to add a linked server pointing its own server as he wanted to use OpenQuery. Let us read his question in details.

SQL SERVER - Quickest Way to Add LoopBack Linked Server for OpenQuery linkedserver

“Hey Pinal,

I just read your earlier blog post here – How to Insert Results of Stored Procedure into a Temporary Table? , I noticed that you have used OPENROWSET. Similarly, I want to use OPENQUERY for my stored procedure which is stored locally and I want to specify the name of the linked server.

Do you know any method by using which I can quickly create a linked server for my machine. Please note that I want to create a loopback linked server so it connects back to the same server, where it is created.

Fantastic question, indeed. Here is the quick and simple way to create a loopback linked server for your own server.

EXEC master.dbo.sp_addlinkedserver @server = N'loopback', @srvproduct=N'',
 @provider=N'SQLNCLI', @datasrc=@@SERVERNAME

That’s it. You are done!

By default the data access option will be enabled for your server. If due to any reason it is disabled, you can run following command and make sure that data access option for your server is enabled.

EXEC master.dbo.sp_serveroption @server=N'loopback', 
@optname=N'data access', @optvalue=N'true'

I hope this simple blog post to help everyone who is trying to build a loopbac server.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Linked Server, SQL Scripts, SQL Server
Previous Post
SQL SERVER – How to Measure Page Splits Counter Value via T-SQL?
Next Post
SQL SERVER – Not Able to Kill SPID with KILL WITH STATUSONLY

Related Posts

Leave a Reply