Local Linked Server

When using OPENQUERY you will sometimes want to make calls to the same server you are working from. The most common reason for this is to query the output of a stored procedure into a temporary table.

For that example, follow this link: (Insert Results of Stored Procedure Into Table)

Before doing that you will have to create a linked server. You can either create the server programatically:

[cc lang=”sql”]
EXEC sp_addlinkedserver
@server=’LOCALSERVER’,
@srvproduct=’SQLSERVER’,
@provider=’SQLNCLI’,
@datasrc=’SERVERNAMEINSTANCENAME’
[/cc]

Or you can create the linked server manually:

1. In SSMS, Expand Server Objects -> Linked Servers -> (Right click on the Linked Server Folder and select “New Linked Server”)


Add New Linked Server

2. The “New Linked Server” Dialog appears.
3. Type in a friendly name that describes your local server (without spaces). I use LOCALSERVER.
4. Provider – Select “Microsoft OLE DB Provider for SQL Server”
5. Product Name – type: SQLSERVER (with no spaces)
6. Datasource – type the SERVERNAMEINSTANCENAME
7. ProviderString – Blank
8. Catalog – Optional (If entered use the default database you will be using)


Linked Server Settings

9. Within the same Dialog on the left menu under “Select a Page”, select Security
10. Select the radio button named “Be made using the login’s current security context”


Linked Server Security Settings

11. Click OK, and the new linked server is created