Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How do I access Oracle 32bit database from MS SQL 2008R2 64bit?

Posted on 2011-04-21
18
Medium Priority
?
946 Views
Last Modified: 2012-06-22
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
0
Comment
Question by:davidthatcher
  • 8
  • 4
  • 3
  • +2
18 Comments
 
LVL 61

Expert Comment

by:HainKurt
ID: 35445679
you need to create a database link on SQL Server
then you can use

select * from dblink.schema.table

0
 
LVL 61

Assisted Solution

by:HainKurt
HainKurt earned 400 total points
ID: 35445683
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
 
LVL 43

Expert Comment

by:Eugene Z
ID: 35445698
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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
LVL 43

Expert Comment

by:Eugene Z
ID: 35445702

<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
 

Author Comment

by:davidthatcher
ID: 35445706
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
 
LVL 43

Assisted Solution

by:Eugene Z
Eugene Z earned 400 total points
ID: 35445715
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
 
LVL 43

Expert Comment

by:Eugene Z
ID: 35445728
what  Windows 2003 do you have installed on  64 x or Itanium 64bit server?
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 35445739
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35445741
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
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35445747
- 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
 
LVL 43

Expert Comment

by:Eugene Z
ID: 35445750
OP_Zaharin:

davidthatcher has windows 2003..

"Allow InProcess" option to enable the property. --> this one good :)
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35445777
- 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
 
LVL 43

Expert Comment

by:Eugene Z
ID: 35445781
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
 
LVL 43

Expert Comment

by:Eugene Z
ID: 35445786
have a good night!
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35445790
- ah! so i still don't need the glasses yet, after all :)
nite2 Eugene!
0
 

Author Comment

by:davidthatcher
ID: 35449585
Thanks everyone.  I will put this information to the test and let you know what happens.
David
0
 
LVL 71

Accepted Solution

by:
Qlemo earned 1200 total points
ID: 35479521
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
 

Author Closing Comment

by:davidthatcher
ID: 35504902
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to recover a database from a user managed backup

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question