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

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: 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.

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.

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: The English-language version of this fix should have the following file attributes or later:

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: 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.

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.