Microsoft KB Archive/255765
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:
In Microsoft SQL Server 7.0, the text of the error message is:
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.
- 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)) 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