Run all store procs at once

Question

Post reply

Run all store procs at once

Mike Levan

SSChampion

Points: 14201
More actions
July 18, 2007 at 7:45 am

#153683

I have 126 store procs in a database and according to the request now I have to create 6 such databases with the same schema, what wud be the best solution to run all the store procs for different DB's at one time.
Is that runnning a maintenance plan or anything other solution?

Viewing 15 posts - 1 through 15 (of 15 total)

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

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 1

Is you req

you have 126 SPs in a database and need to move these into 6 other databases.

if so script the SPs and run against the target servers.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 2

If i have misunderstood let me know and apologises too.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Mike Levan SSChampion Points: 14201 More actions · Answer 3

its ok.

moving is not a problem, actually those procs are for reporting purpose and I have to run all the reports at one time using the same store procs for all the databases.

noeld SSC Guru Points: 96590 More actions · Answer 4

you will have to create either a multithreaded app or multiple clients that pool a single resource file and when you change that "file" your clients will execute the procs.

Cheers!

* Noel

noeld SSC Guru Points: 96590 More actions · Answer 5

You need to call the procedures "at once" right?

So you have to have either separated threads on the client side or separated processes on the client side that call the stored procedures on each of the databases when they are signaled to do so some how!

* Noel

Mike Levan SSChampion Points: 14201 More actions · Answer 6

Is there anything I can do from the database side to perform that job..I mean like a store proc which can run all procs from diffrent db's at once for the reports

David McFarland SSChampion Points: 11815 More actions · Answer 7

Is there any reason you don't want to use a job or jobs for this? They can be fired off automatically, as well as manually, even from Query Analyzer or the command line, if you wish.

Bill Scott SSCrazy Points: 2338 More actions · Answer 8

Are these procs that you want to run going to generate a report or fill a table with data that will be used later by the reports?

If they are preparing data for later use, you can set up a stored procedure that will invoke the other stored procedures. They won't run "all at once", rather they will run the first on the list, then when its done, the next one and so on.

I'd suggest that you put one of these "Run All" stored procedure in each of the databases and then set up jobs in the scheduler to execute them. This way you have all the infrastructure except the job when you make a new copy of the database for your next customer.

You'll need to take your time setting up the schedule, it could be that you may be able to only run a few of the databases simultaniously, depending on how that load affects your server.

Mike Levan SSChampion Points: 14201 More actions · Answer 9

OK I think the conversion is on the wrong track, may be I didnt explain it in the right way, here is what i need.

say for example i have 6 databases on my server which are assigned for 6 different offices where they have to use it for their reporting tool so that they can add,update data in each db belonging to them. when they get into the reporting app they are going to select their database from their drop down list.

for this business I want to use all my procs for different db's but just one set of all these procs. If I have all these procs in each of the db then when I need to change anything in one of the proc there is a chance of missing the changes made in any of the db. So if i had just one set of procs which can be used for all the db's by this way I can not miss any updates made to any of the proc.

I just need a best solution to do this.

David McFarland SSChampion Points: 11815 More actions · Answer 10

So you are just looking for deployment options?

The quick and dirty way, and one that requires no third party tools, is just create a script with any changes, deploy it to each site, and execute it. You can even set this up in a job if you wish. There are many third party tools which are supposed to make this even easier, such as SQL Farms Combine, Red-Gate's SQL Compare, etc., but I don't use them currently, so can't make an objective recommendation.

Mike Levan SSChampion Points: 14201 More actions · Answer 11

is there any other option apart from updating each n every database.

steve smith-401573 SSChampion Points: 10889 More actions · Answer 12

How frequently are you replicating the content across the 6 different offices? If this activity is performed overnight, then I would imagine that a once-daily refresh of any changed procedures would also work.

If, in contrast, you are refreshing the instances every 15 minutes (with transactional updates, of course...), then it MAY be more appropriate to reach across the long-lines (be they fiber optics, microwave, or twisted pair) to pull up the source code. To do that, the question that needs to be answered is, 'how do i reference a stored procedure existing on a remote database instance' or some such. correct? [isolating your original question from all business considerations]

Mike Levan SSChampion Points: 14201 More actions · Answer 13

I have an admin for my reporting, he has to run report from all the 6 offices at once, how can i allow allow him to hit 6 different db's with the same set of store procs and get the final report of 6 offices

David McFarland SSChampion Points: 11815 More actions · Answer 14

I feel like we're going back and forth, or perhaps I'm still not getting it. You went from wanting to run the stored procs at once, to deploying changes, and back to running them at once. Give your admin something fun and interesting to do and create scheduled jobs for your reports, thus running them all at the same time in the various locations, without human intervention. Heck, use Reporting Services and send out e-mails with exports from the reports to the various parties if you wish.

I'm not understanding what the problem is, nor why the various solutions given won't do the trick for you.