"String data, right truncation" warning on a select statement

Question

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

Answer 1

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

Answer 2

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.

Answer 3

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:

 CString sDsn = "Driver={SQL Server};Server=server\\db;Database=Boards;"
               +"Uid=uid;Pwd=pwd;Trusted_Connection=False;Regional=No;"

Answer 4

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.