Is there a way to specify Job ID when creating a job? Or is there a way to update the Job ID of a specific Agent Job that already exists?

I have a software from a vendor that uses the Job ID to reference jobs instead of using the Job Name. As far as I know (and in my testing) - when a job is created the Job ID is random. The vendor has a solution to rerun their Job Creation Tool when you failover. Is there a way to skip this step and make the Job ID the same between AG replicas?

  • 4
    Why not ask the Vendor to fix their mess ? Job id will be autogenerated by sql server. – Kin Dec 6 at 15:49
  • i will, but we already have other dev requests in, it will be some time before it would be addressed if at all. I figured there was no way around it, but i thought I'd ask just in-case there was something I didn't know about. – Kristofer Olafsson Dec 6 at 16:28
  • Has the vendor confirmed they support an AlwaysOn AG configuration? Some vendors' applications do not, even though a company may want that high availability. In some cases, it may make more sense to use a traditional FIC if the vendor does not support AlwaysOn AG. – SQL Sean Dec 7 at 16:44

I'm fairly certain this is a Very Bad Idea™, but... yes.

Using the UI as normal, go ahead and create the job you want. Before you hit Okay in the bottom right, stop; instead click 📜 Script in the top left hand corner of the dialogue window.

One of the batches that you will see in the resulting session will have sp_add_job, one of the parameters of which will be @job_id = @jobId OUTPUT (depending on your SQL Server version & SSMS user scripting preferences).

Awesome! Problem solved! You can just manually supply @job_id, super duper!

Actually...

Hey, jerk! I got an error!

Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server.

Yea, if you take a look at ~line 130 of object_definition(object_id('sp_add_job')), you will see something like...

IF (PROGRAM_NAME() NOT LIKE N'SQLAgent%')
BEGIN
  RAISERROR(14274, -1, -1)
  RETURN(1) -- Failure
END

If only there were some way to pretend to be the SQL Agent...

Or if you're feeling less fancy, you can always...

delete dbo.sysjobactivity 
where job_id = '11111111-1111-1111-1111-111111111111';

update dbo.sysjobs set 
    job_id = '22222222-2222-2222-2222-222222222222'
where job_id = '11111111-1111-1111-1111-111111111111';

...since sysjobs is a user table.

Obviously... it seems like the MS devs who wrote this may have been trying to protect you from yourself... So... maybe keep an eye out for weird, exotic breakage if you try this approach.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.