Friday, January 16, 2015

Oracle Linked Server Error 7354: Data Type Not Supported

A few days ago I was trying to get some data from several tables from an Oracle server. A linked server was already created with the name SERVERA and I used four-part naming in the query.
The data of several tables was already imported successfully when I encountered the following error message for one table:
Msg 7354, Level 16, State 1, Line 1The OLE DB provider "OraOLEDB.Oracle" for linked server "SERVERA" supplied invalid metadata for column "WMBS_TIMESTAMP". The data type is not supported.
After excluding this column I still got the same error message, so I turned to Google and arrived at MS support.

Message 7 at the "Common error messages and how to troubleshoot them" section brings me a bit closer as it seems to address my issue.


It brings me to the following Microsoft Knowledge Base article: 243027. However, as you can read in the name of the KB: it handles Numeric columns in Oracle, while my column is a TIMESTAMP. That leads me to the definition of an Oracle TIMESTAMP data type:


The default is 6. So there are 6 digits of fractional seconds in the Oracle part of the data. However, SQLServer only has defaults of 3 (DATETIME) and 7 (DATETIME2, DATETIMEOFFSET). I think the query engine tries to convert it to a DATETIME2 and fails because of the default precision.


tl;dr
To solve this I used OPENQUERY in which I specified the query and explicitly CAST the value of the specific column to a DATETIME2 data type.
SELECT 
  OtherFields
  ,CAST(WMBS_TIMESTAMP AS DATETIME2)
FROM OPENQUERY
(SERVERA,
  '
    SELECT
      OtherFields
      ,WMBS_TIMESTAMP
    FROM Schema.Table 
  ')

2 comments:

  1. Hi Nicky, The page is not found. 243027 FIX: Numeric column in Oracle causes error 7354. OPen query works fine but not the other way.. Is there any solution for this ?

    ReplyDelete
    Replies
    1. I also noticed the link to the KB-article is dead. I can't find any information about it anymore, sorry.

      Delete

Thanks for taking the time to provide feedback!

Cheers,
Nicky

Featured Post

My DataGrillen Adventure: Speaking, Connecting, and New Friendships

I just got back from an incredible trip to DataGrillen, and I can’t wait to share my experiences with you. If you haven’t heard of it,  Data...