Subscriptions

  • 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.


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • 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.

  • 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


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • 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 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply