help with stored procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

help with stored procedure

Author

Topic

joe8079
Posting Yak Master

127 Posts

Posted - 2013-08-02 : 07:58:30

I have a stored procudure that executes an SSRS subscription and the problem I'm having is the subscription will sometimes fail for whatever reason and I need a way re run the stored procedure below if there is an error. if no error, just execute as normal, but if there is an error, i would like to re run the the stored procedure below. Would this be possible?

EXEC msdb.dbo.sp_start_job @job_name = 'abc' --name of subscription being executed.

Thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-02 : 23:59:12

something like


DECLARE @Status int

SELECT TOP 1 @Status = run_status
FROM dbo.sysjobs j
INNER JOIN dbo.sysjobhistory jh
ON jh.job_id = j.job_id
WHERE j.name = 'abc'
ORDER BY run_date DESC, run_time DESC

IF @Status <> 1
  EXEC msdb.dbo.sp_start_job @job_name = 'abc'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

joe8079
Posting Yak Master

127 Posts

Posted - 2013-08-03 : 00:19:08

thank you! I will try this and let you know how it goes

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-03 : 00:24:13

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-08-06 : 16:07:02

Within the job itself, you can also specify the number of times to re-try a step if it fails.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources