Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Oracle 64bit Connectivity Expand / Collapse
Posted Wednesday, May 27, 2009 12:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, September 27, 2015 12:10 PM
Points: 1, Visits: 199
I'm having difficulty using a linked server connection from SQL Server 2008 64bit to Oracle. I can set up and test the linked server just fine but when I try to execute a query like so:

select * from openquery (ORACLE_SERVER,'select * from myschema.mytable')

I get the following error message:

Msg 9803, Level 16, State 1, Line 1
Invalid data for type "numeric".

We are able to set up the context and execute this query just fine in a sql server 2005 environment. If I explicitly convert number data types to text like this:

select * from openquery (ORACLE_SERVER,'select to_char(mytableid) as mytableid from myschema.mytable ')

Then I get a result set. For obvious reasons, this will not work in our situation. Unfortunately, I can't find any information related to resolving this general issue on the internet. My Oracle DBA has assured me that the drivers are installed correctly. Any help would be greatly appreciated.

Post #724456
Posted Tuesday, June 2, 2009 3:00 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, June 17, 2009 1:38 PM
Points: 63, Visits: 33
There is an article in Oracle's knowledgebase called "metalink". Have your oracle dba log into metalink and look up docID 191368.1 entitled "How to create a sql Server linked server with the oracle provider for OLE DB". This article is very specific about how the OLE provider options are to be set. For example, you are to go to the options of the Oracle provider within SQL server and check the boxes "Dynamic Parameters" and "Allow InProcess".

Another thing to remember with Oracle client on the 64-bit box -- is that you will need both the 32-bit and 64-bit clients installed because a 32-bit app will need the 32-bit client and a 64-bit app will need the 64-bit client. And finally, the default install of a 10g client does not include the OLE provider. You have to go back, do a custom install and make sure to check the boxes for the OLE client services.

Hope this helps you solve your problem.
Post #727744
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse