Please see the question Why do I keep getting data truncated errors in my Perl? first, as that is more likely to contain the answer you are looking for.
Before going into the specifics of an issue in the Microsoft SQL Server ODBC driver, it should be noted that the most common reason for this error is calling SQLGetData or binding a column and supplying too small a buffer for the column data. E.g.:
SQLExecDirect(select var_char_column from table) SQLDescribeCol(column=1) returns a column size of 100 or SQLColAttributes(column=1, SQL_DESC_DISPLAY_SIZE) which also returns 100 SQLFetch SQLGetData(column=1, type=SQL_C_CHAR, buffer, buffer_size = 10)
=Or-
SQLExecDirect(select var_char_column from table) SQLDescribeCol(column=1) returns a column size of 100 or SQLColAttributes(column=1, SQL_DESC_DISPLAY_SIZE) which also returns 100 SQLBindCol(column=1, type=SQL_C_CHAR, buffer, buffer_size = 10) SQLFetch
In both cases, the buffer supplied for the column data was too small, and you will get a SQL_SUCCESS_WITH_INFO
return from SQLGetData/SQLFetch and a state of 01004.
However, there is one specific issue with the Microsoft SQL Server ODBC driver, which can seriously confuse ODBC applications that bind columns as SQL_C_CHAR
. When retrieving column data from a result set as strings, the average ODBC application:
Ordinarily, this works fine with the Microsoft SQL Server ODBC driver, but when "Use Regional Settings" is checked in the SQL Server ODBC driver DSN setup dialog, everything changes. Once "Use Regional Settings" is checked, the ODBC driver returns integer fields using the current regional settings and this generally increases the size of the returned data. For UK machines, the simple integer 1234, is returned as "1,234.00". The problem is that when "Use Regional Settings" is checked, SQLDescribeCol and SQLColAttribute return the same column size as they do when this setting is unchecked. For larger numbers therefore, the column size of 10 will be insufficient to hold the number and you will get "string data right truncated". A simple example illustrates the problem:
SQLGetTypeInfo(SQL_LONGVARCHAR)
this returns a result set describing the SQL_LONGVARCHAR
type of which one of the columns (column 3) is the maximum column size that the server supports for this data type. In Microsoft SQL Server, the maximum size of a SQL_LONGVARCHAR is 2147483647 bytes.
SQLDescribeCol(column=3)
this returns:
(Name : COLUMN_SIZE, Type : 4, Size : 10, Decimal Digits : 0, Nullable : 1)
note the Size : 10
. Most applications will use this number to allocate a buffer to receive the column data, if the column data is being retrieved as SQL_C_CHAR
. (They add 1 for the terminating NULL as well.)
SQLColAttribute(column=3, SQL_DESC_DISPLAY_SIZE)
returns 11
SQLGetData(column=3, buffer, buffer_size=100)
returns "2,147,483,647.00" in the UK, which is too big to fit in the previously returned size of 10 or 11.
We have found a number of applications that are affected by this, but perhaps the most common is Perl's DBD::ODBC, which binds integer columns as SQL_C_CHAR
(quite naturally really given Perl's text processing features). If "Use Regional Settings" is checked and you run make test for DBD::ODBC, you are likely to see something like:
# make test PERL_DL_NONLAZY=1 /usr/local/bin/perl "-MExtUtils::Command::MM" "-e" "test_harness(0, 'blib/lib', 'blib/arch')" t/*.t t/01base.........ok t/02simple.......ok 2/17 DBD::ODBC::st fetchrow failed: [unixODBC][][Microsoft][ODBC SQL Server Driver] String data, right truncation (SQL-01004) (DBD: st_fetch/SQLFetch (long truncated) err=-1) at t/ODBCTEST.pm line 56. DBD::ODBC::st fetchrow failed: [unixODBC][][Microsoft][ODBC SQL Server Driver] String data, right truncation (SQL-01004) (DBD: st_fetch/SQLFetch (long truncated) err=-1) at t/ODBCTEST.pm line 56. Unable to find a suitable test type for field COL_C at t/ODBCTEST.pm line 63. t/02simple.......dubious Test returned status 255 (wstat 65280, 0xff00)
There are two possible solutions to this problem:
-Or-
DSN=mydsn;UID=dbuser;PWD=dbpassword;Regional=No;