Hello and welcome back…
It has been a few months since I have published a blog post. More like 126 days, to be exact. Unfortunately, I have been dealing with some physical and mental health challenges that have kept me from writing. However, those things are behind me, and now I am ready to get back to writing blogs regularly.
Today’s topic is one that I have wanted to blog about for some time now.
Decoding SSRS Subscription Agent Job names is something I have always wondered how other people manage.
Have you ever needed to troubleshoot an SSRS subscription or wanted to execute the subscription manually? SSRS does a horrible job at making that task easy. We will walk through how you can decode the gobilty gook that is the Agent Job Name that SSRS assigns to the subscription.
First things First
Open SSMS and connect to the SQL Server that hosts your SSRS database. If you navigate to SQL Agent, you will see Job Names that mean nothing to you.
What in the world is this supposed to help you figure out???
Each one of the Agent Jobs listed is related to an SSRS Subscription that has been created. Too bad for us, the GUID used as the Job Name has no relation to the SSRS Report Subscription.
Sure, you could try and identify the Subscription you want to execute by looking at the Last Run or Next Run columns. However, that might give you a false sense of security, especially if multiple jobs have the same values.
Agent Jobs Properties
I have highlighted the Job that starts with 511. After that, we will follow the flow to capture the information needed to get to our SSRS Report with the subscription defined.
First, let’s look at what the Agent Job Step for job 511 is actually executing; if you open the job, click on Steps and then open the Job Step Properties. You can see the execution is running a stored procedure and passing in a couple of parameters. The EventData parameter is another random GUID that means nothing to the naked eye. However, that value will be an important value that we will need to use with our Decoder Ring. So please make a note of the EventData 6a6 GUID which I highlighted below.
With this new information available, we can now take that and do some TSQL Decoder Ring magic and run a query.
Query Time
Open a New Query window and run the following query:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SELECT sj.name AS [SQL Job Name], c.Path, c.name, s.LastRunTime, s.description, s.LastStatus, u.UserName, s.SubscriptionID FROM ReportServer..Subscriptions s INNER JOIN ReportServer..Users u ON s.OwnerID = u.UserID INNER JOIN ReportServer..Catalog c ON s.Report_OID = c.ItemID INNER JOIN msdb..sysjobsteps sjs ON sjs.command LIKE '%' + CAST(s.SubscriptionID AS VARCHAR(4000)) + '%' INNER JOIN msdb..sysjobs sj ON sj.job_id = sjs.job_id ORDER BY s.LastRunTime; |
Lastly
Now, what do you see in the output? Sorry I had to blur out some private data to protect the innocent. With that said, you can see a lot of great information from the query results. From the SQL Job Name column, you can see the 511 GUID Job Name we made a note of from the SQL Agent Job list. If you go to the right-hand side of the output and see SubscriptionID, I will see I highlighted the 6a6 GUID. The EventData value helps us relate the two values to come up with the Path and Name to the SSRS Report we are troubleshooting. Now you can easily go to your SSRS Web Portal, navigate to the report in question, and make any changes needed to the Subscription or Execute the Agent Job manually.