September 25, 2008 at 1:04 pm
call 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 be
proactive 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!!
/**
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 load
fails.
The steps below describe how to achieve this in a simple manner
Written by Yussuf Khan 2008-09-25 while he had pnueomina! ;-) but he got the idea after reading a great article
on 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 below
USE YK_Staging
CREATE 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 schedule
5. 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? )
*/
September 26, 2008 at 4:34 am
You 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.
September 26, 2008 at 4:38 am
indeed you can, but can you get a dds to send out an error report if your load has failed? i don't think so..
March 24, 2010 at 4:54 am
Hi, 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.
🙂
March 24, 2010 at 12:48 pm
OK, 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 enjoy
EP
----********************* for SQL Server 2005+************************
--select @@version
SELECT
r.name as 'Report Name',
sub.[description] as 'Description',
j.name as 'JobName',
'Last Run Stat'= isnull(Casewhen 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 J
join ReportSchedule RS
on j.name = convert(sysname, rs.scheduleId)
join Subscriptions Sub
on RS.subscriptionId = sub.subscriptionId
join catalog R
on RS.ReportID = r.itemId
LEFT OUTER JOIN MSDB.dbo.sysjobschedules JS
ON J.job_id = JS.job_id
Left outer join msdb.dbo.sysschedules s
on JS.schedule_id = s.schedule_id
LEFT 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_id
Left outer join (select T0.job_id,T0.run_status,T0.run_date, T0.run_time,'Last Duration'=T0.run_duration from MSDB.dbo.sysjobhistory T0
inner join
(select job_id,'Instance_id'=max(instance_id) FROM MSDB.dbo.sysjobhistory where step_id=1 GROUP BY job_id ) T1
On
T0.job_id=T1.job_id and
T0.instance_id=T1.instance_id) Q2
ON j.job_id = Q2.job_id
WHERE Next_run_time = 0
--and path like '/Genesys%'
UNION
SELECT r.name as 'Report Name',
sub.[description] as 'Description',
j.name as 'JobName',
'Last Run Stat' = isnull(Casewhen 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 J
join ReportSchedule RS
on j.name = convert(sysname, rs.scheduleId)
join Subscriptions Sub
on RS.subscriptionId = sub.subscriptionId
join catalog R
on RS.ReportID = r.itemId
LEFT OUTER JOIN MSDB.dbo.sysjobschedules JS
ON j.job_id = JS.job_id
Left outer join msdb.dbo.sysschedules s
on JS.schedule_id = s.schedule_id
LEFT 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_id
Left outer join (select T0.job_id,T0.run_status, T0.run_date, T0.run_time,'Last Duration'=T0.run_duration from MSDB.DBO.sysjobhistory T0
inner join
(select job_id,'Instance_id'=max(instance_id) FROM MSDB.dbo.sysjobhistory where step_id=1 GROUP BY job_id ) T1
On
T0.job_id=T1.job_id and
T0.instance_id=T1.instance_id) Q2
ON j.job_id = Q2.job_id
WHERE Next_run_time <> 0
-- and path like '/Genesys%'
ORDER BY 1,4,5, 2
July 27, 2010 at 8:55 am
We 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 😛
joy!
July 29, 2010 at 7:55 am
Thanks, it is good to know that it still works in r2, we will continue using it.
🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply