How do I access Oracle 32bit database from MS SQL 2008R2 64bit?
We need to access a view on a 32 bit Oracle 9i - Release 9.2.0.8.0 database running on Windows 2003 Server, from a MS SQL 2008 R2 64 bit system running on Windows Server 2008 64 bit.
We tried installing the Oracle 10g client (10.2.0.4) on the MS SQL server and received version errors; it did install after telling it to ignore system prerequisites. After the install we still cannot get the OraOLEDB.Oracle provider to appear in the providers list. We did see that the 10.2.0.5 patch might help, but do not have immediate access to download it.
The DBA for one of the vendors involved has concluded that it is not possible to link from 64 bit MS SQL to a 32 bit Oracle install. While, I am definitely not a database expert, this just does not sound right to me.
I would welcome input on how to make this connection and on whether or not it is even possible.
Thanks!
David
We tried installing the Oracle 10g client (10.2.0.4) on the MS SQL server and received version errors; it did install after telling it to ignore system prerequisites. After the install we still cannot get the OraOLEDB.Oracle provider to appear in the providers list. We did see that the 10.2.0.5 patch might help, but do not have immediate access to download it.
The DBA for one of the vendors involved has concluded that it is not possible to link from 64 bit MS SQL to a 32 bit Oracle install. While, I am definitely not a database expert, this just does not sound right to me.
I would welcome input on how to make this connection and on whether or not it is even possible.
Thanks!
David
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
was it 32 or 64 bit client?
did you set tnsnames.ora on sql server box?
did you restart sql server box after this oracle client installation?
did you try to create DSN ODBC connection ?
did you set tnsnames.ora on sql server box?
did you restart sql server box after this oracle client installation?
did you try to create DSN ODBC connection ?
<the DBA for one of the vendors involved has concluded that it is not possible to link from 64 bit MS SQL to a 32 bit Oracle install>
it is possible
ASKER
Thanks for the fast response HainKurt,
So...from what I gather, the whole 32bit/64bit notion is not a problem, correct?
I will review the msdn article and try the sp_addlinkedserver command.
Do you have any knowledge about the best 64 bit Oracle client version to use on our MS SQL 2008 R2 server?
David
So...from what I gather, the whole 32bit/64bit notion is not a problem, correct?
I will review the msdn article and try the sp_addlinkedserver command.
Do you have any knowledge about the best 64 bit Oracle client version to use on our MS SQL 2008 R2 server?
David
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
what Windows 2003 do you have installed on 64 x or Itanium 64bit server?
hi david,
- here is the 64bit Oracle Database 10g Client Release 2 (10.2.0.4). did you download this version?
http://www.oracle.com/technetwork/database/10204-winx64-vista-win2k8-082253.html
- when creating a new linked server, select the "Provider Options" button below the OLE DB provider name. Check the "Allow InProcess" option to enable the property.
- here is the 64bit Oracle Database 10g Client Release 2 (10.2.0.4). did you download this version?
http://www.oracle.com/technetwork/database/10204-winx64-vista-win2k8-082253.html
- when creating a new linked server, select the "Provider Options" button below the OLE DB provider name. Check the "Allow InProcess" option to enable the property.
- on Ms SQL Server 2008, the options might available when you right-click on a provider, and select "Properties". then check the "Allow InProcess" option to enable the property.
OP_Zaharin:
davidthatcher has windows 2003..
"Allow InProcess" option to enable the property. --> this one good :)
davidthatcher has windows 2003..
"Allow InProcess" option to enable the property. --> this one good :)
- ah. my eyes playing with me again hehe. thank you for pointing that EugeneZ :)
- i assume David have to be prepared in hand all the possibility of errors during the connection setting process. hope this links on "How to set up and troubleshoot a linked server to an Oracle database in SQL Server" could assist you to proceed David:
http://support.microsoft.com/kb/280106
- i assume David have to be prepared in hand all the possibility of errors during the connection setting process. hope this links on "How to set up and troubleshoot a linked server to an Oracle database in SQL Server" could assist you to proceed David:
http://support.microsoft.com/kb/280106
sorry it is time to logoff:
you are right OP_Zaharin
"MS SQL 2008 R2 64 bit system running on Windows Server 2008 64 bit"
you are right OP_Zaharin
"MS SQL 2008 R2 64 bit system running on Windows Server 2008 64 bit"
have a good night!
- ah! so i still don't need the glasses yet, after all :)
nite2 Eugene!
nite2 Eugene!
ASKER
Thanks everyone. I will put this information to the test and let you know what happens.
David
David
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
With the advice provided and a couple of registry hacks we were able to get the 10g client to work. However, based on Qlemo's comment about the 11g client working with Oracle 9.2 we gave that a try and it worked straight up without any tweaks.
Thanks everyone for the assistance!
DT
Thanks everyone for the assistance!
DT
then you can use
select * from dblink.schema.table