Under some circumstances, the SQL Server 2000/MDAC 2.6 RTM version of Microsoft ODBC Driver for SQL Server (2000.80.194) reuses a parameter binding that is too short. This truncates the parameter value and causes incorrect or no data to be returned.
This behavior occurs only when you are using the SQL Server 2000/MDAC 2.6 ODBC driver against a SQL Server 2000 database; it does not occur when you are connecting to a SQL Server 7.0 server or with earlier versions of the driver.
When a statement is prepared and run by the ODBC driver, a query plan for the statement is cached on the server and a handle for the plan is returned to the driver. The information in this query plan is based partly on the size of the parameters that are being used in the statement.
If the size of the bound parameters change, logic in the driver checks to see if the query plan that was cached for the previous running of the statement can still be used. If the query plan cannot be reused because of increased parameter sizes, the plan is unprepared and then re-prepared with larger parameter sizes.
The driver is incorrectly determining that the older query plan (with the shorter parameter length) can be reused. This causes the parameter values to be truncated, which causes the incorrect results.
To resolve this problem, obtain the latest service pack for Microsoft Data Access Components 2.6. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
Copy the code below into a new Microsoft Visual C++ console application, and then compile the code. Please note that you may need to change the data source name, database user, and password, so that these values refer to valid values for your SQL Server. NOTE: You can use the SMALLER_FIRST constant to determine which parameter is bound first.
With the SMALLER_FIRST constant set to 1 (TRUE), the smaller parameter is bound first, and the second call to SQLExecute returns no data. With the SMALLER_FIRST constant set to 0 (zero), the larger parameter is bound first, and data is returned for both queries.