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 Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 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
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3647 Visits: 6503
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
Old Hand
Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)

Group: General Forum Members
Points: 345 Visits: 401
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 Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 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
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3647 Visits: 6503
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 Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 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
Say Hey Kid
Say Hey Kid (686 reputation)Say Hey Kid (686 reputation)Say Hey Kid (686 reputation)Say Hey Kid (686 reputation)Say Hey Kid (686 reputation)Say Hey Kid (686 reputation)Say Hey Kid (686 reputation)Say Hey Kid (686 reputation)

Group: General Forum Members
Points: 686 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 Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 55
Could you please elaborate on the use of both...
Adi Cohn
Adi Cohn
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3647 Visits: 6503
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
Old Hand
Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)

Group: General Forum Members
Points: 345 Visits: 401
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