Change in Job Execution - SQL Server 2005?

11 Nov 2005 2:48 PM

Amos Soma

I just converted a SQL Server 2000 database to 2005. I have a job whose only
step does the following:

Declare @Command char(240)
Set @Command = '\SDBS\Scheduler\Scheduler.Exe ' +
Convert(char(50),Convert(uniqueidentifier,[JOBID])) + ' ' + (Select
Replace([name],' ','~')
From msdb..sysjobs where job_id=[JOBID])
exec master..xp_cmdshell @Command, NO_OUTPUT

Notice the [JOBID] portion. Under SQL Server 2000, the job id of the job was
passed to the job step. Under 2005, I get an error when I run the job saying
'JOBID' is undefined. Does anyone know what change I need to make?

Amos.

11 Nov 2005 4:06 PM

Andrew J. Kelly

The tokens have changed slightly. Have a look at this subject in 2005 BOL:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/105bbb66-0ade-4b46-b8e4-f849e5fc4d43.htm

--
Andrew J. Kelly SQL MVP

Show quote

"Amos Soma" <amos_j_s***@yahoo.com> wrote in message
news:uYN9s7s5FHA.3544@TK2MSFTNGP09.phx.gbl...
>I just converted a SQL Server 2000 database to 2005. I have a job whose
>only step does the following:
>
> Declare @Command char(240)
> Set @Command = '\SDBS\Scheduler\Scheduler.Exe ' +
> Convert(char(50),Convert(uniqueidentifier,[JOBID])) + ' ' + (Select
> Replace([name],' ','~')
> From msdb..sysjobs where job_id=[JOBID])
> exec master..xp_cmdshell @Command, NO_OUTPUT
>
> Notice the [JOBID] portion. Under SQL Server 2000, the job id of the job
> was passed to the job step. Under 2005, I get an error when I run the job
> saying 'JOBID' is undefined. Does anyone know what change I need to make?
>
> Amos.
>