Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Creating DB link b/w Oracle and SQL server Expand / Collapse
Author
Message
Posted Sunday, January 24, 2010 2:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 1, 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
Posted Monday, January 25, 2010 7:25 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 8:34 AM
Points: 280, Visits: 818
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
Posted Monday, January 25, 2010 8:28 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, August 4, 2014 12:38 PM
Points: 417, Visits: 1,147
Peter Veilleux-436764 (1/25/2010)
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


Provider should be Microsoft OLE DB Provider for Oracle.
Post #853038
Posted Monday, January 25, 2010 11:22 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 1, 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
Posted Monday, January 25, 2010 11:37 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, January 18, 2012 7:43 AM
Points: 351, 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
Posted Wednesday, January 27, 2010 12:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, January 28, 2010 5:19 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, January 18, 2012 7:43 AM
Points: 351, Visits: 406
I thought the question was how to connect to SQL Server From Oracle.


Post #855093
Posted Thursday, January 28, 2010 11:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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.

- Steve.
Post #855516
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse