Monday, May 10, 2010

Easy Workaround for Oracle Linked Server Problem with TIMESTAMP(6) Column

I've got a lot of SQL servers at work. And those servers link to a lot of other servers. MySQL, Oracle, SQL 2005, 2008, etc.
For the most part linked servers are frowned on from my perspective but I can't keep people from getting things done so they do get setup. Most of the time the four part naming convention works just fine although MySQL in particular seems to need OPENQUERY Oracle never has... until today.

For some reason running a query against an 11g R1 Oracle DB kept returning this:
Msg 7354, Level 16, State 1, Line 1557
The OLE DB provider "OraOLEDB.Oracle" for linked server "XYZSERVER01" supplied invalid metadata for column "CREATEDDATE". The data type is not supported.


The column type in Oracle was timestamp(6) and no matter of cast or convert would avoid the problem. Selecting without the offending column still produced the error. I was about to start looking for newer OraOLEDB drivers when I thought about the MySQL issue. I also did a little checking and found this KB:
SELECT
*
FROM OPENQUERY(XYZSERVER, 'SELECT COUNT(foobar) FROM XYZSCHEMA.XYZTABLE')


Worked great; problem solved :)

I'm also going to pose the solution to the forum post below as it seems to be related...
http://social.msdn.microsoft.com/Forums/en/transactsql/thread/fd1cb0c8-efb5-4a94-b00d-b2aeb91dd771

5 comments:

Anonymous said...

Thanks a lot, it works fine :-)

Anonymous said...

Precisely what I was looking for, thanks!

Anonymous said...

Thanks - This works great!

Sam said...

Why not create views at Oracle for those Tables and include columns that are valid to you if you even you need Dts then format it in view as char and things should go fine. ?

Anonymous said...

Thanks! Just what I needed.

Also, you can drop the "OPENQUERY()" syntax in for a table in a JOIN and that works, too.