January 6, 2011 at 2:01 am
Hey,
I've been struggling to find any information on this, so was wondering if anyone could help shed some light on the situation.
What I need to know is, as SA, is there a way of listing/managing subscriptions started by other people? Basically, we have lots of subscriptions set up, but I can see no way of finding out who they belong to. We've had a lot of staff leave before I arrived, so for all I know several of these subscriptions are for staff that have left the company.
Any help would be appreciated.
January 6, 2011 at 7:13 am
Look in the report server database, dbo.Subscriptions table, ExtensionSettings column.
The report server database has pretty much everything you need to know about about a report and its subscriptions.
January 6, 2011 at 9:23 am
Thanks. I've spent some time looking at the Subscriptions table and written an ugly little replace statement to get out all of the relevant information from the extensionsettings and linked to the catalog table to grab the report names. I'll turn it into a pivot statement later to produce a report that'll let us go through and eliminate the subscriptions that are no longer used.
See below for ugly replace statement.
SELECT
usr.username, cat.Path, cat.Name,
SUBSTRING(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CAST(sub.ExtensionSettings AS VARCHAR(8000)),'<','')
,'>',''),'ParameterValues',''),'ParameterValue',''),'NameTO/Name',''),'Value',''),'/Value',''),'NamePATH/Name',''),1,CHARINDEX('//',
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CAST(sub.ExtensionSettings AS VARCHAR(8000)),'<','')
,'>',''),'ParameterValues',''),'ParameterValue',''),'NameTO/Name',''),'Value',''),'/Value',''),'NamePATH/Name','')) - 1)
AS Outs
FROM ReportServer.dbo.Subscriptions sub
LEFT OUTER JOIN ReportServer.dbo.Users usr ON sub.OwnerID = usr.UserID
LEFT OUTER JOIN ReportServer.dbo.Catalog cat ON sub.Report_OID = cat.ItemID
ORDER BY cat.Path
-EDIT-
Thanks again. Modified my query based on the one posted below - still ugly but slight less so.
SELECT SubscriptionID, usr.username AS SubscriptionOwner,
SUBSTRING(cat.Path,2,CHARINDEX('/',cat.Path,2)-2) AS Folder, cat.Name AS ReportName,
REPLACE(REPLACE(CAST(extensionSettings AS XML).value('(//ParameterValue/Value)[1]',
'varchar(max)') + '; ' + CAST(extensionSettings AS XML).value('(//ParameterValue/Value)[2]',
'varchar(max)'),'True',''),'False','') AS Outs
FROM ReportServer.dbo.Subscriptions sub
LEFT OUTER JOIN ReportServer.dbo.Users usr ON sub.OwnerID = usr.UserID
LEFT OUTER JOIN ReportServer.dbo.Catalog cat ON sub.Report_OID = cat.ItemID
January 6, 2011 at 11:12 am
I found a script online - I can't take credit I don't understand querying XML data in SQL server well enough - that gets you what you want.
SELECT SubscriptionID, extensionSettings,
CAST(extensionSettings AS XML).value('(//ParameterValue/Value)[1]',
'varchar(max)') AS col1,
CAST(extensionSettings AS XML).value('(//ParameterValue/Value)[2]',
'varchar(max)') AS col2,
CAST(extensionSettings AS XML).value('(//ParameterValue/Value)[3]',
'varchar(max)') AS col3,
CAST(extensionSettings AS XML).value('(//ParameterValue/Value)[4]',
'varchar(max)') AS col4,
CAST(extensionSettings AS XML).value('(//ParameterValue/Value)[5]',
'varchar(max)') AS col5
from Subscriptions
All you need is col1 so you could trim the other columns away.
I believe, but I am still learning and not quite sure, that the (//ParameterValue/Value)[1]' indicates that you want to return the first value listed in the ParameterValue/Value structure of the XML. If anyone knows querying XML data using T-SQL I would appreciate a short explanation of exactly what each section of the query does. I have read several articles and I don't feel any more enlightened than when I started.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply