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

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.

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