预期长度大得多时，奇怪的“返回的数据与列的预期数据长度不匹配"错误-SQL SERVER 2012 [英] Weird 'returned data that does not match expected data length for column' error while the expected length is much bigger - SQL SERVER 2012

问题描述

在我的项目中，我正在将Access数据库重建为SQL数据库.为此，我将访问数据传输到SQL数据库.我确保它们都具有相同的结构，并且在SQL数据库中正确修改了Access字段.

In my project I am rebuilding my Access database to an SQL Database. So to do this I am transferring the Access DATA to the SQL Database. I made sure they both have the same structure and the Access fields are modified correctly in the SQL database.

对于大多数数据而言，这是可行的. 1张桌子除外.该表给了我以下奇怪的错误信息:

For most of the data this works. Except for 1 table. This table gives me the following weird error message:

OLE DB provider 'Microsoft.ACE.OLEDB.12.0' for linked server 'OPS_JMD_UPDATE' returned data that does not match expected data length for column '[OPS_JMD_UPDATE]...[OrderStatus].Omschrijving'. The (maximum) expected data length is 100, while the returned data length is 21.

因此，这里有一些有关Access和SQL字段/列的更多信息:

• 访问类型:短文本
• SQL类型:nvarchar(MAX)
• 访问其中的列数据:普通字母和& - % é + € . , :是不正常的".
• 一些空的Access记录(允许)
• Access表中共有135314条记录

• Access type: Short text
• SQL type: nvarchar(MAX)
• Access column data in it: Normal letters and & - % é + € . , : being the 'not normal ones'.
• A few empty Access records (which is allowed)
• A total of 135314 record in the Access table

将SQL数据类型设置为nvarchar(MAX)，以使字段永远不会变小，但这似乎无济于事.

Iv'e set the SQL datatype to nvarchar(MAX) so that the field can never be to small, this didn't seem to help though..

* OPS_JMD_UPDATE是链接的Access数据库

*The OPS_JMD_UPDATE is the linked Access database

什么原因导致此问题?是因为不允许使用某些字符还是..?

What causes this problem? Is it because some characters aren't allowed or..?

推荐答案

有1条记录生成了错误.我使用TOP选择和DESC选择固定了确切的记录，然后使用select ascii replace消除了错误！解决了对xQbert的感谢，再次感谢您！

There was 1 record that generated the error. I pinned out the exact record with a TOP select and DESC Select and then used the select ascii replaceto remove the error! Solved thanks to xQbert, once more thank you!

这篇关于预期长度大得多时，奇怪的“返回的数据与列的预期数据长度不匹配"错误-SQL SERVER 2012的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！