Simulate Reporting Services Data Driven Subscriptions on Unsupported Editions

By:   |   Comments (6)   |   Related: > 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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Monday, July 3, 2023 - 7:27:46 PM - JE Ramirez Back To Top (91358)
Daniel:

I am looking to update in a batch, the (//ParameterValue/Value[../Name="Comment"]), where the value is currently null with the following:

To unsubscribe from, <b>@ReportName</b>, <a href = "mailto: [email protected]?Subject= @ReportName" style="color:#FF0000;">Click Here</a>

I have manually added the above from the Report Server UI and it works fine. but with over 500+ reports, that's where the batch insert comes in.

Can I get some help with code?

Wednesday, May 27, 2020 - 2:23:28 PM - Cliff Wire Back To Top (85782)

I did something similar. My problem is where the user can't update the Extension settings or Parameters.

The server principal "DOMAIN\User" is not able to access the database "database" under the current security context.

And similar blocking issues. The above error is from

EXEC AS USER 'user'

EXEC storedProcedure

REVERT

I had a slightly different error when I: ExecuteSPNonQuery("spSendCutSheetToPurch", lstParams);

in the service side of my application

Sunday, November 17, 2019 - 8:49:41 AM - Sylvain Back To Top (83116)

Hi Daniel,

Thank you very much for sharing this tip.

I did something similar to send SSRS report only when there is some data. However when I insert carriage return in the email body and then update the ExtensionSettings column in table subscription, then the email is sent without the carriage return in the email content.

I tried multiple options but nothing works. Do you have an idea?

It's a bit frustrating as when you format email body from the SSRS screen, it works well.

Thanks.

Thursday, June 27, 2019 - 1:53:31 PM - Peter Back To Top (81613)

Hi, 1st off great article i have this issue in play, but a couple of questions behind this

1st off we have the report server on 1 server, and the data server are on another server.  So when i try to put the piece of code you state put in the SQL Agent it errors due to it will not allow Remote Procedure Calls.

So i actually have embedded the update subscriptions into the report directly.

But the issue i get is it updates and runs properly like every other record and update the report and every other report is blank.

Any thoughts

Tuesday, October 2, 2018 - 6:07:42 PM - dbaguy Back To Top (77793)

 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 (41238)

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

 

 














get free sql tips
agree to terms