By: Rick Dobson | Updated: 2017-08-23 | Comments | Related: > SQL Server Agent
Problem
I want to leverage my organization's existing SQL Server Agent Jobs to create new ones. I tried scripting existing jobs to help me learn how to create new ones with T-SQL, but I found the automatically generated scripts neither easy to read nor modify. Please present a couple of examples of modifying automatically generated job scripts to create new ones in a way that will empower me to add new SQL Server Agent Jobs that leverage existing jobs as a model.
Solution
SQL Server Agent, also known as SQL Agent, provides a graphical interface for creating and editing jobs. Additionally, you can automatically generate scripts for creating any job that you initially created with the graphical SQL Agent interface. With knowledge of a few msdb stored procedures and how SQL Agent writes code to create jobs, you can be on your way to developing a library of scripts that can be leveraged for programmatically adding new jobs.
This tip takes a couple of jobs created with a graphical interface in a prior MSSQLTips.com tip and modifies the automatically generated scripts for those jobs to create new jobs. By referring to the prior tip, you will be able to learn about the original jobs to help you better understand the design of the automatically generated scripts for creating jobs. This review will also provide a foundation for understanding how to edit the automatically generated scripts for prior jobs to create scripts for new jobs.
The review of automatically generated scripts will highlight how the built-in scripter invokes a collection of stored procedures from within a transaction. This tip also provides two step-by-step examples for modifying an automatically generated script for an existing job into a script for a new job. Finally, the tip explains and demonstrates the purpose of each newly created job -- especially, the second one to track job history in a table external to the msdb database.
This tip assumes a basic understanding of how jobs are created manually as well as managed by SQL Agent. For example, you should have a minimal degree of familiarity with the following msdb database tables: sysjobs, sysjobsteps, sysjobschedules, sysschedules, and sysjobhistory. If you do not have this background, you can get the appropriate exposure by scanning these links on inventorying SQL Agent jobs and on tracking and counting SQL Agent job runs.
Script a graphically created job that creates a table
One good place to start learning about scripting jobs is from a previously created job. By examining the previous job with the SQL Agent graphical interface, you can learn the features of the job even if you are unfamiliar with how to program a script for a new job.
The following screen shot shows a SQL Agent menu selection for scripting an existing job. This is the first job created in a prior MSSQLTips.com tip. The name of the previously created job is "Create a table". By right-clicking a job name in the Jobs folder under the SQL Server Agent node in Object Explorer, you open the top-level context menu that exposes the Script Job as menu item. In turn, this exposed item shows another context menu, whose first menu item is CREATE To. The New Query Editor Window item below the CREATE To menu item scripts the selected job to a new query window.
The following script shows a very slightly edited version of the automatically generated script for the Create a table job. The edits are just to make the automatically generated code easier to read. There are two types of edits.
The top comment line is a general description the content that follows.
Next, a sequence of lines composed of dashes (such as, -----) divides the overall script into segments. The main segments are:
- BEGIN TRANSACTION and COMMIT TRANSACTION statements
- an EXEC statement for each stored procedure invocation between the BEGIN TRANSACTION and COMMIT TRANSACTION statements
- a conditional ROLLBACK TRANSACTION statement
The BEGIN TRANSACTION and COMMIT TRANSACTION statements wrap a sequence of EXEC statements for a series of stored procedures.
- Each stored procedure plays some part in scripting the Create a table job.
- If any EXEC statement fails for the stored procedures nested between the BEGIN TRANSACTION and COMMIT TRANSACTION statements, control passes to the ROLLBACK TRANSACTION statement. This ROLLBACK TRANSACTION statement ends the transaction without creating the job, and the Messages tab shows a description for the error.
- If all stored procedures within the transaction execute successfully, control passes to the COMMIT TRANSACTION statement, which, in turn, creates the job.
From the following script, this is the list of EXEC statements for stored procedures between the BEGIN TRANSACTION and COMMIT TRANSACTION statements.
- EXEC @ReturnCode = msdb.dbo.sp_add_category
- EXEC @ReturnCode = msdb.dbo.sp_add_job
- EXEC @ReturnCode = msdb.dbo.sp_add_jobstep
- EXEC @ReturnCode = msdb.dbo.sp_update_job
- EXEC @ReturnCode = msdb.dbo.sp_add_jobserver
The first stored procedure (sp_add_category) assigns a custom category identifier to the job by referencing the syscategories table in the msdb database. If the desired category name value does not already exist, then a row is added to the syscategories table with the desired category. The custom category name is "tip 1". Category identifiers facilitate the grouped display of all jobs related to a function, such as for backups or production runs.
The second stored procedure (sp_add_job) inserts a new row in the sysjobs table in the msdb database. This stored procedure allows you to manage and track job level properties, such as job name, job owner, and job_id value. Notice that the script designates the job_id value as a BINARY(16) data type, which is a more generic data type than the uniqueidentifier data type for the job_id column in the sysjobs table. Also pay special attention to the fact that the EXEC statement assigns the job_id column value for the new row in the sysjobs table to the @jobId local variable. The returned parameter designates the job_id for the newly created job, and the local variable is used to synchronize rows in multiple msdb tables for a single job.
The third stored procedure named sp_add_jobstep adds a new row to the sysjobsteps table in the msdb database. Every SQL AGENT job must have at least one job step, but a job can also have more than one job step. Notice especially the @command parameter value. This parameter value indicates what the job step does (for example, it includes the T-SQL code for the job step). Also note that each apostrophe (') within the string for the command parameter value are escaped by another apostrophe. Each job step within a job must have a command setting.
The fourth stored procedure (sp_update_job) explicitly sets the first job step number to use when running the job. In this case, there is only one job step with a step_id value of 1. When defining a multi-step job, you can optionally select the step number for any other step in a job as the first step to execute when the job is invoked.
The fifth stored procedure (sp_add_jobserver) designates the server on which the job should run. It is a good practice to include this stored procedure even when creating a new job based on an edited prior job script . Also, a jobserver column value assignment can become an issue when you are deploying a job from one server to another .
-- scripted output for the Create a table job
USE [msdb]
GO
-----------------------------------------------------------------------------------------
/****** Object: Job [Create a table] Script Date: 7/28/2017 10:33:27 AM ******/
BEGIN TRANSACTION
-----------------------------------------------------------------------------------------
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [tip 1] Script Date: 7/28/2017 10:33:27 AM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'tip 1' AND category_class=1)
BEGIN
-----------------------------------------------------------------------------------------
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'tip 1'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
-----------------------------------------------------------------------------------------
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Create a table',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'The table records numeric and datetime values',
@category_name=N'tip 1',
@owner_login_name=N'ServerName\UserName', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [This step creates a table] Script Date: 7/28/2017 10:33:27 AM ******/
-----------------------------------------------------------------------------------------
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'This step creates a table',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'USE [for_SQL_Server_Agent_jobs]
GO
-- CREATE TABLE in default schema TO ACCEPT number_value and date inserts
IF EXISTS
(
SELECT TABLE_NAME
FROM [for_SQL_Server_Agent_jobs].INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = ''RecordsNumericandDateTimeValues''
)
DROP TABLE [for_SQL_Server_Agent_jobs].dbo.RecordsNumericandDateTimeValues
CREATE TABLE [dbo].[RecordsNumericandDateTimeValues](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[number_value] [nchar](10) NULL,
[date] [datetime] NULL,
CONSTRAINT [PK_RecordsNumericandDateTimeValues] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
) ON [PRIMARY]
GO
',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-----------------------------------------------------------------------------------------
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-----------------------------------------------------------------------------------------
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-----------------------------------------------------------------------------------------
COMMIT TRANSACTION
GOTO EndSave
-----------------------------------------------------------------------------------------
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
Editing the Create a table script for a new job
So long as your need is for another single-step job without a schedule, it is easy to edit the preceding script to generate a new job. The following script is an edited version of the preceding script. The major change is to create a table with a different name in the for_SQL_Server_Agent_jobs database. The new table is for storing job run history details, such as job name along with information about when a job ran. The purpose of the new table is to maintain job run history information outside of the sysjobhistory table in the msdb database. Aside from the new table, there is another programming change, which is the specification of a new data type for the @jobId local variable. Finally, a new row is added to the syscategories table if there is not already one there with a name column value of tip 5.
Aside from the changes noted above, the architecture of the following script is the same as the preceding script. Again, the job is created within a transaction, and the new job is not created unless all stored procedures invoked within the transaction run successfully. Additionally, the names and sequence of stored procedures invoked from within the transaction are identical.
As indicated, the major change for the script below is the designation of a new table to be created. This can be accomplished as easily as updating the string constant assigned to the @command parameter for the sp_add_jobstep stored procedure. The two types of changes are:
- Designating a new table name; the new name of JobRunLog replaces the old name of RecordsNumericandDateTimeValues
- Specifying new columns for the JobRunLog table; the new columns are for maintaining information denoting a job and when it ran, including
- Job name and job_id
- Run_datetime, a datetime data type for denoting when a job run occurred
The DECLARE statement for the @jobId local variable designates a new data type of uniqueidentifier. The automatic scripting capability declares the @jobId variable as a BINARY(16) data type. This switch in the data type from BINARY(16) to uniqueidentifier synchronizes the variable's data type with the table from which it is populated (namely, sysjobs) and the tables that ultimately get populated from the @jobId local variable. After its declaration, this local variable is initially populated by an output parameter assignment from the sp_add_job stored procedure. Then, the local variable is successively used as an input parameter value to the sp_add_jobstep, sp_update_job, and sp_add_jobserver stored procedures.
Towards the top of the following script, you can view the process for creating a new job category named tip 5. The first part of the process is for verifying whether there is already a job category named tip 5. If the tip 5 category does not already exist, then it is created by adding a new row to the syscategories table with the sp_add_category stored procedure. In any event, the sp_add_job stored procedure assigns tip 5 as the value of the category name for the new job.
-- edited script for add a new job (Create a job run log table) to add a new table
-- (JobRunLog) based on the script for the Create a table job
USE [msdb]
GO
-- start a transaction to create a job
BEGIN TRANSACTION
-----------------------------------------------------------------------------------------
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
-- Check for prior existence of a job category named tip 5
-- create the category if it does not already exists
-- in any event assign the category name to the new job
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'tip 5' AND category_class=1)
BEGIN
-----------------------------------------------------------------------------------------
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'tip 5'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
-----------------------------------------------------------------------------------------
-- replace legacy BINARY(16) data type with a uniqueidentifier data type
DECLARE @jobId uniqueidentifier
-- invoke the sp_add_job stored proc to create a job named Create a job run log table
-- @command parameter changes for the new table
-- if the sp generates an error or the return code does not indicate success
-- quit and rollback the job create task
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Create a job run log table',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'The table logs job runs',
@category_name=N'tip 5',
@owner_login_name=N'ServerName\UserName', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/* Object: Step [This step creates a table] */
-----------------------------------------------------------------------------------------
-- assign a new string value for the new table named JobRunLog
-- in the for_SQL_Server_Agent_jobs database
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'This step creates a table',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'USE [for_SQL_Server_Agent_jobs]
GO
-- CREATE TABLE in default schema TO ACCEPT job_id, name, and run_datetime
IF EXISTS
(
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = ''JobRunLog''
)
DROP TABLE dbo.JobRunLog
CREATE TABLE [dbo].[JobRunLog](
job_id [uniqueidentifier] NOT NULL,
[name] [sysname],
[run_datetime] [datetime] NOT NULL,
CONSTRAINT [PK_JobRunLog_job_id_run_datetime] PRIMARY KEY CLUSTERED
(
[job_id], [run_datetime]
)
) ON [PRIMARY]
GO',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-----------------------------------------------------------------------------------------
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-----------------------------------------------------------------------------------------
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
The Automatically Generated Script for a Scheduled Job
The second example of an automatically scripted job is for one with a schedule. This job has the name Insert a value in a table with a schedule. This second automatically scripted job complements the preceding automatically scripted job (Create a table) by inserting a series of values on a schedule into the table created within the first automatically scripted job; recall that the name of the table created in the first job is RecordsNumericandDateTimeValues. The full script for generating the job to insert values into the table appears below. As with the previous automatically scripted code sample, minor editing is performed on the automatically generated script for making the code easier to read.
The general design of the two automatically generated scripts is highly similar, but there is one critical difference. This second automatically generated job script includes one additional stored procedure call inside the transaction for creating the job. The name of the additional stored procedure is sp_add_jobschedule. The EXEC statement for the stored procedure occurs just prior to the last EXEC statement. Here's the full list of EXEC statements in the order in which they are invoked for creating the Insert a value in a table with a schedule job.
- EXEC @ReturnCode = msdb.dbo.sp_add_category
- EXEC @ReturnCode = msdb.dbo.sp_add_job
- EXEC @ReturnCode = msdb.dbo.sp_add_jobstep
- EXEC @ReturnCode = msdb.dbo.sp_update_job
- EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule
- EXEC @ReturnCode = msdb.dbo.sp_add_jobserver
The @command parameter value for the sp_add_jobstep stored procedure call is a string constant with code to insert the value 1 and the current datetime into the RecordsNumericalandDateTimeValues table.
The sp_add_jobschedule designates when and how often the Insert a value in a table with a schedule job is scheduled to run. The stored procedure adds a row in the sysschedules table for the job with the name Run daily every 5 minutes. The other parameters for the sp_add_jobschedule stored parameter indicate settings for the schedule, such as
- @freq_type = 4, @freq_subday_type = 4 and @freq_subday_interval = 5, which means the job runs every 5 minutes daily
- @active_start_date=20170409, which indicates the start date for the schedule's operation is April 9, 2017
By the way, the sp_add_jobschedule stored procedure is widely referred to as deprecated (since at least SQL Server 2008 R2). Nevertheless, it remains in use within Microsoft's own automatically generated script for SQL Agent jobs with a schedule. Therefore, it is not likely the stored procedure will lose support until Microsoft revises its automatic scripting code for jobs with a schedule. You'll see in the next section a work-around to using the sp_add_jobschedule stored procedure.
-- scripted output for the Insert a value in a table with a schedule job
USE [msdb]
GO
/****** Object: Job [Insert a value in a table with a schedule] Script Date: 7/29/2017 5:05:47 PM ******/
BEGIN TRANSACTION
-----------------------------------------------------------------------------------------
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 7/29/2017 5:05:47 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
-----------------------------------------------------------------------------------------
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
-----------------------------------------------------------------------------------------
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Insert a value in a table with a schedule',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'This job has a schedule',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'ServerName\UserName', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [This step inserts the value 1 into the RecordsNumericalandDateTimeValues table with a schedule] Script Date: 7/29/2017 5:05:47 PM ******/
-----------------------------------------------------------------------------------------
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'This step inserts the value 1 into the RecordsNumericalandDateTimeValues table with a schedule',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'USE for_SQL_Server_Agent_jobs
INSERT INTO RecordsNumericandDateTimeValues
SELECT 1, getdate()',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-----------------------------------------------------------------------------------------
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-----------------------------------------------------------------------------------------
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Run daily every 5 minutes',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=5,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20170409,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N'6756ad84-3b00-4618-81a5-05e2ca4fcbd8'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-----------------------------------------------------------------------------------------
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-----------------------------------------------------------------------------------------
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
Editing the Insert a value in a table with a schedule script to create a new job
The edited version of a script for creating a new job with a schedule appears below. The new job is intended to copy fresh data from the sysjobhistory table in the msdb database to the JobRunLog table in the for_SQL_Server_Agent_jobs database. This edited script bears several elements in common with the preceding example, but it is also distinctly different in two areas.
- First, it takes a different approach to specifying the schedule for the job; this is because the script for this job builds a new custom schedule for the new job and then references the new custom schedule.
- Second, the T-SQL code for the @command parameter implements more robust functionality than the simple T-SQL example in the preceding automatically generated script.
Unlike the deprecated sp_add_jobschedule approach to specifying a schedule for a new job, the process for creating a schedule and then relying on a new schedule has two parts.
- You start by invoking the sp_add_schedule stored procedure to specify a new schedule for the new job.
- Later, you invoke the sp_attach_schedule stored procedure to attach the newly created schedule to the new job.
The script for creating a job with a custom schedule still relies on the use of a transaction with a sequence of embedded stored procedure calls. As with the preceding scripts for creating a SQL Agent job, all the stored procedures within a transaction need to run successfully for the new job to be created. Here's the list of the stored procedure calls in the order in which they appear in the script below.
- EXEC @ReturnCode = msdb.dbo.sp_add_category
- EXEC @ReturnCode = msdb.dbo.sp_add_schedule
- EXEC @ReturnCode = msdb.dbo.sp_add_job
- EXEC @ReturnCode = msdb.dbo.sp_add_jobstep
- EXEC @ReturnCode = msdb.dbo.sp_update_job
- EXEC @ReturnCode = msdb.dbo.sp_attach_schedule
- EXEC @ReturnCode = msdb.dbo.sp_add_jobserver
The string constant for the @command parameter in the sp_add_jobstep procedure call is interesting from several perspectives.
- Through the parameter, this procedure populates a non-msdb table with job run history in a way that solves an issue with a prior MSSQLTips.com tip on tracking and counting job runs (URL not yet available because the tip is in queue). The prior tip closed with a presentation of how to write job run history data from the sysjobhistory table to an external table in a non-msdb database, but the process permitted duplicate rows for the same job run. The code in the @command argument does not permit the entry of duplicate rows in an external table for job run history.
- Also, the code in the @command parameter value demonstrates how to convert and combine run_date and run_time values from int data types in the sysjobhistory table to one datetime value in the external JobRunLog table.
- The code inside the @command parameter value illustrates how to use an EXCEPT set operator between the sysjobhistory table and the JobRunLog table to ensure that no duplicate rows are entered into the JobRunLog table no matter how many times the Insert into JobRunLog table with a schedule job is run.
Selected parameters for the sp_add_schedule procedure merit commentary.
- The new schedule created with the stored procedure has the name Weekly on Saturday Morning at 1 AM; the code uses the @name parameter to designate the schedule name.
- The schedule_uid output parameter value is saved in the scheduleuid local variable to demonstrate that this operation is possible. However, the operation is not required because the sp_attach_schedule procedure call uses the @name parameter instead of the @schedule_uid parameter to reference a schedule for the new job. You can use either the @name or @schedule_uid parameter to attach a schedule to a job, but both schedule identifiers are not required concurrently.
- @freq_type = 8, @freq_interval = 64, and freq_recurrence_factor = 1 indicates the schedule runs once per week on Saturday.
- @freq_subday_type = 1 and active_start_time = 010000 specifies that the schedule runs just at 1 AM and not recurring times throughout a day.
-- edited script for adding a new job (Insert into JobRunLog table with a schedule)
-- to add new job run history since last update to the table (JobRunLog) based on
-- the script for the Insert a value in a table with a schedule job
USE [msdb]
GO
-- start a transaction to create a job
BEGIN TRANSACTION
-----------------------------------------------------------------------------------------
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
-- Check for prior existence of a job category named tip 5
-- create the category if it does not already exist
-- in any event assign the category name to the new job
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'tip 5' AND category_class=1)
BEGIN
-----------------------------------------------------------------------------------------
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'tip 5'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
-----------------------------------------------------------------------------------------
-- add a schedule for the job
DECLARE @scheduleuid uniqueidentifier
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysschedules WHERE name=N'Weekly on Saturday Morning at 1 AM')
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_schedule
--EXEC msdb.dbo.sp_add_schedule
@schedule_name = N'Weekly on Saturday Morning at 1 AM',
@enabled = 1,
@freq_type = 8,
@freq_interval = 64,
@freq_recurrence_factor = 1,
@active_start_date=20170729,
@active_start_time = 010000,
@freq_subday_type = 1,
@schedule_uid = @scheduleuid OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
-----------------------------------------------------------------------------------------
-- add the job
-- replace legacy BINARY(16) data type with a uniqueidentifier data type
DECLARE @jobId uniqueidentifier
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Insert into JobRunLog table with a schedule',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'This job has a schedule',
@category_name=N'tip 5',
@owner_login_name=N'ServerName\UserName', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-----------------------------------------------------------------------------------------
-- add a job step
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'This step inserts the sysjobhistory table contents into JobRunLog with a schedule',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'
-- insert all overall job rows from sysjobhistory table not already in JobRunLog table
insert into for_SQL_Server_Agent_jobs.dbo.JobRunLog
select
sysjobs.job_id
,sysjobs.name
,
-- expression to combine run_date and run_time column values with int datatype
-- to a single datetime column value for run_datetime
CAST(
STUFF(
STUFF(CAST(run_date as varchar(8)),5,0,''-''),8,0,''-''
) -- run_date_trans
+'' '' -- date and time separator
+ STUFF(
STUFF(RIGHT(REPLICATE(''0'', 6) + CAST(run_time as varchar(6)), 6), 3, 0, '':''), 6, 0, '':''
) -- run_time_trans
AS datetime) run_datetime
from msdb.dbo.sysjobhistory
inner join msdb.dbo.sysjobs on sysjobs.job_id = sysjobhistory.job_id
where step_id = 0
except
select * from for_SQL_Server_Agent_jobs.dbo.JobRunLog',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-----------------------------------------------------------------------------------------
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-----------------------------------------------------------------------------------------
EXEC @ReturnCode = msdb.dbo.sp_attach_schedule
@job_name = N'Insert into JobRunLog table with a schedule',
@schedule_name = N'Weekly on Saturday Morning at 1 AM'
-----------------------------------------------------------------------------------------
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
Next Steps
- This article presents four scripts for creating SQL Agent jobs. Additionally, another script is added to the tip's download for creating the for_SQL_Server_Agent_jobs database. To run the four scripts for creating jobs as is, you will require access to a database named for_SQL_Server_Agent_jobs. Please be sure to update the five scripts as specified in code comments for database file location and @owner_login_name parameter value.
- After configuring your environment as indicated, start by invoking the first job creation script. The job should create a table named RecordsNumericandDateTimeValues in the for_SQL_Server_Agent_jobs database.
- After confirming the first script works, try to edit the first script as indicated in the tip. The edited script will create another table named JobRunLog in the for_SQL_Server_Agent_jobs database.
- Next, open the third script from the download for this tip into a New Query tab and run it. This script will create a job that starts populating the RecordsNumericandDateTimeValues table -- once every five minutes on a daily basis.
- After confirming the third script works, start editing the third script as indicated in the tip to re-create the fourth script. This re-created fourth script will create a new job for populating the JobRunLog table. The schedule for this job runs just once a week on Saturday morning at 1 AM. However, you can also manually run the job on demand. The list of job runs will grow at least every 5 minutes if you previously confirmed the third script is working as intended by manually running the third script on demand.
- Finally, remember to take advantage of the MSSQLTips.com treasure trove of tips on SQL Agent. This link presents an index for prior SQL Agent tips.
About the author
Rick Dobson is an author and an individual trader. He is also a SQL Server professional with decades of T-SQL experience that includes authoring books, running a national seminar practice, working for businesses on finance and healthcare development projects, and serving as a regular contributor to MSSQLTips.com. He has been growing his Python skills for more than the past half decade -- especially for data visualization and ETL tasks with JSON and CSV files. His most recent professional passions include financial time series data and analyses, AI models, and statistics. He believes the proper application of these skills can help traders and investors to make more profitable decisions.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2017-08-23
