Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
WJHamel
Aged Yak Warrior
651 Posts |
 Posted - 2011-12-05 : 09:59:30
|
| I have a create script for a mult step agent job. I would like that script to check for the existence of, and drop if exists, the agent job first before executing the create. Typical stuff, but the other info i've found in-the-wild suggests the following syntax:DECLARE @jobId binary(16) SELECT @jobId = job_id FROM msdb.dbo.sysjobs WHERE (name = N'Name of Your Job') IF (@jobId IS NOT NULL) BEGIN EXEC msdb.dbo.sp_delete_job @jobId END DECLARE @ReturnCode int EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Name of Your Job' When i modify this script appropriately, it has no effect on the existing script and causes the create script to complain at the job already existing, as if the code to drop it were not even there. Any suggestions?Bangers and Mash are neither "Bangers" nor "Mash". |
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2011-12-05 : 10:03:02
|
| I've also tried the following at the beginning of the create script (this one contains my actual values):DECLARE @JobID BINARY(16) DECLARE @ReturnCode INT SELECT @ReturnCode = 0 -- Delete the job with the same name (if it exists) SELECT @JobID = job_id FROM msdb.dbo.sysjobs WHERE (name = N'[Create Sizing_DB]') IF (@JobID IS NOT NULL) BEGIN -- Check if the job is a multi-server job IF (EXISTS (SELECT * FROM msdb.dbo.sysjobservers WHERE (job_id = @JobID) AND (server_id <> 0))) BEGIN -- There is, so abort the script RAISERROR (N'Unable to import job ''[Create Sizing_DB]'' since there is already a multi-server job with this name.', 16, 1) END ELSE -- Delete the [local] job EXECUTE msdb.dbo.sp_delete_job @job_name = N'[Create Sizing_DB]' SELECT @JobID = NULL END Also has no effect.Bangers and Mash are neither "Bangers" nor "Mash". |
 |
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2011-12-05 : 10:08:32
|
| Gheez. I need to stop posting before i'm done troubleshooting. Disregard the above. I got the following to fire off correctly:USE [msdb]GODECLARE @JobID BINARY(16) DECLARE @ReturnCode INT SELECT @ReturnCode = 0 -- Delete the job with the same name (if it exists) SELECT @JobID = job_id FROM msdb.dbo.sysjobs WHERE (name = N'Create Sizing_DB') IF (@JobID IS NOT NULL) BEGIN -- Check if the job is a multi-server job IF (EXISTS (SELECT * FROM msdb.dbo.sysjobservers WHERE (job_id = @JobID) AND (server_id <> 0))) BEGIN -- There is, so abort the script RAISERROR (N'Unable to import job ''Create Sizing_DB'' since there is already a multi-server job with this name.', 16, 1) END ELSE -- Delete the [local] job EXECUTE msdb.dbo.sp_delete_job @job_name = N'Create Sizing_DB' SELECT @JobID = NULL END My error was the bracketing of the job name.Bangers and Mash are neither "Bangers" nor "Mash". |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-12-05 : 10:09:38
|
| job id is a uniqueidentifier but that sholdn't matter.Have a look at sysjobs to see what the name of the job is. It probably doesn't have the [].Also if you don't have permission it won't return anything.Just run the check statement and see if it returns a job id.also delete job can take a job name so you can use an exists statement rather than returning the job id.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
Agent Jobs - create script