sql-serverreporting-servicessql-server-2008-r2ssms

Hide Report Services Jobs in SSMS


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?


Solution

  • 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/