SQL Server – T-SQL Commands for Linked Servers

Send Us a Sign! (Contact Us!)

--> (Word)	--> (PDF)	--> (Epub)	--> (Text)
--> (XML)	--> (OpenOffice)		--> (XPS)

Show More Info

This article has been read [post_view] times. This article has been read [post_view time="day"] times today. This article has been read [post_view time="month"] times this month. This article has been read [post_view time="year"] times this year. This article has 274 total words. (Including header) This article has been updated on [rup_display_update_date] [tweet]

In addition to creating linked servers using SQL Server Management Studio (SSMS) you can create linked servers using T-SQL commands. T-SQL offers several advantages over SSMS. T-SQL [gs script]s are self-documenting, letting you easily see how the linked servers are created.

They can also be easily copied and rerun if you want to create a new linked server or recreate an old linked server.

To create a new linked server named OR-PORT-VORA11G, you can use the command:

EXEC master.dbo.sp_addlinkedserver @server = N'OR-PORT-VORA11G', 
  @srvproduct=N'OraOLEDB', @provider=N'OraOLEDB.Oracle', @datasrc=N'ORCL'

You can then map all of the logins to the Oracle scott login using the following command:

EXEC master.dbo.sp_addlinkedsrvlogin 
  @rmtsrvname=N'OR-PORT-VORA11G',@useself=N'False',
  @locallogin=NULL,@rmtuser=N'scott',@rmtpassword='tiger'

If you’re interested in listing all of your linked servers and their OLE DB providers, run the command:

EXEC sp_linkedservers 

You can also list the servers that can be accessed as OLE DB data sources by running the command:

SELECT * FROM sysservers

SOURCE	LINK (Sqlmag.com)	LANGUAGE	ENGLISH