SSMS/SSRS - Get Stored Procedure Name Used by SSRS Dataset From Query

Question

I have a query (unfinished) that gets generic SysJob/ReportServer information. The only issue I'm having is trying to retrieve the name of the stored procedure a SSRS report uses. I've tried multiple queries but in my current attempt I tried to get the procedure name from the sysjobsteps.command column, unfortunately it only contains the Subscriptions.SubscriptionID column, which when tracking back to that table, leads me nowhere as well... Any ideas? Thanks!

SELECT JOBS.Name AS [JobName(ID)]
    , CAT.Name AS [CatName] 
    , SCH.SubscriptionID,
    , SUB.LastRunTime
    , STEPS.command -- @eventData = ReportSchedule.SubscriptionID
FROM msdb.dbo.sysjobs JOBS
    INNER JOIN msdb.dbo.sysjobsteps STEPS ON JOBS.job_id = STEPS.job_id
    INNER JOIN ReportServer.dbo.ReportSchedule SCH ON JOBS.name = CAST(SCH.ScheduleID AS sysname)
    INNER JOIN ReportServer.dbo.Subscriptions SUB ON SCH.SubscriptionID = SUB.SubscriptionID
    INNER JOIN ReportServer.dbo.Catalog CAT ON SUB.Report_OID = CAT.ItemID
WHERE JOBS.enabled = 1
ORDER BY CAT.Name

An example of the data in the "sysjobsteps.command" column:

exec [ReportServer].dbo.AddEvent @EventType='TimedSubscription', @EventData='f3296cc8-79e1-4a99-9723-c1880da25a8d'

SOLUTION (messy, but works):

SELECT REPLACE(LEFT(SUBSTRING(CAST(CONVERT(xml, CONVERT(varbinary(max), CAT.content)) AS VARCHAR(MAX)),
    CHARINDEX('<CommandText>', CAST(CONVERT(xml, CONVERT(varbinary(max), CAT.content)) AS VARCHAR(MAX))), 150),
    CHARINDEX('</CommandText>', SUBSTRING(CAST(CONVERT(xml, CONVERT(varbinary(max), CAT.content)) AS VARCHAR(MAX)),
    CHARINDEX('<CommandText>', CAST(CONVERT(xml, CONVERT(varbinary(max), CAT.content)) AS VARCHAR(MAX))), 150)) - 1),
    '<CommandText>', '') AS ProcName
FROM ReportServer.dbo.Catalog

I'm pretty sure you can't get that from the `ReportServer` database. If I recall correctly the details of the report, the executable, is stored in an encrypted format, as a `varbinary`. You could likely inspect the `rpt` files in your project, but it wouldn't be in the same query as above; you would need do use something like PoSh. — Thom A, Dec 26 '18 at 16:50

score 2 · Accepted Answer · answered Dec 26 '18 at 18:54

2

Take a look at: Query to find list of *all* SSRS DataSets across *all* SSRS reports on ReportServer instance

One of its answers has a fairly large and well documented example of how to get shared and embedded data sets.

answered Dec 26 '18 at 18:54

Sam

143
1
7

Thanks, took a bit of work but this lead me to exactly what I needed! – haag1 Dec 26 '18 at 20:12
if it took more work than the answer provided @haag1 then I think it'd warrant an answer to your own question :) It was a solid question that isn't asked often. – S3S Dec 26 '18 at 20:56

SSMS/SSRS - Get Stored Procedure Name Used by SSRS Dataset From Query

1 Answers1