May 15, 2012 at 9:18 am
How to pass parameter to a sql job in 2005?
If there is a store proc that executes and needs to pass a parameter of varchar data type to a sql server job, how should it be done?
May 15, 2012 at 9:30 am
One way I have accomplished this in the past is to store the parameter in a table and alter the stored procedure to read the parameter from the table.
As far As I know (an I welcome any correction in my assertions) there is no way to pass a parameter to a SQL Job
:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:
May 15, 2012 at 11:56 am
I am curious as to why you want to pass a parameter to a SQL Agent Job. Are you using Agent as a way to execute stored procedures asynchronously?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 15, 2012 at 2:32 pm
One of our subs does something similar to what opc asking. The application creates and launches a job as the actual execution of the procedure takes way to long to expect a user to wait to continue. What they do is have the application create a new job with the stored procedure call with the required parameters already in the job. They also have a nightly clean up job that removes the old jobs. I would more likely have made the job delete after run using the delete_level setting.
The problem with this is that there will never be any history for a job that is deleted.
Not sure if this is what you are looking for but I would lean more toward what Robin suggests by using a table to store parameters. This is what we do a lot and is updatable by other activities or applications that need to make a change for the next run.
You also don't exactly need to make a change to the stored procedure itself. Just pull the info from the table into variables in the job t-sql and execute the procedure with those variables as the parameters.
May 15, 2012 at 2:54 pm
DBA_Dom (5/15/2012)
One of our subs does something similar to what opc asking. The application creates and launches a job as the actual execution of the procedure takes way to long to expect a user to wait to continue. What they do is have the application create a new job with the stored procedure call with the required parameters already in the job. They also have a nightly clean up job that removes the old jobs. I would more likely have made the job delete after run using the delete_level setting.The problem with this is that there will never be any history for a job that is deleted.
Not sure if this is what you are looking for but I would lean more toward what Robin suggests by using a table to store parameters. This is what we do a lot and is updatable by other activities or applications that need to make a change for the next run.
You also don't exactly need to make a change to the stored procedure itself. Just pull the info from the table into variables in the job t-sql and execute the procedure with those variables as the parameters.
Where I was going with it was that executing a stored procedure asynchronously using Service Broker is a much more robust and cleaner option than leveraging SQL Agent in the way you described, and how I imagined the OP might be leveraging it.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply