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:
Or you can create the linked server manually:
- In SSMS, Expand Server Objects -> Linked Servers -> (Right click on the Linked Server Folder and select “New Linked Server”)
- The “New Linked Server” Dialog appears.
- Type in a friendly name that describes your local server (without spaces). I use LOCALSERVER.
- Provider – Select “Microsoft OLE DB Provider for SQL Server”
- Product Name – type: SQLSERVER (with no spaces)
- Datasource – type the SERVERNAMEINSTANCENAME
- ProviderString – Blank
- Catalog – Optional (If entered use the default database you will be using)
- Within the same Dialog on the left menu under “Select a Page”, select Security
- Select the radio button named “Be made using the login’s current security context”
- Click OK, and the new linked server is created