October 12, 2009 at 1:27 pm
Forgive me if the wording of this is off ...
What I'm working on right now is a way to have a step inside of a SQL Job run for a different set of criteria where the arguments or variables it uses incrmenet each time the job is called. This does soudn somewhat like a procedural prcess where you are updateing rows 1 row at a time but that's not whats actually going on here. I have many rows of data with 2 or more columns with numeric values that must be updated.
For reasons of reosurces and the like I can't simply say UPDATE all. Instead I need to call the job step and each time pass to it a small range of rows to update and those rows it updates increment with each call to the job. I imagined the easiest way to do this would be to use a utilty table to store values that would represnet the last set of criteria the Job ran for. This way the job could dertmine where to pick back up with the updates.
Imagine you have an INVOICE table that has rows with dates spannning the past 10 years and the Job Step is set up to perform an update on those invoices dated within the first Month-Year of whatever Month-Year the oldest Invoice is a part of. The next time the job step is called it would know to increment the month-Year date by 1 Month and then start the upate on those Invoices in the next month.
Has anyone had to do anything like this before?
Kindest Regards,
Just say No to Facebook!October 12, 2009 at 1:45 pm
why aren't you just using a single sp with a while loop? and then just call that sp from a job?
October 12, 2009 at 3:30 pm
The actual T-SQL doing the update is in a STORED PROCEDURE. What I'm looking for is a way in a SQL JOB (so it can be scheduled to start on specific times& days) to call that SP multiple times, each time passing to it the range of dates that are next. It is very much like a loop, actually a loop within a loop because in addition to the dates being passed to the SP, it also accepts an accounting entity. And so what happens is you call the SP and pass to it an accounting entity and a Start Date and End date that spans no more then 1 month. You call this SP for each month for that accounting entity as span between the Month/Year of the odlest Trnas to that entity thru to the current Month. Once the SP has been called for the complete range of months for that entitye, you would start over with the next accounting entity.
And no sadly enough, the SP can't be changed to work with more then one accounting entity or more then one month as it's proprietary.
So are you implying that I setup a Loop in the step of a job or are you taking about a Loop within an SP?
I've never tried an actual loop within a Job Step. I could wrap the SP within my own SP that woudl have a loop but I need more flexability with scheduling and how to respond to a failure if one occurs. This is why I was looking at a SQL Job that would cycle thru each Month/Year within the date range and call the SP passing in the acounting entity being updated.
Kindest Regards,
Just say No to Facebook!Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply