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.

Related: Incremental Index Maintenance with T-SQL

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 

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

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.