Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
The Trade-off Between SQL Server Security and Performance - Free Webinar
 

Simulate Reporting Services Data Driven Subscriptions on Unsupported Editions


By:   |   Last Updated: 2016-04-15   |   Comments (2)   |   Related Tips: > Reporting Services Development

Problem

You want to take advantage of the SQL Server Reporting Services Data Driven Subscriptions, but itís not supported in your edition of Reporting Services. In this tip I will show you that with a little scripting work you can have the same functionality.

Solution

If we are planning to emulate the behavior of the SQL Server Reporting Services Data Driven Subscriptions first we need to identify the job that fires the report execution. For those of you who donít know, Reporting Services subscriptions are processed as SQL Server Agent Jobs. Unfortunately the Job names arenít descriptive about the subscription per se, but we can translate the Job name with a simple query.

use ReportServer
GO
SELECT  a.Path,
		a.Name,
		b.ScheduleID,
		a.Description
FROM    dbo.Catalog a
        INNER JOIN dbo.ReportSchedule b ON b.ReportID = a.ItemID;

Now that we have identified the SQL Server Agent Job responsible for the subscription letís take a look at what it does. As we can see in the next query, there is one single statement that runs a stored procedure named AddEvent. This stored procedure receives two parameters: @EventType to specify what kind of event we are adding to the queue and @EventData that in this case is the SubscriptionID column of the Subscription table in the ReportServer database.

exec [ReportServer].dbo.AddEvent @EventType='TimedSubscription',
 @EventData='089d8744-bbf2-4734-8a71-392dd956121b'

The Subscription table contains all the subscription configuration settings. There are three columns that we must focus on if we want to emulate a Data Driven Subscription. These columns are of type NTEXT, but contains XML data. In the next sections I will describe them and show an example of the XML data.

ExtensionSettings

This column contains the main subscription settings, like the report render format, email recipients list or the file share path, depending on the delivery extension used.

<ParameterValues>
	<ParameterValue>
	<Name>TO</Name>
	<Value>Daniel</Value>
	</ParameterValue>
	<ParameterValue>
	<Name>IncludeReport</Name>
	<Value>True</Value>
	</ParameterValue>

	<ParameterValue>
	<Name>RenderFormat</Name>
	<Value>MHTML</Value>
	</ParameterValue>
	
	<ParameterValue>
	<Name>Subject</Name>
	<Value>@ReportName executed at @ExecutionTime</Value>
	</ParameterValue>
	
	<ParameterValue>
	<Name>IncludeLink</Name>
	<Value>True</Value>
	</ParameterValue>
	
	<ParameterValue>
	<Name>Priority</Name>
	<Value>NORMAL</Value>
	</ParameterValue>
</ParameterValues>

Parameters

This section contains the Reportís input parameters.

<ParameterValues>
	<ParameterValue>
	<Name>version_string</Name>
	<Field>VERSION</Field>
	</ParameterValue>
</ParameterValues>

DataSettings

This is the column that contains the Data Driven subscription settings like the query definition and the results mapping.

<DataSet>
	<Fields>
		<Field>
			<Alias>TO</Alias>
			<Name>TO</Name>
		</Field>
	</Fields>
	<Query>
		<CommandType>Text</CommandType>
		<CommandText>SELECT  TOP 1 '[email protected] ' AS [TO]
		FROM    sys.dm_exec_requests
		WHERE   blocking_session_id > 0
		</CommandText>
		<Timeout>30</Timeout>
	</Query>
	<CaseSensitivity>False</CaseSensitivity>
	<AccentSensitivity>False</AccentSensitivity>
	<KanatypeSensitivity>False</KanatypeSensitivity>
	<WidthSensitivity>False</WidthSensitivity>
</DataSet>

As you may guess, in order to simulate a Data Driven Subscription you must dynamically change the values of the ExtensionSettings and Parameters columns. We donít need to change the value of DataSettings column because if our edition of Reporting Services doesnít support Data Driven subscriptions, the report server will omit its value.

Sample

To show how to emulate a Data Driven Subscription I will use my previous tip Automatically Send Performance Dashboard Reports during a database event using Reporting Services.

After you create a standard subscription to the report and identify the SQL Server Agent Job name that fires the report execution with the first query on this tip, you have to change the jobís step code to make it only process the report when there is a blocking session on the SQL Server instance. The following query is one way we can achieve that.

IF EXISTS ( SELECT  0
  FROM    sys.dm_exec_requests
  WHERE   blocking_session_id > 0 )
  BEGIN 
  EXEC [ReportServer].dbo.AddEvent @EventType = 'TimedSubscription',
  @EventData = 'a0d292d9-3776-4bc0-be0e-8c71502d5e73'
END

Now suppose you have a products catalog report that you want to send to your customers in New York. With a Data Driven subscription you can use a function that returns the email addresses of your customers. Because the number of customers, therefore email recipients, can change over time, see how we can incorporate the same functionality with an edition that doesnít include this feature.

To change the email recipient dynamically, we must copy the value of ExtensionSettings column of the Subscriptions table for the report to be subscribed. The following query will get us that information.

use ReportServer
GO

SELECT  Description ,
        CAST(ExtensionSettings AS XML) ExtensionSettings ,
        CAST(Parameters AS XML) Parameters ,
        CAST(DataSettings AS XML) DataSettings
FROM    dbo.Subscriptions

