“字符串数据，右截断” select 语句上的警告
我正在将 Access 2003 数据库升级到 SQL Server Express 2008。表似乎已创建正常，数据看起来也正常。
我有一个连接到该数据库的 MFC 应用程序。连接访问时工作正常，但是当我连接到 SQL Server 时，我在 select 语句上收到以下错误。
DBMS: Microsoft SQL Server
Version: 10.50.1600
ODBC Driver Manager Version: 03.80.0000
Warning: ODBC Success With Info on field 0.
String data, right truncation
State:01004,Native:0,Origin:[Microsoft][ODBC SQL Server Driver]
返回的数据应该是 8 个字符，但最右边的字符被截断后只有 7 个字符。
访问前端可以正确从SQL Server读取数据。
SQL Server表中的字段定义为长度为8的nvarchar。
读取该字段的代码看起来像
CDatabase Database;
CString sSerialNumber = "00000000";
CString SqlString;
CString sDsn = "Driver={SQL Server};Server=server\\db;Database=Boards;Uid=uid;Pwd=pwd;Trusted_Connection=False";
Database.Open(NULL,false,false,sDsn);
CRecordset recset( &Database );
SqlString.Format("Select SerialNumber from boards where MACAddress = '%s'",mac);
recset.Open(CRecordset::forwardOnly,SqlString,CRecordset::readOnly);
recset.GetFieldValue("SerialNumber",sSerialNumber);
这样之后，sSerialNumber应该是12345678，但它是1234567
感谢您的帮助
I am upscaling an access 2003 database to SQL Server Express 2008. The tables appear to be created ok and the data looks ok.
I have an MFC application that connects to this database. It worked fine connecting to access, but when I connect to SQL Server I am getting the following error on a select statement.
DBMS: Microsoft SQL Server
Version: 10.50.1600
ODBC Driver Manager Version: 03.80.0000
Warning: ODBC Success With Info on field 0.
String data, right truncation
State:01004,Native:0,Origin:[Microsoft][ODBC SQL Server Driver]
The data that is returned should be 8 characters but is only 7 with the right most character truncated.
The access front end can read the data from SQL Server correctly.
The field in the SQL Server table is defined as nvarchar with a length of 8.
The code to read the field looks something like
CDatabase Database;
CString sSerialNumber = "00000000";
CString SqlString;
CString sDsn = "Driver={SQL Server};Server=server\\db;Database=Boards;Uid=uid;Pwd=pwd;Trusted_Connection=False";
Database.Open(NULL,false,false,sDsn);
CRecordset recset( &Database );
SqlString.Format("Select SerialNumber from boards where MACAddress = '%s'",mac);
recset.Open(CRecordset::forwardOnly,SqlString,CRecordset::readOnly);
recset.GetFieldValue("SerialNumber",sSerialNumber);
After this, sSerialNumber should be 12345678 but its 1234567
Thanks for the help
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
我同意这与驱动程序有关。 {SQL Server} 驱动程序是为与 SQL 2000 一起使用而引入的。{SQL Native Client} 是随 2005 一起推出的。理想情况下，对于 2008 数据库，您应该使用最新的 {SQL Server Native Client 10.0}。较新的驱动程序向后兼容旧版本的 SQL Server。
I'd agree that this is driver related. The {SQL Server} driver was introduced for use with SQL 2000. {SQL Native Client} came along with 2005. Ideally, for your 2008 database, you should use the newest {SQL Server Native Client 10.0}. The newer drivers are backward compatible with older versions of SQL Server.
更改我的驱动程序
“驱动程序={SQL Server};”
到
驱动程序={SQL Native Client};
已经使问题消失了，但我不确定发生了什么。我会继续调查
Changing my driver from
"Driver={SQL Server};"
to
Driver={SQL Native Client};
has made the problem go away, but I'm not sure what was going on. I'm going to keep looking into it
通过一些谷歌搜索，我了解到，显然，有时，特别是当在 MS SQL Server ODBC 驱动程序 DSN 设置对话框中选中“使用区域设置”时，ODBC 会将由所有数字组成的字符串视为数字，并返回它，如“12345678.00”，它不适合您给它的空间。解决方案是在对话框中或者更永久地在连接字符串中关闭该设置：
From a bit of Googling, I've learned that apparently, at times, particularly when "Use Regional Settings" is checked in the MS SQL Server ODBC driver DSN setup dialog, ODBC will treat a string made up of all digits, as a number, and return it like "12345678.00" which doesn't fit into the space you've given it. The solution is to turn that setting off, either in the dialog box, or, more permanently, in the connection string:
如果您绝对必须深入了解这一点，请创建一个最小的存储过程，它将“选择”定义为 varchar(17) 的本地变量 - 任何大于原始大小 2 倍的大小都可以。现在调用存储过程而不是动态 SQL，看看会返回什么。然后您可以使用完全相同的大小重复它 (nvarchar(8))。如果旧驱动程序容易混淆，您的小存储过程可以作为简单的数据适配器并稳定打字 - 比摆弄表定义容易得多。
另外，检查 inreface/connection 类上是否有任何参数/属性来指定字符编码，并确保它是 unicode (utf-16)。我假设你的代码是针对 unicode 编译的。如果不是，您需要首先做出决定（Nvarchar 中的 N 表示 unicode，否则它只是 varchar）。您肯定需要双方都匹配的字符编码，否则您将遇到其他虚假错误。
If you absolutely have to dig to the bottom of this, make a minimal stored procedure that will "select" local var defined as varchar(17) - any size more than 2x your original size will do. Now call the sproc instead of dynamic SQL and see what comes back. Then you can repeat it with exactly the same size (nvarchar(8)). Your little sproc serves as easy data adapter and to stabilize typing if old driver tends to get confused - much easier than fiddling with table definition.
Also, check if there's any param/property on inreface/connection classes to specify character encoding and make sure that it's unicode (utf-16). I assume that your code gets compiled for unicode. If not, you need to make decision about that first (N in Nvarchar means unicode, otherwise it would be just varchar). You definitely need character encoding matched at both sides or you will have other spurious errors.