Data Driven Subscriptions without SQL Enterprise
-
Introduction
Do you have SQL Reports that need to go to different people but each person's report has different parameters? Don't have the resources to purchase the Enterprise edition of SQL 2000? The only solution available in Standard edition, besides writing your own front end in .NET was to create a subscription for each unique report, person and parameter. After only a short amount of time, you've got a ton of subscriptions to manage.
Well, that's the situation I was in, and it was quickly becoming a nightmare managing all of the subscription change requests. I started thinking "Reporting Services is just a regular ol' database with an ASP.NET front end. There's got to be a way to do it..."
Well, EURIKA! It seems the subscription information is stored in a table named, of all things "Subscriptions" and the parameter information is stored in, Yep, "Parameters". ... Those tricky Microsoft guys!
Anyway I wrote a stored procedure that will look for predefined text in the subscription and replace it with what you supply. It may not be as pretty as the version in SQL Enterprise, but this one gets the job done, and it is very useful!
I have not included any error checking and currently this SP only allows for one parameter. Feel free to modify the code as you see fit. Any suggestions or comments are welcome, email me.
Thank you and I hope this helps or at least gives you ideas on where to go next.
Instructions
- Create the stored procedure. Shown below.
- Create a new subscription for the report that you want to set up the data driven subscription for. Use the following settings:
- Now set the schedule of the report to run once.
- Set the beginning date and ending date to a date prior to today to keep the report from running unless you call it.
- In Enterprise Manager find the job you just created. It will have a Last Run Status of “Unknown” and the Next Run Date will be “Date and time are not available”. This job's name is what you’ll need to pass to the stored procedure as the Schedule ID. You can also run the query below to list the Job Names directly from the Report Server Database. Your job should be the first one listed.
- To execute the subscription, simply call the stored procedure passing the parameter values.
To Address: |TO| (pipe + TO + pipe)
Carbon Copy Address: |CC|
Blind Copy Address: |BC|
Reply To Address: |RT|
Comment / Body: |BD|
Parameter 1: |P1|
SELECT ReportSchedule.ScheduleID, Subscriptions.ModifiedDate
FROM Subscriptions INNER JOIN
ReportSchedule ON Subscriptions.SubscriptionID = ReportSchedule.SubscriptionID
WHERE (Subscriptions.Description LIKE N'%|TO|%')
ORDER BY Subscriptions.ModifiedDate DESC
Conclusion
Now you are ready to call your procedure from within your code.
Again, any comments, suggestions or improvements are welcome, email me.
Stored Procedure
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/*
PROCEDURE DESCRIPTION:
This procedure will replace the predefined fields for a SQL Reporting Services
Subscription allowing a "DATA DRIVEN SUBSCRIPTION"
INPUT:
@scheduleIDThe Job Name in SQL Server 2000
@emailTO The TO address of the email
@emailCC The Carbon Copy address of the email
@emailBCC The Blind Copy address of the email
@emailReplyTO The Reply TO address of the email
@emailBODY Any text that you want in the email body
@param1 The value of the parameter defined as P1 in the subscription
OUTPUT:
None
WRITTEN BY:
Jason L. Selburg
NOTES:
This procedure does not have error checking or return codes included.
Feel free to modify this code as you see fit.
*/
CREATE procedure dbo.usp_data_driven_subscription
( @scheduleID uniqueidentifier,
@emailTO varchar (2000) = '',
@emailCC varchar (2000) = '',
@emailBCC varchar (2000) = '',
@emailReplyTO varchar (2000) = '',
@emailBODY varchar (8000) = '',
@param1 varchar (256) = ''
)
as
DECLARE
@ptrval binary(16),
@PARAMptrval binary(16),
@TOpos int,
@CCpos int,
@BCCpos int,
@RTpos int,
@BODYpos int,
@PARAM1Pos int,
@length int,
@subscriptionID uniqueidentifier
-- set the subscription ID
SELECT @subscriptionID = SubscriptionID
FROM ReportSchedule WHERE ScheduleID = @scheduleID
-- set the text point for this record
SELECT @ptrval = TEXTPTR(ExtensionSettings)
FROM Subscriptions WHERE SubscriptionID = @subscriptionID
-- set the text point for this record
SELECT @PARAMptrval = TEXTPTR(Parameters)
FROM Subscriptions WHERE SubscriptionID = @subscriptionID
-- set the start position for the TO Address
SELECT @TOpos = patindex('%|TO|%', ExtensionSettings) - 1
FROM Subscriptions WHERE SubscriptionID = @subscriptionID
IF isnull(@TOpos, '') <> '' and @TOpos > 0 and len(@emailTo) > 0
-- change the TO address
UPDATETEXT Subscriptions.ExtensionSettings
@ptrval
@TOpos
4
@emailTo
-- set the start position for the CC Address
SELECT @CCpos = patindex('%|CC|%', ExtensionSettings) - 1
FROM Subscriptions WHERE SubscriptionID = @subscriptionID
IF isnull(@CCpos, '') <> '' and @CCpos > 0 and len(@emailCC) > 0
-- change the TO address
UPDATETEXT Subscriptions.ExtensionSettings
@ptrval
@CCpos
4
@emailCC
-- set the start position for the BCC Address
SELECT @BCCpos = patindex('%|BC|%', ExtensionSettings) - 1
FROM Subscriptions WHERE SubscriptionID = @subscriptionID
IF isnull(@BCCpos, '') <> '' and @BCCpos > 0 and len(@emailBCC) > 0
-- change the TO address
UPDATETEXT Subscriptions.ExtensionSettings
@ptrval
@BCCpos
4
@emailBCC
-- set the start position for the REPLY TO Address
SELECT @RTpos = patindex('%|RT|%', ExtensionSettings) - 1
FROM Subscriptions WHERE SubscriptionID = @subscriptionID
IF isnull(@RTpos, '') <> '' and @RTpos > 0 and len(@emailReplyTO) > 0
-- change the REPLY TO address
UPDATETEXT Subscriptions.ExtensionSettings
@ptrval
@RTpos
4
@emailReplyTO
-- set the start position for the BODY Text
SELECT @BODYpos = patindex('%|BD|%', ExtensionSettings) - 1
FROM Subscriptions WHERE SubscriptionID = @subscriptionID
IF isnull(@BODYpos, '') <> '' and @BODYpos > 0 and len(@emailBODY) > 0
-- change the REPLY TO address
UPDATETEXT Subscriptions.ExtensionSettings
@ptrval
@BODYpos
4
@emailBODY
-- set the start position for the Parameter 1
SELECT @PARAM1Pos = patindex('%|P1|%', Parameters) - 1
FROM Subscriptions WHERE SubscriptionID = @subscriptionID
IF isnull(@PARAM1Pos, '') <> '' and @PARAM1Pos > 0 and len(@param1) > 0
-- change the Parameter 1 value
UPDATETEXT Subscriptions.Parameters
@PARAMptrval
@PARAM1Pos
4
@param1
-- run the job
exec msdb..sp_start_job @job_name = @scheduleID
-- this give the report server time to execute the job
WAITFOR DELAY '00:00:10'
-- set the start position for the TO Address
SELECT @TOpos = patindex('%' + @emailTO + '%', ExtensionSettings) - 1
FROM Subscriptions WHERE SubscriptionID = @subscriptionID
SELECT @length = len(@emailTO)
IF @length > 0
-- replace the addresses with the original |TO|
UPDATETEXT Subscriptions.ExtensionSettings
@ptrval
@TOpos
@length
'|TO|'
-- set the start position for the TO Address
SELECT @CCpos = patindex('%' + @emailCC + '%', ExtensionSettings) - 1
FROM Subscriptions WHERE SubscriptionID = @subscriptionID
SELECT @length = len(@emailCC)
IF @length > 0
-- replace the addresses with the original |CC|
UPDATETEXT Subscriptions.ExtensionSettings
@ptrval
@CCpos
@length
'|CC|'
-- set the start position for the TO Address
SELECT @BCCpos = patindex('%' + @emailBCC + '%', ExtensionSettings) - 1
FROM Subscriptions WHERE SubscriptionID = @subscriptionID
SELECT @length = len(@emailBCC)
IF @length > 0
-- replace the addresses with the original |BC|
UPDATETEXT Subscriptions.ExtensionSettings
@ptrval
@BCCpos
@length
'|BC|'
-- set the start position for the REPLY TO Address
SELECT @RTpos = patindex('%' + @emailReplyTO + '%', ExtensionSettings) - 1
FROM Subscriptions WHERE SubscriptionID = @subscriptionID
SELECT @length = len(@emailReplyTO)
IF @length > 0
-- replace the addresses with the original |RT|
UPDATETEXT Subscriptions.ExtensionSettings
@ptrval
@RTpos
@length
'|RT|'
-- set the start position for the BODY Text
SELECT @BODYpos = patindex('%' + @emailBODY + '%', ExtensionSettings) - 1
FROM Subscriptions WHERE SubscriptionID = @subscriptionID
SELECT @length = len(@emailBODY)
IF @length > 0
-- replace the addresses with the original |RT|
UPDATETEXT Subscriptions.ExtensionSettings
@ptrval
@BODYpos
@length
'|BD|'
-- set the start position for the Parameter
SELECT @PARAM1Pos = patindex('%' + @param1 + '%', Parameters) - 1
FROM Subscriptions WHERE SubscriptionID = @subscriptionID
SELECT @length = len(@param1)
IF @length > 0
-- replace the addresses with the original |P1|
UPDATETEXT Subscriptions.Parameters
@PARAMptrval
@PARAM1Pos
@length
'|P1|'
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
This was removed by the editor as SPAM
-
Thanks Jason, this will come in handy for me soon
-
This came in handy for me and it works beautifully!!! Jason you’re the man, thanks for all your help!
-
This saved me a lot of trouble. I had a data-driven subscription working on my local server. We got a new firewall and it quit working.
The production server can send email through the firewall but doesn't have the Enterprise edition of SQL.
For the newbies like me:
1. usp_data_driven_subscription goes in the database called ReportServer
2. I called usp_data_driven_subscription from VB.NET inside a copied try-catch block, but had trouble debugging until I remembered that
ex.ToString tells more than ex.Message (ex is my exception)
3. I had to pass 2 integer parameters instead of 1 text parameter. RS did not like the pipe characters, so I put in 123456789 and 987654321 for the UPDATETEXT commands. They ranged from 1-digit to 3-digit integers, with lots of opportunity for the same digits in the parameters. Instead of trying to replace my integers with the placemarkers at the end of the loop, I put the whole original Parameter string back. It's a big XML string.
-- put the original Parameters back
UPDATETEXT Subscriptions.Parameters
@PARAMptrval
0
NULL
'[ParameterValues][ParameterValue][Name]cu[/Name][Value]123456789[/Value][/ParameterValue][ParameterValue][Name]sw[/Name][Value]987654321[/Value][/ParameterValue][/ParameterValues]'
(replace square brackets with angles)
-
I've been meaning to post this for quite a while, but here's an updated procedure that works much better....
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[data_driven_subscription]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[data_driven_subscription]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure dbo.data_driven_subscription
( @scheduleID uniqueidentifier,
@emailTO varchar (2000) = ' ',
@emailCC varchar (2000) = ' ',
@emailBCC varchar (2000) = ' ',
@emailReplyTO varchar (2000) = ' ',
@emailBODY varchar (8000) = ' ',
@param1 varchar (256) = ' ',
@p1 varchar(8000),
@sub varchar(1000),
@renderFormat varchar(50) = 'PDF'
 
as
DECLARE
@ptrval binary(16),
@PARAMptrval binary(16),
@TOpos int,
@CCpos int,
@BCCpos int,
@RTpos int,
@BODYpos int,
@PARAM1Pos int,
@length int,
@subscriptionID uniqueidentifier,
@job_status int,
@I int, -- the rest were added by hugh
@starttime datetime,
@lastruntime datetime,
@execTime datetime,
@dValues varchar (8000),
@pValues varchar (8000)
set @starttime = DATEADD(second, -2, getdate())
set @job_status = 1
set @I = 1
set @emailTO = rtrim(@emailTO)
set @emailCC = rtrim(@emailCC)
set @emailBCC = rtrim(@emailBCC)
set @emailReplyTO = rtrim(@emailReplyTO)
set @emailBODY = rtrim(@emailBODY)
set @param1 = rtrim(@param1)
-- set the subscription ID
SELECT @subscriptionID = SubscriptionID
FROM ReportSchedule WHERE ScheduleID = @scheduleID
set @dValues = ''
set @pValues = ''
if IsNull(@emailTO, '') <> ''
set @dValues = @dValues + '<ParameterValue><Name>TO</Name><Value>' + @emailTO + '</Value></ParameterValue>'
if IsNull(@emailCC, '') <> ''
set @dValues = @dValues + '<ParameterValue><Name>CC</Name><Value>' + @emailCC + '</Value></ParameterValue>'
if IsNull(@emailBCC, '') <> ''
set @dValues = @dValues + '<ParameterValue><Name>BCC</Name><Value>' + @emailBCC + '</Value></ParameterValue>'
if IsNull(@emailReplyTO, '') <> ''
set @dValues = @dValues + '<ParameterValue><Name>ReplyTo</Name><Value>' + @emailReplyTO + '</Value></ParameterValue>'
if IsNull(@emailBODY, '') <> ''
set @dValues = @dValues + '<ParameterValue><Name>Comment</Name><Value>' + @emailBODY + '</Value></ParameterValue>'
if IsNull(@sub, '') <> ''
set @dValues = @dValues + '<ParameterValue><Name>Subject</Name><Value>' + @sub + '</Value></ParameterValue>'
if IsNull(@dValues, '') <> ''
set @dValues = '<ParameterValues>' + @dValues +
'<ParameterValue><Name>IncludeReport</Name><Value>True</Value></ParameterValue>'
if IsNull(@dValues, '') <> ''
set @dValues = @dValues + '<ParameterValue><Name>RenderFormat</Name><Value>' +
@renderFormat + '</Value></ParameterValue>' +
'<ParameterValue><Name>IncludeLink</Name><Value>False</Value></ParameterValue></ParameterValues>'
if IsNull(@p1, '') <> '' and IsNull(@param1, '') <> ''
set @pValues = '<ParameterValues><ParameterValue><Name>' +
@p1 +
'</Name><Value>' +
@param1 +
'</Value></ParameterValue></ParameterValues>'
if IsNull(@dValues, '') <> '' and IsNull(@pValues, '') <> ''
BEGIN
update Subscriptions set extensionsettings = '' WHERE SubscriptionID = @SubscriptionID
update Subscriptions set parameters = '' WHERE SubscriptionID = @SubscriptionID
-- set the text point for this record
SELECT @ptrval = TEXTPTR(ExtensionSettings)
FROM Subscriptions WHERE SubscriptionID = @SubscriptionID
UPDATETEXT Subscriptions.ExtensionSettings
@ptrval
null
null
@dValues
-- set the text point for this record
SELECT @PARAMptrval = TEXTPTR(Parameters)
FROM Subscriptions WHERE SubscriptionID = @SubscriptionID
UPDATETEXT Subscriptions.Parameters
@PARAMptrval
null
null
@pValues
-- run the job
exec msdb..sp_start_job @job_name = @scheduleID
-- this give the report server time to execute the job
SELECT @lastruntime = LastRunTime FROM ReportServer..Schedule WHERE ScheduleID = @scheduleID
While (@starttime > @lastruntime)
Begin
print '...'
print @lastruntime
WAITFOR DELAY '00:00:03'
SELECT @lastruntime = LastRunTime FROM ReportServer..Schedule WHERE ScheduleID = @scheduleID
End
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
I haven't tried it yet, but it looks like a decent solution - perhaps decent is modest considering the cost of moving to Enterprise edition. One thing you might want to add support for is the chance that any of the data points you're passing may have a symbol you need to escape out. For example, maybe someone builds a report that has <VALUE> in the subject. If you get some bad tags you'll wind up with a malformed XML doc that probably wont execute correctly. Probably not hard to track down, but also probably going to happen on the day when you just don't have time for it!
-
Thanks Andy. I guess I hadn't thought of that as I developed this to solve an in house need and that wasn't somethingI have to deal with. but that is a very good point.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
Hi Jason,
I have used your store procedure.and it works fine.
But I have one problem I need to send my Report to multiple users based on different parameter values.
for example if parameter value is 100 then Report will send to abc@xyz.com
if parameter value is 200 then Report will send to aaa@xyz.com
if parameter value is 300 theb Report will send to bbb@xyz.com
Using this store proceduer it is not possible.can you please let me know what I need to modify in this
store proceduer or do i need to create any new sp?
Thanks.
-
Thank you for this one Mr. Selburg, especially the improved one that allows for saving the report to file.
Well done!
-
This really helped me to schedule reports , I have sql server 2012 std version ,
-
Hi Sir,
I am using SQL Server 2008 R2 & using your SP to achieve what I want. what I am trying to achieve is, I am working on Loan Mortgage system. whenever status of any loan changes I want to mail details of that loan in predefined format to one specific Email Id. I tried to call your SP. I passed Loan Number through @param1 in your SP. which in turn gives me the desired output i.e. sending mail with desired Loan Number.
Initially it worked fine, giving me desired output but now when i use same SP on same server without single change it is not working. whenever mail is fired it sends blank PDF without any data but just formatting of the report. I think it is not able to catch the value for the parameter. Where am i going wrong?? Please kindly reply at earliest.
Many thanks in advance.
-
here what should we pas in Report parameter Values textbox on subscription page of report manager??
-
I am still getting blank pdf file. I tried both the versions that you have mentioned in this blog but I am not getting output that i want. 🙁
-
Does anybody have any solution for this issue??
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply