PRB: SQL Server Stored Procedure's Out Parameters Can Return Incorrect Values (224591)

MORE INFORMATION

Steps To Reproduce Behavior

1. In the SQL Server 7.0 Query Analyzer select the test database Pubs.
2. 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
   
   					

   
   
   
3. Use VC App Wizard to create a new console application and modify the code as follows:
   
   Note You must change User ID=<username> and Password =<strong 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 "stdafx.h"
      #include "stdio.h"
      #import "C:\PROGRA~1\COMMON~1\System\ado\msado15.dll" no_namespace rename ("EOF", "EOF2")
   
      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=<username>;Password=<strong 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;
      }
   
   					

   
   
4. Change the Datasource, User ID and password in the connection string above.