Execute T-SQL Code on Many Servers at One Time
December 1, 2006
>>Script Language and Platform: MS T-SQL
Do you have more than 10 sql servers you must manage? Most DBAs do; I have over 120 SQL instances to manage. I used to use ISQL/OSQL in a batch to execute a command on all of these instances. It works but not very nicely. Below is the code I use to collect information of all my servers from T-SQL. No more batch files for me!
Basically, this code will create a table that houses your instance names and 'sa' passwords, create linked connection, execute your code, drop the link connection and finally delete the temp table. To add more instances, just add another line to the "insert into" section of the code. My first usage of this code is to connect to all of my production servers and report back the job status on a daily basis. I only see the failed jobs and start my day from there.
You must execute this from a SQL 2005 connection as the linked server code is slightly different on SQL 2000 than SQL 2005. But that's ok, as I connect to SQL 7.0, SQL 2000 and SQL 2005 servers. Read the comments at the beginning of the code which shows you how to modify it for your environment.
I hope that this code will make you life easier, as it has done for me.
Author: Dr. Discovery
Disclaimer: We hope that the information on these script pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided "as -is", without any warranty, whether express or implied, of its accuracy, completeness, or fitness for a particular purpose... Disclaimer Continued
Back to Database Journal Home