SQLServerCentral.com / Reporting Services / Reporting Services / Simple Event driven subscriptions in SSRS / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 31 Mar 2015 15:22:43 GMT20RE: Simple Event driven subscriptions in SSRShttp://www.sqlservercentral.com/Forums/Topic576337-150-1.aspxThanks, it is good to know that it still works in r2, we will continue using it.:-)Thu, 29 Jul 2010 07:55:36 GMTAbs-225476RE: Simple Event driven subscriptions in SSRShttp://www.sqlservercentral.com/Forums/Topic576337-150-1.aspxWe are upgrading to SQL 2008 R2. I've just tried setting this up as per 2005 instructions above and it works just fine , no changes required at all :-Pjoy!Tue, 27 Jul 2010 08:55:47 GMTnahk.fussuyRE: Simple Event driven subscriptions in SSRShttp://www.sqlservercentral.com/Forums/Topic576337-150-1.aspxOK, take the code below and modify it to dump everything into a temp table. Then select jobName from the table where lastRunStat in ('cancelled' or 'failed') then issue a sp_start_job using the job name. It could also be used for sending out an alert email. Use at your own risk! and enjoyEP ----********************* for SQL Server 2005+************************--select @@versionSELECT r.name as 'Report Name', sub.[description] as 'Description', j.name as 'JobName', 'Last Run Stat'= isnull(Case when Q2.run_status = 1 then 'Succeeded' when Q2.run_status = 0 then 'Failed' when Q2.run_status = 2 then 'Retry' when Q2.run_status = 3 then 'Canceled' when Q2.run_status = 4 then 'Running' End,'NA'), 'Last Run Date' = isnull ( CASE q2.run_date WHEN 0 THEN 'N/A' ELSE substring(convert(varchar(15),q2.run_date),1,4) + '/' + substring(convert(varchar(15),q2.run_date),5,2) + '/' + substring(convert(varchar(15),q2.run_date),7,2) end, 'N/A'), 'Last Run Time' = isnull( CASE len(q2.run_time) WHEN 3 THEN cast('00:0' + Left(right(q2.run_time,3),1) +':' + right(q2.run_time,2) as char (8)) WHEN 4 THEN cast('00:' + Left(right(q2.run_time,4),2) +':' + right(q2.run_time,2) as char (8)) WHEN 5 THEN cast('0' + Left(right(q2.run_time,5),1) +':' + Left(right(q2.run_time,4),2) +':' + right(q2.run_time,2) as char (8)) WHEN 6 THEN cast(Left(right(q2.run_time,6),2) +':' + Left(right(q2.run_time,4),2) +':' + right(q2.run_time,2) as char (8)) END, 'NA'), 'Job Enab' = CASE J.Enabled WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END, 'Sched Enab' = CASE S.Enabled WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END, 'Sched Freq' = CASE s.freq_type WHEN 1 THEN 'Once' WHEN 4 THEN 'Daily' WHEN 8 THEN 'Weekly' WHEN 16 THEN 'Monthly' WHEN 32 THEN 'Monthly relative' WHEN 64 THEN 'When SQLServer Agent starts' END, 'Occurs'= s.freq_subday_interval,'Sub Freq Interval'= Case s.freq_subday_type when 4 then 'Minutes' When 8 then 'Hours' End, 'Start Date' = CASE active_start_date WHEN 0 THEN null ELSE substring(convert(varchar(15),active_start_date),1,4) + '/' + substring(convert(varchar(15),active_start_date),5,2) + '/' + substring(convert(varchar(15),active_start_date),7,2) END, 'Start Time' = CASE len(active_start_time) WHEN 3 THEN cast('00:0' + Left(right(active_start_time,3),1) +':' + right(active_start_time,2) as char (8)) WHEN 4 THEN cast('00:' + Left(right(active_start_time,4),2) +':' + right(active_start_time,2) as char (8)) WHEN 5 THEN cast('0' + Left(right(active_start_time,5),1) +':' + Left(right(active_start_time,4),2) +':' + right(active_start_time,2) as char (8)) WHEN 6 THEN cast(Left(right(active_start_time,6),2) +':' + Left(right(active_start_time,4),2) +':' + right(active_start_time,2) as char (8)) END, isnull(CASE len(Q1.run_duration) WHEN 1 THEN cast('00:00:0' + cast(Q1.run_duration as char) as char (8)) WHEN 2 THEN cast('00:00:' + cast(Q1.run_duration as char) as char (8)) WHEN 3 THEN cast('00:0' + Left(right(Q1.run_duration,3),1) +':' + right(Q1.run_duration,2) as char (8)) WHEN 4 THEN cast('00:' + Left(right(Q1.run_duration,4),2) +':' + right(Q1.run_duration,2) as char (8)) WHEN 5 THEN cast('0' + Left(right(Q1.run_duration,5),1) +':' + Left(right(Q1.run_duration,4),2) +':' + right(Q1.run_duration,2) as char (8)) WHEN 6 THEN cast(Left(right(Q1.run_duration,6),2) +':' + Left(right(Q1.run_duration,4),2) +':' + right(Q1.run_duration,2) as char (8)) END,'NA') as 'Avg Duration', isnull(CASE len(Q2.[Last Duration]) WHEN 1 THEN cast('00:00:0' + cast(Q2.[Last Duration] as char) as char (8)) WHEN 2 THEN cast('00:00:' + cast(Q2.[Last Duration] as char) as char (8)) WHEN 3 THEN cast('00:0' + Left(right(Q2.[Last Duration],3),1) +':' + right(Q2.[Last Duration],2) as char (8)) WHEN 4 THEN cast('00:' + Left(right(Q2.[Last Duration],4),2) +':' + right(Q2.[Last Duration],2) as char (8)) WHEN 5 THEN cast('0' + Left(right(Q2.[Last Duration],5),1) +':' + Left(right(Q2.[Last Duration],4),2) +':' + right(Q2.[Last Duration],2) as char (8)) WHEN 6 THEN cast(Left(right(Q2.[Last Duration],6),2) +':' + Left(right(Q2.[Last Duration],4),2) +':' + right(Q2.[Last Duration],2) as char (8)) END,'NA') as 'Last Duration'FROM MSDB.dbo.sysjobs Jjoin ReportSchedule RS on j.name = convert(sysname, rs.scheduleId)join Subscriptions Sub on RS.subscriptionId = sub.subscriptionIdjoin catalog R on RS.ReportID = r.itemIdLEFT OUTER JOIN MSDB.dbo.sysjobschedules JS ON J.job_id = JS.job_idLeft outer join msdb.dbo.sysschedules s on JS.schedule_id = s.schedule_idLEFT OUTER JOIN (SELECT job_id, avg(run_duration) AS run_duration FROM MSDB.dbo.sysjobhistory GROUP BY job_id) Q1 ON J.job_id = Q1.job_idLeft outer join (select T0.job_id,T0.run_status,T0.run_date, T0.run_time,'Last Duration'=T0.run_duration from MSDB.dbo.sysjobhistory T0inner join (select job_id,'Instance_id'=max(instance_id) FROM MSDB.dbo.sysjobhistory where step_id=1 GROUP BY job_id ) T1OnT0.job_id=T1.job_id andT0.instance_id=T1.instance_id) Q2ON j.job_id = Q2.job_idWHERE Next_run_time = 0--and path like '/Genesys%'UNIONSELECT r.name as 'Report Name', sub.[description] as 'Description', j.name as 'JobName', 'Last Run Stat' = isnull(Case when Q2.run_status = 1 then 'Succeeded' when Q2.run_status = 0 then 'Failed' when Q2.run_status = 2 then 'Retry' when Q2.run_status = 3 then 'Canceled' when Q2.run_status = 4 then 'Running' End,'NA'), 'Last Run Date' = isnull ( CASE q2.run_date WHEN 0 THEN 'N/A' ELSE substring(convert(varchar(15),q2.run_date),1,4) + '/' + substring(convert(varchar(15),q2.run_date),5,2) + '/' + substring(convert(varchar(15),q2.run_date),7,2) end, 'N/A'), 'Last Run Time' = isnull( CASE len(q2.run_time) WHEN 3 THEN cast('00:0' + Left(right(q2.run_time,3),1) +':' + right(q2.run_time,2) as char (8)) WHEN 4 THEN cast('00:' + Left(right(q2.run_time,4),2) +':' + right(q2.run_time,2) as char (8)) WHEN 5 THEN cast('0' + Left(right(q2.run_time,5),1) +':' + Left(right(q2.run_time,4),2) +':' + right(q2.run_time,2) as char (8)) WHEN 6 THEN cast(Left(right(q2.run_time,6),2) +':' + Left(right(q2.run_time,4),2) +':' + right(q2.run_time,2) as char (8)) END, 'NA'), 'Job Enab' = CASE j.Enabled WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END, 'Sched Enabled' = CASE s.Enabled WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END, 'Sched Freq' = CASE freq_type WHEN 1 THEN 'Once' WHEN 4 THEN 'Daily' WHEN 8 THEN 'Weekly' WHEN 16 THEN 'Monthly' WHEN 32 THEN 'Monthly relative' WHEN 64 THEN 'When SQLServer Agent starts' END, 'Occurs'=freq_subday_interval, 'Sub Freq Interval'= Case freq_subday_type when 4 then 'Minutes' When 8 then 'Hours' End, 'Start Date' = CASE next_run_date WHEN 0 THEN null ELSE substring(convert(varchar(15),next_run_date),1,4) + '/' + substring(convert(varchar(15),next_run_date),5,2) + '/' + substring(convert(varchar(15),next_run_date),7,2) END, 'Start Time' = isnull(CASE len(next_run_time) WHEN 3 THEN cast('00:0' + Left(right(next_run_time,3),1) +':' + right(next_run_time,2) as char (8)) WHEN 4 THEN cast('00:' + Left(right(next_run_time,4),2) +':' + right(next_run_time,2) as char (8)) WHEN 5 THEN cast('0' + Left(right(next_run_time,5),1) +':' + Left(right(next_run_time,4),2) +':' + right(next_run_time,2) as char (8)) WHEN 6 THEN cast(Left(right(next_run_time,6),2) +':' + Left(right(next_run_time,4),2) +':' + right(next_run_time,2) as char (8)) END,'NA'), isnull(CASE len(Q1.run_duration) WHEN 1 THEN cast('00:00:0' + cast(Q1.run_duration as char) as char (8)) WHEN 2 THEN cast('00:00:' + cast(Q1.run_duration as char) as char (8)) WHEN 3 THEN cast('00:0' + Left(right(Q1.run_duration,3),1) +':' + right(Q1.run_duration,2) as char (8)) WHEN 4 THEN cast('00:' + Left(right(Q1.run_duration,4),2) +':' + right(Q1.run_duration,2) as char (8)) WHEN 5 THEN cast('0' + Left(right(Q1.run_duration,5),1) +':' + Left(right(Q1.run_duration,4),2) +':' + right(Q1.run_duration,2) as char (8)) WHEN 6 THEN cast(Left(right(Q1.run_duration,6),2) +':' + Left(right(Q1.run_duration,4),2) +':' + right(Q1.run_duration,2) as char (8)) END,'NA') as 'Avg Duration', isnull(CASE len(Q2.[Last Duration]) WHEN 1 THEN cast('00:00:0' + cast(Q2.[Last Duration] as char) as char (8)) WHEN 2 THEN cast('00:00:' + cast(Q2.[Last Duration] as char) as char (8)) WHEN 3 THEN cast('00:0' + Left(right(Q2.[Last Duration],3),1) +':' + right(Q2.[Last Duration],2) as char (8)) WHEN 4 THEN cast('00:' + Left(right(Q2.[Last Duration],4),2) +':' + right(Q2.[Last Duration],2) as char (8)) WHEN 5 THEN cast('0' + Left(right(Q2.[Last Duration],5),1) +':' + Left(right(Q2.[Last Duration],4),2) +':' + right(Q2.[Last Duration],2) as char (8)) WHEN 6 THEN cast(Left(right(Q2.[Last Duration],6),2) +':' + Left(right(Q2.[Last Duration],4),2) +':' + right(Q2.[Last Duration],2) as char (8)) END,'NA') as 'Last Duration'FROM MSDB.dbo.sysjobs Jjoin ReportSchedule RS on j.name = convert(sysname, rs.scheduleId)join Subscriptions Sub on RS.subscriptionId = sub.subscriptionIdjoin catalog R on RS.ReportID = r.itemIdLEFT OUTER JOIN MSDB.dbo.sysjobschedules JSON j.job_id = JS.job_idLeft outer join msdb.dbo.sysschedules s on JS.schedule_id = s.schedule_idLEFT OUTER JOIN (SELECT job_id, avg(run_duration) AS run_duration FROM MSDB.dbo.sysjobhistory GROUP BY job_id) Q1ON j.job_id = Q1.job_idLeft outer join (select T0.job_id,T0.run_status, T0.run_date, T0.run_time,'Last Duration'=T0.run_duration from MSDB.DBO.sysjobhistory T0inner join (select job_id,'Instance_id'=max(instance_id) FROM MSDB.dbo.sysjobhistory where step_id=1 GROUP BY job_id ) T1OnT0.job_id=T1.job_id andT0.instance_id=T1.instance_id) Q2ON j.job_id = Q2.job_idWHERE Next_run_time &lt;&gt; 0 -- and path like '/Genesys%'ORDER BY 1,4,5, 2Wed, 24 Mar 2010 12:48:09 GMTEric PetersonRE: Simple Event driven subscriptions in SSRShttp://www.sqlservercentral.com/Forums/Topic576337-150-1.aspxHi, this works well in 2005 and is a fine distinction between data driven subscriptions as they are time based wheras with this script you can fire your reports based on the completion of your sql jobs.:-)Wed, 24 Mar 2010 04:54:21 GMTAbs-225476RE: Simple Event driven subscriptions in SSRShttp://www.sqlservercentral.com/Forums/Topic576337-150-1.aspxindeed you can, but can you get a dds to send out an error report if your load has failed? i don't think so..Fri, 26 Sep 2008 04:38:04 GMTnahk.fussuyRE: Simple Event driven subscriptions in SSRShttp://www.sqlservercentral.com/Forums/Topic576337-150-1.aspxYou can use data driven subscription which will not send email if your query output is blank. You can specify query and set up criteria in data driven subscription. It will not work in simple subcription.Fri, 26 Sep 2008 04:34:38 GMTdva2007Simple Event driven subscriptions in SSRShttp://www.sqlservercentral.com/Forums/Topic576337-150-1.aspxcall me pedantic but i've never really liked my current (2005) ssrs subscriptions. Being that you can only set them up to run at a defined time. What if your ssis load fails and there is no data for the report? I don't like sending out blank reports! also i'd like to inform support if this is the case by sending them a report that contains the error details so they can investigate, and why not while we are at it beproactive and mail the users telling them the load failed and my support minions are frantically scratching there shiny heads while looking into the problem. hey why not also empower the 'brighter' users to be able to subscribe to these events themselves, without adding any more admin on it poor old me!how to do that though? Am i just looking for shangri-la ? its not that much to ask is it?well see the below sql script with just a few 'comments' in it!! I've got it to work fine in 2005, maybe someone can try this in 2008 to see if it still works. if there is enough interest i may do up a proper article with pretty pictures and detailed step by step guide! or equally if I'm barking mad and there is an easier way to do this let me know! I know you could probably do something with notification services, but these keeps it all in sql and ssis.and of course if i've just reinvented a wheel that everyones already been using i'd like to know!![code]/**script for setting up SSRS 'event' driven schedules.so what is it?ssrs subscriptions work on a time basis ie they are triggered at certain times.this is troublesome when you are loading data via ssis and sending ssrs reports via subscriptions , as it is preferably to send out a subscription when the data loads ok, rather than at a fixed time also it would be nice to be able to send the error log report to support instead of the report if the data loadfails.The steps below describe how to achieve this in a simple mannerWritten by Yussuf Khan 2008-09-25 while he had pnueomina! ;-) but he got the idea after reading a great articleon sqlcentral that describes an even more powerful, flexible but way more complicated way to do this:http://www.sqlservercentral.com/articles/Development/2824/but i likes this way, its nice and easy*/--1. Setup Synonym's in your working dbs that point to Schedule table in the reportingserver you use , so you can use the same ssis code and sp between your dev/uat/prod environs.----for server called YKZBORBD1 with a default SSRS database on the same sql instance, and a db called YK_Staging run belowUSE YK_StagingCREATE SYNONYM [dbo].[ReportingScheduleTable] FOR [YKZBORBD1].[ReportServer].[dbo].[Schedule]--2. Create the below Stored proc in each working db (YK_Staging for example) CREATE PROC RunReportServerSchedule(@EventName AS NVARCHAR(1000)) as /* Written by Yussuf Khan 2008-08-25, your local sql guru Runs a ReportServer Shared Schedule called @EventName PreRequisites: The report server MUST be on the same server as this database/stored proc (unless you want to change the code below) A synonym setup called 'ReportingScheduleTable' to point to the schedule table in your report server database. It Will fail if the @EventName is not setup as a shared schedule in your SSRS server */ DECLARE @myScheduleID AS NVARCHAR(1000) --ReportingScheduleTable is a Synomn used to reference the schedule table in the reporting services database. SELECT @myScheduleID=s.ScheduleID FROM dbo.ReportingScheduleTable AS s WHERE NAME = @EventName--ok now we have the schedule--now we have the scheduleID its the same name as the sql agent job --(and all reports for the shared schedule get added/removed to this job by ssrs! sweeeetness), --so lets run it baby EXEC msdb..sp_start_job @job_name =@myScheduleID/*3. Go onto the Reporting Server (as user with admin rights), and create new 'shared schedules' for your events I'd suggest a naming convention something along these lines : WhenMRSLoadCompletedOK WhenMRSLoadCompletedWithErrors where MRS is the system and LoadCompletedOK (or LoadCompletedWithErrors) is the specific event set the schedule as a once off one for a time in the past (so won't ever get triggered!)4. Now for the report setup your data driven or normal subscription and set it to run on the shared schedule basis you require eg. for the report subscribe to the WhenMRSLoadCompletedOK event, for your error report (which just lists your 'logentries.*') which will have your errors in it, subscribe to the WhenMRSLoadCompletedWithErrors shared schedule5. Now in your ssis packages at end of your dataload processing do some conditional logic to check if the load is ok, as we have an onError event in all our packages that writes a 'fatal Error' message to a 'LogEntries' table, we can check the log and if no fatal errors you can run: --exec RunReportServerSchedule 'WhenMRSLoadCompletedOK'.if errors runs the error schedule. ta da. job done --exec RunReportServerSchedule 'WhenMRSLoadCompletedWithErrors'You can now have event driven subscriptions. Good as normal users can use these events to subscribe to reports themselves in ssrs sono mundane admin! if your users have any cop on that is!Tested with SQL server 2005 SSRS and SSIS SP2. *********Remember SSIS OnError event does not get triggered if your package fails to validate!! this can happen when remote server connections fail (or are just too slow to respond) a common one for me as some of the data servers are on slow connections the other side of europe or someones messed big time with your DDL. You won't get an error report as the package just won't run at all. Still trying to find a good solution for that scenerio! (maybe another sql job that runs x mins after dataload job(which runs at y) and if no 'dataload started' message in log since y then send an email? )*/[/code]Thu, 25 Sep 2008 13:04:01 GMTnahk.fussuy