Microsoft KB Archive/167908

{|
 * width="100%"|

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