Oracle Linked Server query error - unable to read certain data

  • denappel

    Old Hand

    Points: 301

    We have a problem with a query from a SQL Server 2008R2 - 64bit to an Oracle Linked Server.

    We use the Oracle 64-bit driver for Windows.

    When we query a certain table we get the following error:

    Msg 7347, Level 16, State 1, Line 1 OLE DB provider 'OraOLEDB.Oracle' for linked server 'SERV_VISION2' returned data that does not match expected data length for column '[SERV_VISION2]..[VISION].[INV_M001].CUS_NM'. The (maximum) expected data length is 30, while the returned data length is 24.

    The problem seems to be the data in the CUS_NM column. In the rows where he gives this error the data is like this:

    ¿¿¿

    So like a question mark but upside down. There doesn't seem to be a problem with the length of the column, for one reason he can't handle this data I guess.

    Could it be a collation problem?

    Did anyone experience this problem? Anyone a clue how to solve this?

    Thanks in advance for your answers!

    Best regards,

    Koen

  • Lowell

    SSC Guru

    Points: 323331

    i see there is a connect item for two other ODBC drivers with the same issue here....

    http://connect.microsoft.com/SQLServer/feedback/details/126972/mssql2005-returned-data-that-does-not-match-expected-data-length-for-column

    one poster say if he checks this in the settings he gets the error...which implies if it's not set, it will work?

    In ODBC options I checked "Report Char columns as Wide Char columns" and I get the following error:

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • denappel

    Old Hand

    Points: 301

    Hi Lowell,

    Thanks for your answer.

    I couldn't resolve the issue with setting

    the force SQL_WCHAR support (in odbc datasources)

    for the oracle connection to true or to false.

    Neither setting made any difference...

    Regards,

    Koen

  • davoscollective

    SSCertifiable

    Points: 6325

    Hi,

    I had this problem recently using OpenQuery to a linked Oracle 10g server from SQL Server 2005.

    I had this error

    Msg 7347, Level 16, State 1

    OLE DB provider 'OraOLEDB.Oracle' for linked server '####' returned data that does not match expected data length for column '[OraOLEDB.Oracle].'####''. The (maximum) expected data length is 64, while the returned data length is 16.

    I used this conversion function inside the openquery statment (on the Oracle side of things):

    To_NChar()

    Put that around the columns reporting length limitations.

    -- Note that To_Char does not work which is what I tried first.

    Distributed queries attempt to match data types.

    (BOL)

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

    Too many types, not generally helpful.

    I found this page of mappings more useful

    http://www.carlprothman.net/Default.aspx?tabid=97

    Most of the time when I am using OpenQuery it is against an OLAP cube and the data retuned is Ntext (I can't find anything in BOL about that so I can't verify that conjecture). When I need to insert those into a SQL Server Varchar field I have to explicitly cast it as Varchar on the SQL Server side of things.

    That didn't work in this situation with Oracle.

    I took a stab in the dark that if I could get it into the mapped version of Ntext then I could do what I want at the SQL Server end. In that last link apparently NCLOB is the equivalent of Ntext so this Oracle function To_NCLOB() works, as does To_NChar() (returns the Oracle datatype Nvarchar2). I think NChar is preferable to the NCLOB conversion for some superstituous reason (smaller datatypes are cooler?).

    Whacky error anyhow. I put in some hardcode strings in place of the columns and tried various lengths and found it is actually expecting exactly 64 bytes (32 nchars) despite the error saying its a maximum length. Oracle confuses me... I love SQL Server!

  • Devendra (Dev) Shirbad

    SSC-Forever

    Points: 42488

    Please verify DBCOLUMNFLAGS properties for OLEDB provider.

    It should be 'DBCOLUMNFLAGS_ISFIXEDLENGTH = false' for VARCHAR.

  • denappel

    Old Hand

    Points: 301

    Hi,

    Thanks a lot for your help, this solution works for me! 🙂

    Damn we were already looking for more exotic solutions,

    I'm quite happy that we can solve it this way.

    Indeed if I run the query distributed with openquery,

    and I put To_NChar() around the column that gave the error

    it works smoothly. (below the working query)

    SELECT *

    FROM OPENQUERY(SERV_VISION, 'SELECT LANG_DIV

    ,WARE_DIV

    ,CAUSE_CD

    ,To_NChar(CAUSE_NM) as CAUSE_NM

    ,UNKNOWN_FLG

    ,DR_DIV

    ,CRT_DT

    ,CRT_ID

    ,UPD_DT

    ,UPD_ID

    ,CLASS_CD1

    ,CLASS_CD2 from VISION.INV_M008')

    Thanks a lot!

    Best regards,

    Koen

  • j9fortier

    SSC Enthusiast

    Points: 128

    Hi,

    I having same issue with trying to select data from a "Universe" database via a SQL Server Link.

    The SQL Server is 64 Bit. The Universe driver is 64 bit. I am able to create the linked server and return some, but not all records.

    I receive the error message

    Msg 7347, Level 16, State 1, Line 2

    OLE DB provider 'MSDASQL' for linked server 'ACCTSERV' returned data that does not match expected data length for column '[ACCTSERV]...[JC_PROJ_INFO].JC_PROJ_KEY'. The (maximum) expected data length is 8, while the returned data length is 9.

    I have tried using CAST , LTRIM, RTRIM, SUBSTRING. I am at my wit's end trying to find a solution on the web.

    Any other suggestions would be greatly appreciated.

    Jeannine

  • davoscollective

    SSCertifiable

    Points: 6325

    You should probably have started that in a new thread, your question will get lost if you tag it on to the end of an old thread like this.

    Anyway, assuming you are using the 'rocket u2' universe product

    With this tool:

    U2 Metadata Manager (U2 MDM)

    You can manage the metadata in your universe files.

    I am guessing that SQL Server is reading the metadata of your universe data structures, which doesn't match the contents of the universe data structures. Get the metadata up to date and it should be fine.

  • j9fortier

    SSC Enthusiast

    Points: 128

    Thanks for your reply. I will check out the metadata.

    Sincerely! Jeannine

  • raviteja.jampani36

    SSC Rookie

    Points: 31

    Dev - Monday, November 28, 2011 11:52 PM

    Please verify DBCOLUMNFLAGS properties for OLEDB provider.It should be 'DBCOLUMNFLAGS_ISFIXEDLENGTH = false' for VARCHAR.

    Please let me know where to check and change this property for OLEDB provider

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply