October 11, 2016 at 4:20 am
Hi,
Not sure if anyone has any advice here. I'm doing data driven subscriptions without enterprise edition and I've hacked together something to dynamically change the email address and report parameter on the fly then kick off the subscription in a while loop.
I've tested this a few times with 10 iterations and it seems to be pretty stable - we want to do it live with 75 iterations once a week, I've put a 10 second delay between calls to keep things relatively calm....
Obvs this is unsupported, but does it strike anyone immediately that this is a really dumb idea?
CREATE Procedure [SSRSAdmin].[AutomatedSupplierReport]
as
--Initialise Variables
DECLARE @countloop INT
DECLARE @stoploop INT
DECLARE @email VARCHAR(100)
DECLARE @supplier VARCHAR(50)
DECLARE @oldmail VARCHAR(100)
DECLARE @jobid VARCHAR(40)
DECLARE @subsid VARCHAR(40)
--Both the subscription id and the job id can change outside of your control, the only thing you have control over is the report name.
--Therefore derive the job and subscription ids from the report name.
--Do not change the report name. All you gotta do is not change it, then things keep working, see? That is all.
SET @subsid = (SELECT
[ReportServer].dbo.Subscriptions.SubscriptionID
FROM [ReportServer].dbo.Subscriptions
INNER JOIN [ReportServer].dbo.ReportSchedule
ON [ReportServer].dbo.ReportSchedule.SubscriptionID = [ReportServer].dbo.Subscriptions.SubscriptionID
INNER JOIN [ReportServer].dbo.Schedule
ON [ReportServer].dbo.ReportSchedule.ScheduleID = [ReportServer].dbo.Schedule.ScheduleID
INNER JOIN [ReportServer].dbo.Catalog
ON [ReportServer].dbo.ReportSchedule.ReportID = Catalog.ItemID
AND [ReportServer].dbo.Subscriptions.Report_OID = Catalog.ItemID
WHERE Catalog.name = 'Expected Receipts')
SET @jobid = (SELECT
[ReportServer].dbo.Schedule.ScheduleID AS JobID
FROM [ReportServer].dbo.Subscriptions
INNER JOIN [ReportServer].dbo.ReportSchedule
ON [ReportServer].dbo.ReportSchedule.SubscriptionID = [ReportServer].dbo.Subscriptions.SubscriptionID
INNER JOIN [ReportServer].dbo.Schedule
ON [ReportServer].dbo.ReportSchedule.ScheduleID = [ReportServer].dbo.Schedule.ScheduleID
INNER JOIN [ReportServer].dbo.Catalog
ON [ReportServer].dbo.ReportSchedule.ReportID = Catalog.ItemID
AND [ReportServer].dbo.Subscriptions.Report_OID = Catalog.ItemID
WHERE Catalog.name = 'Expected Receipts')
SET @countloop = (SELECT min(ID) from BI.dbo.EmailTest)
SET @stoploop = (SELECT max(ID) from BI.dbo.EmailTest)
SET @email = (SELECT SupplierEmail from BI.dbo.EmailTest where ID = @countloop)
SET @supplier = (SELECT Supplier from BI.dbo.EmailTest where ID = @countloop)
SET @oldmail = 'richard.gardner@email.com'
WHILE (@countloop <= @stoploop)
BEGIN
UPDATE Subscriptions
set Parameters=
'<ParameterValues><ParameterValue><Name>Supplier</Name><Value>' + @supplier + '</Value></ParameterValue></ParameterValues>'
, ExtensionSettings = CONVERT(NTEXT,REPLACE(CONVERT(VARCHAR(MAX),ExtensionSettings),@oldmail,@email))
FROM ReportServer.dbo.Subscriptions
WHERE SubscriptionID = @subsid
EXEC msdb.dbo.sp_start_job @jobid
WAITFOR DELAY '00:00:10'
--post doing stuff
SET @oldmail = @email
SET @countloop = @countloop + 1
SET @email = (SELECT SupplierEmail from BI.dbo.EmailTest where ID = @countloop)
SET @supplier = (SELECT Supplier from BI.dbo.EmailTest where ID = @countloop)
END
--reinitialise for the next run, as long as the set statement for @oldmail matches the one here it doesn't matter who's email it is
UPDATE Subscriptions
set ExtensionSettings = CONVERT(NTEXT,REPLACE(CONVERT(VARCHAR(MAX),ExtensionSettings),@oldmail,'richard.gardner@email.com'))
FROM ReportServer.dbo.Subscriptions
WHERE SubscriptionID = @subsid
October 11, 2016 at 4:25 am
richard.gardner 6009 (10/11/2016)
Obvs this is unsupported, but does it strike anyone immediately that this is a really dumb idea?
As long as it works, you should be fine. Things get compicated when it stops working: from that moment on you're on your own, which you could be fine with or not.
I have done unsupported things as well with the reporting services database. You just have to know exactly what you're doing. I don't see anything harmful in your code, it just seems to update email addresses.
-- Gianluca Sartori
October 11, 2016 at 4:33 am
Yes, that was kinda my thinking, thanks for the reassurance. I suppose it might be worth putting the update inside a transaction in case the database flakes out mid flow.
October 11, 2016 at 5:00 am
My only 2 cents is "is 10 seconds long enough?"
I do "data driven subscription" myself, using standard, however, sometimes my reports take a little more than 10 seconds. If you start a subscription again (after changing its parameters) before the prior has finished, the old subscription will be overwritten and won't complete. I therefore had to add a check to see if the report had completed, and loop out if it waits too long.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 11, 2016 at 5:06 am
That's quite useful Thom, could I ask what you're looking at to do that check? I was assuming that lifting the parameters would occur prior to the report being rendered, but I guess it might be picking up the email address after rendering so I do run the risk of sending it to the wrong recipient (?)
October 11, 2016 at 6:15 am
richard.gardner 6009 (10/11/2016)
That's quite useful Thom, could I ask what you're looking at to do that check? I was assuming that lifting the parameters would occur prior to the report being rendered, but I guess it might be picking up the email address after rendering so I do run the risk of sending it to the wrong recipient (?)
All the parameters/recipients/etc are stored in that subscriptions run, so if you amend any of these while a subscription is running it won't effect it.
There's a table, ExecutionLogStorage, which has a Status field. rsSuccess represents that the execution was successful. You'll need to make use of the catalog table in a join as well to get your subscription.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 13, 2016 at 1:13 am
Alternatively you can call a report from Powershell and feed it parameters then send the resulting file by email using Powershell commands. This gives you the ability to change the results and get better control over the content of the email. I do this for a client.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply