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:

EXEC sp_addlinkedserver
@server='LOCALSERVER',
@srvproduct='SQLSERVER',
@provider='SQLNCLI',
@datasrc='SERVERNAME\INSTANCENAME'

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 SERVERNAME\INSTANCENAME
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

• openquery local server
• openquery local
• linked server local
• local linked server
• openquery on local server