I'm a brand new Junior SQL Developer. I have inherited several reports, which are on Report Manager. Some of them seem to have subscriptions set up, but they contain invalid email addresses.
I understand I can modify each of these reports and their subscriptions one at a time through Report Manager by clicking the report, going to Manage, Subscriptions, etc.; however, this seems like a huge waste of time and effort and it doesn't offer a very solid solution for the future.
I would much rather spend time on something that makes more sense. Is there some way I can access all of my Report Manager report Subscriptions from SSMS? I'd like to be able to see what email addresses they're hard-coded to right now and then modify that to select from a list of employee emails in our HR database. That way if an email changes later on, or if we want to remove one person from ALL subscriptions it won't involve a ton of time.
Also, does anyone know how to avoid the ridiculous job-naming conventions that seem to come with Report Manager? I don't know what Report "3AC1BB6F-D782-46DD-99F5-CCA1E6257091" is tied to, but if it could be modified to default to something like "ReportName+Microsoft'sTypicalBSNamingCovention", that'd be fine and would make some sense.
I hope that makes sense. Thank you all in advance for sharing your experience and knowledge with me. :^)
Here is a query that will allow you to view subscriptions, assuming you know where the Report Server database is, of course.
SELECT A.SubscriptionID, A.Report_OID, B.Name,
Path, A.LastRunTime, A.Description, C.UserName AS Owner, A.LastStatus, A.ExtensionSettings
FROM Subscriptions A INNER JOIN Catalog B
ON A.Report_OID = B.ItemID
INNER JOIN Users C
ON A.OwnerID = C.UserID
WHERE A.ExtensionSettings LIKE '%[email protected]%' -- this can be more than an email, like a paramater value
ORDER BY A.LastRunTime DESC
This a query that you can use to update a subscription.
UPDATE Subscriptions SET
ExtensionSettings = REPLACE(CAST(ExtensionSettings AS nvarchar(max)), 'name', 'new_name')
WHERE ExtensionSettings LIKE '%[email protected]%'
Use the update cautiously, or you may end up rebuilding subscriptions by hand.
Do not mess with the subscription jobs created by Report Manager. If you need to locate the job for a particular subscription, just try to make sure each subscription has a somewhat unique schedule. Sort the job list by last run date to find that last time a subscription fired. Right click to run. That works well enough for me, anyway.