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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy