SSRS Subscription Creation steps

A report subscription is a report snapshot generated on some defined scheduled time and delivered to the intended viewers by the report server. 

SSRS allows us to create two types of subscriptions, e.g., standard subscriptions and data-driven subscription.

SSRS has provided two delivery extensions out of the box; the first one lets SSRS save the report subscription to a file share location whereas the second one sends the report in email to the specified recipients. There is one more extension; if you have SSRS installation in Share Point Integrated mode, which delivers the report to a Share Point document library.

Report Subscription

To setup subscription for a report, go to Report Manager, navigate to the folder that contains the report, point to the report for which you want to set up a subscription, click on the report as shown below:

On the report page, click on Subscriptions on the top

 This will show the options; New Subscription; Click on New Subscription

When you click on the New Subscription link on the page shown above, it will bring up a page with two parts. In the first part you can define the report delivery method and in the second part you can specify subscription processing options. The Report Delivery Options section will change depending on the delivery method you choose.

For example as you can see below, I have chosen a delivery method of E-Mail and hence it has options to specify TO, CC, BCC, subject line, whether to include the report as link or as an attachment, etc.

If i change the delivery method to Windows File Share, the fields on screen will change to let us specify the file name, share location, credential to use to copy the file at that location, overwrite options, etc.

The second part of the screen is used to define the subscription processing options. Generally, we can specify two settings here. First define a schedule for the subscription to process and second you specify the parameter values to be used during processing of the reports.

Once created, we can view the subscription and its execution details in the subscription detail page as shown below. You can click on the Edit hyperlink to modify the subscription or select a subscription and click on the Delete button to delete it.

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.

Sending Mail using SQL Server Express Edition

SQL Server express edition,We either use CLR integration or configure SQL Mail using MSDB system database.

Here I am discussing sending mail using MSDB system database.By default the MSDB database installed when we install SQL Server. The below tables used to confiure sysmail account.

SELECT *FROM msdb.dbo.sysmail_account

SELECT *FROM msdb.dbo.sysmail_configuration

SELECT *FROM msdb.dbo.sysmail_principalprofile

SELECT *FROM msdb.dbo.sysmail_profile

SELECT *FROM msdb.dbo.sysmail_profileaccount

SELECT *FROM msdb.dbo.sysmail_profileaccount

First of all enable Database Mail XPs through below code to configure database mail.

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'Database Mail XPs', 1;

GO

RECONFIGURE

GO

And then to configure SQL mail we need to follow below steps.

1. Use sysmail_add_account_sp stored procedure of MSDB database to configure sysmail account.

EXECUTE msdb.dbo.sysmail_add_account_sp

@account_name = 'DBA',

@description = 'Sent Mail using MSDB',

@email_address = 'raghwendra.mishra@spluspl.com',

@display_name = 'DBA',

@username='raghwendra.mishra@spluspl.com',

@password='Admin@123',

@mailserver_name = 'smtp.Your Mail server.com'

2. Use sysmail_add_profile_sp stored procedure of MSDB database to configure Database Profile.

EXECUTE msdb.dbo.sysmail_add_profile_sp

@profile_name = 'DBA',

@description = 'Profile used to send mail'

3. Use sysmail_add_profileaccount_sp stored procedure of MSDB database to map database mail account to Profile.

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

@profile_name = 'DBA',

@account_name = 'DBA',

@sequence_number = 1

4. To Grants permission for a database user or role to use a Database Mail profile use sysmail_add_principalprofile_sp

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

@profile_name = 'DBA',

@principal_name = 'public',

@is_default = 1 ;

5.   Send Mail using Created Profile.

exec msdb.dbo.sp_send_dbmail @profile_name = 'DBA', 

@recipients = 'raghwendra.mishra@spluspl.com', 

@subject = 'Mail Test', 

@body = 'Mail Sent Successfully', 

@body_format = 'text'

After executing above query check your email Inbox you will get mail like: