Microsoft KB Archive/290175

= FIX: SQL Server ODBC Driver Does Not Transfer Return Values from Stored Procedures =

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:

&quot;[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '?'.&quot;

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 &quot;param1&quot; and &quot;param3&quot; should contain the values 2 and 1 respectively after calling SQLMoreResults:
 * 1) include 
 * 2) include 
 * 3) include 
 * 4) include 

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 * ) &quot;LocalPubs&quot;, SQL_NTS,    ( unsigned char * ) &quot;sa&quot;, SQL_NTS,     ( unsigned char * ) &quot;&quot;, 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 * ) &quot;{ ? = call sp_myproc(?, ?)}&quot;, 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

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.