HEELPBOOK - SQL Server - T-SQL Commands for Linked Servers ###################

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 overSSMS. T-SQL scripts 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



############ ARTICLE INFO #############

Article Month: May
Article Date: 09/05/2012
Permalink: http://heelpbook.altervista.org/2012/sql-server-t-sql-commands-for-linked-servers/
Source: http://www.sqlmag.com/article/tsql3/t-sql-commands-for-linked-servers
Language: English

View more articles on: http://www.heelpbook.net/
Follow us on Facebook: http://it-it.facebook.com/pages/HeelpBook/100790870008832
Follow us on Twitter: https://twitter.com/#!/HeelpBook
Follow us on RSS Feed: http://feeds.feedburner.com/Heelpbook
Follow us on Delicious: http://delicious.com/heelpbook