Hide Report Services Jobs in SSMS

Question

I have a Report Services instance that creates hundreds of jobs. The jobs are in serial format (ie. xxxxxx-xxx-xxxxx-xxxx-xxxx) and clutter up the jobs section view in SSMS. Is there any way to hide these jobs?

Hyde

Answer 1

The quick way to do this is to edit the underlying proc that SSMS uses to fetch the job list to ignore any jobs created by 'Report Server'.

1. Right click & Modify msdb.dbo.sp_help_category (system stored proc) to bring up the procedures code.
2. Around line 19, change @where_clause from VARCHAR(500) to VARCHAR(MAX)
3. Around line 96/97 you'll notice the final EXECUTE statement, just before this line add the following:

-- Ignore 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 '

For more information refer to the original article from which this answer is based:

http://timlaqua.com/2012/01/hiding-ssrs-schedule-jobs-in-ssms/

Dejesus

The only thing i can think of to solve your problem is update the stored procedure that SSMS uses to select jobs in SQL Agent, i think it is [msdb].[dbo].[sp_help_job]. i am not sure, you need further research – Toh

Answer 2

I followed the steps above, but added a variable inside the sp_help_category stored procedure:

DECLARE @ShowSSRS BIT 
SELECT @ShowSSRS = ShowSSRS FROM ShowSSRS

ShowSSRS is a table I added to the msdb database that has one bit field, also named ShowSSRS that will let me toggle between true and false. Typically I set it to false because we have a lot of SSRS reports that clutter the list. When I need to troubleshoot one, I set it to true, refresh and they all appear. I actually have an SSRS report that lists all the jobs, so I know which GUID is for which job.

In the code that is added around line 96/97, I simply check the variable:

IF @ShowSSRS = 0
      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 '

Tessitura

Answer 3

Use this filter setting (works in SQL Server 2019)

Lodmilla