Microsoft KB Archive/255765

From BetaArchive Wiki
Knowledge Base

Article ID: 255765

Article Last Modified on 9/16/2003


  • Microsoft SQL Server 6.5 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition

This article was previously published under Q255765


You may receive error messages with your application that you do not receive when you run the same statements through ISQL. For example, the following 8152 error message might occur in Microsoft SQL Server 6.5:

Msg 8152, Level 16, State 1
Column 'a' of table 'pubs.dbo.Warn_Me' cannot accept 4 bytes (1 max).

In Microsoft SQL Server 7.0, the text of the error message is:

Msg 8152, Level 16, State 9
String or binary data would be truncated.
The statement has been terminated.


The error messages can occur if the application uses Open Database Connectivity (ODBC) to connect. ISQL uses the DB-Library API to connect. When ODBC connects, ODBC sets these options:

   SET TEXTSIZE 2147483647

The SET ANSI_DEFAULTS ON also sets ANSI_WARNINGS ON, which then causes the 8152 error message. If you set ANSI_WARNINGS to ON, you will also see the error occur in ISQL.

There are two ways you can confirm which settings are set by ODBC applications:

  • Start SQL Server with trace flag 4032, which logs the server's receive buffers into the errorlog.

    sqlservr -c -T4032

    Be cautious when you use trace flag 4032. This trace flag is verbose and you can fill disk space quickly on a server that has a lot of activity.


  • Connect any ODBC application to SQL Server 6.5, and then use SQL Trace. You will see the ODBC settings in the trace file.


To work around this problem, either define the column in the table to accept the values or you can trim or convert the values before you insert them. This is a change in behavior from Microsoft SQL Server 6.0 to SQL Server 6.5, which is described in the following Microsoft Knowledge Base article:

149921 INF: ODBC ANSI Upgrade Changes From SQL Server 6.0 to 6.5


The behavior in SQL Server 7.0 is the same as in SQL Server 6.5, although the error message text has been altered slightly. Also, the behavior is the same whether you are using ISQL, ISQL/w, OSQL, or Query Analyzer. The only consideration is whether ANSI_WARNINGS is set to ON. You can see this if you run the following code:

create table Warn_Me (a char(1))
set ansi_warnings on 
insert warn_me values ("1234")

Regardless of whether you run the preceding statements from ISQL, OSQL, ISQL/w, or Query Analyzer, you receive the error message if ANSI_WARNINGS is set ON. If ANSI_WARNINGS is set OFF, the statement runs without error and the value is truncated to "1", for the single character that is allowed by the column.

Additional query words: err msg

Keywords: kbprb KB255765