SSRS Subscriptions Log/Alert

Subscriptions in SQL Reporting Services enable you to configure the automatic delivery of specified reports by e-mail or to a file share at scheduled intervals.

After subscribing the reports reporting service creates a time based job which executes as per scheduled time.However when job executes it doesn't means that you subscription executed successful.

If you have subscribed the report which is having million of records it may take time to deliver the reports at the destination and some time it get failed without any notification,

Whereas you may have see that jobs(in SQL Agent) executed successfully within a second.

To avoid such situation below script will help you to get notify whether the subscribed report delivered to the destination path without any error or not.

Declare @ServerIP VARCHAR(100) =@@SERVERNAME 

Declare @Recepients VARCHAR(2000)='raghwendra.mishra@spluspl.com'

Declare @MailProfile VARCHAR(100)='Capex'

Declare @strSubject VARCHAR(100)='Subscription Report Status on '+@@SERVERNAME+ ' as on '+CONVERT(VARCHAR(50),GETDATE())

Declare @TableHTML varchar(MAX)

SELECT

@TableHTML ='

<p style="margin-top: 0; margin-bottom: 0"><font face="Verdana" size="4">Subscription Status</font></p>' +   

'<table style="BORDER-COLLAPSE: collapse" borderColor="#111111" cellPadding="0"  bgColor="#ffffff" borderColorLight="#000000" border="1"> 

<tr> 

<th align="Center"  bgColor="#000080"> <font face="Verdana" size="1" color="#FFFFFF">Instance Name</font></th> 

<th align="Center"  bgColor="#000080"> <font face="Verdana" size="1" color="#FFFFFF">Report Path</font></th> 

<th align="Center"  bgColor="#000080"> <font face="Verdana" size="1" color="#FFFFFF">Date Start</font></th> 

<th align="Center"  bgColor="#000080"> <font face="Verdana" size="1" color="#FFFFFF">Time Start</font></th> 

<th align="Center"  bgColor="#000080"> <font face="Verdana" size="1" color="#FFFFFF">DataRetrieval Duration</font></th> 

<th align="Center"  bgColor="#000080"> <font face="Verdana" size="1" color="#FFFFFF">DateProcessing Duration</font></th> 

<th align="Center"  bgColor="#000080"> <font face="Verdana" size="1" color="#FFFFFF">DataRendering Duration</font></th> 

<th align="Center"  bgColor="#000080"> <font face="Verdana" size="1" color="#FFFFFF">Time End</font></th> 

<th align="Center"  bgColor="#000080"> <font face="Verdana" size="1" color="#FFFFFF">Execution Time</font></th> 

<th align="Center"  bgColor="#000080"> <font face="Verdana" size="1" color="#FFFFFF">Ownar</font></th> 

<th align="Center"  bgColor="#000080"> <font face="Verdana" size="1" color="#FFFFFF">Status</font>

</th>   

</tr>' 

SELECT

@TableHTML = @TableHTML +

'<tr>

<td><font face="Verdana" size="1">' + el2.InstanceName +'</font></td>' +

'<td><font face="Verdana" size="1">' + el2.ReportPath + '</font></td>' +  

'<td><font face="Verdana" size="1">' + convert(varchar(12),el2.TimeStart)+'</font></td>'+ 

'<td><font face="Verdana" size="1">' + convert(varchar(12),el2.TimeStart,108) + '</font></td>'+

'<td><font face="Verdana" size="1">' + case When  TimeDataRetrieval=0 Then '-'else (

right('00' + convert(varchar(2), convert(int, TimeDataRetrieval/(1000*60*60))), 2) +':'+

right('00' + convert(varchar(2), convert(int, (TimeDataRetrieval%(1000*60*60))/(1000*60) )), 2)+':'+

right('00' + convert(varchar(2), convert(int, ((TimeDataRetrieval%(1000*60*60))%(1000*60))/1000)), 2)) end + '</font></td>'+

'<td><font face="Verdana" size="1">' + case When  TimeProcessing=0 Then '-'else (

right('00' + convert(varchar(2), convert(int, TimeProcessing/(1000*60*60))), 2) +':'+

right('00' + convert(varchar(2), convert(int, (TimeProcessing%(1000*60*60))/(1000*60) )), 2)+':'+

right('00' + convert(varchar(2), convert(int, ((TimeProcessing%(1000*60*60))%(1000*60))/1000)), 2))END + '</font></td>'+

'<td><font face="Verdana" size="1">' + case When  TimeRendering=0 Then '-'else (

right('00' + convert(varchar(2), convert(int, TimeRendering/(1000*60*60))), 2) +':'+

right('00' + convert(varchar(2), convert(int, (TimeRendering%(1000*60*60))/(1000*60) )), 2)+':'+

right('00' + convert(varchar(2), convert(int, ((TimeRendering%(1000*60*60))%(1000*60))/1000)), 2)) end + '</font></td>'+

'<td><font face="Verdana" size="1">' + convert(varchar(12),el2.TimeEnd,108) +'</font></td>' +

'<td><font face="Verdana" size="1">' + CONVERT(VARCHAR, el2.TimeEnd-el2.TimeStart, 8) + '</font></td>' +

'<td><font face="Verdana" size="1">' + el2.UserName + '</font></td>' +  

CASE  el2.[Status] 

WHEN 'rsSuccess' THEN

   '<td bgcolor="#CEE685"><b><font face="Verdana" size="1">'+ el2.[Status] +'</font></b></td>' 

    ELSE

'<td bgcolor="#FF8282"><b><font face="Verdana" size="1">'+ el2.[Status] +'</font></b></td>'

        END  + '</tr>'  

FROM ReportServer..ExecutionLog2 el2

WHERE el2.ReportPath = '/Reports/DUMP004'

AND el2.TimeStart > CONVERT(VARCHAR, GETDATE(), 101)

ORDER BY el2.TimeStart DESC

SELECT

@TableHTML = @TableHTML + '</table>'+  

'<p style="margin-top: 0; margin-bottom: 0"> </p>

<hr color="#000000" size="1">

<p style="margin-top: 0; margin-bottom: 0"><font face="Verdana" size="2">Regards,</font></p> 

<p style="margin-top: 0; margin-bottom: 0"><font face="Verdana" size="2">InVision DBA</font></p>' 

EXEC msdb.dbo.sp_send_dbmail 

@profile_name = @MailProfile,   

@recipients=@Recepients,

@subject = @strSubject,   

@body = @TableHTML,

@body_format = 'HTML'   

Following things to be done:

1- Identify the duration of Subscription execution duration.

2-Create another job with provided script and schedule it after subscription--if possible add 1 step in daily monitoring job (If applicable).

3- change mail profile and send recipient from script.

About the author:

Raghawendra Mishra is a consultant in Systems Plus Pvt. Ltd. Within Systems Plus, he actively contributes to the areas of Technology and Information Security. He can be contacted at raghwendra.mishra@spluspl.com