Sample Header Ad - 728x90

SQL Server Agent Job - Adding Version Number

1 vote
4 answers
1337 views
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](https://stackoverflow.com/questions/15411316/sql-server-agent-job-adding-version-number/48627860#48627860) 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](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-update-job-transact-sql) 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](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-category-transact-sql) but that presents the value in the GUI drop down as available for all jobs. Possible, but suboptimal. I can use [sp_update_job](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-update-jobstep-transact-sql) 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](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addextendedproperty-transact-sql) 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.
Asked by James Jenkins (6318 rep)
Feb 5, 2018, 06:42 PM
Last activity: Feb 26, 2018, 12:18 PM