TOP 1 WITH TIES

Paul Flessner, Senior VP of Server Applications at Microsoft, shares his vision about the future of SQL Server in his latest letter here.

Today a colleague asked me this question.
How do you get current Job Name from a job step?

Well, I did'nt know the answer :(

It should be simple. If we want to know the current procedure name from inside the procedure, we can use something like OBJECT_NAME(@@PROCID)
Do we have something like @@JOBID? Well, NO.

I turned to google and learned about SQL Agent Tokens. That was news to me.

So SQL Agent Tokens are placeholders that are automatically replaced by the subsystem during runtime.
In SQL Server 2000, you can refer to an SQL Agent token like [TOKENNAME]. Token names are case sensitive.

Here is an example.

SELECT output_file_name
FROM msdb..sysjobsteps
WHERE job_id = [JOBID]
AND step_id = [STEPID]

SQL Server Agent in SQL Server 2005 has changed the syntax used in tokens from "[X]" to "$(X)" where X is the token name

Here are some relevant links.

http://www.microsoft.com/technet/prodtechnol/sql/2005/newsqlagent.mspx

http://sqldev.net/sqlagent/SQLAgentStepTokens.htm