May 12, 2008 at 7:05 am
I have a few SQL Scripts that i need to run overnight. I know how to schedule a script - i have done this for several scripts but the problem i have now, is that i need to run the same script across multiple databases - about 80 at current and that number will be increasing to 200 ish by the years end. The script is basically just a collection of simple tasks - data cleansing, such as removing redundant records (based on custom criteria), ensuring data entered during the day is capitalised if necessary etc. Nothing that would take longer than a minute (if anywhere near that) altogether.
I am not sure the best way to go about running this across numerous databases besides rewriting the script to individually point to each database, which seems like a lot of work - is there any options in SS2K5 to schedule jobs to run over any given databases without having the schedule it seperately for each database???
Thanks in advance, hope this makes sense 🙂
May 12, 2008 at 7:17 am
Are you literally running exactly the same script in each database? They all have the same tables and columns to clean up, etc?
If so, then you could turn it into a dynamic script and have the script put in the database name for each database on the server.
declare DBs cursor local fast_forward for
select name
from sys.databases
where database_id > 4
and name not like 'reportserver%'
declare @Script varchar(max)
open DBs
fetch next from DBs into @Script
while @@fetch_status = 0
begin
select @Script = 'use ' + @Script + '
go
... Your script here' -- your script
exec sp_executesql @script
fetch next from DBs into @Script
end
There's also a system proc that will run a script on a "for each Database" basis, but since it's undocument (and thus subject to change without notice), I don't like to use it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 12, 2008 at 7:49 am
GSquared, thanks for your prompt reply. Yes, the databases i need to run this script on are identical in structure. And they named in a uniform fashion so for the first part of your code i assume i could use (assume the databases are named Company_Data_1, Company_Data_2, Company_Data_3 etc...):
declare DBs cursor local fast_forward for
select name
from sys.databases
where name like 'Company_Data%'
or something along those lines...
this definately sounds like the way to go, thanks again in advance.
May 12, 2008 at 12:43 pm
Your modification to the cursor should work in your case.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 13, 2008 at 3:28 am
Im not entirely sure on the pros and cons of Cursors, but dont i need to "deallocate" it once its finished? or is that implied somewhere in that code? Thanks.
May 13, 2008 at 8:51 am
Also im having trouble with the 'GO' in your code... Seems that anywhere i put that errors the script. Removing it enabled the script to run.
May 13, 2008 at 8:57 am
Your right, the "go" will break it. Sorry about that.
On the deallocation, yes, you'll need to do that. I just wrote the skeleton of the code, you'll need to flesh it out.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 13, 2008 at 9:07 am
Excellent. Thanks very much for the help, thats saved me a whole bunch of time!! 😀
EDIT: Just finished my code, works like a charm, thanks again 🙂
May 14, 2008 at 6:01 am
Try this:
Make one of your sql agents a master agent for all your sql servers.
(Right click the SQL Server Agent and select multi server administration and make this a master). Then make the other servers targets.
Then when you create the job to run your scripts you can specify the target servers of your scripts. (Sometimes the GUI can be your friend)
May 14, 2008 at 6:04 am
All the databases i need to run this on are on one server so i dont need to run across multiple servers. But ill certainly keep that in mind if i find i need to do that at some point. Thanks.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply