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
davidthatcherAsked:
Who is Participating?
 
QlemoConnect With a Mentor Batchelor, Developer and EE Topic AdvisorCommented:
To clarify: You need the same bitness drivers for linked servers than you have as MSSQL Server. 32bit and 64bit cannot see each other (in this case). The target systems bitness is unrelated.
You should rather use Ora 11g on W2008R2. It still can connect to 9.2 (but not 9.0 and older).
0
 
HainKurtSr. System AnalystCommented:
you need to create a database link on SQL Server
then you can use

select * from dblink.schema.table

0
 
HainKurtConnect With a Mentor Sr. System AnalystCommented:
you need this command

sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ]   [ , [ @provider= ] 'provider_name' ]  [ , [ @datasrc= ] 'data_source' ]   [ , [ @location= ] 'location' ]   [ , [ @provstr= ] 'provider_string' ]   [ , [ @catalog= ] 'catalog' ]

http://msdn.microsoft.com/en-us/library/ms190479.aspx
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Eugene ZCommented:
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 ?

0
 
Eugene ZCommented:

<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
0
 
davidthatcherAuthor Commented:
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
0
 
Eugene ZConnect With a Mentor Commented:
check solution: you must install 64 bit client

Windows Server 2008 64bit SQL Server 2008 - Oracle 9i 32 Bit

http://social.msdn.microsoft.com/Forums/en-IE/sqldataaccess/thread/88de41a3-09f7-4105-8522-f15488bc637e
0
 
Eugene ZCommented:
what  Windows 2003 do you have installed on  64 x or Itanium 64bit server?
0
 
OP_ZaharinCommented:
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.
0
 
OP_ZaharinCommented:
- 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.
0
 
Eugene ZCommented:
OP_Zaharin:

davidthatcher has windows 2003..

"Allow InProcess" option to enable the property. --> this one good :)
0
 
OP_ZaharinCommented:
- 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
0
 
Eugene ZCommented:
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"
0
 
Eugene ZCommented:
have a good night!
0
 
OP_ZaharinCommented:
- ah! so i still don't need the glasses yet, after all :)
nite2 Eugene!
0
 
davidthatcherAuthor Commented:
Thanks everyone.  I will put this information to the test and let you know what happens.
David
0
 
davidthatcherAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.