Microsoft KB Archive/255765

From BetaArchive Wiki
< Microsoft KB Archive
Revision as of 12:53, 21 July 2020 by X010 (talk | contribs) (Text replacement - """ to """)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Knowledge Base


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