How to fix Msg 7347 - linked server returned data that does not match expected data length for column

09 November,2020 by Jack Vamvas

Question: I've found an issue we have with BI Connector over MongoDB. I'm extracting data from MongoDB to SQL Server - using the MongoDB ODBC Data Source Configuration and Linked Server. Basically the BI Connector fails when the string in the column gets too long.  When running the job to bring in the data to the production workflow data into a  Datawarehouse application we get the following error.

Msg 7347, Level 16, State 1, Line 1

OLE DB provider 'MSDASQL' for linked server 'MongoDB_PROD_mydb' returned data that does not match expected data length for column '[MSDASQL].mywork.mytasks.mytaskInfo'. The (maximum) expected data length is 5332, while the returned data length is 5970.

Completion time: 2020-11-05T17:14:10.2206504+00:00

I was able to identify the record I think that  is causing the issue. In the short term while we look at a more perm   fix for this is it possible to either edit the text highlight or put a trim around the column so we can continue to bring in all the Production data so Business Intelligence can continue their development. Or is there a more permanent fix?

Answer: One  way to get around this problem is to construct your SQL statement , such as :

declare @myStmt varchar(max)

set @myStmt = 'select * from my_collection'

EXECUTE (@myStmt) AT MongoDB_PROD_mydb

The Execute AT method will create a pass-through query  to the server .

In contrast if you execute something like the below - you will get an error:

select *  from openquery([MongoDB_PROD_mydb],'select * from my_collection ')

Msg 7347, Level 16, State 1, Line 1

OLE DB provider 'MSDASQL' for linked server 'MongoDB_PROD_mydb' returned data that does not match expected data length for column '[MSDASQL].mywork.mytasks.mytaskInfo'. The (maximum) expected data length is 5332, while the returned data length is 5970.

 

EXEC AT versus OPENQUERY

From a query perspective you need to decide on your  retreived data requirements. The  EXEC At programming method, you can only get back a data set, or execute remote code.With OpenQuery, you get a  working data set & can use  a join in working with data on the current and remote server.

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to fix Msg 7347 - linked server returned data that does not match expected data length for column


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer