When I change the code to use WITH (NOLOCK) for the stored procedure in the constructor of the Update report to the following, I do not seem to get the Timeout Expired error and that stored proc in the constructor of the Update rpt does not seem to have the ASYNC_NETWORK_IO that is blocking the Update stored proc in the Update report's ReportViewer file:
ALTER Procedure [dbo].[ap_OvenProgramDataByOvenProgramID_3]
@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 WITH (NOLOCK)
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
I have read that I can get "dirty reads" with this approach. This approach seems to work though. I would prefer of course to refrain from using the WITH (NOLOCK) and find the solution to why the Update report's constructor's Select stored procedure gets the ASYNC_NETWORK_IO wait and blocks the Update stored proc.
I guess that without the WITH (NOLOCK) there is some locking that is not being unlocked and that is causing the ASYNC_NETWORK_IO wait to block the Update stored proc from running.
Hviezdoslav
Tuesday
11-SEP-2012