How to Connect to Oracle With SQL Management Studio

Microsoft provides the SQL Server Management Studio (SSMS) as an interface to its SQL Server databases. The SSMS application allows users to manage the databases on a server. Database management options include adding and removing databases, modifying database and table structure and modifying or accessing data. In addition to managing Microsoft SQL Server databases, there is an option to configure linked servers. A linked server is a file or another database application -- from Microsoft or another vendor such as Oracle -- that can be accessed through the SSMS interface. This allows for easy access to the underlying data in queries. When you create a linked server in SSMS, the connection is actually stored in the database, so it is accessible even from other applications.

...

Step

Install the Oracle client software on the Microsoft SQL Server machine. Follow Oracle's instructions for this.

Step

Open SSMS and connect to the database you want the linked server on.

Step

Click "New Query" at the top of the screen.

Step

Open SQL Server Configuration Manager to create a new alias for the Oracle database if you do not have one defined already. If the alias is already created, move to step 8.

Step

Expand the SQL Native Client Configuration option in the SQL Server Configuration Manager.

Step

Right-Click on Aliases. Click "New Alias."

Step

Follow the onscreen instructions to create a new Alias for the Oracle database.

Step

Type the command to add a linked server, as shown:

Step

exec sp_addlinkedserver @server='[myServer]', @srvproduct='Oracle', @provider='OraOLEDB.Oracle', @datasrc='[alias]'

Step

Replace "myServer" with the name you want the linked server to have. Replace "alias" with the alias you created earlier. Do not change the product or provider names.

Step

Execute the query. Verify the linked server displays in SSMS by clicking "Server Objects" on the left, followed by "Linked Servers."