Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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


View products that this article applies to.

This article was previously published under Q290175

↑ Back to the top


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.

↑ Back to the top


Cause

This is a bug in SQL Server ODBC driver that ships with MDAC 2.6.

↑ Back to the top


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
				

↑ Back to the top


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.

↑ Back to the top


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
				

↑ Back to the top


Keywords: KB290175, kbqfe, kbmdac260sp1fix, kbfix, kbbug

↑ Back to the top

Article Info
Article ID : 290175
Revision : 5
Created on : 10/17/2003
Published on : 10/17/2003
Exists online : False
Views : 467