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:

EXEC sp_addlinkedserver
@server='LOCALSERVER',
@srvproduct='SQLSERVER',
@provider='SQLNCLI',
@datasrc='SERVERNAMEINSTANCENAME'

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”)
  2. Add New Linked Server

    Add New Linked Server

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

    Linked Server Settings

  11. Within the same Dialog on the left menu under “Select a Page”, select Security
  12. Select the radio button named “Be made using the login’s current security context”
  13. Linked Server Security Settings

    Linked Server Security Settings

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

2 comments
kiran 28 Nov 2011 at 12:39 pm

i just started my sql dba job am new one in this field.
1-I hv configuere Linked server,bt i dont know how it is used?
I hv added Link server throgh SSMS.As shown above.
How i can utilised it.

2-I hv db in datacentre room,so that i hv give 2 cilent conncetion to my near room.aleardy there r 2 m/c’s with OS Win7.
My db server installed on SQl server 2008 stnd R2 edition.
wat is requirement for cilent M/c’s.pls suggest me.

Sree 17 Aug 2010 at 11:54 pm

Thank you it helped me.

Featured Articles

 Site Author