Menu
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
474
The essence of the SQL driver error is that a string is too long for the corresponding attribute/container in the RE database, so the string truncates on the right while being inserted. When this error occurs the transaction causing it cannot be processed successfully in its current state.
How to fix “String or binary data would be truncated” The main reason behind this error is the more amount of data that we are trying to store in a column than a specific column can store. So a quick solution to solve this error is by increase the column size.
The "String or binary data would be truncated" error indicates that the procedure is attempting to store something in the DBServerInfo table that is larger than the column allows. The two known reasons this can occur are: SQL Server has at least one database whose name exceeds 25 characters in length.
So if your package is failing due to a truncation error on a string column, simple way is to add a dummy row as first row in the Excel file with data greater than 255 characters in the column that is causing truncation error.
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.
111
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
