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.