如何将SQL Server作业名传递给该作业中的存储过程?

I have a job:

我有一个工作:

RunProcedures

It has steps:

它的步骤:

Step 1: Do Something
Step 2: Do something Else
Step 3: Email

步骤1:做一些事情步骤2:做一些其他的事情步骤3:电子邮件

In step 3, I have:

在步骤3中，我有:

EXEC spSendSuccessEmail -- and here's where I want to pass the job name.

Here's the code for the stored procedure above:

下面是上面存储过程的代码:

ALTER PROCEDURE [dbo].[spSendSuccessEmail] @JobName VARCHAR(30)
AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @EmailBody VARCHAR(50)

    SET @EmailBody = @JobName + ' ran successfully'

    BEGIN TRY
        EXEC msdb.dbo.sp_send_dbmail 
            @profile_name = 'DBTeam',
            @recipients = 'user@universe.com',
            @copy_recipients = 'user2@universe.com',
            @subject = 'Process Complete',
            @body = @EmailBody,
            @importance = 'Normal',
            @sensitivity = 'Normal';
    END TRY
    BEGIN CATCH
        EXEC spGetDatabaseErrorInfo
    END CATCH           
END

How do I pass to this stored procedure the name of the job it's in?

如何将它所在的作业的名称传递给这个存储过程?

I read on tokens but I'm a little confused on how to accomplish this task.

我读了一些令牌，但我对如何完成这项任务有点困惑。

Can anyone give me a hand?

谁能帮我一个忙吗?

1 个解决方案

#1

UPDATE I did some testing and for some versions of SQL Server(2005 sp1 and beyond), you'll have to escape the Token. I've added the escape command to the code below.

更新我做了一些测试，对于一些版本的SQL Server(2005 sp1等)，您必须转义令牌。我已经将escape命令添加到下面的代码中。

$(JOBID) is the token you want to use, and you can query from msdb..sysjobs to find the job name.

$(JOBID)是您想要使用的令牌，您可以从msdb查询。sysjobs查找工作名称。

For sql server 2005 sp1 and beyond:

适用于sql server 2005 sp1及以上版本:

declare @jobName varchar(100)

select @jobName = name from msdb..sysjobs where job_id = $(ESCAPE_NONE(JOBID))

exec spSendSuccessEamil @jobName

For sql server 2005 and earlier:

适用于sql server 2005及以上版本:

declare @jobName varchar(100)

select @jobName = name from msdb..sysjobs where job_id = $(JOBID)

exec spSendSuccessEamil @jobName

Just use one of these as your command text within the 3rd step of your job.

在工作的第3步中使用其中一个作为命令文本。

#1