Friday, March 12, 2010

Management Studio - Scripting Agent Jobs Annoyance :/

I've noticed this a few times, but thought I'd finally note it because I still cannot think of any sensible reason for it. It's an annoyance / feature when you script sql agents jobs in Management Studio via the 'Generate Scripts' option.

To reproduce -
1) Right click an agent job
2) Select 'Script Job as'
3) Select either 'DROP To' or 'DROP And CREATE To' ...

My annoyance is at the top of the generated script in the delete step (sp_delete_job). You'll note below that the job to be deleted is referenced by job_id which is server specific (meaning the script cannot be reused without modification...
IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'my agent job')
EXEC msdb.dbo.sp_delete_job @job_id=N'c3776ebf-d7f4-4b97-bc74-c2d3aa745054', @delete_unused_schedule=1
GO

The modification is a simple one, and is demonstrated below. Pass the name of the job via the @job_name parameter rather than the @job_id. Maybe there is some security / safety reason for this behaviour but it just seems a pain to me...
IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'my agent job')
EXEC msdb.dbo.sp_delete_job @job_name= N'my agent job', @delete_unused_schedule=1
GO

No comments: