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

View products that this article applies to.

This article was previously published under Q318606

Expand all | Collapse all

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.

Back to the top

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.

Back to the top

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 (http://support.microsoft.com/kb/290211/EN-US/ ) 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

Back to the top

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.

Back to the top

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.

Back to the top