Using sp_add_jobstep

Question

Using sp_add_jobstep

JuanBob

SSCertifiable

Points: 5059
More actions
July 24, 2006 at 12:34 pm

#113891

I am using this to add a jobstep to an existing job. This step is to call a separate stored procedure that accepts two parameters. I am having trouble with the syntax for @additional_parameters. Here is my code. My @additional_parameters is obviously not correct.
EXEC msdb.dbo.sp_add_jobstep
  @job_name = 'SampleJob',
  @step_id = 1,
  @step_name = 'SampleJob_Step1',
  @subsystem = 'TSQL',
  @command = 'EXEC DevDatabase.dbo.spProcess',
  @additional_parameters = @id, @userId,
  @database_name = 'DevDatabase'
Any help would be much appreciated!

MSSQL 2000

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

@additional_parameters is going to need to be either on @parameter that is a concatenated string, or you will need to pass it in dynamically and read the two @parameter independently within the SP:

EXEC msdb.dbo.sp_add_jobstep

@job_name = 'SampleJob',

@step_id = 1,

@step_name = 'SampleJob_Step1',

@subsystem = 'TSQL',

@command = 'EXEC DevDatabase.dbo.spProcess',

@additional_parameters = "@id, @userId",

@database_name = 'DevDatabase'

I wasn't born stupid - I had to study.

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 2

Your additional parameters value needs to be wrapped in single quotes. Assuming that you want to have both @id and @userid as parameters, replace your line with:

@additional_parameters = CHAR(33) + @id + ', ' + @userid + CHAR(33)

Hope this helps

Mark

Farrell Keough SSCoach Points: 17414 More actions · Answer 3

CHAR(33) is an exclamation point. You can use CHAR(34) double quotes or CHAR(39) single quotes... But you will still probably need something within you SP to seperate out those @Parameters, (is my guess...)

I wasn't born stupid - I had to study.

JuanBob SSCertifiable Points: 5059 More actions · Answer 4

All great points and I greatly appreciate it! I ended up using, "@command = @commandValue," and setting the @commandValue to include the exec and parameters together:

Set @commandValue = "EXEC DevDatabase.dbo.spProcess '" + @id + "', '" + @userId + "'" .

Hopefully that is appropriate, but it works perfectly from what I can tell.

Using sp_add_jobstep

Cookies on SQLServerCentral