By the way, here is the stored proc that is used in the constructor of the Update report. I did not write it. It was written by someone who used to work here. It is the same stored procedure that is used in the Update report of the Windows application.

USE [PowisData]

GO

/****** Object: StoredProcedure [dbo].[ap_OvenProgramDataByOvenProgramID] Script Date: 09/10/2012 12:39:37 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER Procedure [dbo].[ap_OvenProgramDataByOvenProgramID]

@OvenProgramID int

/*************************************************************************************/

-- 11/14/2004 JohnP. - returns all data for a given OvenProgramStepID

--

/*************************************************************************************/

As Begin -- Procedure

Set NoCount On

Declare @Return int

Select OP.OvenProgramID,OP.OvenID,O.OvenName,OP.OvenProgramID, OP.OvenProgramStart,

OP.ProbeCount, OP.RunTime,OPS.OvenProgramStepID, OPS.OvenProgramStepName, OPD.OvenProgramDataID,

OPD.StageRunTime,

Case When StageNumber = '1' And StageRunTime = 0 Then 'START' Else StageNumber End as StageNumber,

OPD.OvenStageTypeID, OST.OvenStageTypeDescription,OPD.DryBulbSetPoint, OPD.WetBulbSetPoint, OPD.InternalSetPoint,

OPD.RelativeHumiditySetPoint, OP.ControlType,

Case When OPD.PowerFailure = 1 Then -999 Else OPD.DryBulbValue End as DryBulbValue,

Case When OPD.PowerFailure = 1 Then -999 Else OPD.WetBulbValue End as WetBulbValue,

Case When OPD.PowerFailure = 1 Then -999 Else OPD.InternalValue End as InternalValue,

Case When OPD.PowerFailure = 1 Then -999 Else OPD.RelativeHumidityValue End as RelativeHumidityValue,

Case When OPD.PowerFailure = 1 Then -999 Else OPD.ProbeData01 End as ProbeData01,

Case When OPD.PowerFailure = 1 Then -999 Else OPD.ProbeData02 End as ProbeData02,

Case When OPD.PowerFailure = 1 Then -999 Else OPD.ProbeData03 End as ProbeData03,

Case When OPD.PowerFailure = 1 Then -999 Else OPD.ProbeData04 End as ProbeData04,

Case When OPD.PowerFailure = 1 Then -999 Else OPD.ProbeData05 End as ProbeData05,

Case When OPD.PowerFailure = 1 Then -999 Else OPD.ProbeData06 End as ProbeData06,

Case When OPD.PowerFailure = 1 Then -999 Else OPD.ProbeData07 End as ProbeData07,

Case When OPD.PowerFailure = 1 Then -999 Else OPD.ProbeData08 End as ProbeData08,

Case When OPD.PowerFailure = 1 Then -999 Else OPD.ProbeData09 End as ProbeData09,

Case When OPD.PowerFailure = 1 Then -999 Else OPD.ProbeData10 End as ProbeData10,

Case When OPD.PowerFailure = 1 Then -999 Else OPD.ProbeData11 End as ProbeData11,

Case When OPD.PowerFailure = 1 Then -999 Else OPD.ProbeData12 End as ProbeData12,

Case When OPD.PowerFailure = 1 Then -999 Else OPD.ProbeData13 End as ProbeData13,

Case When OPD.PowerFailure = 1 Then -999 Else OPD.ProbeData14 End as ProbeData14,

OP.Comments,OPD.Accelerated, OPD.PowerFailure, OPD.Disabled

From dbo.OvenProgram OP

Inner Join dbo.OvenProgramStep OPS

On OP.OvenProgramID = OPS.OvenProgramID

Inner Join dbo.OvenProgramData OPD

On OPS.OvenProgramStepID = OPD.OvenProgramStepID

Inner Join dbo.Oven O

On OP.OvenID = O.OvenID

Inner Join dbo.OvenStageType OST

On OST.OvenStageTypeID = OPD.OvenStageTypeID

Where OP.OvenProgramID = @OvenProgramID

Order By OP.OvenProgramStart, O.OvenName, OPD.StageRunTime, OPD.OvenProgramDataID

Select @Return = @@Error

ProcedureExit:

Return @Return

ErrorHandler:

Goto ProcedureExit

End -- Procedure

So I am not sure if this stored procedure is inefficient because it's Select statement is in the Recent Expensive Queries section and I am not experienced enough personally to modify this stored procedure to make it more efficient. Like I said though, sometimes this stored proc does not block the update stored procedure while other times it does. Also, the Complete report opens with no problem and it uses in its constructor this same stored proc but the Complete report's ReportViewer file does not allow the user to update comments.

Hviezdoslav

Monday

10-SEP-2012