Download ODBC Drivers for Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase & DB2.

Why do I get "String data, right truncation" retrieving result sets from Microsoft SQL Server?

First, refer to Why do I keep getting data truncated errors in my Perl?

Before going into the specifics of an issue in the Microsoft SQL Server ODBC driver, the most common reason for this error is calling SQLGetData or binding a column and supplying too small a buffer for the column data. For example:

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. SQLGetData or SQLFetch return SQL_SUCCESS_WITH_INFO and a state of 01004.

However, there is one specific issue with the Microsoft SQL Server ODBC driver, which can confuse ODBC applications that bind columns as SQL_C_CHAR. When retrieving column data from a result set as strings, the average ODBC application:

  1. Issues the query.
  2. Calls SQLNumResultCols to find out how many columns are in the result set.
  3. Calls SQLColAttribute to get the display size.
  4. For each column, calls SQLGetData or SQLBindCol, passing a buffer based on the reported display size.

Ordinarily, this works fine with the Microsoft SQL Server ODBC driver, but when Use Regional Settings is turned on in the DSN setup dialog, everything changes. Once Use Regional Settings is turned on, 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 turned on, SQLDescribeCol and SQLColAttribute return the same column size as they do when this setting is turned off. For larger numbers therefore, the column size of 10 is insufficient to hold the number and you 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 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've 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 turned on and you run make test for DBD::ODBC, you are likely to get:

# 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: