FIX: SQL Server ODBC Driver reuses incorrect parameter length

View products that this article applies to.

This article was previously published under Q280294

SYMPTOMS

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.

Back to the top

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.

Back to the top

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

Back to the top

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.

Back to the top

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

MDAC

This problem was corrected in Microsoft Data Access Components (MDAC) 2.6 Service Pack 1 and MDAC 2.7.

SQL Server

This problem was corrected in SQL Server 2000 Service Pack 1.

Back to the top

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);
}

Back to the top