SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Passing parameter value to SQL job


Passing parameter value to SQL job

Author
Message
Husain_Kachwala
Husain_Kachwala
SSC Veteran
SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)

Group: General Forum Members
Points: 214 Visits: 55
Hi All,

I have a situation. I have a job in sql server 2005 which executes a stored procedure.The problem is that this stored procedure accepts two parameters @StartDate datetime,
@EndDate datetime.

How do I pass these parameters to the job?
Adi Cohn
Adi Cohn
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8099 Visits: 6594
You do it the exact same way that you pass a parameter to a stored procedure from a batch. The fact that you invoke the stored procedure from a job has no impact on the way that you work with parameters.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
raj acharya
raj acharya
Say Hey Kid
Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)

Group: General Forum Members
Points: 697 Visits: 410
if you want to pass parameter into sql job ...first of job means...you must put facility that when sp executes it automatically take parameter from somewhere ...now another thing ....i m giving you one idea .....put startdate and enddate in one table as per the currentdate....it will inserted into that and when sp executes it will take latest parameter from that table.....

Raj Acharya
Husain_Kachwala
Husain_Kachwala
SSC Veteran
SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)

Group: General Forum Members
Points: 214 Visits: 55
Thanks Adi & Raj for the reply.

The situation is that the user will only execute the job. I wanted to know if there is some mechanism in sql server 2005 wherein once the job is invoked, the user is asked to enter parameter values for the same.
Adi Cohn
Adi Cohn
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8099 Visits: 6594
I’m not sure that you have to do it as a job. A job is a scheduled task. It is executed according to the time and date. If a user has to execute it, then maybe it should be just a stored procedure that gets the parameter from the user.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Husain_Kachwala
Husain_Kachwala
SSC Veteran
SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)

Group: General Forum Members
Points: 214 Visits: 55
Yeah you are right but the situation is such that the business users wont bother executing the stored procedure.

Changes at table level is not possible since the system would be in production soon.
krayknot
krayknot
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1862 Visits: 534
Husain_Kachwala (4/10/2009)
Hi All,

I have a situation. I have a job in sql server 2005 which executes a stored procedure.The problem is that this stored procedure accepts two parameters @StartDate datetime,
@EndDate datetime.

How do I pass these parameters to the job?


Use the batch file or you can also use temp table

kshitij kumar
kshitij@krayknot.com
www.krayknot.com
Husain_Kachwala
Husain_Kachwala
SSC Veteran
SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)

Group: General Forum Members
Points: 214 Visits: 55
Could you please elaborate on the use of both...
Adi Cohn
Adi Cohn
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8099 Visits: 6594
I’m sorry but I have a feeling that we don’t understand each other (maybe because English is the second language for both of us). Can you try and explain the situation? From what I understand you need to run a stored procedure. The stored procedure should get parameters. What I don’t understand is if it should run when a user wants it to run or based on date and time. Can you pleas explain what you need?

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
raj acharya
raj acharya
Say Hey Kid
Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)

Group: General Forum Members
Points: 697 Visits: 410
hi i think your requirement is like...for example you have schedule job for procedure and you want that each time when job executes it will ask users for parameter .....is this right then

this kind of thing can't possible when you automatically schedule job.....in job sp will never ask for parameter you should do arrangement like that it can get input parameter when it runs.....

can u post me your requirement in detail.....but as per i told you there is no mechanism like during when job executes it will ask for parameter ....it can happen only through application side ...or you can generate report for that...

Raj Acharya
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search