ForEach Loop and an ADO Recordset

Question

Post reply

ForEach Loop and an ADO Recordset

Evil Kraig F

SSC Guru

Points: 100851
More actions
January 31, 2011 at 4:30 pm

#230932

Hey folks,
I'm trying to deal with some hairy requirements on a cleanup process. One of the things I want to do is take a dataset from a source, drop it to an ADO recordset, then loop the records one by one performing a number of tasks. I've got some significant error controls that need to occur so I need row by row tasking.
Now, I *believe* I can do this with a dataflow then feed the ForEach... but I can't quite nail down how to do this. Anyone know of a walkthrough/explaination (preferably non-blog, websense blocks a lot of my options) that they could provide?
- Craig Farrell
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

Found most of what I was looking for, thanks to anyone who tried.

http://www.codeproject.com/KB/database/foreachadossis.aspx

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 2

Craig Farrell (1/31/2011)
I've got some significant error controls that need to occur so I need row by row tasking.

It's very odd to hear such a justification coming from you, Craig. Mind if I ask what those controls are?

--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)

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

Jeff Moden (2/1/2011)
Craig Farrell (1/31/2011)
I've got some significant error controls that need to occur so I need row by row tasking.
It's very odd to hear such a justification coming from you, Craig. Mind if I ask what those controls are?

No, but please understand I'm going to be a *little* vague, on purpose.

We have multiple databases on multiple servers. These databases have tie ins to the file system, and they all end up dependent on each other to keep our internal apps working. Now, there's a project to cleanup the process, specifically the naming mechanics on these file system folder names.

So, the end goal will be to change multiple tables in multiple databases simultaneously while changing file system items, and make sure that if any item fails, everything rolls back.

Now, some of my prep work, such as checking for previous foldername existence and the like, will be done via looping mechanics. I'll do some other validation in rowsets as well.

The end item though, where we do the changes and make sure nothing goes awkward, will be done at a row by row level so I can control row level failures at the transaction level, without an all-or-nothing failure point on the entire set. Add to that I don't want to open multi-server transactions with the chance of lock escalations, so I want to control it to a single record, especially if those transactions end up sitting around waiting for the file system to finish a name change/file move.

So, the reasoning: lock control, multiple servers, and file system interaction are driving me to feel more confident in a row-level loop method to keep everything as small and tight as possible on a per-change basis, even if it will take longer in the overall scheme of things.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 4

Thanks for the feedback, Craig. Yep... not that it matters but I agree with that justification. Please understand that, considering the source, I just had to ask. Thanks again. 🙂

--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)

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 5

Jeff Moden (2/3/2011)
Thanks for the feedback, Craig. Yep... not that it matters but I agree with that justification. Please understand that, considering the source, I just had to ask. Thanks again. 🙂

No worries. Hopefully anyone who was following along will realize there are specific times row by row makes sense, although rare. Besides, I certainly don't have all the answers. You or someone else could have pointed out "Yeah, but just do this", and I would have blushed, stuttered, and gone and checked it out. 😉

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA