locked
SSRS- Create a Job Script to run failed Subscriptions. RRS feed

  • Question

  • Hello,

    Is there a way to create SQL Server Agent Job Script to know the failed subscriptions and kickoff all the failed subscriptions?

    I have a query that shows all the failed subscriptions for each and every day...

    I need to create a SQL Server Agent Job Script that runs every day to re-run the failed subscriptions. 

    Here is the Query to show all the Failed Subscriptions. 

    SELECT Cat.[Name],
           Own.UserName,
           ISNULL(REPLACE(Sub.[Description], 'send e-mail to ', ''), ' ') AS Recipients, Sub.SubscriptionID,
           Sub.[LastStatus],
           Cat.[Path],
           Sub.[LastRunTime]
    FROM dbo.[Subscriptions] Sub WITH (NOLOCK)
        INNER JOIN <g class="gr_ gr_402 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" data-gr-id="402" id="402">dbo</g>.[Catalog] Cat WITH (NOLOCK)
            ON Sub.[Report_OID] = Cat.[ItemID]
        INNER JOIN dbo.[ReportSchedule] Rep WITH (NOLOCK)
            ON (
                   Cat.[ItemID] = Rep.[ReportID]
                   AND Sub.[SubscriptionID] = Rep.[SubscriptionID]
               )
        INNER JOIN dbo.[Users] Own WITH (NOLOCK)
            ON Sub.[OwnerID] = Own.[UserID]
    WHERE Sub.[LastStatus] NOT LIKE '%was written%' --File Share subscription
          AND Sub.[LastStatus] NOT LIKE '%pending%' --Subscription in progress. No result yet
          AND Sub.[LastStatus] NOT LIKE '%mail sent%' --Mail sent successfully.
          AND Sub.[LastStatus] NOT LIKE '%New Subscription%' --New Sub. Not been executed yet
          AND Sub.[LastStatus] NOT LIKE '%been saved%' --File Share subscription
          AND Sub.[LastStatus] NOT LIKE '% 0 errors.' --Data Driven subscription
          AND Sub.[LastStatus] NOT LIKE '%succeeded%' --Success! Used in cache refreshes
          AND Sub.[LastStatus] NOT LIKE '%successfully saved%' --File Share subscription
          AND Sub.[LastStatus] NOT LIKE '%New Cache%' --New cache refresh plan
          AND Sub.[LastRunTime] > GETDATE() - 1
    ORDER BY Own.UserName;


    Thanks, Shyam.

    • Edited by ShyamReddy Tuesday, January 15, 2019 12:12 AM
    Monday, January 14, 2019 9:12 PM

