Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

SQL Scripts & Samples

Posted Dec 1, 2006

Execute T-SQL Code on Many Servers at One Time

By DatabaseJournal.com Staff

>>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

Download Script:

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

SQL Scripts & Samples Archives

Latest Forum Threads
SQL Scripts & Samples Forum
Topic By Replies Updated
sql help!! add 1 August 22nd, 11:58 AM
How To Combine These 2 Queries into 1 Query ? tarek_land 1 June 7th, 08:37 AM
solving query svibuk 1 February 3rd, 06:08 AM
converting from a character string to uniqueidentifier saturnius 4 January 4th, 05:56 AM