SQL Server – Adding Version Number to SQL Server Agent Job

jobssql serverversion control

I would like to know how to add a version number to a SQL Server Agent job without using the description field, and with it being an attribute of the job.

A similar question was asked at StackOverflow Sql Server Agent Job – Adding Version Number but that question did not include the criteria of not using the description field. (accepted answer implies the description field was viable solution)

I have a script to update jobs, and I want to capture version information without overwriting any existing descriptions and being able to search for current version on a single field (without combing existing/new comments with version info)

I can use sp_update_job to update most fields, but the only one not in use that will take strings is @category_name and it is limited to values in sp_help_category. (Edit following day >) It can be updated with sp_add_category but that presents the value in the GUI drop down as available for all jobs. Possible, but suboptimal.

I can use sp_update_job this would be suboptimal as steps are parts of jobs. I don't see any reasonable solutions in there. I did consider creating a step named "Version 1.0.0" or similar but that was wrong on many levels. EDIT After much research and testing it became clear that this was the optimal approach.

You can not use sp_addextendedproperty to hold a version number in a job. Doing so would require changing the value of 'level1_object_type' to 'JOB' which is not an option. Source

I can use a table to list modifications. But that would not be an attribute of the job, and is subject to human error insertion. Possibly I might use a table, where a hash of the command field (@command) and/or schedule is used as a unique identifier. This would/should be unique to job version, while not a direct attribute it would be a derived attribute.

Solution to apply to SQL Server 2008R2 and later by preference, SQL Server 2012 and later by requirement.

Best Answer

This is a very interesting question. I think I agree with @Gareth Lyons in some way. My suggestion is that you append your version information in the step name of the first job step (after all, all jobs have at least a job step to be meaningful)

So what you can do is like this: For the first time, say my job [test_job] has the following steps

Initial Job Steps

Now I can run the following code to append the version info to the 1st step name

-- initially appending version name to step 1 name 
declare @job_name sysname = 'test_job';
declare @ver varchar(128) = '<V 1.0.0>';

declare @step_1st_name sysname;
select @step_1st_name=step_name + ' - ' + @ver --adding version string to the step 1 name
from msdb.dbo.sysjobsteps s 
inner join msdb.dbo.sysjobs j 
on s.job_id = j.job_id 
and j.name=@job_name
and s.step_id = 1;


exec msdb.dbo.sp_update_jobstep @job_name=@job_name, @step_id=1, @step_name= @step_1st_name;
go

After running the script, you will see the job step name changed to the following

Initial appending version

Later, if we need to update the version, we can easily operate on the 1st step name as the following

-- updating version name later when it is necessary

declare @job_name sysname = 'test_job';
declare @ver varchar(128) = '<V 2.0.0>';

declare @step_1st_name sysname;
select @step_1st_name=step_name 
from msdb.dbo.sysjobsteps s 
inner join msdb.dbo.sysjobs j 
on s.job_id = j.job_id 
and j.name=@job_name
and s.step_id = 1;

-- replace old version with new version number
set @step_1st_name = substring(@step_1st_name, 1, charindex('<V', @step_1st_name)-1) + @ver;
exec msdb.dbo.sp_update_jobstep @job_name=@job_name, @step_id=1, @step_name= @step_1st_name;
go

If we run the script, we can see the job 1st step name is changed to the following

update the version

It is also very easy for you to extract the version number from any job's first step name.

I hope this may help you.

Related Question