ADO Command Object Records Affected broken when moving to SQL Server 2008

Question

My company is in the process of moving a database from SQL 2000 to SQL 2008. I am in the process of testing some of our legacy applications and came across a VB6 app that is running into a bug. The code is attempting to run a simple update for a record and check that the Execute method has affected more then one row using the RecordsAffected parameter.

The VB6 code

cmd.ActiveConnection = gcnADODE
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "Wendy_OffshoreQC_UpdateQCRecord"

Set QCID = cmd.CreateParameter("@QCId", adInteger, adParamInput)
QCID.value = 1
cmd.Parameters.Append QCID

Set QCBatchFailurePercentage = cmd.CreateParameter("@QCBatchFailurePercentage", adDecimal, adParamInput)
QCBatchFailurePercentage.Precision = 18
QCBatchFailurePercentage.NumericScale = 2
QCBatchFailurePercentage.value = 1.11
cmd.Parameters.Append QCBatchFailurePercentage

Set QCBatchCompleteDate = cmd.CreateParameter("@QCBatchCompleteDate", adDate, adParamInput)
QCBatchCompleteDate.value = Format(Now, "yyyy-mm-dd hh:mm:ss")
cmd.Parameters.Append QCBatchCompleteDate  
cmd.Execute numRecordsUpdate

success = numRecordsUpdate > 0

The stored procedure create code

ALTER proc [dbo].[Wendy_OffshoreQC_UpdateQCRecord]
  @QCId int
 ,@QCBatchFailurePercentage decimal (18,2)
 ,@QCBatchCompleteDate smalldatetime

AS

UPDATEtblQC
SET
  QCBatchFailurePercentage=@QCBatchFailurePercentage 
 ,QCBatchCompleteDate=@QCBatchCompleteDate
WHERE
 QCID=@QCId

I'm not sure which the ADO connection ends up using. The outside provider is the SQL Server OLE DB provider, but in the extended properties the drive says the SQL Server. I'm going to try fixing some of the code to be consistent with the driver that is used.

Provider=SQLOLEDB.1;Password=1234;Extended 
Properties="driver={SQL Server};uid=username;database=db;
server=server";Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;
Workstation ID=PICASWS186;Use Encryption for Data=False;Tag with column collation when 
possible=False

However numRecordsUpdate always returns -1 instead of 1 like it did on SQL 2000. I know I can change the stored procedure to return @@rowcount or just remove the check for success, but there are other parts of the code with this pattern that are having a similar issue.

Answer 1

I don't know that there's a better answer than to use SET NOCOUNT ON in the stored procedure, and use an OUTPUT parameter to return @@ROWCOUNT.

Perhaps you could try using a different provider - are you currently using ADO.net, or OLEDB, or SQL Native Client? Changing the provider might help, as I know that SET NOCOUNT ON used to cause issues with ADO.net.