Article ID: 290175
Article Last Modified on 10/17/2003
APPLIES TO
- Microsoft Data Access Components 2.6
- Microsoft SQL Server 2000 Standard Edition
This article was previously published under Q290175
SYMPTOMS
When running a stored procedure with the SQL Server ODBC driver that ships with Microsoft Data Access Components (MDAC) version 2.6, you may receive the following error message:
In addition to the above syntax error, the driver does not populate return values from stored procedures.
This bug only affects stored procedures that are opened using any type of server-side cursor (Keyset, Static, or Dynamic). The default Forward-Only, Read-Only cursor does not exhibit any of the above problems.
The problem only occurs when you call the SQLExecDirect ODBC function, directly or indirectly, to execute the stored procedure. The problem does not occur if you call SQLPrepare to prepare the statement before calling SQLExecute.
CAUSE
This is a bug in SQL Server ODBC driver that ships with MDAC 2.6.
RESOLUTION
To resolve this problem, obtain the latest service pack for Microsoft Data Access Components 2.6. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
300635 INFO: How to Obtain the Latest MDAC 2.6 Service Pack
Hotfix
The English version of this fix should have the following file attributes or later:
Date Time Version Size File name Platform ------------------------------------------------------------------- 02/15/01 5:53P 2000.80.256.0 28,742 Odbcbcp.dll x86 02/15/01 5:53P 2000.80.256.0 471,119 Sqlsrv32.dll x86 02/15/01 5:44P 2000.80.256.0 90,112 Sqlsrv32.rll x86
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 Data Access Components 2.6 Service Pack 1.
MORE INFORMATION
A client application that makes ODBC API calls, as in the code below, will not have its output variables populated with output data from SQL Server even after calling SQLMoreResults. After calling SQLExecDirect, if you check the error message (as done in the code below by calling SQLGetDiagRec), you will see the above mentioned error.
Any client application that uses ActiveX Data Objects (ADO) with the Microsoft OLEDB Provider for ODBC Driver (MSDASQL), Remote Data Objects (RDO), Microsoft Foundation Classes (MFC), or any other mechanism to access SQL Server, using the SQL Server ODBC driver that ships with MDAC 2.6, will have these problems.
In the following code, the parameters "param1" and "param3" should contain the values 2 and 1 respectively after calling SQLMoreResults:
#include <windows.h> #include <stdio.h> #include <sql.h> #include <sqlext.h> void main() { HENV henv; HDBC hdbc; HSTMT hstmt; SQLRETURN sr; SQLCHAR* theDiagState = new SQLCHAR[50]; SQLINTEGER theNativeState; SQLCHAR* theMessageText = new SQLCHAR[255]; SQLSMALLINT iOutputNo; long param1 = 0; long param2 = 0; long param3 = 0; SQLINTEGER cbValue1 = sizeof(long); SQLINTEGER cbValue2= sizeof(long); SQLINTEGER cbValue3= sizeof(long); SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, & henv ); sr = SQLSetEnvAttr( henv, SQL_ATTR_ODBC_VERSION, ( void * ) SQL_OV_ODBC3, 0 ); sr = SQLAllocHandle( SQL_HANDLE_DBC, henv, & hdbc ); //Please note that the DSN name is LocalPubs here. Chenge the DSN name UserID //and Password here. sr = SQLConnect( hdbc, ( unsigned char * ) "LocalPubs", SQL_NTS, ( unsigned char * ) "sa", SQL_NTS, ( unsigned char * ) "", SQL_NTS ); sr = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, & hstmt ); sr = SQLSetStmtAttr( hstmt, SQL_ATTR_CURSOR_TYPE, ( void * ) SQL_CURSOR_DYNAMIC, SQL_IS_INTEGER ); sr= SQLBindParameter( hstmt, 1, SQL_PARAM_OUTPUT, SQL_C_LONG, SQL_INTEGER, sizeof( long ), 0, & param1, sizeof( long ), & cbValue1 ); sr = SQLBindParameter( hstmt, 2, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, sizeof( long ), 0, & param2, sizeof( long ), & cbValue2 ); sr = SQLBindParameter( hstmt, 3, SQL_PARAM_OUTPUT, SQL_C_LONG, SQL_INTEGER, sizeof( long ), 0, & param3, sizeof( long ), & cbValue3 ); sr = SQLExecDirect( hstmt, ( unsigned char * ) "{ ? = call sp_myproc(?, ?)}", SQL_NTS ); if (sr != SQL_SUCCESS) { //With this bug you will get an error message. Check this message in theMessageText. SQLGetDiagRec(SQL_HANDLE_STMT,hstmt,1,theDiagState,&theNativeState,theMessageText,100,&iOutputNo); } //With the fix, you should get the return value after calling this SQLMoreResult sr = SQLMoreResults( hstmt ); //Free allocated memory and disconnect SQLFreeStmt( hstmt, SQL_CLOSE ); SQLFreeStmt( hstmt, SQL_DROP ); SQLDisconnect( hdbc ); SQLFreeHandle( SQL_HANDLE_DBC, hdbc ); SQLFreeHandle( SQL_HANDLE_ENV, henv ); delete theMessageText; delete theDiagState; }
Here is a sample stored procedure used with the above ODBC API code:
create proc ret2 ( @p1 int, @p2 int output ) as declare @x int select @x = @p1 select @p2 = 1 return 2
Additional query words: SQLSRV SQLSRV32 PARAM
Keywords: kbbug kbfix kbqfe kbmdac260sp1fix KB290175