CODEWITHSUNDEEP
linked-serversql-server-2017
Gabriele D'Onufrio
Gabriele D'Onufrio

Reputation: 424

OLE DB provider 'for linked server returned data that does not match expected data length for

I get an error querying a remote postgresql server from my sql server 2017 Standard via a linked server

this is the query:

    SELECT CAST(test AS VARCHAR(MAX))  FROM  OpenQuery(xxxx, 
     'SELECT  corpo::TEXT as test From public.notification')

and this is the error message:

    Msg 7347, Level 16, State 1, Line 57
    OLE DB provider 'MSDASQL' for linked server 'xxx' returned data that                 does not match expected data length for 
    column '[MSDASQL].test'. The (maximum) expected data length is 1024,         while the returned data length is 7774.

Even without conversions the error stills

For the odbc and linked server I followed this handy guide.

Upvotes: 7

Views: 24725

Answers (3)

Tim
Tim

Reputation: 85

I'm running SQL Server 2019 on a Windows Server 2019 Standard OS. I also have the latest Simba Spark ODBC driver and created a linked server to it that works great.

However we have a table that holds memos. The System and Manual text of this table are defined as a String in Azure Databricks and can contain thousands of characters. Every attempt, based on every suggestion I've read failed when pulling long strings from Azure Databricks. Until I came up with this segmentation query.

Break up the column text using a SUBSTRING command then stitch it back together.

It looks like this:

SELECT ID, TXTPART1 + TXTPART2 + TXTPART3 AS MEMO_TXT
FROM OPENQUERY([LinkedServerName], 'SELECT ID, 
SUBSTRING(<FIELD NAME>, 1, 255) AS TXTPART1,
SUBSTRING(<FIELD NAME>, 256, 255) AS TXTPART2,
SUBSTRING(<FIELD NAME>, 511, 255) AS TXTPART3
FROM TABLE_NAME
WHERE <whatever>
')

In my case, I took it out 33 parts to capture 8,161 possible characters. It works flawlessly.

Yes, it's a pain and ugly, but functional and provides a work around to the issue many of us are facing until both Microsoft and Simba fix the issue.

I hope this helps anybody struggling with the MSDASQL issue.

Upvotes: 0

Dimitris Tsitsamis
Dimitris Tsitsamis

Reputation: 41

In my case, I was reading the data through a view. Apparently, the data size of one column was changed in the underlying table but the view still reported to the linked server the original smaller size of the column. The solution was to open the view with MSSMS and save it again.

Upvotes: 4

Đức Tr&#237; B&#249;i
Đức Tr&#237; B&#249;i

Reputation: 46

Can you try this?

SELECT  *
FROM    OPENQUERY(xxxx, '\
SELECT  TRIM(corpo) AS test
FROM    public.notification;
') AS oq
  1. I prefer using OPENQUERY since it will send the exact query to the linked server for it to execute.
  2. MySQL currently has problem with casting to VARCHAR data type, so I using TRIM() function to cheat it.

Upvotes: 2

Related Questions