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.
Things You'll Need
- Administrative access to the Microsoft SQL Server machine
- Oracle client software
Install the Oracle client software on the Microsoft SQL Server machine. Follow Oracle's instructions for this.
Open SSMS and connect to the database you want the linked server on.
Click "New Query" at the top of the screen.
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.
Expand the SQL Native Client Configuration option in the SQL Server Configuration Manager.
Right-Click on Aliases. Click "New Alias."
Follow the onscreen instructions to create a new Alias for the Oracle database.
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.
Execute the query. Verify the linked server displays in SSMS by clicking "Server Objects" on the left, followed by "Linked Servers."
Tips & Warnings
- Two OLE DB providers have been tested for use with Microsoft SQL. They are the Microsoft OLE DB Provider for Oracle and the Oracle Provider for OLE DB (220.127.116.11.0) for Windows 2000/NT.
References & Resources
- Microsoft: OLE DB Providers Tested with SQL Server
- Microsoft: Linking Servers
- Microsoft: Oracle Provider for OLE DB
- Microsoft: How To: Create a Server Alias for Use by a Client (SQL Server Configuration Manager)
- Oracle: Oracle Provider for OLE DB Developer's Guide
- Oracle: Oracle Database Client Installation Guide