PRB: Output Parameters Wrong after ADO Command.Execute Call

ID: Q167908

The information in this article applies to:

ActiveX Data Objects (ADO), versions 1.0, 1.5, 2.0, 2.1 SP2, used with:
- 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

SYMPTOMS

When calling a stored procedure on SQL Server 6.5 using the ActiveX Data Objects (ADO) Command.Execute method, output parameters are not 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, please see the following article in the Microsoft Knowledge Base:

Q152174 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)

NOTE: Assigning a recordset to the return value of Com1.Execute() is optional. The query could be an action query, and not return records. If not, VBA releases the returned recordset automatically if no assignment is performed.

In Visual C++, you must 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 if you are using #import to manipulate ADO:


   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;

   java users must set the recorset to null:
   rs = null;
   then call
   system.gc();

STATUS

This behavior is by design.

Additional query words: kbdse

Keywords : kbnokeyword kbADO kbVBp kbVC kbVJ kbGrpMDAC kbIIS
Version : WINDOWS:1.0,1.5,2.0,2.1 SP2,4.0,5.0
Platform : WINDOWS
Issue type : kbprb