April 18, 2008 at 11:55 am
I am interested in creating a SQL Agent job that will execute the same stored procedure in all databases, but I would prefer that the one job run everything concurrently, rather than sequentially.
I know I can either using a looping routine or sp_msforeachdb " exec procname" that would do the processing sequentially, but what about all at the same time?
FYI (someone ALWAYS asks!), the stored procedure truncates data in two tables (located in almost every DB) and inserts new data/does a table refresh and summarizes data (counts of similar records, dates, etc.) based on the last week's new data entries (inserts) along with all of the historical data in the respective DB.
Thanks! 🙂
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
April 18, 2008 at 12:13 pm
I have not actually done it, but it sounds to me that this may be more a job for Replication...?
-- Cory
April 18, 2008 at 12:28 pm
1) If the databases are not being constantly added and removed (so you don't need the process to be dynamic for new databases) you could create an SSIS package with several ExecuteSQL components
2) If you want it dynamic, you could create a job that loops through your databases and for each database creates it's own SQL Agent job and starts it. For the sake of saving history, it could re-use an existing job if there was already one for the specified database and delete any jobs for databases that no longer exist.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply