FIX: SQL Server ODBC Driver Resets Connection Attributes When Used in Connection Pooling (318606)


The information in this article applies to:

  • Microsoft Data Access Components 2.6
  • Microsoft Data Access Components 2.7
  • Microsoft ODBC Driver for SQL Server 2000 2000.80.194
  • Microsoft ODBC Driver for SQL Server 2000 2000.80.380
  • Microsoft ODBC Driver for SQL Server 2000 2000.81.7713.0
This article was previously published under Q318606

SYMPTOMS

When an application uses connection pooling through ActiveX Data Objects (ADO), OLE DB, or Open Database Connectivity (ODBC), and the following conditions are true
  • The default settings (for example, ANSI padding or warnings behavior) of the DSN used by the SQL Server ODBC driver have been changed.

    -and-The connection is reused.
the application's attributes are set back to the default settings of the SQL Server ODBC driver. As a result, database connections that need for the ANSI Nulls, padding, and warnings attributes to be set to "on" may receive the following error message when you insert data:
[Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would be truncated.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.
This behavior does not occur with the SQL Server driver that is included with Microsoft Data Access Components (MDAC) 2.5 or any of its service packs. This behavior occurs only with MDAC 2.6 Service Pack 1 (SP1) and MDAC 2.6 Service Pack 2 (SP2) SQL Server drivers, or with MDAC 2.7 SQL Server drivers.

CAUSE

This behavior occurs because the SQL Server ODBC driver sends a command to the SQL Server computer to reset the connection attributes every time that a connection is reused from the connection pool.

RESOLUTION

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

The English-language version of this fix should have the following file attributes or later: 
   Date         Time  Version         Size   File name
   --------------------------------------------------
   02/25/02    11:55A 2000.81.8425.0 389120 Sqlsrv32.dll
   02/25/02    11:55A 2000.81.7713.0  90112 Sqlsrv32.rll
   02/25/02    11:55A 2000.81.8425.0  24576 Odbcbcp.dll

WORKAROUND

A connection that relies on settings in the DSN that are not the default settings can send the SQL Server commands to set the connection attributes every time that the connection opens or reuses a connection from the pool, as follows: 
SQLExecDirect( hstmt, "set ansi_nulls off", SQL_NTS );
This forces the ANSI Nulls attribute into an "off" setting for the connection until the connection is closed or released back to the pool and then reused.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was first corrected in Microsoft SQL Server 2000 Service Pack 3.
Modification Type:MinorLast Reviewed:9/27/2005
Keywords:kbHotfixServer kbQFE kbSQLServ2000sp3fix kbbug kberrmsg kbfix kbSQLServ2000preSP3fix KB318606 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.