April 19, 2022 at 1:13 pm
Hi forum,
First time post.
I am looking for some help with SSRS Running in SQL Standard 2019.
I have reports that are parameterised by store.
I need to schedule the emailing of reports, and have created one subscription for store 1.
Aside from the store number the rest of the subscription is the same, rather than creating 18 subscriptions manually is there any way of doing it either programatically, or duplicating the one I have and then setting the parameter?
Thanks in advance
Andy
April 19, 2022 at 5:28 pm
Have you looked at the SQL job that is created when you schedule a report?
You should be able to leverage that code to generate your set of 18 reports.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
April 20, 2022 at 1:49 pm
Hello Michael,
Thanks for your reply, are you referring to the SQL Server Agent Job?
If so this seems to contain none of the readable details of the job such as the method of delivery or more importantly the parameters.
Perhaps I am looking at the wrong place?
Thanks
Andy
April 20, 2022 at 4:33 pm
It should be possible. The stored procedure to create a subscription is ReportServer.dbo.CreateSubscription. You could either trace a call to the proc when creating a subscription via the RS Manager & emulate/modify that data, or query dbo.Subscriptions, dbo.Schedule, dbo.ReportSchedule, & dbo.Catalog to see what the data needs to look like.
As far as I can tell, that does not, however, create the SQL Agent job (named with a guid corresponding to ScheduleID) that appear to run a subscription on schedule.
SSRS Subscriptions and Delivery (Reporting Services) says "Reporting Services includes a scheduling and delivery processor, which provides functionality for scheduling reports and delivering them to users". But I consistently observed SQL Agent Jobs that directly correlate to the ScheduleIDs & schedule times of SSRS subscriptions, w/ a job step also named for ScheduleID, executing ReportServer.dbo.AddEvent w/ an @EventData parameter set to Schedule.EventData value. If required as it appears to be, and not somehow automatically created in response to creating the subscription via CreateSubscription, the script to create job would be easy enough to generate consistently.
http://blogs.lessthandot.com/index.php/datamgmt/datadesign/not-a-fan-of-the-report-manager-in-ssrs/
Obviously, none of this is recommended or supported by Microsoft.
April 20, 2022 at 4:53 pm
Hello Michael,
Thanks for your reply, are you referring to the SQL Server Agent Job?
If so this seems to contain none of the readable details of the job such as the method of delivery or more importantly the parameters.
Perhaps I am looking at the wrong place?
Thanks
Andy
Sorry. I kind of got lost with that answer.
When a subscription is created, the SQL job contains a call to the proc "AddEvent"
exec [ReportServer].dbo.AddEvent @EventType='TimedSubscription', @EventData='7163c9a3-6e00-442e-babc-92b48e9c115d'
The value for the @EventData parameter is the guid of the subscription, which is contained in the Subscription table.
The Subscription table contains a column named "Parameters". You can either insert rows into this table with your different parameters, or execute the proc "CreateSubscription".
In the SQL Job, you can call the AddEvent proc 18 times with the different GUIDS.
Honestly, there are only 18, correct? There's not really a compelling reason to do this programmatically unless the parameters need to be modified regularly.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
April 20, 2022 at 9:09 pm
The ideal solution would be to upgrade to Enterprise Edition - and then you could use a data-driven subscription. Or you could build out a process using powershell to automate generating and sending the reports.
You could then schedule that PS script to run through the agent or through task scheduler.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 21, 2022 at 4:25 pm
Thank you Michael, Jeffrey and ratbak.
@ Michael - thanks that makes more sense now, from your original post I was thinking I would see the parameter in plain text in the SQL job, I did not realise the eventdata string was responsible for calling it.
I guess the follow on question, is if the fix is to create additional entries in the subscriptions table, how would you generate the values for subscription ID, Owner ID, as they look system generated?
You are correct that one report for 18 stores it sounds more hassle than it is worth, but I will have multiple reports so investing some time now may save me some in the future!
@ ratbak - thanks for sending those over, I will look further into those but suspect they may be a bit advanced for me!
@ Jeffrey - thanks for your feedback, enterprise is out of reach financially.
Many Thanks
Andy
April 21, 2022 at 8:01 pm
Thank you Michael, Jeffrey and ratbak.
@ Michael - thanks that makes more sense now, from your original post I was thinking I would see the parameter in plain text in the SQL job, I did not realise the eventdata string was responsible for calling it.
I guess the follow on question, is if the fix is to create additional entries in the subscriptions table, how would you generate the values for subscription ID, Owner ID, as they look system generated?
You are correct that one report for 18 stores it sounds more hassle than it is worth, but I will have multiple reports so investing some time now may save me some in the future!
@ ratbak - thanks for sending those over, I will look further into those but suspect they may be a bit advanced for me!
@ Jeffrey - thanks for your feedback, enterprise is out of reach financially.
Many Thanks
Andy
I guess you didn't read the second part of my post. Enterprise Edition would be the ideal - but if that isn't possible you could also use powershell to execute the report with each parameter in the expected format. And - you could basically do the same thing as a data-driven subscription, which is read from a table to process each row, execute the report and deliver the report through email or place it on a file share.
The only other requirement would then be how that script gets scheduled - which could be through an agent job or task scheduler.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 22, 2022 at 7:45 am
Hi Jeffrey, sorry I did see that but I am not if 'building out a process' in Powershell is something that may be technically beyond me.
Thanks
Andy
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply