posted on Tuesday, January 03, 2006 12:54 PM
by
joe.sack
RS T-SQL tip: Identify which reports are being emailed via subscriptions in Reporting Services 2000
If you need to identify which reports are emailed to specific users (either to remove them from the subscription - or just as an FYI), you can execute the following query in the ReportServer, SQL Server 2000 Reporting Services database:
SELECT [Name] ReportName
FROM ReportServer.dbo.Catalog
WHERE ItemID IN (SELECT Report_OID
FROM ReportServer.dbo.Subscriptions
WHERE ExtensionSettings LIKE '%joesack@test.com%')
ORDER BY Name
Wildcards are used as the email is embedded in an XML format in the ExtensionSettings column. The query example then returns any reports with a subscription email to joesack@test.com.