May 27, 2009 at 12:41 pm
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.
Thanks,
Ted
June 2, 2009 at 3:00 pm
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.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply