March 15, 2013 at 1:02 pm
I have a report with 4 subscriptions for emailing the report at 4 different times a day. its data driven running the following script:
DECLARE @WaitForTime datetime
DECLARE @StopWaitTime datetime
DECLARE @WaitIncrement int
DECLARE @StopIncrement int
DECLARE @PackageRetVal int
DECLARE @ReturnValue int
SET @ReturnValue = 0
SET @WaitIncrement = 5
SET @StopIncrement = 90
SET @WaitForTime = DATEADD(minute, @WaitIncrement, getdate())
SET @StopWaitTime = DATEADD(minute, @StopIncrement, getdate())
WHILE (@WaitForTime < @StopWaitTime)
BEGIN
SELECT top 1 @PackageRetVal = DataCode
FROM dbo.sysssislog (NOLOCK)
WHEREsource like 'Import_SQLPlexStaging_Statusmart_DailyApps'
AND[event] = 'PackageEnd'
ANDDATEDIFF(mi, StartTime, getdate()) < 90
ORDER BY ID desc
IF @PackageRetVal is not null
BEGIN
IF @PackageRetVal = 0 -- no error
SET @ReturnValue = 1
ELSE
SET @ReturnValue = 0
BREAK
END
IF (getdate() >= @StopWaitTime)
BEGIN
SET @ReturnValue = 0 -- timed out
BREAK
END
WAITFOR Time @WaitForTime
SET @WaitForTime = DATEADD(minute, @WaitIncrement, getdate())
END
SELECT TodaysDate = getdate()
SELECT Results = @ReturnValue
I can set it to execute a minte from my current time for testing and it runs fine and emails the report but when I scheddule it for the time it needs o run I get the following error
Error: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Does anyone know how I can fix it?
March 15, 2013 at 3:23 pm
I figured it out. In my code there is a wait command that occasionally is called. I changed the timeout setting in the subscription to 1200 and it ran fine.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply