Getting the text of existing sprocs is apparently pretty easy: EXEC sp_helptext N'sp_get_composite_job_info';


I've been having trouble with ordering the results of a stored procedure, probably by putting results in a temp table. Seeing, in this case, the code to create the temp table the sproc's giving back should be useful.

Although, in my case, no dice. I ended up cheating and trivially rewriting the sproc and the sproc it called.

sp_help_job calls sp_get_composite_job_info, which ends with a statement with its own ORDER BY, which is all I was interesting in changing.

So a quick change there...

-- ...
FROM @filtered_jobs fj
LEFT OUTER JOIN msdb.dbo.sysjobs_view sjv ON (fj.job_id = sjv.job_id)
LEFT OUTER JOIN msdb.dbo.sysoperators so1 ON (sjv.notify_email_operator_id =
LEFT OUTER JOIN msdb.dbo.sysoperators so2 ON (sjv.notify_netsend_operator_id =
LEFT OUTER JOIN msdb.dbo.sysoperators so3 ON (sjv.notify_page_operator_id =
LEFT OUTER JOIN msdb.dbo.syscategories sc ON (sjv.category_id = sc.category_id)
--ORDER BY sjv.job_id

... and a quick change in `sp_help_job`...

-- Generate results set...
--EXECUTE sp_get_composite_job_info @job_id,
EXECUTE WACK_sp_get_composite_job_info @job_id,

... and I'm working. (Or I could have just overwritten spgetcompositejobinfo with one that sorted different, but that's obviously destructive, and usually A Very Bad Idea.)

Labels: , ,