October 30, 2017 at 11:15 pm
Comments posted to this topic are about the item Create SSRS Data Driven Subscriptions on Standard Edition
October 31, 2017 at 6:05 am
Bernard,
Good solution - we had the same issue with multiple clients but wanted a user capable driven solution that did SSRS, Crystal and Excel - we found R-Tag Manager works well for users but that cost is $999 cheaper than the Enterprise addition and users love it - interface is a bit weird but one your used to it it works and you can "batch" solutions.
I will use your solution for my canned stuff and for clients who wont spend money.
Thank you so much for sharing.
October 31, 2017 at 6:15 am
Not a great fan of looping through the parameters.
It's also worth noting that if you have a subscription running, and try to run it again (at least with 2012-) then the old subscription will not be completed. This is a real pain if you have multiple events occur within a very short time period, as if one event starts before another finishes, the earlier report will not be submitted.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 31, 2017 at 7:43 am
Thanks for this.
What do you think about wrapping the report execution in a try/catch? I'd want to make sure if the report execution errors out for any reason, we get a chance to safely restore the original parameters.
October 31, 2017 at 8:07 am
cerulean47 - Tuesday, October 31, 2017 7:43 AMThanks for this.What do you think about wrapping the report execution in a try/catch? I'd want to make sure if the report execution errors out for any reason, we get a chance to safely restore the original parameters.
You wouldn't be able to do this with a TRY...CATCH. When you run an SSRS report using T-SQL, all you effectively do is mark the subscription to be run; the database engine doesn't do the work. If the report/subscription fails is a completely separate task.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 31, 2017 at 9:14 am
Thom A - Tuesday, October 31, 2017 8:07 AMcerulean47 - Tuesday, October 31, 2017 7:43 AMThanks for this.What do you think about wrapping the report execution in a try/catch? I'd want to make sure if the report execution errors out for any reason, we get a chance to safely restore the original parameters.
You wouldn't be able to do this with a TRY...CATCH. When you run an SSRS report using T-SQL, all you effectively do is mark the subscription to be run; the database engine doesn't do the work. If the report/subscription fails is a completely separate task.
I see. The timed delay makes me a little nervous. Is it possible to poll to determine if the report successfully started?
October 31, 2017 at 5:12 pm
Wrote this for a client years ago, still running today.
Couple of tips:
1.) Handle the parameters and extension settings as the whole XML BLOB; don't chunk it up. Code is much cleaner, and format is guaranteed to remain intact.
--Not all variables declared for brevity, but you get the idea
DECLARE
@extensionSettings XML,
@parameters XML
SELECT
@extensionSettings = ExtensionSettings,
@parameters = [Parameters]
FROM dbo.Subscriptions
WHERE SubscriptionID = @subscriptionID;
--Set Extension Options
SET @extensionSettings.modify('replace value of (/ParameterValues/ParameterValue[Name="TO"]/Value[1]/text())[1] with sql:variable("@Email")');
SET @extensionSettings.modify('replace value of (/ParameterValues/ParameterValue[Name="Subject"]/Value[1]/text())[1] with sql:variable("@emailSubject")');
SET @extensionSettings.modify('replace value of (/ParameterValues/ParameterValue[Name="Comment"]/Value[1]/text())[1] with sql:variable("@BodyString")');
--Set Report Options
SET @parameters.modify('replace value of (/ParameterValues/ParameterValue[Name="ID"]/Value[1]/text())[1] with sql:variable("@ID")');
SET @parameters.modify('replace value of (/ParameterValues/ParameterValue[Name="Month"]/Value[1]/text())[1] with sql:variable("@ReportMonth")');
UPDATE dbo.Subscriptions
SET
ExtensionSettings = CAST(@extensionSettings AS nvarchar(max)),
[Parameters] = CAST(@parameters AS nvarchar(max))
WHERE SubscriptionID = @subscriptionID;
2.) Don't rely on SQL Agent to execute; it takes way longer, and job calls are async, so you need extra code deployed to check job status before you can change things and run the next iteration of the report. You can inject Events directly to SSRS, and your procedure essentially fires all the iterations of the report instead of SSRS, based on the data driven set you put in the proc.
--Write a loop or cursor around this and include your updates to the subscription
--Queue the Report for execution with the parameter / extension changes you've made
EXEC dbo.AddEvent @EventType = 'TimedSubscription', @EventData = @subscriptionID;
--Delay to ensure it is queued
WAITFOR DELAY '00:00:01.500';
--If the execution is still queued, or running, wait
WHILE EXISTS (SELECT TOP 1 1 FROM dbo.Event WHERE EventData = @subscriptionID ) OR EXISTS (SELECT TOP 1 1 FROM dbo.Notifications WHERE SubscriptionID = @subscriptionID)
BEGIN
WAITFOR DELAY '00:00:01.000';
END
--At this point, we would loop, set parameters / extensions on the subscription for the next iteration of the report, and run another AddEvent
I run about 700 reports a month like this through 2 proc calls (PDF Export to fileshare, and Emailed); takes about 20 minutes and the two reports can run in parallel. Definitely better than eating Enterprise licensing cost. 😉
Josh Lewis
November 1, 2017 at 10:59 am
November 7, 2017 at 6:16 am
Thom A - Tuesday, October 31, 2017 6:15 AMNot a great fan of looping through the parameters.It's also worth noting that if you have a subscription running, and try to run it again (at least with 2012-) then the old subscription will not be completed. This is a real pain if you have multiple events occur within a very short time period, as if one event starts before another finishes, the earlier report will not be submitted.
Hi Thom,
Thanks for your reply.
Your comment above has also been catered for in the main stored proc with this piece of code, which will wait for one subscription to end before starting another:
-- we need to wait for our turn at using the subscription system
WHILE EXISTS(SELECT 1 FROM tempdb.sys.objects WHERE name = '##ReportInUse')
WAITFOR DELAY '00:00:30';
CREATE TABLE ##ReportInUse (ReportID int);
November 7, 2017 at 6:22 am
Ronzo - Wednesday, November 1, 2017 10:59 AMIt is worth noting that if you are using SQL 2016 and above, you can use the built-in STRING_SPLIT function instead of "rolling your own". It is limited to single character separators, but is much faster and a lot less code to write/maintain.
Hi Ronzo,
The new SPLT_STRING function is only available in this version of SQL: SQL Server 2016 RC0
June 12, 2018 at 7:54 pm
epowell 61898 - Tuesday, October 31, 2017 6:05 AMBernard,
Good solution - we had the same issue with multiple clients but wanted a user capable driven solution that did SSRS, Crystal and Excel - we found R-Tag Manager works well for users but that cost is $999 cheaper than the Enterprise addition and users love it - interface is a bit weird but one your used to it it works and you can "batch" solutions.
I will use your solution for my canned stuff and for clients who wont spend money.
Thank you so much for sharing.
Thanks for your help. I like "R-Tag Manager".
December 24, 2019 at 4:52 am
Bernard,
Thnx for your solution , we did an upgrade from 2012 Developer to 2016 Standard . I have never did SSRS earlier (Data Driven subscriptions part). So i might need some help with this solution. I will get with you and thnx again for your solution .
July 1, 2020 at 11:02 am
This was removed by the editor as SPAM
July 1, 2020 at 11:05 am
Hi, I am trying to understand the purpose of this workaround Data Driven Subscription. We are upgrading SSRS 2012 Enterprise to 2016 Standard Edition. We often use the data driven for to send the report if it contains data and "end time workaround". Is it possible to add IF/ELSE and CASE statements into this script? Here is the template we use in the data driven query portion. Please let me know your thoughts. Any help would be appreciated. Thanks in advance.
DECLARE @now DATETIME = GETDATE(), @StartDate DATETIME
IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 2 AND 6 -- Monday to Friday
AND DATEPART(HOUR, @now) IN (8,9,10,11,12,13,14,15,16,17,18,19,20) --8am to 8pm
BEGIN
SET @StartDate = CASEWHEN DATEPART(WEEKDAY, @now) = 2 -- Monday
AND DATEPART(HOUR, @now) = 8 -- 8am
THEN DATEADD(HOUR, -60, @now) -- set it to 8:00pm Friday
WHEN DATEPART(WEEKDAY, @now) BETWEEN 3 AND 6 -- Tuesday to Friday
AND DATEPART(HOUR, @now ) = 8 -- 8am
THEN DATEADD(HOUR, -12, @now) -- Set it back to 8pm
ELSE DATEADD(HOUR, -1, @now)
END
END
DECLARE @Count INT
--INSERT FROM QUERY HERE
SELECT@Count = COUNT(*)
--Check if the report contains data
IF (@Count > 0 AND @StartDate IS NOT NULL)
BEGIN
SELECT email_address = ''
,bcc = ''
,StartDate = @StartDate
,Enddate = @now
END
ELSE
SELECT email_address = ''
,bcc = ''
,StartDate = DATEADD(HOUR, -1, @now)
,Enddate = @now
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply