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 1

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

Video of the Day

Step 2

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

Step 3

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

Step 4

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 5

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

Step 6

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

Step 7

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

Step 8

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

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

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 9

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

Video of the Day