HOW TO: Troubleshoot Differences Between An Application and ISQL (255765)

SYMPTOMS

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.

CAUSE

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
   SET ANSI_DEFAULTS ON
   SET CURSOR_CLOSE_ON_COMMIT OFF
   SET IMPLICIT_TRANSACTIONS OFF
   SET QUOTED_IDENTIFIER OFF

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.

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

WORKAROUND

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

MORE INFORMATION

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))
go
set ansi_warnings on 
go
insert warn_me values ("1234")
go

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.