openquery conversion failed because the data value overflowed the data type used by the provider

Question

I'm using SQL Server 2008 to fetched data from another server using linked server.

SELECT * FROM OPENQUERY (METER, 
       'SELECT 
             ME.Col1
            ,ME."ext"               AS ext         
            ,ME."t-date"                AS Date
            ,ME."per-id"                    AS Person
            ,PE."con-id"                AS Contact
            ,PE."add-id"                AS Address
            ,SC."contact-title"         AS Member
            ,SC."given"             AS Giver
            ,SC."surname"               AS lastname 

        FROM PU."member" ME
            LEFT JOIN PU."personal" PE
              ON ME."per-id" = PE."per-id"
            LEFT JOIN PU."cont" SC
              ON PE."contactid" = SC."contactid"

        WHERE ME."t-date" IS NULL 
           OR ME."t-date" >= NOW()')

I'm getting error:

 OLE DB provider "MSDASQL" for linked server "METER" returned message    "Multiple-step OLE DB operation generated errors. Check each OLE DB status   value, if available. No work was done.".
  Msg 7341, Level 16, State 2, Line 1
  Cannot get the current row value of column "[MSDASQL].T_DATE" from OLE DB   provider "MSDASQL" for linked server "METER". Conversion failed because the data   value overflowed the data type used by the provider.

If I remove where condition and executing this, I'm getting same error as well. So I think "Date" is not causing any issue here. Please Correct me if I'm wrong.

However, if I remove ,SC."contact-title" AS Member from the above query, it gives me result and works fine.

I got information that:

Length of the data in each field must be less than the length described in the schema. For contact-title defined as certain character and data need to be less than that. - I understand this.

My question is in select query I'm trying to fetch the data and I'm not inserting any where. But, still why this character length mismatch ? As per my understanding if I'm inserting it somewhere then there could be a issue. But just select should not give issue.

I'm just fetching data from 3rd party server and displaying. Is there any issue could be at my end/my database? If yes, how?

Could anyone has faced similar issue and how to solve this?

can you try ,SC."contact-title" with out the AS Member? — Dr. Stitch
What is the Linked server pointing to? another SQL server or an Oracle Server? Try converting the T-DATE to DateTime in the OPENQUERY itself if it's MSSQL and if it's Oracle the length of the milliseconds field might be too long. — Sam
@Sam , could you please tell me in details, how you would like me to convert this? Any example by looking at query would help? — AskMe
CAST(ME."t-date" AS DATETIME) AS Date But this would work only if the linked server METER is a MSSQL Server. Can you tell me what the linked server is pointing to? Is it an Access DB or a MSSQL DB or an Oracle DB? — Sam
Its proactive / Oracle. I think, issue is not with date. Its with "SC."contact-title" AS Member" . Somehow ODBC driver is not allowing the length greater than schema specified length. So I need to TRIM data value to make less than schema specified length (that is 15). Any idea to receive data that is less than 15? I mean, the data may be more than 15, however, I will only read less than 15. — AskMe

AskMe AskMe · Accepted Answer · 2016-07-16T06:29:36

Just changed SC."contact-title" to substring(SC."contact-title",1,14) and it worked. Note- both SUBSTR and SUBSTRING is working fine.

openquery conversion failed because the data value overflowed the data type used by the provider

1 Answers