首先，你有没有机会看一下 service broker?从你的描述来看，这似乎是你真正想要的.

First of all, have you had a chance to look at service broker? From your description, it sounds like that's what you actually want.

不同之处在于您将数据放入 SB 队列而不是开始工作，SB 将异步调用您的处理过程，并且完全回避已经运行的工作等问题.它会自动生成/终止其他线程和需求决定，它负责秩序等.

The difference would be instead of starting a job, you put your data into a SB queue and SB will call your processing proc asynchronously and completely side-step issues with already-running jobs etc. It will auto spawn/terminate additional threads and demand dictates, it takes care of order etc.

这是一个很好的(并且相关的)教程.http://www.sqlteam.com/article/centralized-asynchronous-审计与服务经纪人

Here's a good (and vaguely related) tutorial. http://www.sqlteam.com/article/centralized-asynchronous-auditing-with-service-broker

让我们假设无论出于何种原因您都不能使用 SB(但说真的，一定要这样做！).

Let's assume that you can't use SB for whatever reason (but seriously, do!).

如何使用作业 spid 的 context_info.

What about using the job spid's context_info.

1. 您的工作调用了一个单独执行每个步骤的包装程序.

2. 包装过程中的第一条语句是

1. Your job calls a wrapper proc that execs each step individually.

2. The first statement inside the wrapper proc is

DECLARE @context_info VARBINARY(30)
SET @context_info = CAST('MyJob1' AS VARBINARY)
SET CONTEXT_INFO @context_info

SET CONTEXT_INFO 0x0

IF NOT EXISTS (SELECT * FROM master..sysprocesses WITH (NOLOCK) WHERE context_info=CAST('MyJob1' AS VARBINARY))
    EXEC StartJob

当您的包装过程终止或连接关闭时，您的 context_info 将消失.

When your wrapper proc terminates or the connection is closed, your context_info goes away.

您还可以使用全局临时表(即##JobStatus)，当所有引用它的 spid 断开连接或显式删除它时，它们将消失.

You could also use a global temp table (i.e. ##JobStatus) They will disappear when all spids that reference it disconnect or if it's explicitly dropped.

只是一些想法.