FIX: SQL Server ODBC Driver reuses incorrect parameter length (280294)

CAUSE

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.

RESOLUTION

MDAC 2.6

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:

300635 How to obtain the latest MDAC 2.6 service pack

SQL Server 2000

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

290211 How to obtain the latest SQL Server 2000 service pack

Hotfix

NOTE: The following hotfix was created prior to Microsoft SQL Server 2000 Service Pack 1.

The English version of this fix should have the following file attributes or later:

   Date          Version          Size             File name
   ------------------------------------------------------------
   10/13/2000    2000.80.212.0    471,119 bytes    Sqlsrv32.dll	
   10/13/2000    2000.80.212.0     90,112 bytes    Sqlsrv32.rll		
   10/13/2000    2000.80.212.0     28,742 bytes    Odbcbcp.dll

Please note that the hotfix contains three files. Due to version dependencies, all three files must be installed together.

WORKAROUND

Do one of the following to work around the problem:

If possible, use an earlier version of the SQL Server ODBC driver, such as the MDAC 2.5 Service Pack 1 (SP1) version (3.70.0820).
When the parameter is bound the first time, give it the largest size necessary, up to the maximum size of the column.

MORE INFORMATION

Steps to Reproduce Behavior

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.

Sample Code

#include "stdafx.h"

#include <windows.h>
#include <stdlib.h>
#include <stdio.h>
#include <sql.h>
#include <sqlext.h>


#define SMALLER_FIRST 1		//Use this to determine which parameter is bound first.
				//Set to 1 to reproduce the failure.


void main(void)
{
	
	SQLHANDLE henv;
	SQLHANDLE hdbc;
	SQLHANDLE hstmt;
	RETCODE rc;

	char Statement[50] = {"select title_id from pubs..titles where title = ?"};		
	char dsn[10] = {"DSNName"};
	char user[5] = {"DatabaseUser"};
	char pass[5] = {"Password"};

	char m_titleid[10] = {"\0"};
	char param2[22] = {"The Gourmet Microwave"};
	char param1[14] = {"Net Etiquette"};
	param1[13] = '\0';
	param2[21] = '\0';

	long sqlnts = SQL_NTS;
	long m_bytesRet = SQL_NTS;

	
	
	rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
	
	rc = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, 
			(SQLPOINTER) SQL_OV_ODBC3, SQL_IS_UINTEGER);
	
	rc = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
	
	rc = SQLConnect(hdbc, (SQLCHAR *)dsn, SQL_NTS, (SQLCHAR *) user, SQL_NTS, 
			(SQLCHAR *) pass, SQL_NTS);
	
	rc = SQLAllocHandle(SQL_HANDLE_STMT,hdbc,&hstmt);
	
	rc = SQLPrepare(hstmt, (SQLCHAR *)Statement, SQL_NTS);

#if SMALLER_FIRST	//Put smaller parameter first.
	rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_WCHAR, 13, 0,
			(SQLCHAR*)param1, 13, &sqlnts);	
#else
	rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_WCHAR, 21, 0,
			(SQLCHAR*)param2, 21, &sqlnts);	
#endif

	rc = SQLExecute(hstmt);

	rc = SQLFetch(hstmt);

	if (rc == SQL_NO_DATA_FOUND)
	{
		printf("Parameter #1 (%s) returned no data.\n", param1);
		SQLFreeStmt(hstmt, SQL_DROP);
		SQLDisconnect(hdbc);
		SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
		SQLFreeHandle(SQL_HANDLE_ENV, henv);
		exit(0);
	}
	else
	{
		SQLGetData(hstmt, 1, SQL_C_CHAR, m_titleid, 10, &m_bytesRet);
		printf("Title_id for parameter (%s) = %s\n", SMALLER_FIRST?param1:param2, m_titleid);
	}

	rc = SQLFreeStmt(hstmt, SQL_CLOSE);

	memset(m_titleid,0,10);

#if SMALLER_FIRST	//Put larger parameter last.
	rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_WCHAR, 21, 0,
			(SQLCHAR*)param2, 21, &sqlnts);	
#else
	rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_WCHAR, 13, 0,
			(SQLCHAR*)param1, 13, &sqlnts);	
#endif

	rc = SQLExecute(hstmt);

	rc = SQLFetch(hstmt);

	if (rc == SQL_NO_DATA_FOUND)
	{
		printf("Parameter #2 (%s) returned no data.\n", param2);
		SQLFreeStmt(hstmt, SQL_DROP);
		SQLDisconnect(hdbc);
		SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
		SQLFreeHandle(SQL_HANDLE_ENV, henv);
		exit(0);
	}
	else
	{
		SQLGetData(hstmt, 1, SQL_C_CHAR, m_titleid, 10, &m_bytesRet);
		printf("Title_id for parameter (%s) = %s\n", SMALLER_FIRST?param2:param1, m_titleid);
	}

	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	rc = SQLDisconnect(hdbc);
	SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
	SQLFreeHandle(SQL_HANDLE_ENV, henv);
}