Article ID: 280294
Article Last Modified on 11/2/2007
APPLIES TO
- Microsoft Data Access Components 2.6
- Microsoft ODBC Driver for Microsoft SQL Server 3.7
- Microsoft SQL Server 2000 Enterprise Edition
- Microsoft SQL Server 2000 Personal Edition
- Microsoft SQL Server 2000 Standard Edition
- Microsoft SQL Server 2000 Workgroup Edition
- Microsoft SQL Server 2000 Developer Edition
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.
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.
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.
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);
}
Additional query words: query returns returned no results parameter length sp_cursorprepexec truncated size incorrect plan cached Sqlsrv32 Odbcbcp
Keywords: kbhotfixserver kbbug kbfix kbmdac260sp1fix kbqfe kbsqlserv2000sp1fix KB280294
