Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Passing parameter value to SQL job Expand / Collapse
Author
Message
Posted Friday, April 10, 2009 1:25 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 12, 2011 6:03 AM
Points: 20, 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?
Post #694649
Posted Friday, April 10, 2009 1:52 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:03 AM
Points: 2,112, Visits: 5,427
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/
Post #694654
Posted Friday, April 10, 2009 3:41 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 12, 2013 1:15 AM
Points: 187, Visits: 376
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
Post #694683
Posted Friday, April 10, 2009 4:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 12, 2011 6:03 AM
Points: 20, 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.
Post #694697
Posted Friday, April 10, 2009 4:21 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:03 AM
Points: 2,112, Visits: 5,427
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/
Post #694705
Posted Friday, April 10, 2009 4:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 12, 2011 6:03 AM
Points: 20, 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.
Post #694709
Posted Friday, April 10, 2009 4:47 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 7, 2011 1:41 AM
Points: 346, 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
Post #694712
Posted Friday, April 10, 2009 5:21 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 12, 2011 6:03 AM
Points: 20, Visits: 55
Could you please elaborate on the use of both...
Post #694723
Posted Friday, April 10, 2009 5:42 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:03 AM
Points: 2,112, Visits: 5,427
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/
Post #694737
Posted Friday, April 10, 2009 6:11 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 12, 2013 1:15 AM
Points: 187, Visits: 376
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
Post #694758
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse