Dynamic USE

I want to run a .sql script that updates data from specific tables using
OSQL. The problem i am having is that i need to run the same script on 300
servers. The database naming scheme are as follows:
server 1 : server1_testdb
server 2 : server2_testdb
server 3: server3_testdb
Question:
How can I write one script that can be used for all 300 servers. All
databases have the same structure and are identical except that it resides in
300 servers that is where the prefix (server1_, server2_, etc.) come from.
Does it mean I have to write 300 scripts to specify the database where the
script will be run.
Ex.
script 1: USE server1_testdb
UPDATE tbl_sample ...
script 2: USE server2_testdb
UPDATE tbl_sample ...
script 3: USE server3_testdb
UPDATE tbl_sample ...
Is there a way to employ the 'USE' statement dynamically? Is there a way to
pass a variable to the USE statement such as:
DECLARE @.sampledb sysname
SELECT @.sampledb = master.sysdatabases.name.........
USE @.sampledb
Help highly appreciated.Looks to me like you should do this with SMO (SQL 2005) or DMO (earlier).
In both cases, your connect strings will determine which server/DB to use.
At that point, it's all SQL.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:24723016-1F59-4B31-A779-988ECA6EA444@.microsoft.com...
I want to run a .sql script that updates data from specific tables using
OSQL. The problem i am having is that i need to run the same script on 300
servers. The database naming scheme are as follows:
server 1 : server1_testdb
server 2 : server2_testdb
server 3: server3_testdb
Question:
How can I write one script that can be used for all 300 servers. All
databases have the same structure and are identical except that it resides
in
300 servers that is where the prefix (server1_, server2_, etc.) come from.
Does it mean I have to write 300 scripts to specify the database where the
script will be run.
Ex.
script 1: USE server1_testdb
UPDATE tbl_sample ...
script 2: USE server2_testdb
UPDATE tbl_sample ...
script 3: USE server3_testdb
UPDATE tbl_sample ...
Is there a way to employ the 'USE' statement dynamically? Is there a way to
pass a variable to the USE statement such as:
DECLARE @.sampledb sysname
SELECT @.sampledb = master.sysdatabases.name.........
USE @.sampledb
Help highly appreciated.|||If you have SQL Server 2005 among your servers and all your servers are
visible from the server with SQL Server 2005 installed use SQLCMD
command-line utility and its features combined with some simple Windows
shell commands.
Create a sql script (save it as script.sql) like:
USE $(dbname)
GO
UPDATE ... -- put your statement here
Create simple Windows batch (save it as batch.bat in the same location as
script.sql) like:
for /L %%n IN (1,1,300) DO sqlcmd -S server%%n -E -v
dbname=server%%n_testdb -i script.sql
Then just run the batch and you should get what you want. I assume that you
have the same Windows login on all SQL Server instances.
Let me know if it works.
--
Regards
Pawel Potasinski
[http://www.potasinski.pl]
U¿ytkownik "morphius" <morphius@.discussions.microsoft.com> napisa³ w
wiadomo¶ci news:24723016-1F59-4B31-A779-988ECA6EA444@.microsoft.com...
>I want to run a .sql script that updates data from specific tables using
> OSQL. The problem i am having is that i need to run the same script on
> 300
> servers. The database naming scheme are as follows:
> server 1 : server1_testdb
> server 2 : server2_testdb
> server 3: server3_testdb
> Question:
> How can I write one script that can be used for all 300 servers. All
> databases have the same structure and are identical except that it resides
> in
> 300 servers that is where the prefix (server1_, server2_, etc.) come from.
> Does it mean I have to write 300 scripts to specify the database where the
> script will be run.
> Ex.
> script 1: USE server1_testdb
> UPDATE tbl_sample ...
> script 2: USE server2_testdb
> UPDATE tbl_sample ...
> script 3: USE server3_testdb
> UPDATE tbl_sample ...
> Is there a way to employ the 'USE' statement dynamically? Is there a way
> to
> pass a variable to the USE statement such as:
> DECLARE @.sampledb sysname
> SELECT @.sampledb = master.sysdatabases.name.........
> USE @.sampledb
> Help highly appreciated.
>