I created the following scalar function in the AdventureWorks database that returns the email address of the persons from New York. If you don't have a copy of the AdventureWorksDW2012 database, you can download it for free from CodePlex at this link http://msftdbprodsamples.codeplex.com/releases/view/55330.

USE AdventureWorks2012
go
CREATE FUNCTION GetPersonEmail ( )
RETURNS VARCHAR(100)
AS
    BEGIN
        DECLARE @Out VARCHAR(1000) 

        SELECT  @out = SUBSTRING(( SELECT TOP ( 10 )
                    ';' + e.EmailAddress AS [text()]
           FROM     Person.Person P
                    INNER JOIN Person.EmailAddress E ON E.BusinessEntityID = P.BusinessEntityID
                    INNER JOIN Person.BusinessEntityAddress BEA ON BEA.BusinessEntityID = P.BusinessEntityID
                    INNER JOIN Person.Address ADDR ON ADDR.AddressID = BEA.AddressID
                    INNER JOIN [Person].[StateProvince] ST ON ST.StateProvinceID = ADDR.StateProvinceID
           WHERE    ST.StateProvinceCode = 'NY'
                    AND ST.CountryRegionCode = 'US'
         FOR
           XML PATH('')
         ), 2, 1000)  
        RETURN @out

    END

After copying the data of ExtensionSettings we have to edit the job step in SQL Server Agent and add a bit of logic to the code. The next query is to replace the job step code. As you can see I do an update to the ExtensionSettings column of the Subscriptions table with the concatenation of the value we have previously copied with the results returned by the function.

  UPDATE  ReportServer.dbo.Subscriptions
  SET     ExtensionSettings = '<ParameterValues>
  <ParameterValue>
  <Name>TO</Name>
  <Value>'
  + CAST(AdventureWorks2012.dbo.GetPersonEmail() COLLATE Latin1_General_CI_AS_KS_WS AS VARCHAR(100))
  + '</Value>
  </ParameterValue>
  <ParameterValue>
  <Name>IncludeReport</Name>
  <Value>True</Value>
  </ParameterValue>
  <ParameterValue>
  <Name>RenderFormat</Name>
  <Value>MHTML</Value>
  </ParameterValue>
  <ParameterValue>
  <Name>Subject</Name>
  <Value>@ReportName executed at @ExecutionTime</Value>
  </ParameterValue>
  <ParameterValue>
  <Name>IncludeLink</Name>
  <Value>True</Value>
  </ParameterValue>
  <ParameterValue>
  <Name>Priority</Name>
  <Value>NORMAL</Value>
  </ParameterValue>
  </ParameterValues>'
  WHERE   SubscriptionID = 'A0D292D9-3776-4BC0-BE0E-8C71502D5E73'
  
EXEC [ReportServer].dbo.AddEvent @EventType = 'TimedSubscription',
  @EventData = 'a0d292d9-3776-4bc0-be0e-8c71502d5e73'
Next Steps


Last Updated: 2016-04-15


next webcast button


next tip button



About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, October 02, 2018 - 6:07:42 PM - dbaguy Back To Top

 thanks, this was perfect!

I used it for a statement mailout where the recipient email address, and the report parameter change depo on the customer. I created one subscription on the report i wanted, then executed it from a loop, updating the Subscriptions ExtensionSettings and Parameters inside the loop, running the AddEvent to kickoff the report, then WAITFOR 15secs for the report to render before executing the loop again. I couldve tried to serialize it with an entry in the Notifications table (or even the LastStatus in Subscriptions) but i found the loop executed before the entry shows up in Notifications...  

 

declare @i int,
             @rows int

declare @tab1 table (id int identity(1,1) primary key clustered not null,
                     cust_no int not null,
                     email_address nvarchar(255) not null)

insert @tab1
select distinct cust_no, [customer email] from otherdatabbase.dbo.statements order by 1
select @rows = @@rowcount

select @i = 1

while @i <= @rows
begin

     update Subscriptions
     set ExtensionSettings = '<ParameterValues><ParameterValue><Name>TO</Name><Value>' + email_address +
                        '</Value></ParameterValue><ParameterValue><Name>IncludeReport</Name><Value>True</Value></ParameterValue><ParameterValue><Name>RenderFormat</Name><Value>PDF</Value></ParameterValue><ParameterValue><Name>Subject</Name><Value>@ReportName was executed at @ExecutionTime</Value></ParameterValue><ParameterValue><Name>IncludeLink</Name><Value>False</Value></ParameterValue><ParameterValue><Name>Priority</Name><Value>NORMAL</Value></ParameterValue></ParameterValues>',
         Parameters = '<ParameterValues><ParameterValue><Name>custno</Name><Value>' + convert(nvarchar(10),cust_no) + '</Value></ParameterValue></ParameterValues>'
     from @tab1 where id = @i  and Subscriptions.SubscriptionID = '6BAFF76C-E514-49FE-86FB-AE77B62C21C0' 

     exec ReportServer.dbo.AddEvent @EventType = 'TimedSubscription', @EventData = '6BAFF76C-E514-49FE-86FB-AE77B62C21C0'
     waitfor delay '00:00:15'
    
     select @i = @i+1
end


Friday, April 15, 2016 - 8:41:28 AM - Kris Maly Back To Top

Awesome.

Such article are very good.

Appreciate for publishing such articles.

Try similar for other things in SQL Server.

I enjoyed reading this article and appreciate your volunteership. Please keep doing what your doing and don't giveup this is helping community.

 

Thanks a lot

 

 


Learn more about SQL Server tools