July 18, 2007 at 7:45 am
July 18, 2007 at 7:48 am
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
July 18, 2007 at 7:49 am
If i have misunderstood let me know and apologises too.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 18, 2007 at 8:04 am
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.
July 18, 2007 at 11:11 am
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
July 18, 2007 at 12:09 pm
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
July 19, 2007 at 8:17 am
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
July 19, 2007 at 8:39 am
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.
July 19, 2007 at 8:44 am
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.
July 20, 2007 at 7:23 am
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.
July 20, 2007 at 8:50 am
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.
July 20, 2007 at 8:53 am
is there any other option apart from updating each n every database.
July 20, 2007 at 10:09 am
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]
July 20, 2007 at 11:19 am
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
July 20, 2007 at 2:30 pm
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.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply