Is there any way to identify which SQL Agent job is running a stored procedure?
有没有办法确定哪个SQL代理作业正在运行存储过程？
The reason I want this is that I'd like to have a separate step, that runs on failure, that has a stored procedure which will email the log file. To get the log file (we always use only one log file per job, not per step), I'd like to just be able to get the log file by querying the MSDB tables (select output_file_name from sysjobsteps).
我想要这个的原因是我想要一个单独的步骤，它运行失败，有一个存储过程将通过电子邮件发送日志文件。要获取日志文件（我们每个作业只使用一个日志文件，而不是每个步骤），我希望能够通过查询MSDB表来获取日志文件（从sysjobsteps中选择output_file_name）。
I could pass a parameter with the job name to the stored procedure, but I'd like to not need to do that.
我可以将带有作业名称的参数传递给存储过程，但我不想这样做。
Any ideas?
有任何想法吗？
thanks!
谢谢！
1 个解决方案
#1
1
Do you mean which job is calling the logging procedure? ie who is calling me is that what you mean? if thats the case then this may be relevant... SQL Server Agent - get my own job_id .. the failure step where you post your logging proc could use the jobid retrieved by the method described in the link... imho would work.. youd need to add a job id parm though.. and each failure step in each job would have to have two lines... one to get the id and one to call the proc.
你的意思是哪个工作正在调用日志记录程序？即谁叫我是你的意思？如果是这样的情况那么这可能是相关的... SQL Server代理 - 获取我自己的job_id ..您发布日志记录过程的失败步骤可以使用通过链接中描述的方法检索的jobid ... imho将工作。你需要添加一个作业ID parm ..并且每个作业中的每个失败步骤都必须有两行......一个用于获取id，一个用于调用proc。
#1
1
Do you mean which job is calling the logging procedure? ie who is calling me is that what you mean? if thats the case then this may be relevant... SQL Server Agent - get my own job_id .. the failure step where you post your logging proc could use the jobid retrieved by the method described in the link... imho would work.. youd need to add a job id parm though.. and each failure step in each job would have to have two lines... one to get the id and one to call the proc.
你的意思是哪个工作正在调用日志记录程序？即谁叫我是你的意思？如果是这样的情况那么这可能是相关的... SQL Server代理 - 获取我自己的job_id ..您发布日志记录过程的失败步骤可以使用通过链接中描述的方法检索的jobid ... imho将工作。你需要添加一个作业ID parm ..并且每个作业中的每个失败步骤都必须有两行......一个用于获取id，一个用于调用proc。