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