tim laqua dot com Thoughts and Code from Tim Laqua

Hiding SSRS Schedule Jobs In SSMS

Everybody hates these things. If you're in the Activity Monitor or browsing via the SSMS tree view, these GUID jobs that represent SSRS subscriptions are really just none of our concern. Sure, I can admit that I've seen people manually fire these to re-send a given subscription, but you can just do that using the AddEvent proc in a query window. Personally - I don't want to see these... usually...

Connect to the database instance you want to filter the agent jobs out on
Browse to Databases > System Databases > msdb > Programmability > Stored Procedures > System Stored Procedures
Right-click on dbo.sp_help_category and select Modify...

At the top, change the definition of @where_clause to NVARCHAR(MAX)

19

  DECLARE @where_clause   NVARCHAR(MAX)

At the bottom, add in a few lines to append the @where_clause variable with a predicate that filters out the Report Server category when it's you from your workstation (so you can still see that category from another machine if you need to).

94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112

  SELECT @cmd = @cmd + N'FROM msdb.dbo.syscategories '
 
  SET @where_clause += N'
  AND
	CASE
		WHEN 
			name = ''Report Server'' 
			AND (
				SELECT RTRIM(nt_username) + ''|'' + RTRIM(hostname) 
				FROM sys.sysprocesses 
				where spid = @@spid) = ''tlaqua|TIMSLAPTOP''  THEN 0
		ELSE 1
	END = 1 '
 
  -- Execute the query
  EXECUTE (@cmd + @where_clause + N'ORDER BY category_type, name')
 
  RETURN(@@ERROR) -- 0 means success
END

So, what on earth are we doing here? First, replace my nt_username with yours and replace my hostname with yours. From my less-than-exhaustive trial and error testing, it seems that when either the SSMS Jobs node is expanded or the Job Activity Monitor fetches jobs, two DB calls are made - one to fetch categories and another to fetch jobs. I tried filtering out the jobs portion originally and that yielded some errors. So I'm assuming it's trying to marry the categories and the jobs internally, and it expects there to be jobs for each category the first query returned. By not returning the Report Server category at all, the resulting merged list of jobs doesn't contain any jobs belonging to that category (logically an INNER JOIN).

Sure, this is a dirty hack, but I don't mind.

Update (2012-01-11)
Here is the predicate for hiding those jobs from ALL SSMS clients:

96
97
98
99
100
101
102
103
104
105
106

  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 '