locked
rerun failed subscriptions RRS feed

  • Question

  • Hi All

    I have more than 200 data driven subscriptions. i am trying to create a SQL job that will automatically re-run all failed data driven subscriptions. See my code below.

     However I get the following error. Any help would be greatly appreciated.

    Msg 512, Level 16, State 1, Line 4

    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    Msg 14294, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 25

    Supply either @job_id or @job_name to identify the job.

    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) --sysname equivalent to nvarchar(128)
    		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%')
    )
    
    EXEC msdb..sp_start_job @job_name = @ScheduleId


     

     

    Wednesday, July 13, 2011 2:27 PM

Answers

  • Hi Avron,

    Thanks for your question. There is one case being ignored in your T-SQL query: if the @ScheduleId is NULL, you couldn’t execute the system procedure.

    I had added one estimate in the query, please try again with below T-SQL statement.

    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) --sysname equivalent to nvarchar(128)

                                    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 it helps you. If there is anything unclear, please feel free to let me know.

    Thanks,
    Sharp Wang


    Please remember to mark the replies as answers if they help you and unmark them if they provide no help.
    • Marked as answer by Avron Adams Friday, July 15, 2011 10:01 AM
    Friday, July 15, 2011 12:54 AM

All replies

  • Hi Avron,

    Thanks for your question. There is one case being ignored in your T-SQL query: if the @ScheduleId is NULL, you couldn’t execute the system procedure.

    I had added one estimate in the query, please try again with below T-SQL statement.

    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) --sysname equivalent to nvarchar(128)

                                    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 it helps you. If there is anything unclear, please feel free to let me know.

    Thanks,
    Sharp Wang


    Please remember to mark the replies as answers if they help you and unmark them if they provide no help.
    • Marked as answer by Avron Adams Friday, July 15, 2011 10:01 AM
    Friday, July 15, 2011 12:54 AM
  • Thanks Sharp. I was able to find a solution using a cursor, but yours is another. I will definately store this in my arsenal.

    Your help is greatly appreciated.

    Thanks

    Avron

    Friday, July 15, 2011 10:03 AM
  • Hello! I have done I data-driven subscription for a report. The subscription consists of sending about 4000 e-mail massages to different addresses with pdf reports. In table [ReportServer].[dbo].[Subscriptions] 'laststatus' field I see such information: "processed 4380, 243 errors". How can I get these errors? In [ReportServer].[dbo].[ExecutionLog2] view I can see only "rsSuccess" status for these reports (I think this status says about successful pdf rendering but not about email sending status). Thank you in advance.

    Monday, February 13, 2012 7:49 AM
  • vovikalex - This is what I am working on right now.

    First option: "Enable the remote errors" by going to SSMS. Look at the "dbo.ConfigInfo" table. Enable remote errors is set to false by default, update the table and set its value to true. There are 2 ways more, you can google them. Another way is, connect to "Reporting Services" in SSMS. Right click on the instance, go to "Advanced" and enable the remote errors there. Restart the reporting server and see the "LastStatus" column in dbo.Subscriptions table. (I hope it shows you the actual message instead of done 15 processed 10 errors:5)

    Second option: Follow this:

    http://mosshowto.blogspot.com/2009/03/reporting-services-2008-errors-check.html

    look at the log files in your SQL installation directory and look for the particular subscriptionid/jobname.

    Btw, did you ever find another solution for this?


    • Edited by SqlCraze Friday, August 17, 2012 2:29 PM
    Friday, August 17, 2012 2:18 PM