TechNucleus

Recently I am working with Oracle database and I want to see the list of tables and views. As we all know its not as friendly as SQL server. So I want to connect (Linked Server) the oracle to SQL SERVER for quick view of the data. Here are the steps I followed to setup a linked server.

1. Install Oracle Database 10g Client
• Select the Custom Installation
• Select the following under available products components
• Oracle Database utilities
• SQL * Plus
• Oracle windows Interfaces
• Oracle ODBC Driver
• Oracle Provider for OLEDB
• Oracle Data Provider for .NET
• Oracle Net
2. At the point where we have to do the configuration, enter the following information
• Service Name (may not be service name, but it is the first box that you type something in) – enter ORADB1 (for example)
• Then select TCP
• In the next box enter you Oracle DB server name where you want to connect.
• Change the port to 1501 (for example)
• If there is a textbox for Net Service name - it is ORADB1 (for example)
3. REBOOT
4. Open SQL Server management Studio and right click on “OraOLEDB.Oracle” under Server Objects –> Linked Servers –> Providers and Select Properties. Here Enable “Allow inprocess”
5. Right click on Linked Server and select “New Linked Server”. Enter the following
• Linked Server : A name of your choice
• Provider : Oracle Provider for OLE DB
• Product Name : A name of your choice
• Data Source : This must match the Host you defined in TNSNAMES.ora which you can find in C:\oracle\product\10.2.0\client_2\NETWORK\ADMIN (mine is client_2, yours might be different).
• Under Security tab, Select “Be made using this security context” and enter user name and password to connect oracle db.

 

Now I can see the Oracle data into SQL Server management studio and able to query if I want to.

SELECT * FROM [LINKED SERVER NAME]..[NAME Appears before Table name].[TABLE NAME]

SQL Server 2005 provides a way to generate a scripts for database schema such as Tables, Stored Procedures, Triggers, Functions, Views etc. That’s a good feature you need when you want to move the database schema form one environment to another environment. But what about data? You guys might faced a challenge to fetch data from production to development and you can not use backup and restore option since If you do so, what ever changes you made in development database will be vanished.