Trying to avoid a possible race condition
-
SCOPE_IDENTITY() works in 2000 so you can use it.
The only questions I have is what do you do if the sp runs (with delete) and for some bizarre reason the app breaks and doesn't do its export job? How do you recover from that? Can you afford to not recover from that?
Depending on how complex the data processing is, you might even be able to do it on the server. If you have very little or no processing of the data to do, the bcp utility can be a very good alternative to your problem.
Can you elaborate on what needs to be done with the data so we may offer alternative solutions?
-
The data pulled will be used to generate a few specifically formatted text files. The files themselves will be generated by putting together formatted strings from the data pulled, which will then be written to the file once complete. The names of these files will be a hash of the information pulled. All this is being done so that the follow-up app can pick up the files it's expecting to find, in the way it's expecting to find them.
I see your point now regarding the DELETE needing to be separate. No, we would not be able to recover from a loss like that other than to start to originating process over again. Something else I will need to consider is what to do if the app should break while in the middle of creating these files; but that's not really a SQL question.
-
This can be quite simple with any level of failure. You can add another column like TagDate and set it to getdate() when setting the tag in place.
Then have a job run every n minutes and scan for tags older than let's say 15 minutes. If any are found you can delete the tag so that the rows get processed again. You could also add an e-mail notification to warn of a catastrophic failure. You just have to figure out what are the normal delay of a failure. But from what you said I doubt that it will take more than a few seconds to complete the whole data processing and have the rows deleted from the queue.
-
Great idea! Thanks!
And thanks again to you both for the help.
-
The dummy BatchID table is a good one, but I'd probably use it to collect some parametrics about each batch...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 5 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply