Tag: hide subscriptions in ssms

How to hide Report Service jobs in SSMS

When you create subscriptions for your SSRS reports, it creates jobs in the SQL Server Agent with GUID’s when viewed in SSMS. This can make finding proper jobs more difficult if you have to scroll through hundreds or thousands of SSRS reports.

Read more: How to hide Report Service jobs in SSMS

On my server I have over 500 report subscriptions along with other database jobs. How do I hide all of these? Keep reading to find out!

We will need to modify a system supplied stored procedure. You do this at your own risk. I do not accept responsibility if there are problems on your system after making this change. That said, I have been doing this for over 10 years without any problems.

Open up the MSDB database and go to Programmability and open up the dbo.sp_help_category stored procedure. Right click and choose modify.

We need to make two changes to this procedure. First, we need to find the @where_clause variable. Mine is on line #19 below.

We need to change the 500 to MAX as seen below.

Now we need to go to the bottom of the stored procedure and look for the final execute. On mine, it was line #97.

We will need to put this T-SQL code in front of the final execute and then execute the stored procedure to save it to the database.

-- Hide Reporting Services Jobs IN SSMS
SET @where_clause += N'
AND
CASE
WHEN
name = ''Report Server''
AND (
SELECT program_name
FROM sys.sysprocesses
where spid = @@spid) = ''Microsoft SQL Server Management Studio'' THEN 0
ELSE 1
END = 1 '

Before:

After:

Now we can refresh the jobs.

After refreshing, all of the GUID jobs are no longer displaying. If you are still seeing the GUID jobs, make sure you executed the stored procedure to save it to the database.

One caveat: sometimes after patching, the GUID SSRS reports will reappear. Just bookmark this page and follow these steps again to fix.

To read more about sp_help_category, follow this link: https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-help-category-transact-sql?view=sql-server-ver16