Create SQL Server job automatically

sql sql-server sql-server-2008 sql-server-agent

14,115

Solution 1

You would need to dynamically create the job script and then execute it. You could try something like the following or change this to a stored proc with input parameters for the job owner and database name.

DECLARE @JobName VARCHAR(20)  --Job Name
DECLARE @Owner VARCHAR(200)   --Job Owner
DECLARE @DBName VARCHAR(200)  --Database Name
DECLARE @JobCode VARCHAR(4000) --Create Statement for Job
SET @JobName = 'Test2'
SET @Owner = 'BrianD'
SET @DBName = 'master'
SET @JobCode = 'USE msdb
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
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''' + @JobName + ''', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N''No description available.'', 
        @category_name=N''[Uncategorized (Local)]'', 
        @owner_login_name=N''' + @Owner + ''', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N''Version and Prod Level'', 
        @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''select SERVERPROPERTY(''''productversion''''), SERVERPROPERTY(''''productlevel'''')'', 
        @database_name=N''' + @DBName + ''', 
        @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'
Exec (@JobCode)

Hopefully this will get you going in the right direction. If you need more help let me know.

Solution 2

Using the example from BrainD I'd like to point out that his idea of using variables is just fine, however, passing them to the stored procedures using dymanic SQL most certainly isn't the right approach. Rather, use the parameters of the stored procedures to directly pass the variables directly to where they are needed.

DECLARE @JobName VARCHAR(20)  --Job Name
DECLARE @Owner VARCHAR(200)   --Job Owner
DECLARE @DBName VARCHAR(200)  --Database Name
DECLARE @JobCode VARCHAR(4000) --Create Statement for Job
SET @JobName = 'Test2'
SET @Owner = 'BrianD'
SET @DBName = 'master'

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
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= @JobName, 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'No description available.', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name= @Owner, 
        @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Version and Prod Level', 
        @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'select SERVERPROPERTY(''productversion''), SERVERPROPERTY(''productlevel'')', 
        @database_name=@DBName, 
        @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

Solution 3

Futher to BrianD's answer above, you can define variables (actually more like preprocessor macros than variables) at the sqlcmd level; these are enclosed in parentheses with a leading $ sign:

SET @JobName = '$(JobName)'
SET @Owner = '$(Owner)'

This allows you to pass the information on the command line to sqlcmd with the -v switch:

sqlcmd -... -i create_jobs.sql -v JobName=Job1 -v Owner=BrianD

Solution 4

I was coming across the same problem today and how I tackle it is so simple Do the following

Create a stored proc that creates the job with parameter @serverName nvarchar(128)
```
Create Proc CreateAJob(@serverName nvarchar(128))
```

Now easy, you can get the server name using the following

Declare @serverName nvarchar(128)
SELECT @servername=@@SERVERNAME

Execute the stored proc to create your job
```
EXEC CreateJob @serverName
```

That is it.

I am using visual studio db project and I did steps 2 and 3 int the postdeployment script

View more solutions

14,115

Author by

George2

Updated on June 04, 2022

Comments

George2 almost 2 years

I am writing SQL Server deployment scripts which create SQL Server job automatically on a specific SQL Server server/instance. I have found that I can extract the sql statement which can be used to create SQL Server job automatically by using script job as => Create To.

My confusion is that, I find the database name and Owner account name are hardcoded in the sql scripts generated. When I am using sqlcmd to execute the sql scripts on another computer to perform deployment, the database name and Owner account name may be different, so I need a way to pass the database name and Owner account name to the SQL Server job creation script and let the script use the provided database name and Owner account name (other than hard coded ones).

Any ideas how to do that?
George2 almost 15 years

Thanks BrianD, do you think besides job owner and database name, i need to make any other values as configurable parameters to make the script more flexible?
George2 almost 15 years

Thanks BrianD, do you think besides job owner and database name, i need to make any other values as configurable parameters to make the script more flexible?
George2 almost 15 years

I have a related question here, appreciate if you could take a look, stackoverflow.com/questions/1219067/…
George2 almost 15 years

I have a related question here, appreciate if you could take a look, stackoverflow.com/questions/1219067/…
Pearl almost 15 years

You could make almost anything you want variable. You might start with Job Name, Description and Category. It's really up to you and what you will use it for. I have a few job creation scripts for creating/starting/stoping traces that I use. It's up to you and how much time you want to put into it.
George2 almost 15 years

Thanks, I have a related question here, appreciate if you could help. stackoverflow.com/questions/1219067/…
deroby about 12 years

I'm actually flabbergasted at the solution above. Why would you do this putting all the code in dynamic SQL while all you need to do is simply pass the variable to the msdb procedures ??? Will add an IMHO more logical script below... I mean, that's what parameters are for, no ?
Dan Hunex over 11 years

In order to create the stored proc, just go to the sql managment studio and create a sql job. Then do right click on the job you just created and do a Drop and Create, then you can modify the script so generated.
Serge Belov over 11 years

Thanks but what I meant it's best to include all relevant information into your answer. You can edit, etc.
Kiquenet over 7 years

Maybe using scripting (bat,cmd,ps1) with sqlcmd command for create job , and too start jog, view status, and view history and logs when finished.