none
How to rerun the failed report subscription.

    Question

  • Hi,

    Is there any way to rerun the report subscription jobs based on job status.

    Version: SSRS 2008

    Regards,


    Vaishu

    Tuesday, December 17, 2013 3:45 PM

Answers

  • Hi Vaishu,

    As you'd expect, there’s a table in the ReportServer database called dbo.Subscriptions, where LastStatus of the Subscription is stored. And based on ScheduledId column which is connected to Job, we can use T-SQL statement to find the subscription which is failed, and then execute the correspond jobs.

    In order to return the failed subscriptions, please refer to the following query in SQL Server Management Studio.

    DECLARE @ScheduleId NVARCHAR (50)
    SET @ScheduleId = (SELECT rs.ScheduleID FROM ReportServer.dbo.Catalog c WITH(NOLOCK) INNER JOIN ReportServer.dbo.Subscriptions sub WITH(NOLOCK) ON (c.ItemID = sub.Report_OID) INNER JOIN ReportServer.dbo.ReportSchedule rs WITH(NOLOCK) ON (c.ItemID = rs.ReportID AND sub.SubscriptionID = rs.SubscriptionID) INNER JOIN ReportServer.dbo.Schedule sch WITH(NOLOCK) ON (rs.ScheduleID = sch.ScheduleID) INNER JOIN msdb.dbo.sysjobs sj WITH(NOLOCK) ON (cast(rs.ScheduleID as sysname)=sj.name) INNER JOIN msdb.dbo.sysjobschedules sjs WITH(NOLOCK) ON (sj.job_id = sjs.job_id) INNER JOIN msdb.dbo.sysschedules ss WITH(NOLOCK) ON (sjs.schedule_id = ss.schedule_id) WHERE (sub.LastStatus = 'Done: 1 processed of 1 total; 1 errors.' OR sub.LastStatus LIKE 'Failure sending mail%') ) if isnull(@ScheduleId,'')<>'' begin EXEC msdb..sp_start_job @job_name = @ScheduleId end

    Hope this helps.

    Thanks,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    Wednesday, December 18, 2013 7:17 AM
    Moderator

All replies

  • Hi Vaishu,

    As you'd expect, there’s a table in the ReportServer database called dbo.Subscriptions, where LastStatus of the Subscription is stored. And based on ScheduledId column which is connected to Job, we can use T-SQL statement to find the subscription which is failed, and then execute the correspond jobs.

    In order to return the failed subscriptions, please refer to the following query in SQL Server Management Studio.

    DECLARE @ScheduleId NVARCHAR (50)
    SET @ScheduleId = (SELECT rs.ScheduleID FROM ReportServer.dbo.Catalog c WITH(NOLOCK) INNER JOIN ReportServer.dbo.Subscriptions sub WITH(NOLOCK) ON (c.ItemID = sub.Report_OID) INNER JOIN ReportServer.dbo.ReportSchedule rs WITH(NOLOCK) ON (c.ItemID = rs.ReportID AND sub.SubscriptionID = rs.SubscriptionID) INNER JOIN ReportServer.dbo.Schedule sch WITH(NOLOCK) ON (rs.ScheduleID = sch.ScheduleID) INNER JOIN msdb.dbo.sysjobs sj WITH(NOLOCK) ON (cast(rs.ScheduleID as sysname)=sj.name) INNER JOIN msdb.dbo.sysjobschedules sjs WITH(NOLOCK) ON (sj.job_id = sjs.job_id) INNER JOIN msdb.dbo.sysschedules ss WITH(NOLOCK) ON (sjs.schedule_id = ss.schedule_id) WHERE (sub.LastStatus = 'Done: 1 processed of 1 total; 1 errors.' OR sub.LastStatus LIKE 'Failure sending mail%') ) if isnull(@ScheduleId,'')<>'' begin EXEC msdb..sp_start_job @job_name = @ScheduleId end

    Hope this helps.

    Thanks,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    Wednesday, December 18, 2013 7:17 AM
    Moderator
  • I trying to use this code but Im getting the following error:

    Msg 512, Level 16, State 1, Line 2
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    Can you help me, please? Thank you.

    Thursday, April 17, 2014 4:15 PM
  • Hi Vaishu, I wrote this sp to make the job on our server. Hope this help people with the same problem.

     

    USE [ReportServer]
    GO
    -- ================================================
    -- Template generated from Template Explorer using:
    -- Create Procedure (New Menu).SQL
    --
    -- Use the Specify Values for Template Parameters 
    -- command (Ctrl-Shift-M) to fill in the parameter 
    -- values below.
    --
    -- This block of comments will not be included in
    -- the definition of the procedure.
    -- ================================================
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author: Roberto Barcia
    -- Create date: 04/17/2014
    -- Description: Subscription failed control and re-running
    -- =============================================
    ALTER PROCEDURE [dbo].[sp_FailedSubscription]
    -- Add the parameters for the stored procedure here
    /* 
    -- How to test this store procedure
    EXEC [dbo].[sp_FailedSubscription]
    */

    AS
    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Create temp table, control if exist
    IF OBJECT_ID('tempdb..#Failed') IS NOT NULL 
    BEGIN 
      DROP TABLE #Failed
    END
    CREATE TABLE #Failed (id int, NewJob nvarchar(100))

    -- Insert Failed subscription into the temp table
    INSERT INTO  #Failed (id, NewJob)
    SELECT DISTINCT ROW_NUMBER() OVER(order BY j.Name) AS id, 
          'EXEC [msdb].[dbo].[sp_start_job] @job_name = ''' + CAST(j.Name AS varchar(40)) + ''''  AS NewJob
    FROM  msdb.dbo.sysjobs j  
    JOIN  msdb.dbo.sysjobsteps js ON js.job_id = j.job_id 
    JOIN  [ReportServer].[dbo].[Subscriptions] s  ON js.command LIKE '%' + CAST(s.subscriptionid AS varchar(40)) + '%' 
    WHERE s.LastStatus LIKE 'Failure sending mail%';

    -- VAR Declaration
    DECLARE @TotalFailed int = (SELECT COUNT(*) FROM #Failed)
    DECLARE @CurrentFailed int = 1
    DECLARE @FailedId int
    DECLARE @SQL varchar(1000)

    -- Executing Failed Subscription again
    WHILE @CurrentFailed <  @TotalFailed + 1          
    BEGIN 
     -- Get datta
     SET @FailedId = (SELECT Id FROM #Failed WHERE Id = @CurrentFailed)
     SET @SQL = (SELECT NewJob FROM #Failed WHERE Id = @CurrentFailed)
     -- Execute job
     EXEC (@SQL)
     -- test 
     -- PRINT @SQL

     -- next one
     SET @CurrentFailed = @CurrentFailed +1
    END
        -- test
    -- PRINT @TotalFailed
    -- SELECT * FROM #Failed

    -- Drop temp file
    DROP TABLE #Failed
    END
    GO



    • Edited by IlCapo Thursday, April 17, 2014 11:55 PM
    Thursday, April 17, 2014 11:45 PM