Q167908 - PRB: Output Parameters Wrong after ADO Command.Execute Call

PRB: Output Parameters Wrong after ADO Command.Execute Call

Q167908

The information in this article applies to:

ActiveX Data Objects (ADO), versions 1.0, 1.5, 2.0, 2.1, 2.5, 2.6, used with:
- Microsoft Transaction Server, versions 1.0, 2.0
- Microsoft Visual C++, 32-bit Editions, versions 4.2, 5.0, 6.0
- Microsoft Visual InterDev, version 1.0
- Microsoft Visual Basic Professional Edition for Windows, versions 4.0, 5.0, 6.0
- Microsoft Visual Basic Enterprise Edition for Windows, versions 4.0, 5.0, 6.0

SYMPTOMS

When calling a stored procedure on Microsoft SQL Server 6.5 or SQL Server 7.0 using the ActiveX Data Objects (ADO) Command.Execute method, output parameters are not returned.

CAUSE

The current version of the SQL Server ODBC driver or OLE DB Provier 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 kbADO100 kbADO200 kbADO210 kbVBp kbVBp600 kbVC kbVJ kbGrpDSMDAC kbDSupport kbIIS kbADO250 kbMDAC260
Issue type : kbprb
Technology : kbAudDeveloper kbADOsearch