Sql Server Agent Job - Adding Version Number

Question

I created a monitoring tool using sql server 2008 r2 dmvs and dmfs. I created a sql server job for it via tsql with 9 steps to deployed it to the client site.

My question is to find out a way to include a version stored with the job somehow so that I know when to upgrade the job for the next deployment. Any ideas how to do that?

Thanks in Advance!

score 0 · Accepted Answer · answered Mar 14 '13 at 14:08

You can add custom properties to database objects like this:

EXEC sp_addextendedproperty
    N'YourProperyName', N'SomeValue',
    N'SCHEMA', N'dbo',
    N'TABLE', N'MyTable'

But I don't know if it works for jobs. Or maybe use Description field of a job:

select top 10 * from msdb.dbo.sysjobs

score 0 · Answer 2 · answered Mar 31 '14 at 16:50

You can use sp_addextendedproperty to Version the msdb.dbo.sysjobs table or a column (e.g. version_number); however, you cannot do this for a particular record. Since the TYPE of the value of an extended property is SQLVARIANT, you could store XML on the msdb.dbo.sysjobs table, depicting the versions of all the jobs.

Or you could simply create a table in your database and store the job_id (GUID) and your current version number--a custom solution.

For what it's worth, here is how you can use sp_addextendedproperty to add, view, update and delete an extended property on the sysjobs table. I went as far as the COLUMN, but you could stop with the table:

--Add the 'Version' extended property to msdb.dbo.sysjobs.[version_number] column.
EXECUTE sp_addextendedproperty N'Version', '1.0.0.0', 'SCHEMA', N'dbo', 'TABLE', N'sysjobs', 'COLUMN', N'version_number';

--Update the 'Version' extended property
EXECUTE sp_updateextendedproperty N'Version', '1.0.0.2', 'SCHEMA', N'dbo', 'TABLE', N'sysjobs', 'COLUMN', N'version_number';

--View the current value of 'Version'
SELECT objtype, objname, name, value
FROM fn_listextendedproperty(N'Version', 'SCHEMA', N'dbo', 'TABLE', N'sysjobs', 'COLUMN', N'version_number');

--Drop the 'Version' extended property
EXECUTE sp_dropextendedproperty N'Version', 'SCHEMA', N'dbo', 'TABLE', N'sysjobs', 'COLUMN', N'version_number';

score 0 · Answer 3 · edited Jun 20 '20 at 09:12

You can not use sp_addextendedproperty to hold a version number in a job

[ @level1type= ] { 'level1_object_type' }

Is the type of level 1 object. level1_object_type is varchar(128), with a default of NULL. Valid inputs are AGGREGATE, DEFAULT, FUNCTION, LOGICAL FILE NAME, PROCEDURE, QUEUE, RULE, SYNONYM, TABLE, TABLE_TYPE, TYPE, VIEW, XML SCHEMA COLLECTION, and NULL. Source

Doing so would require changing the value of 'level1_object_type' to 'JOB' which is not an option.

Sql Server Agent Job - Adding Version Number

3 Answers3