Creating DB link b/w Oracle and SQL server

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Working with Oracle » Creating DB link b/w Oracle and SQL server

Creating DB link b/w Oracle and SQL server

Rate Topic

Display Mode

Topic Options

Author

Message

ram_reddyprasad

Posted Sunday, January 24, 2010 2:23 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, April 01, 2010 2:49 AM
Points: 10, Visits: 39

Hello Everyone,

Here i have Oracle Database 9i and SQL Server 2005 databases.
I have some tables in sql server db and i want to access from Oracle.

How to create a link between these two servers
please do needful.........

Kindly waiting for the replay...

Thanks,

Post #852699

pveilleux

Posted Monday, January 25, 2010 7:25 AM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, May 09, 2013 6:50 PM
Points: 259, Visits: 758

I did this at my last job. Since I'm no longer there, I don't have complete details, but here are some general steps. And I'm a SQL Server guy, so my Oracle is not exactly the best.

1. download and install the Oracle 9i client. I think it's free from Oracle.
2. configure client using Net Manager. We used the tnsnames.ora file, but there is another way to talk to Oracle.
3. verify your connectivity to Oracle using Net Manager
4. create a linked server in SQL Server (Server Objects - Linked Servers). Not sure what the Provider name will be, since I don't have Oracle drivers installed here.

From there, it's pretty much the same as configuring any other linked server.

Good luck....

Peter

Post #852989

rlondon

Posted Monday, January 25, 2010 8:28 AM

SSC-Addicted

Group: General Forum Members
Last Login: Friday, May 03, 2013 12:41 PM
Points: 417, Visits: 1,122

Peter Veilleux-436764 (1/25/2010)

Provider should be Microsoft OLE DB Provider for Oracle.

Post #853038

ram_reddyprasad

Posted Monday, January 25, 2010 11:22 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, April 01, 2010 2:49 AM
Points: 10, Visits: 39

Thanks for the reply,

Where i need to install oracle client software ??
in SQL server or Oracle Server

We have both servers & databases...

I need to access the tables of sql server database from oracle......

Thanks,

Post #853422

vstitte

Posted Monday, January 25, 2010 11:37 PM

Old Hand

Group: General Forum Members
Last Login: Wednesday, January 18, 2012 7:43 AM
Points: 350, Visits: 406

Try these links
Connect to SQL Server from Oracle
Connect to SQL Server from Oracle with screenshots

I am not an Oracle guy but I read this article few months back.

Post #853423

"Walkair" Steve Rezhener

Posted Wednesday, January 27, 2010 12:23 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, July 20, 2010 7:30 PM
Points: 4, Visits: 11

Ram,

You should install Oracle Client on your development machine (similar to SQL Native Client installation). One thing to remember is that Oracle Client installation creates system variables and sometimes skips an important BIN folder. Without BIN folder SSIS/SQL Server can't find essential Oracle drivers/DLLs.

Good luck,
Steve.

Post #854646

vstitte

Posted Thursday, January 28, 2010 5:19 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, January 18, 2012 7:43 AM
Points: 350, Visits: 406

I thought the question was how to connect to SQL Server From Oracle.

Post #855093

"Walkair" Steve Rezhener

Posted Thursday, January 28, 2010 11:24 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, July 20, 2010 7:30 PM
Points: 4, Visits: 11

vstitte,

You're probably right, but I figured they will use SSIS for data-exchange anyway. If that is the case then my post is relevant, otherwise my post was for PR purposes only. Tongue

- Steve.

Post #855516

« Prev Topic | Next Topic »

Permissions