How to send PARAMETER to a SQL JOB while calling

Question

How to send PARAMETER to a SQL JOB while calling

kirankumar342

SSC Rookie

Points: 37
More actions
September 7, 2015 at 1:48 am

#399001

Hi All,
Following description specifies my requirement. Can you guys go through this and provide me a better solution.
Thanks in advance.
Requirement: Need to pass parameters to a procedure which was called inside a SQL JOB
Description: I have two jobs.
1st JOB: Process my pending queue items and create a list of actions for each queue id with a specific Batch ID.
2nd JOB: Perform the list of actions for each Batch ID by accepting the Batch ID as input.
I need to call the 2nd job inside the first one.

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

You can't do it directly, but you could have the first job write the parameter values to a database table, and the second job read from that table.

John

kirankumar342 SSC Rookie Points: 37 More actions · Answer 2

Hi John,

Thanks for responding. Now I did like that. I created one more procedure which can get the batch id from the physical table then call the second procedure with that ID.

Can you confirm that there is no way to call like that as in my previous post?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

You cannot pass a parameter directly to a job, because jobs are designed to be run automatically on a schedule without user input. You'll have to do some workaround like John suggests.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

kirankumar342 SSC Rookie Points: 37 More actions · Answer 4

Thanks Shaw.

Is it possible to call a job inside a procedure?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Sure

EXEC sp_start_job

It takes the job name or ID, server name and step to start the job at as parameters.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass