Microsoft KB Archive/255765

= HOW TO: Troubleshoot Differences Between An Application and ISQL =

Article ID: 255765

Article Last Modified on 9/16/2003

-

APPLIES TO


 * 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



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.

Additional query words: err msg

Keywords: kbprb KB255765

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.