SQL Server代理 - 获取我自己的job_id

I'm running a SQL Server 2008 64 bit Developer Edition with Service Pack 1 installed. I have a SQL Server Agent Job. Within this job I want to get the job_id of my own job.
On MSDN (http://msdn.microsoft.com/en-us/library/ms175575(v=SQL.100).aspx) you can find a description of using tokens in job steps. Wow, great, that's what I'm looking for!! Just use (JOBID).
Since SQL Server 2005 SP1 you have to use macro like $(ESCAPE_NONE(JOBID)). No problem.
But if you try the example:

我正在运行安装了Service Pack 1的SQL Server 2008 64位开发人员版。我有一个SQL Server代理作业。在这份工作中,我希望获得自己工作的职位。在MSDN(http://msdn.microsoft.com/en-us/library/ms175575(v=SQL.100).aspx)上,您可以找到在作业步骤中使用令牌的说明。哇,太棒了,这就是我要找的!!只需使用(JOBID)。从SQL Server 2005 SP1开始,您必须使用像$(ESCAPE_NONE(JOBID))这样的宏。没问题。但是如果你试试这个例子:

DECLARE @name NVARCHAR(128)
select @name =  name from msdb.dbo.sysjobs where job_id = $(ESCAPE_SQUOTE(JOBID))
PRINT @name

you get:
Incorrect syntax near 'ESCAPE_SQUOTE'. (Microsoft SQL Server, Error: 102)
Ok, now from the scratch:

你得到:'ESCAPE_SQUOTE'附近的语法不正确。 (Microsoft SQL Server,错误:102)好的,现在从头开始:

PRINT N'$(ESCAPE_SQUOTE(JOBID))'

results in 0xE33FE637C10B3C49A6E958BB3EF06959 but the job_id is 37E63FE3-0BC1-493C-A6E9-58BB3EF06959
The "N'" I think makes an implicit conversion to NVARCHAR of the (JOBID)...
Ok, I think I have to care about the datatype of (JOBID). In the book "SQL Server 2008 Administration" on page 168/169 there's also an example of using (JOBID):

结果是0xE33FE637C10B3C49A6E958BB3EF06959,但是job_id是37E63FE3-0BC1-493C-A6E9-58BB3EF06959我认为“N”是隐式转换为(JOBID)的NVARCHAR ...好吧,我想我必须关心(的数据类型) JOBID)。在第168/169页的“SQL Server 2008管理”一书中,还有一个使用(JOBID)的示例:

declare @jobid binary(16)
SELECT @jobid =Convert(Uniqueidentifier,$(ESCAPE_NONE(JOBID)))

results in:
Incorrect syntax near '('. (Microsoft SQL Server, Error: 102)
I'm totally confused now. Could please someone help me with a good advice or solution. Every kind of help is appreciated.

导致:'('。(Microsoft SQL Server,错误:102)附近的语法不正确我现在完全感到困惑。可以请某人帮我提供一个好的建议或解决方案。各种帮助表示赞赏。

Best regards Helmut

最好的问候赫尔穆特

4 个解决方案

#1

Just forget what parser is saying - variable resolution is done at runtime. Parser does not know about that.

忘记解析器说的是什么 - 变量解析是在运行时完成的。 Parser不知道这一点。

#2

thanks for your answers. The problem is that I tried to parse the statement in job step. Then I got this error. While running the job there's no problem. My very best solution now is:

谢谢你的回答。问题是我试图在作业步骤中解析语句。然后我收到了这个错误。在完成工作的同时没有问题。我现在最好的解决方案是:

declare @JobID uniqueidentifier
SELECT @JobID = $(ESCAPE_NONE(JOBID));
PRINT 'My JobID is  ' + Convert(char(255), @JobID)

Now you handle with @JobID, but as far as I know until now you have to convert always to char(255). Thanks to user state_dba on MSDN.

现在你处理@JobID,但据我所知,到目前为止你必须将always转换为char(255)。感谢MSDN上的用户state_dba。

#3

We had trouble with this recently and did not go the route you found in MSDN. Instead, we recovered the jobid from dbo.sysjobs by name directly (the opposite of your example) and then used that within the job to check execution status (exiting out of long running while loop if job state had changed).

我们最近遇到了麻烦,并没有找到你在MSDN中找到的路线。相反,我们直接从dbo.sysjobs恢复了jobid(与你的例子相反),然后在作业中使用它来检查执行状态(如果作业状态已经改变,则退出长时间运行循环)。

declare @jobid uniqueidentifier
SELECT @jobid = job_id from msdb.dbo.sysjobs where name = '[blah]'

#4

This may sound obvious, but I get the error you quoted from your first sample, if I run it in a query window, but it runs perfectly well when I paste that script into a job step.

这可能听起来很明显,但是如果我在查询窗口中运行它,我会从第一个示例中得到您引用的错误,但是当我将该脚本粘贴到作业步骤时它运行得非常好。

You can only use these tokens within job steps. And, given that we're not expecting any quotes in the jobid token, I'd use ESCAPE_NONE whenever you reference it.

您只能在作业步骤中使用这些令牌。并且,鉴于我们不期望在jobid令牌中有任何引号,每当你引用它时我都会使用ESCAPE_NONE。

#1