July 8, 2015 at 7:54 pm
i want to call a stored procedure continuously. i have only option to schedule using sql job, triggers doesnt help.
Job calls a sp which loads data. My question is how to schedule it in what interval, sp sometime take 1 sec to complete or 5 minutes or 20 minutes based on number of records it need to process.
if i schedule every 5 minutes, job may be running when next schedule try to start the job.
Any alternate solution. job doesn't need to be continuous, requirement is to call proc continuously to process records.
July 8, 2015 at 9:49 pm
Not enough information to really help. If you run it as a scheduled job and have it scheduled to run every minute, for instance, if the job is still active when the next scheduled execution nothing happens.
Depending on how the data is coming in. An alternative may be Service Broker.
July 8, 2015 at 9:57 pm
thanks i will schedule a job to run every 10 minutes in that way it will run continuously
July 9, 2015 at 1:24 am
If you schedule it every 10 minutes, then it will run every 10 minutes (unless the job is still running at the point it would run again, in which case, it doesn't start a second time)
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
July 9, 2015 at 10:17 am
It's not pretty, and I'm not sure why actually running continuously would be better than running every 10 minutes, but if you really wanted it to start a new run immediately after the last run finished, you could wrap the job step TSQL in a WHILE 1=1 loop. Then you just kick off the job once and it runs forever (or until it errors out or the server stops).
Of course, then you don't get logging for each run of the job, since it's all one execution.
To make things more complicated and even less pretty, you could have a parent job that within a WHILE 1=1 loop, checks to see if the data load job is running. If it is running, then don't do anything. If it isn't, start the data load job.
Then you get logging for each run of the data load job, if you'd prefer to have it.
Still, while the above would get you what you wanted, I'd probably keep things simple and just go with scheduling it every 10 minutes. Worst case scenario then is that you wait a little while between runs.
Cheers!
July 9, 2015 at 10:39 am
Again, what we don't know is how this all is supposed to work. All we know is that a stored procedure is supposed to be run continuously to import data. How is this data getting brought into the database? What is the stored procedure actually doing? Is there a better way to process the data?
One possible solution would be to use Service Broker. Before going down that path, however, we at least need the answer to the questions above, and possibly some others.
If the solution to this problem is to run a job every ten minutes to run a stored procedure and it meets the users requirements, then maybe the additional work needed to implement a SB solution isn't necessary.
July 9, 2015 at 12:06 pm
Hmm... theoretically you could try making two jobs, the first step of each job does your data load, then the next step calls the other job. That way they'll bounce back and forth after each one finishes the data load.
July 9, 2015 at 12:35 pm
mxy (7/8/2015)
i want to call a stored procedure continuously. i have only option to schedule using sql job, triggers doesnt help.Job calls a sp which loads data. My question is how to schedule it in what interval, sp sometime take 1 sec to complete or 5 minutes or 20 minutes based on number of records it need to process.
if i schedule every 5 minutes, job may be running when next schedule try to start the job.
Any alternate solution. job doesn't need to be continuous, requirement is to call proc continuously to process records.
Quick thought, this logic is fundamentally flawed. You want to process the files when they are ready, no sooner and preferably no later either (from what I gathered). That is not achieved by constantly running a stored procedure, an approach which is more resembling to a "drug before diagnostics" or "shoot and then ask". Why not have a file system event listener which triggers what ever needed when a new file is ready? Or a message based system like Lynn mentioned?
😎
July 10, 2015 at 4:23 pm
Thanks for all your suggestions.
this is a temporary solution we are going to have external service which is going to call stored procedure whenever these is a new record.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply