Article ID: 318606
Article Last Modified on 9/27/2005
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:
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.
Keywords: kberrmsg kbbug kbfix kbsqlserv2000presp3fix kbqfe kbsqlserv2000sp3fix kbhotfixserver KB318606
