Create SQL Server job automatically

Question

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?

score 4 · Accepted Answer · answered Aug 01 '09 at 16:14

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.

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, Aug 02 '09 at 12:38
I have a related question here, appreciate if you could take a look, http://stackoverflow.com/questions/1219067/strange-scripts-generated-by-sql-server-when-exporting-a-job — George2, Aug 02 '09 at 14:58
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. — BrianD, Aug 02 '09 at 17:06
Thanks, I have a related question here, appreciate if you could help. http://stackoverflow.com/questions/1219067/strange-scripts-generated-by-sql-server-when-exporting-a-job — George2, Aug 02 '09 at 17:18
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 ? — deroby, Apr 02 '12 at 15:50
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. — Kiquenet, Jan 18 '17 at 11:49

score 3 · Answer 2 · answered Apr 02 '12 at 15:59

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

score 2 · Answer 3 · answered Aug 01 '09 at 22:34

2

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

answered Aug 01 '09 at 22:34

Ken Keenan

9,818
5
32
49

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 Aug 02 '09 at 12:38
I have a related question here, appreciate if you could take a look, http://stackoverflow.com/questions/1219067/strange-scripts-generated-by-sql-server-when-exporting-a-job – George2 Aug 02 '09 at 14:59

score 1 · Answer 4 · edited Nov 21 '12 at 21:09

1

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

edited Nov 21 '12 at 21:09

j0k

22,600
28
79
90

answered Nov 21 '12 at 20:50

Dan Hunex

5,172
2
27
38

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. – Dan Hunex Nov 26 '12 at 22:42
Thanks but what I meant it's best to include all relevant information into your answer. You can edit, etc. – Serge Belov Nov 27 '12 at 00:14

score 0 · Answer 5 · edited Oct 07 '17 at 04:07

I even got it better ...Create a stored procedure without a parameter and inside of the stored proc do :

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

Then assign @servername to the server_name parameter of sp_add_jobserver as in below:

msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name =@serverName

In order to create stored proc that creates a sql job, first create it using the sql management studio and then right click on the job and do Drop and Create....

Create SQL Server job automatically

5 Answers5

Linked