Answers

  • Hi ShyamReddy

    You could try to add the above query in a new sql agent job , in there you could aslo assign the schedule about the new sql agent job.

    You could add the sql agent job via ssms .

    Or you could just add the job by sql query .

    Below query are generate by the sql agent job which I create in ssms and then export it.

    USE [msdb]
    GO
    
    /****** Object:  Job [restart_failed_job]    Script Date: 16/01/2019 15:34:46 ******/
    BEGIN TRANSACTION
    DECLARE @ReturnCode INT
    SELECT @ReturnCode = 0
    /****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 16/01/2019 15:34:46 ******/
    IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
    BEGIN
    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    
    END
    
    DECLARE @jobId BINARY(16)
    EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'restart_failed_job', 
    		@enabled=1, 
    		@notify_level_eventlog=0, 
    		@notify_level_email=0, 
    		@notify_level_netsend=0, 
    		@notify_level_page=0, 
    		@delete_level=0, 
    		@description=N'restart failed ssrs subscription job at 10:00 pm', 
    		@category_name=N'[Uncategorized (Local)]', 
    		@owner_login_name=N'<serername>\<username>', @job_id = @jobId OUTPUT
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    /****** Object:  Step [step1]    Script Date: 16/01/2019 15:34:46 ******/
    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'step1', 
    		@step_id=1, 
    		@cmdexec_success_code=0, 
    		@on_success_action=1, 
    		@on_success_step_id=0, 
    		@on_fail_action=2, 
    		@on_fail_step_id=0, 
    		@retry_attempts=0, 
    		@retry_interval=0, 
    		@os_run_priority=0, @subsystem=N'TSQL', 
    		@command=N'USE ReportServer;  
    GO  
    DECLARE usesub CURSOR FOR  
    SELECT Rep.ScheduleID,
    Cat.[Name],
           Own.UserName,
           ISNULL(REPLACE(Sub.[Description], ''send e-mail to '', ''''), '' '') AS Recipients, Sub.SubscriptionID,
           Sub.[LastStatus],
           Cat.[Path],
           Sub.[LastRunTime]
    FROM dbo.[Subscriptions] Sub WITH (NOLOCK)
        INNER JOIN dbo.[Catalog] Cat WITH (NOLOCK)
            ON Sub.[Report_OID] = Cat.[ItemID]
        INNER JOIN dbo.[ReportSchedule] Rep WITH (NOLOCK)
            ON (
                   Cat.[ItemID] = Rep.[ReportID]
                   AND Sub.[SubscriptionID] = Rep.[SubscriptionID]
               )
        INNER JOIN dbo.[Users] Own WITH (NOLOCK)
            ON Sub.[OwnerID] = Own.[UserID]
    WHERE Sub.[LastStatus] NOT LIKE ''%was written%'' --File Share subscription
          AND Sub.[LastStatus] NOT LIKE ''%pending%'' --Subscription in progress. No result yet
          AND Sub.[LastStatus] NOT LIKE ''%mail sent%'' --Mail sent successfully.
          AND Sub.[LastStatus] NOT LIKE ''%New Subscription%'' --New Sub. Not been executed yet
          AND Sub.[LastStatus] NOT LIKE ''%been saved%'' --File Share subscription
          AND Sub.[LastStatus] NOT LIKE ''% 0 errors.'' --Data Driven subscription
          AND Sub.[LastStatus] NOT LIKE ''%succeeded%'' --Success! Used in cache refreshes
          AND Sub.[LastStatus] NOT LIKE ''%successfully saved%'' --File Share subscription
          AND Sub.[LastStatus] NOT LIKE ''%New Cache%'' --New cache refresh plan
          AND Sub.[LastRunTime] > GETDATE() - 1
    ORDER BY Own.UserName
    
      
    OPEN usesub;  
    declare @ScheduleID varchar(max)  
    declare @Name varchar(max) 
    declare @UserName varchar(max) 
    declare @Recipients varchar(max) 
    declare @SubscriptionID varchar(max) 
    declare @LastStatus varchar(max) 
    declare @Path varchar(max) 
    declare @LastRunTime datetime
    declare @xx varchar(max)
    -- Perform the first fetch.  
    FETCH NEXT FROM usesub INTO @ScheduleID,@Name,@UserName,@Recipients,@SubscriptionID,@LastStatus,@Path,@LastRunTime;  
    select @xx = @ScheduleID 
    USE msdb;  
    
    EXEC dbo.sp_start_job @xx;  
     
    
    -- Check @@FETCH_STATUS to see if there are any more rows to fetch.  
    WHILE @@FETCH_STATUS = 0  
    BEGIN  
       -- This is executed as long as the previous fetch succeeds.  
       FETCH NEXT FROM usesub;  
    END  
      
    CLOSE usesub;  
    DEALLOCATE usesub;  
    GO  ', 
    		@database_name=N'msdb', 
    		@flags=0
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'failed_subscription_job_restart', 
    		@enabled=1, 
    		@freq_type=4, 
    		@freq_interval=1, 
    		@freq_subday_type=1, 
    		@freq_subday_interval=0, 
    		@freq_relative_interval=0, 
    		@freq_recurrence_factor=0, 
    		@active_start_date=20190116, 
    		@active_end_date=20190116, 
    		@active_start_time=220000, 
    		@active_end_time=235959, 
    		@schedule_uid=N'f82f1342-642f-425d-bbc0-b9985212963e'
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    COMMIT TRANSACTION
    GOTO EndSave
    QuitWithRollback:
        IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
    EndSave:
    GO
    
    (seems need to run the query under the msdb database )

    You could modify the schedule on your demand.

    Hope it can help you.

    Best Regards,

    Eric Liu


    Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread.


    • Edited by Mitarai Queen Wednesday, January 16, 2019 7:44 AM
    • Proposed as answer by Mitarai Queen Friday, January 18, 2019 6:00 AM
    • Marked as answer by ShyamReddy Friday, January 18, 2019 6:09 AM
    Wednesday, January 16, 2019 7:43 AM

All replies

  • Hi ShyamReddy

    According to your description ,seems you want to use the tsql to re-start the failed sql agent jobs right ?

    If so , you could try the T-sql via cursor like below.

    USE ReportServer;  
    GO  
    DECLARE usesub CURSOR FOR  
    SELECT Rep.ScheduleID,
    Cat.[Name],
           Own.UserName,
           ISNULL(REPLACE(Sub.[Description], 'send e-mail to ', ''), ' ') AS Recipients, Sub.SubscriptionID,
           Sub.[LastStatus],
           Cat.[Path],
           Sub.[LastRunTime]
    FROM dbo.[Subscriptions] Sub WITH (NOLOCK)
        INNER JOIN dbo.[Catalog] Cat WITH (NOLOCK)
            ON Sub.[Report_OID] = Cat.[ItemID]
        INNER JOIN dbo.[ReportSchedule] Rep WITH (NOLOCK)
            ON (
                   Cat.[ItemID] = Rep.[ReportID]
                   AND Sub.[SubscriptionID] = Rep.[SubscriptionID]
               )
        INNER JOIN dbo.[Users] Own WITH (NOLOCK)
            ON Sub.[OwnerID] = Own.[UserID]
    WHERE Sub.[LastStatus] NOT LIKE '%was written%' --File Share subscription
          AND Sub.[LastStatus] NOT LIKE '%pending%' --Subscription in progress. No result yet
          AND Sub.[LastStatus] NOT LIKE '%mail sent%' --Mail sent successfully.
          AND Sub.[LastStatus] NOT LIKE '%New Subscription%' --New Sub. Not been executed yet
          AND Sub.[LastStatus] NOT LIKE '%been saved%' --File Share subscription
          AND Sub.[LastStatus] NOT LIKE '% 0 errors.' --Data Driven subscription
          AND Sub.[LastStatus] NOT LIKE '%succeeded%' --Success! Used in cache refreshes
          AND Sub.[LastStatus] NOT LIKE '%successfully saved%' --File Share subscription
          AND Sub.[LastStatus] NOT LIKE '%New Cache%' --New cache refresh plan
          AND Sub.[LastRunTime] > GETDATE() - 1
    ORDER BY Own.UserName
    
      
    OPEN usesub;  
    declare @ScheduleID varchar(max)  
    declare @Name varchar(max) 
    declare @UserName varchar(max) 
    declare @Recipients varchar(max) 
    declare @SubscriptionID varchar(max) 
    declare @LastStatus varchar(max) 
    declare @Path varchar(max) 
    declare @LastRunTime datetime
    declare @xx varchar(max)
    -- Perform the first fetch.  
    FETCH NEXT FROM usesub INTO @ScheduleID,@Name,@UserName,@Recipients,@SubscriptionID,@LastStatus,@Path,@LastRunTime;  
    select @xx = @ScheduleID 
    USE msdb;  
    
    EXEC dbo.sp_start_job @xx;  
     
    
    -- Check @@FETCH_STATUS to see if there are any more rows to fetch.  
    WHILE @@FETCH_STATUS = 0  
    BEGIN  
       -- This is executed as long as the previous fetch succeeds.  
       FETCH NEXT FROM usesub;  
    END  
      
    CLOSE usesub;  
    DEALLOCATE usesub;  
    GO  

    Reference : sp_start_job (Transact-SQL)

    Hope it can help you.

    Best Regards,

    Eric Liu


    Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread.

    Tuesday, January 15, 2019 5:52 AM
  • Hello Eric,

    Thank you for your response. I think you didn't understand my question correctly. 

    The above my T-SQL Script return the failed subscriptions on daily basis.

    I need a script(T-SQL) to go and kickoff the failed subscriptions. I would like to add that script as a SQL Server Agent Job and run on a daily basis. 



    Thanks, Shyam.

    Tuesday, January 15, 2019 4:24 PM
  • Hi ShyamReddy

    You could try to add the above query in a new sql agent job , in there you could aslo assign the schedule about the new sql agent job.

    You could add the sql agent job via ssms .

    Or you could just add the job by sql query .

    Below query are generate by the sql agent job which I create in ssms and then export it.

    USE [msdb]
    GO
    
    /****** Object:  Job [restart_failed_job]    Script Date: 16/01/2019 15:34:46 ******/
    BEGIN TRANSACTION
    DECLARE @ReturnCode INT
    SELECT @ReturnCode = 0
    /****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 16/01/2019 15:34:46 ******/
    IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
    BEGIN
    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    
    END
    
    DECLARE @jobId BINARY(16)
    EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'restart_failed_job', 
    		@enabled=1, 
    		@notify_level_eventlog=0, 
    		@notify_level_email=0, 
    		@notify_level_netsend=0, 
    		@notify_level_page=0, 
    		@delete_level=0, 
    		@description=N'restart failed ssrs subscription job at 10:00 pm', 
    		@category_name=N'[Uncategorized (Local)]', 
    		@owner_login_name=N'<serername>\<username>', @job_id = @jobId OUTPUT
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    /****** Object:  Step [step1]    Script Date: 16/01/2019 15:34:46 ******/
    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'step1', 
    		@step_id=1, 
    		@cmdexec_success_code=0, 
    		@on_success_action=1, 
    		@on_success_step_id=0, 
    		@on_fail_action=2, 
    		@on_fail_step_id=0, 
    		@retry_attempts=0, 
    		@retry_interval=0, 
    		@os_run_priority=0, @subsystem=N'TSQL', 
    		@command=N'USE ReportServer;  
    GO  
    DECLARE usesub CURSOR FOR  
    SELECT Rep.ScheduleID,
    Cat.[Name],
           Own.UserName,
           ISNULL(REPLACE(Sub.[Description], ''send e-mail to '', ''''), '' '') AS Recipients, Sub.SubscriptionID,
           Sub.[LastStatus],
           Cat.[Path],
           Sub.[LastRunTime]
    FROM dbo.[Subscriptions] Sub WITH (NOLOCK)
        INNER JOIN dbo.[Catalog] Cat WITH (NOLOCK)
            ON Sub.[Report_OID] = Cat.[ItemID]
        INNER JOIN dbo.[ReportSchedule] Rep WITH (NOLOCK)
            ON (
                   Cat.[ItemID] = Rep.[ReportID]
                   AND Sub.[SubscriptionID] = Rep.[SubscriptionID]
               )
        INNER JOIN dbo.[Users] Own WITH (NOLOCK)
            ON Sub.[OwnerID] = Own.[UserID]
    WHERE Sub.[LastStatus] NOT LIKE ''%was written%'' --File Share subscription
          AND Sub.[LastStatus] NOT LIKE ''%pending%'' --Subscription in progress. No result yet
          AND Sub.[LastStatus] NOT LIKE ''%mail sent%'' --Mail sent successfully.
          AND Sub.[LastStatus] NOT LIKE ''%New Subscription%'' --New Sub. Not been executed yet
          AND Sub.[LastStatus] NOT LIKE ''%been saved%'' --File Share subscription
          AND Sub.[LastStatus] NOT LIKE ''% 0 errors.'' --Data Driven subscription
          AND Sub.[LastStatus] NOT LIKE ''%succeeded%'' --Success! Used in cache refreshes
          AND Sub.[LastStatus] NOT LIKE ''%successfully saved%'' --File Share subscription
          AND Sub.[LastStatus] NOT LIKE ''%New Cache%'' --New cache refresh plan
          AND Sub.[LastRunTime] > GETDATE() - 1
    ORDER BY Own.UserName
    
      
    OPEN usesub;  
    declare @ScheduleID varchar(max)  
    declare @Name varchar(max) 
    declare @UserName varchar(max) 
    declare @Recipients varchar(max) 
    declare @SubscriptionID varchar(max) 
    declare @LastStatus varchar(max) 
    declare @Path varchar(max) 
    declare @LastRunTime datetime
    declare @xx varchar(max)
    -- Perform the first fetch.  
    FETCH NEXT FROM usesub INTO @ScheduleID,@Name,@UserName,@Recipients,@SubscriptionID,@LastStatus,@Path,@LastRunTime;  
    select @xx = @ScheduleID 
    USE msdb;  
    
    EXEC dbo.sp_start_job @xx;  
     
    
    -- Check @@FETCH_STATUS to see if there are any more rows to fetch.  
    WHILE @@FETCH_STATUS = 0  
    BEGIN  
       -- This is executed as long as the previous fetch succeeds.  
       FETCH NEXT FROM usesub;  
    END  
      
    CLOSE usesub;  
    DEALLOCATE usesub;  
    GO  ', 
    		@database_name=N'msdb', 
    		@flags=0
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'failed_subscription_job_restart', 
    		@enabled=1, 
    		@freq_type=4, 
    		@freq_interval=1, 
    		@freq_subday_type=1, 
    		@freq_subday_interval=0, 
    		@freq_relative_interval=0, 
    		@freq_recurrence_factor=0, 
    		@active_start_date=20190116, 
    		@active_end_date=20190116, 
    		@active_start_time=220000, 
    		@active_end_time=235959, 
    		@schedule_uid=N'f82f1342-642f-425d-bbc0-b9985212963e'
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    COMMIT TRANSACTION
    GOTO EndSave
    QuitWithRollback:
        IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
    EndSave:
    GO
    
    (seems need to run the query under the msdb database )

    You could modify the schedule on your demand.

    Hope it can help you.

    Best Regards,

    Eric Liu


    Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread.


    • Edited by Mitarai Queen Wednesday, January 16, 2019 7:44 AM
    • Proposed as answer by Mitarai Queen Friday, January 18, 2019 6:00 AM
    • Marked as answer by ShyamReddy Friday, January 18, 2019 6:09 AM
    Wednesday, January 16, 2019 7:43 AM
  • Thank you, Eric

    The above script works for me. 

    Thanks Again..!!


    Thanks, Shyam.


    • Edited by ShyamReddy Thursday, January 17, 2019 11:57 PM
    Thursday, January 17, 2019 11:56 PM
  • Hi ShyamReddy

    Seems  you have solved you issue . If your issue was solved ,you could mark the appreciate reply as answer ,so that other members in this forum could get benefit from it.

    Thanks for your support and understanding .

    Best Regards,

    Eric Liu


    Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread.

    Friday, January 18, 2019 5:59 AM