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.
“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)
