Microsoft KB Archive/241147

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

Article ID: 241147

Article Last Modified on 12/5/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft ODBC Driver for Microsoft SQL Server 3.7
 * Microsoft Data Access Components 2.1
 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.6
 * Microsoft Data Access Components 2.7

-



This article was previously published under Q241147



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.



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.



STATUS
This behavior is by design.



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:
 * 1) include 
 * 2) include 
 * 3) include 
 * 4) include 

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.

Additional query words: outparam param resultset multiple oledb

Keywords: kbbug kbdatabase kbprb KB241147

-

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

© Microsoft Corporation. All rights reserved.