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

Easysoft ODBC-ODBC Bridge

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

Article:
00990
Last Reviewed:
25th January 2024
Revision:
2

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:

  1. Issues the query.
  2. Calls SQLNumResultCols to find out how many columns are in the result set.
  3. Calls SQLDescribeCol for each column to find out the type of the column and sometimes 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 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:

Applies To

Knowledge Base Feedback

* Did this content help you?
* Please select one option based on your first choice:

(* Required Fields)