I am writing a program that will connect to each of the 2600+ databases (v2000 and v2005) in our environment and pull permissions. i had written a script that would load the privs into the temp database for each server and then push them to a central repository. however, some of the temp dbs are configured differently from the other databases so i kept getting collate (?) errors.
so now, i'm looking to try something different. is it possible to write a stored procedure that will spawn connections to multiple servers, run queries on the databases, and insert the data to local tables all at the same time so that i dont have to wait for each server & database to complete?
If i need to clarify, please let me know.
Start main procedure
get list of servers
for each server:
connect and get list of databases
for each database:
pull permissions and send back to original database
repeat simultaneously on each database
repeat simultaneously on each server