Create SQL Server Job Automatically

Create SQL Server job automatically

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.

Automatically create scripts for all SQL Server Jobs

After some further investigation I found a good script that did the job
Generate-Scripts-for-SQL-Server-Objects

The code below is what I needed:

DECLARE @object int
exec sp_OACreate 'SQLDMO.SQLServer', @object OUT
exec sp_OASetProperty @object, 'LoginSecure', TRUE
exec sp_OAMethod @object,'Connect(sqltest)'
exec sp_OAMethod @object, 'Jobserver.Jobs().Script()'
exec sp_OADestroy @object

Thanks for the help anyway

How to start SQL Server agent jobs automatically

When you start SQL Server Agent all jobs (if they are not disabled) will run automatically and it's no need to control each job. Just make sure that jobs are enabled.

Schedule SQL Job X minutes after SQL Server Agent starts

Sure, just make the first step:

WAITFOR DELAY '00:15:00';

(Or, probably better, you could try to resolve whatever "some issues" are.)

However, note that someone can restart the Agent service without restarting SQL Server; or, they could set SQL Server Agent to not automatically start at startup, so that the next time the SQL Server service is restarted, your procedure will not run.

If you want to tie some startup activity to SQL Server starting, you could probably look into startup procedures, but if you need it to wait 15 minutes, the first thing in the procedure would be the above WAITFOR. Also startup procedures can't have input parameters (or output parameters, but that is less likely to be an issue for a stored procedure you're calling from a job).

Finally, SQL Server 2008 R2? That was barely still in support at the beginning of the last decade.

How can I schedule a job to run a SQL query daily?

1. Expand the SQL Server Agent node and right click the Jobs node in SQL Server Agent and select 'New Job'

2. In the 'New Job' window enter the name of the job and a description on the 'General' tab.

3. Select 'Steps' on the left hand side of the window and click 'New' at the bottom.

4. In the 'Steps' window enter a step name and select the database you want the query to run against.

5. Paste in the T-SQL command you want to run into the Command window and click 'OK'.

6. Click on the 'Schedule' menu on the left of the New Job window and enter the schedule information (e.g. daily and a time).

7. Click 'OK' - and that should be it.

(There are of course other options you can add - but I would say that is the bare minimum you need to get a job set up and scheduled)

---

Related Topics