Execution of Code on Multiple Servers Remotely

Total article views: 178 | Views in the last 30 days: 19

 Rate this |   Join the discussion |  Briefcase

Do you have more than 10 sql servers you must manage?
Most DBA s do, I have over 120 sql instances to manage. Before I used to use ISQL/OSQL in a batch to execute a command on all these instances. It works but not very nice. 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 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 enironment.

I hope that this code will make you life easier as it has done for me.

Total article views: 178 | Views in the last 30 days: 19