Microsoft KB Archive/224591

= PRB: SQL Server Stored Procedure's Out Parameters Can Return Incorrect Values =

Article ID: 224591

Article Last Modified on 11/4/2003

-

APPLIES TO


 * Microsoft ActiveX Data Objects 1.5
 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.1
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7

-



This article was previously published under Q224591



SYMPTOMS
When using ADO and the default server side cursor is out, parameters return NULL values.



CAUSE
ADO doesn't fill in the out parameters for a recordset created using adUseServer (server side cursor) until the returned recordset is closed.



RESOLUTION
Use one of the following methods to get the correct output values from the stored procedure.

  Select Client side server adUseClient. In the code below insert the following line just before the comm->Execute call.

conn->CursorLocation=adUseClient;   Close the returned recordset before getting the value of the output parameter. In the code below insert the following line just before obtaining the parameter values.

rs->Close; 



STATUS
This behavior is by design.



Steps To Reproduce Behavior
 In the SQL Server 7.0 Query Analyzer select the test database Pubs.  Create the following stored procedure. This stored procedure returns a recordset and an out parameter count. if exists (select * from sysobjects where id = object_id(N'[dbo].[GetJobs]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop proc GetJobs go  create proc GetJobs @id as int, @count as int [out] as   begin Select @count = Count(*) from jobs where job_id >@id Select * from jobs where job_id >@id end go

</li>  Use VC App Wizard to create a new console application and modify the code as follows:

Note You must change User ID= and Password = to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database. #include "stdio.h"  #import "C:\PROGRA~1\COMMON~1\System\ado\msado15.dll" no_namespace rename ("EOF", "EOF2")
 * 1) include "stdafx.h"

struct InitOle { InitOle { ::CoInitialize(NULL); } ~InitOle { ::CoUninitialize;  } } _init_InitOle_;

int main(int argc, char* argv[]) {   _variant_t varErr((long)0, VT_ERROR); _CommandPtr comm(__uuidof(Command)); _ConnectionPtr conn(__uuidof(Connection));

_bstr_t connstr="Provider=SQLOLEDB.1;Persist Security Info=False;User ID= ;Password= ;Initial Catalog=pubs;Data Source=(local)"; conn->Open(connstr, "", "", adConnectUnspecified); comm->ActiveConnection=conn; comm->CommandText="GetJobs"; comm->CommandType = adCmdStoredProc ; comm->Parameters->Refresh; _variant_t recs;

comm->Parameters->Item[_variant_t((short)1)]->Value= _variant_t((long)5); _RecordsetPtr rs = comm->Execute(&recs, &vtMissing,adCmdStoredProc);

_variant_t recordcount= comm->Parameters->Item[_variant_t((short)2)]->Value;

printf("recordcount = %li\n", (long)recordcount); return 0; }

</li> Change the Datasource, User ID and password in the connection string above.</li></ol>

The recordcount variant that the above code returns is of type VT_NULL rather than the number of records that the stored procedure returns.

Keywords: kbdatabase kbprb KB224591

-

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

© Microsoft Corporation. All rights reserved.