自动创建 SQL Server 作业 [英] Create SQL Server job automatically

问题描述

我正在编写 SQL Server 部署脚本，它会在特定的 SQL Server 服务器/实例上自动创建 SQL Server 作业.我发现我可以通过使用脚本作业作为 => 创建到来提取可用于自动创建 SQL Server 作业的 sql 语句.

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.

我的困惑是，我发现数据库名称和所有者帐户名称在生成的 sql 脚本中是硬编码的.当我在另一台计算机上使用sqlcmd执行sql脚本进行部署时，数据库名和Owner账户名可能不同，所以我需要一种方法将数据库名和Owner账户名传递给SQL Server作业创建脚本和让脚本使用提供的数据库名称和所有者帐户名称(硬编码的除外).

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?

推荐答案

您需要动态创建作业脚本，然后执行它.您可以尝试类似以下操作或将其更改为带有作业所有者和数据库名称输入参数的存储过程.

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.

这篇关于自动创建 SQL Server 作业的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！