I needed a way for me and my teammates to see and edit all report subscriptions across a Sharepoint site
\nwith SQL Reporting Services in Integrated mode.<\/p>\n
There are two parts here; the first is a function that returns text between delimiters. This is important to get
\nthe site and folder from the ItemPath in the ReportServer’s Catalog table.<\/p>\n
This function takes a string, delimiter and position. For example
\ndbo.fn_ParseStr(‘Server\/SubSite\/Folder’,’\/’,2)
\nWill Return the SubSite as this is the 2nd item delimited.<\/p>\n
dbo.fn_ParseStr(‘Server\/SubSite\/Folder’,’\/’,3)
\nWill Return the Folder as it is the 3rd item delimited.<\/p>\n
I needed a way for me and my teammates to see and edit all report subscriptions across a Sharepoint site with SQL Reporting Services in Integrated mode. There are two parts here; the first is a function that returns text between delimiters. This is important to get the site and […]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"footnotes":""},"categories":[2,10,11],"tags":[99,106,111],"class_list":["post-310","post","type-post","status-publish","format-standard","hentry","category-computer","category-sharepoint","category-software","tag-sharepoint-2010","tag-sql-reporting-services","tag-ssrs"],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/www.markwheeler.com\/index.php?rest_route=\/wp\/v2\/posts\/310","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.markwheeler.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.markwheeler.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.markwheeler.com\/index.php?rest_route=\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.markwheeler.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=310"}],"version-history":[{"count":0,"href":"https:\/\/www.markwheeler.com\/index.php?rest_route=\/wp\/v2\/posts\/310\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.markwheeler.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=310"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.markwheeler.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=310"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.markwheeler.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=310"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}create Function dbo.fn_ParseStr(@Message Varchar(1000), @delimiter char(1), @index int )
\nReturns Varchar(1000)
\nAs
\nBegin
\nDeclare
\n@curIndex int = 0,
\n@pos int = 1,
\n@prevPos int = 0,
\n@result varchar(1000)
\nwhile @pos > 0
\nBegin
\nset @pos = CHARINDEX(@delimiter, @Message, @prevPos);
\nif(@pos > 0)
\nbegin-- get the chars between the prev position to next delimiter pos
\nset @result = SUBSTRING(@message, @prevPos, @pos-@prevPos)
\nend
\nelse
\nbegin--get last delim message
\nset @result = SUBSTRING(@message, @prevPos, LEN(@message))
\nend
\nif(@index = @curIndex)
\nbegin
\nreturn @result
\nend
\nset @prevPos = @pos + 1
\nset @curIndex = @curIndex + 1;
\nend
\nreturn ''--not found
\nEnd
\n<\/code>
\nHere is the query to return the fields for the report and also builds the link to edit the subscription.
\n
\nUSE [ReportServer]
\nGO
\nSELECT
\ndistinct(S.[SubscriptionID]),
\nS.[ModifiedDate],
\nS.[Description],
\nS.[LastStatus],
\nS.[LastRunTime],
\nC.ItemID as CatalogID,
\n--- This substring removes the GUID from the Path in the Catalog table.
\nsubstring(C.[Path], 40, 255) as Path ,
\ndbo.fn_ParseStr(C.[Path],'\/',2) as Site,
\ndbo.fn_ParseStr(C.[Path],'\/',3) as Folder,
\nC.[Name],
\n[U1].[UserName] AS [Owner],
\n[URL2] = 'https:\/\/yourservername\/'+ dbo.fn_ParseStr(C.[Path],'\/',2) +
\n'\/_layouts\/ReportServer\/SPSubscriptionProperties.aspx?SubscriptionID=' + CAST(S.[SubscriptionID] AS VARCHAR(80)) +
\n'&list=' + CAST(F.[tp_ID] AS VARCHAR(80)) + '&ID=' + CAST(D.[DoclibRowId] AS VARCHAR(80))
\nFROM
\nReportServer.dbo.[Subscriptions] S
\nJOIN ReportServer.dbo.[Catalog] C ON S.[Report_OID] = C.[ItemID]
\nJOIN ReportServer.dbo.[Users] [U1] ON S.[OwnerID] = [U1].[UserID]
\n-- Your Sharepoint Content Database
\nJOIN WSS_Content_.dbo.AllDocs D on D.Id = C.[ItemID]
\nJOIN WSS_Content_.dbo.Lists F on F.tp_ID = D.ListId<\/code><\/p>\n","protected":false},"excerpt":{"rendered":"