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

Article translations Article translations
Article ID: 290175 - View products that this article applies to.
This article was previously published under Q290175
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

On This Page

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:
"[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '?'."
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
				

Properties

Article ID: 290175 - Last Review: February 28, 2014 - Revision: 3.2
APPLIES TO
  • Microsoft Data Access Components 2.6
  • Microsoft SQL Server 2000 Standard Edition
Keywords: 
kbnosurvey kbarchive kbbug kbfix kbmdac260sp1fix kbqfe KB290175

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com