Friday, March 23, 2012

Running the same query on multiple servers

Hi, I periodically run the same query on multiple servers. Currently I
do this via query analyzer and would like to cut down the time it takes
to switch servers.
Is there a tool I can configure multiple connections on and run the same
query on selected servers.
Thanks
Dilan
create linked servers and run the queries as
select ... from servername.databasename.objectname
Dandy Weyn
[MCSE-MCSA-MCDBA-MCDST-MCT Community Leader]
SQL Server Technologist
http://www.dandyman.net
Check my SQL Server Resource Pages at http://www.dandyman.net/sql
"Dilan A" <dilan.a@.youtelus.net> wrote in message
news:up7hf.129920$S4.96172@.edtnps84...
> Hi, I periodically run the same query on multiple servers. Currently I do
> this via query analyzer and would like to cut down the time it takes to
> switch servers.
> Is there a tool I can configure multiple connections on and run the same
> query on selected servers.
> Thanks
> Dilan
|||Dandy Weyn [Dandyman] wrote:
> create linked servers and run the queries as
> select ... from servername.databasename.objectname
>
Thanks. I neglected to mention that the statements are always almost
updates to existing stored procedures and are supplied by our vendor as
patches. I received them as attachments in emails and have to save them
and then run them on about 8 servers.
Needless to say I am looking to click and go...
|||You can either use DMO or oSql to do this pretty easily. Both methods allow
you to easily connect to another server and issue the same commands. You do
have to write some code but it should only be a few lines and a google
search will most likely find some examples.
Andrew J. Kelly SQL MVP
"Dilan A" <dilan.a@.youtelus.net> wrote in message
news:up7hf.129920$S4.96172@.edtnps84...
> Hi, I periodically run the same query on multiple servers. Currently I do
> this via query analyzer and would like to cut down the time it takes to
> switch servers.
> Is there a tool I can configure multiple connections on and run the same
> query on selected servers.
> Thanks
> Dilan
|||I tend to just use a batch file e.g. create 3 folders Output,Servers,Source
under a main folder (in my example it's DBADeploy). Create a batchfile as
below called deploy.bat in C:\DBADeploy
@.echo off
FOR /F %%f in (C:\DBADeploy\Servers\servers.txt) do
C:\DBADeploy\Source\Update.bat %%f
In the Servers folder create a file called servers.txt which is a list of
the target servers
In the Source folder create a file called update.sql with you code in it
(including USE database statements) and a batch file called Update.bat with
the contents below
@.echo off
set server=%1
set outputtemp=%1
set outputtemp=%outputtemp:\=_%
set outputfile="C:\DBADeploy\Output\%outputtemp%.txt"
osql -S %server% -d master -n -E -w 200 -h-1 -l 15 -i
"C:\DBADeploy\Source\update.sql" -o %outputfile%
echo Completed %1
Once that's setup then anything you want to deploy you can just stick in
update.sql and run deploy.bat and you're done. You can check the output for
each server in the Output folder.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Dilan A" <dilan.a@.youtelus.net> wrote in message
news:up7hf.129920$S4.96172@.edtnps84...
> Hi, I periodically run the same query on multiple servers. Currently I do
> this via query analyzer and would like to cut down the time it takes to
> switch servers.
> Is there a tool I can configure multiple connections on and run the same
> query on selected servers.
> Thanks
> Dilan

No comments:

Post a Comment