Database Connectivity using Linked Server. (Numeric Values)

So I established a connection from my SQL Server 2008 Express Edition to our Oracle Database. When I query varchar based values, it is fine, but for any numeric value, it throws me an error like thi开发者_JAVA百科s one.

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

How do I get around this?

I've run into this myself. Your error is caused by Number fields not being properly handled by the linkage. The easiest fix is to cast to a varchar inside your OPENQUERY, and to cast back to a number on the other side

SELECT CONVERT(INT, YourField ) AS YourField   
FROM OPENQUERY (LINKEDSERVER, 
'SELECT TO_CHAR(YourField ) AS YourField FROM RemoteTable');

Maybe this helps...

http://msdn.microsoft.com/en-us/library/ms151817.aspx

One individual said that they found the answer in Oracle's Metalink DocID 369814.1. They said the solution is to install an Oracle OLEDB driver with minimum version 10.2.0.2.20. They installed an Oracle client 10.2.0.3 and then applied the applicable patch (described in the Metalink DocID 369814.1).

I found this post at http://forums.oracle.com/forums/thread.jspa?threadID=337842&start=15&tstart=135.

This seems like a better solution than converting to a char datatype, then converting back to a numeric datatype. I assume that those conversions will cost you in performance.

We had the same problem in one of our environments and the problem was that we had set up the linked server incorrectly.

We changed it to Microsoft OLD DB Provider for Oracle and the problems was resolved. Hope this helps.