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 over SSMS. 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

Learn more: 4 Techniques to Improve the Readability of Your Dynamic T-SQL Code

Comments

Plain text