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
You can also list the servers that can be accessed as OLE DB data sources by running the command
SELECT * FROM sysservers