USE [master]
GO
IF OBJECT_ID('dbo.sp_TSQLJobCreate') IS NULL
EXEC('CREATE PROCEDURE dbo.sp_TSQLJobCreate AS SELECT 1 AS ID')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE dbo.sp_TSQLJobCreate
@job_name NVARCHAR(256),
@step_name NVARCHAR(256),
@database_name SYSNAME,
@TSQLCommand NVARCHAR(MAX),
@Overwrite BIT = 1,
@notify_email_operator_name NVARCHAR(256) = NULL,
@owner_login_name NVARCHAR(256) = NULL
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
IF @owner_login_name IS NULL
BEGIN
DECLARE @ServicePath NVARCHAR(156) = N'SYSTEM\CurrentControlSet\Services\' + CASE WHEN @@SERVICENAME = 'MSSQLSERVER' THEN 'MSSQLSERVER'
ELSE 'MSSQL$' + @@SERVICENAME
END
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
@ServicePath,
N'ObjectName',
@owner_login_name OUTPUT,
N'no_output'
END
DECLARE @jobId BINARY(16)
IF @Overwrite = 1
BEGIN
SELECT @jobId = job_id
FROM msdb.dbo.sysjobs (NOLOCK)
WHERE name = @job_name
IF @@ROWCOUNT = 1
EXEC msdb.dbo.sp_delete_job
@job_id = @jobId
SET @jobId = NULL
END
EXEC msdb.dbo.sp_add_job
@job_name = @job_name,
@enabled = 1,
@notify_level_eventlog = 0,
@notify_level_email = 2,
@notify_level_netsend = 2,
@notify_level_page = 2,
@delete_level = 0,
@category_name = N'[Uncategorized (Local)]',
@owner_login_name = @owner_login_name,
@notify_email_operator_name = @notify_email_operator_name,
@job_id = @jobId OUTPUT
EXEC msdb.dbo.sp_add_jobserver
@job_id = @jobId,
@server_name = @@SERVERNAME
EXEC msdb.dbo.sp_add_jobstep
@job_name = @job_name,
@step_name = @step_name,
@step_id = 1,
@cmdexec_success_code = 0,
@on_success_action = 1,
@on_fail_action = 2,
@retry_attempts = 0,
@retry_interval = 0,
@os_run_priority = 0,
@subsystem = N'TSQL',
@command = @TSQLCommand,
@database_name = @database_name,
@flags = 4
IF @notify_email_operator_name <> ''
EXEC msdb.dbo.sp_update_job
@job_name = @job_name,
@enabled = 1,
@start_step_id = 1,
@notify_level_eventlog = 0,
@notify_level_email = 2,
@notify_level_netsend = 2,
@notify_level_page = 2,
@delete_level = 0,
@description = N'',
@category_name = N'[Uncategorized (Local)]',
@owner_login_name = @owner_login_name,
@notify_email_operator_name = @notify_email_operator_name,
@notify_netsend_operator_name = N'',
@notify_page_operator_name = N''
GO
EXEC sys.sp_ms_marksystemobject
sp_TSQLJobCreate
GO