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. In other words, he wanted a loopback linked server, and here is the quickest way to add one.
“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=@@SERVERNAMEThat’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.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
