Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Subscription getting a Time out error Expand / Collapse
Author
Message
Posted Friday, March 15, 2013 1:02 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 10:26 AM
Points: 340, Visits: 760
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)
WHERE source like 'Import_SQLPlexStaging_Statusmart_DailyApps'
AND [event] = 'PackageEnd'
AND DATEDIFF(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?
Post #1431694
Posted Friday, March 15, 2013 3:23 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 10:26 AM
Points: 340, Visits: 760
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.
Post #1431795
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse