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.

PRB: SQL Server ODBC Driver Returns Output Parameter as a Result Set in a Plain SQL Statement


View products that this article applies to.

This article was previously published under Q241147

↑ Back to the top


Symptoms

The SQL Server ODBC Driver version 3.70.06.23 and later does not support the return of an output parameter from an SQL SELECT statement. Instead, the driver returns the output parameter as a result set. For example, when executing "Select ?=Max(qty) from sales" in the Pubs sample database, the value of Max(qty) is not returned as an output parameter, but rather as a result set.

The native OLE DB provider for SQL Server does support the return of an output parameter from a SELECT statement.

↑ Back to the top


Resolution

To retrieve an output parameter, you have the following options:
  • From ActiveX Data Objects (ADO) or OLE DB, use the native OLE DB provider for SQL Server (SQLOLEDB).
  • If you are using ODBC, retrieve the output parameter from the result set. See the example below for details.
  • Instead of using an SQL SELECT statement, use a stored procedure to return the output parameter.

↑ Back to the top


Status

This behavior is by design.

↑ Back to the top


More information

Steps to Reproduce Behavior

Use the ODBC Administrator to create a new data source name (DSN) called "TestBug" that uses the SQL Server ODBC Driver to connect to the SQL Server Pubs database. The following code is an ODBC application that demonstrates how the output parameter is returned:
#include <windows.h>
#include <stdio.h>
#include <sql.h>
#include <sqlext.h>

int main(int argc, char* argv[])
{
	SQLHENV         m_SQLEnvironment;
	SQLHDBC         m_SQLConnection;
	SQLHSTMT	m_SQLStatement;
	BOOL            m_Connected;
	SQLRETURN       iReturn;
	DWORD           returnValue = 0;
	DWORD           resultValue = 0;
	long            lBufLength = sizeof(returnValue);
	long            lBufLength1 = sizeof(resultValue);

	//Connect
	iReturn = SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&m_SQLEnvironment);
	iReturn = SQLSetEnvAttr(m_SQLEnvironment,SQL_ATTR_ODBC_VERSION,(SQLPOINTER) SQL_OV_ODBC3,0);
	iReturn = SQLAllocHandle(SQL_HANDLE_DBC,m_SQLEnvironment,&m_SQLConnection);

	//CHANGE THE DSN NAME HERE along with the length of the DSN.
	iReturn = SQLConnect(m_SQLConnection,(SQLCHAR*) "TestBug",SQL_NTS,(SQLCHAR*)"sa",SQL_NTS,(SQLCHAR*)"",SQL_NTS);
	m_Connected = TRUE;

	//Run Query
	iReturn = SQLAllocHandle(SQL_HANDLE_STMT,m_SQLConnection,&m_SQLStatement);
	iReturn = SQLPrepare(m_SQLStatement,(SQLCHAR*) "Select ?=Max(qty) from sales",SQL_NTS);
	iReturn = SQLBindParameter(m_SQLStatement,1,SQL_PARAM_OUTPUT,SQL_C_SLONG,SQL_INTEGER,0,0,&returnValue,0,&lBufLength);  	
	iReturn = SQLExecute(m_SQLStatement);
	iReturn = SQLFetch(m_SQLStatement);
	if (iReturn == SQL_SUCCESS || iReturn == SQL_SUCCESS_WITH_INFO)
	{
		SQLGetData(m_SQLStatement,1,SQL_C_SLONG,&resultValue,0,&lBufLength);
	}
	iReturn = SQLMoreResults(m_SQLStatement);
	while (iReturn != SQL_NO_DATA)
		iReturn = SQLMoreResults(m_SQLStatement);

	//DISCONNECT
	iReturn = SQLFreeHandle(SQL_HANDLE_STMT,m_SQLStatement);
	iReturn = SQLDisconnect(m_SQLConnection);
	iReturn = SQLFreeHandle(SQL_HANDLE_DBC,m_SQLConnection);
	iReturn = SQLFreeHandle(SQL_HANDLE_ENV,m_SQLEnvironment);
	m_SQLStatement = NULL;
	m_SQLConnection = NULL;
	m_SQLEnvironment = NULL;
	return 0;
}
				
In the above example, the output parameter is not returned in the returnValue variable after the call to the SQLMoreResults function returns SQL_NO_DATA. Rather, the SQLGetData function returns the parameter in the resultValue variable.

↑ Back to the top


Keywords: KB241147, kbprb, kbdatabase, kbbug

↑ Back to the top

Article Info
Article ID : 241147
Revision : 5
Created on : 12/5/2003
Published on : 12/5/2003
Exists online : False
Views : 462