I know how to loop through one set, but wasn't sure if there was an easy way to
loop over two variables, something like:

Get list of servers
Get next server from Server recordset --a
For Each Server
Get List of Databases
Get next database from DB Recordset --b
For Each Database on server
Do Stuff
Loop Databases (back to b)

Loop Servers (back to a)
Exit


I know I can do one or the other without too much trouble, but has anyone done
this successfully? Do I just need to point the DB loop to B with a success to
Loop Servers which will then take me back to a?

Thanks.

Hello Peter,

Based on my scope, this is not currently support in SQL 2000 DTS package.
You may want to check if SQL 2005 can meet your requirement because it has
enhancement on this respect.

For more information on this, please go to SQL 2005 newsgroup:

http://communities.microsoft.com/new...lserver2005&sl
cid=us

Thanks & Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

================================================== ===


This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------

Thanks for the quick response, Peter. This does work in SSIS. I've actually
got this doing exactly what I needed it to. I was kind of wondering if it were
possible in DTS 2000 as that would allow me to easily deploy to current and
not-yet-upgraded systems in the future. We're trying to make a generic (and
basic) DB Size Trending system that will pull in everything. I know I can make
things loop with some ActiveX magic, I just wasn't sure about nested loops. I
can probably mimic it with an Exec Package task - just not the cleanest way to
handle that. :-(

Thanks again.

-Pete Schott

petery (AT) online (DOT) microsoft.com (Peter Yang [MSFT]) wrote:

You can loop in DTS, by manipulating the workflow, and it works fine as you
probably know. Nesting loops is of course possible as well, just takes a bit
more brain power to work it all out. However I have seen perfectly designed
systems just fail when the workflow manipulation became to complex. I
suggest you split this into two packages, one for the server loop, and one
for the databases. Easier to code, manage and more reliable I expect.

I would also suggest that why not just loop servers and perform the work all
on one connection, enumerating the databases servers side. It will be
faster, especially for remote servers, and simpler.


--
Darren Green
http://www.sqldts.com
http://www.sqlis.com


"Peter A. Schott" <paschott (AT) newsgroup (DOT) nospam> wrote