How to Link to an Oracle Table From an SQL Server

Link your Oracle server from SQL server to allow internal database connections.

Linked servers provide your SQL Server database users with the ability to query other databases on the network while connected to only the SQL Server. Linking servers is beneficial for large networks that have several types of databases. You can link an Oracle server to a SQL Server using the "sp_linkedservers" stored procedure, which is an internal system function provided by the database engine.

Instructions

1. Click the Windows "Start" button and select "All Programs." Click "SQL Server" and then click "SQL Server Management Studio." This opens your main console.

2. Right-click your SQL Server database name on the left and select "New Query." This opens an editor where you enter your stored procedure command.

3. Enter the following text into the editor:

   EXEC sp_addlinkedserver 'OracleLinkedServer', 'MSDAORA', 'OracleServer'

   The "OracleLinkedServer" is the name of the link, which is shown in your SQL Server console. The "OracleServer" parameter is the name of your Oracle server database. The "MSDAORA" is the required Oracle database driver.

4. Press the "F5" key to execute the linked server command. Notice on the left side of the window that the linked server name is listed.

References

• SQL Magazine: Setting Up an Oracle Linked Server