PRB: Output Parameters Wrong after ADO Command.Execute Call

• ActiveX Data Objects (ADO) included with: - Microsoft Active Server Pages, versions 1.0, 1.0b - Microsoft Transaction Server 1.0 - Microsoft Visual C++, 32-bit Editions, versions 4.2, 5.0 - Microsoft Visual InterDev, version 1.0 - Microsoft Visual Basic Professional and Enterprise Editions for

       Windows, versions 4.0, 5.0
  

  - Microsoft Visual J++, versions 1.0, 1.1

SYMPTOMS

When calling a stored procedure on SQL Server 6.5 using the ADO Command.Execute method, output parameters aren't returned.

CAUSE

The current version of the SQL Server ODBC driver (2.65.0240) returns output parameters as the last packet it sends back to the client. The ODBC driver is implemented such that an application must process through all of the result sets returned by the procedure before the output parameters are filled. For more information, see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q152174
   TITLE:      INFO: Output Parameters, Return Codes and the ODBC Driver

RESOLUTION

If you are using Visual Basic and you are receiving a recordset back from the Execute call, set the recordset to "Nothing" and then retrieve the results of the output parameters. Here is a Visual Basic example of what the code might look like:

   Dim Conn1 As Connection
   Dim Com1  As Command
   Dim Param1 As Parameter
   Dim rs As Recordset

   Set Conn1 = CreateObject("ADODB.Connection")
   Set Com1 = CreateObject("ADODB.Command")

   Conn1.ConnectionString = "Data Source=MyDataSource;
        PWD=;UID=sa;Database=pubs"
   Conn1.Open

   Com1.ActiveConnection = Conn1
   Com1.CommandText = "{call ParamTest(?)}"

   Set Param1 = Com1.CreateParameter(, adInteger, adParamOutput)
   Com1.Parameters.Append Param1
   Set Param1 = Nothing

   Set rs = Com1.Execute()

   'Free the recordset
   Set rs = Nothing

   ' display result
   Debug.Print Com1.Parameters(0)

In Visual C++, it is important that you call Release() on the returned recordset before the values of the Parameters collection are queried. Here is an example of what the code would look like:

   hr= piCommand->Execute( &varRecordsAffected,
                           &varArray,
                 adCmdText,
                           &piRecordset);
   if (FAILED(hr))goto ErrorExit;
   hr= piCommand->get_Parameters(&piParameters);
   if (FAILED(hr)) goto ErrorExit;

   piRecordset->Release(); // Do this before get parameters

   ADOParameter * piParameter;
   hr= piParameters->get_Item(COleVariant(0L),&piParameter);
   if (FAILED(hr)) goto ErrorExit;

   COleVariant varValue;
   hr= piParameter->get_Value(&varValue);
   if (FAILED(hr)) goto ErrorExit;

STATUS

This behavior is by design.

Keywords          : adoall adoengall adoengdb adoiis adoother adovb adovc adovj
Version           : 1.0 1.1 4.0 5.0
Platform          : WINDOWS
Issue type        : kbprb