SQL Server Performance Forum – Threads Archive
Using Open Query
Hi all, I am trying to connect to our mainframe and import some data to sql2000.The statement works but assoon as ther is +- 3000 or more records that needs
to be copied ,query anylyzer comes back with this message. Here is the query. SELECT * into temptable FROM OPENQUERY ( test,’SELECT * FROM tablename’) Server: Msg 7347, Level 16, State 1, Line 1
OLE DB provider ‘MSDASQL’ returned an unexpected data length for the fixed-length column ‘[MSDASQL].USER_ID’. The expected data length is 5, while the returned data length is 3.
OLE DB error trace [Non-interface error: Unexpected data length returned for the column: ProviderName=’MSDASQL’, TableName='[MSDASQL]’, ColumnName=’USER_ID’, ExpectedLength=’5′, ReturnedLength=’3′].
have you tried it without the SELECT * INTO? I’d just trying running the select first to see if that works. Tom Pullen
DBA, Oxfam GB
Yes I have, on some tables it still does not work. I know that this works because on small tables it works , but not on all of them. If I return top 1 then it works ,so I am looking to see if there are any time out’s that is set on the connection???
Timeouts might be something to do with it, although the error doesn’t indicate that. It may be worth trying OPENROWSET instead. Tom Pullen
DBA, Oxfam GB
I had the same problem today, and just trying various fruitless attempts, I tried a RTRIM() command around the offending field, and it worked for me.
quote:Originally posted by Player
Hi all,
I am trying to connect to our mainframe and import some data to sql2000.
The statement works but assoon as ther is +- 3000 or more records that needs
to be copied ,query anylyzer comes back with this message. Here is the query. SELECT * into temptable FROM OPENQUERY ( test,’SELECT * FROM tablename’) Server: Msg 7347, Level 16, State 1, Line 1
OLE DB provider ‘MSDASQL’ returned an unexpected data length for the fixed-length column ‘[MSDASQL].USER_ID’. The expected data length is 5, while the returned data length is 3.
OLE DB error trace [Non-interface error: Unexpected data length returned for the column: ProviderName=’MSDASQL’, TableName='[MSDASQL]’, ColumnName=’USER_ID’, ExpectedLength=’5′, ReturnedLength=’3′].
The problem is that one of the fields being returned is a blank or NULL CHAR field. To resolve this in the Mysql ODBC settings select the option "Pad CHAR to Full Length".
Cheers,
-Jim Roberts
The statement works but assoon as ther is +- 3000 or more records that needs
to be copied ,query anylyzer comes back with this message. Here is the query. SELECT * into temptable FROM OPENQUERY ( test,’SELECT * FROM tablename’) Server: Msg 7347, Level 16, State 1, Line 1
OLE DB provider ‘MSDASQL’ returned an unexpected data length for the fixed-length column ‘[MSDASQL].USER_ID’. The expected data length is 5, while the returned data length is 3.
OLE DB error trace [Non-interface error: Unexpected data length returned for the column: ProviderName=’MSDASQL’, TableName='[MSDASQL]’, ColumnName=’USER_ID’, ExpectedLength=’5′, ReturnedLength=’3′].
]]>