Stored Procedure Update on Multiple Servers

[Phil]

Hi,

I am wondering if this is at all possible, I have a number of SQL Server
2000/2005 servers with different names with the same database name on them
but within different locations and containing different data depeneding on
the location but all the structures are the same.
At the moment what I have to do is when changing a Stored Procedure or
Table, I have to manually connect to all the servers and change them
individually which takes considerable time and is proned to the odd one being
missed out.
What could be done is amend the procedures in the syscomments table which I
really dont want to do so would just go along with droping it and re-creating
it, though with altering the table designs to add or removed columns I am not
sure what the best practice would be to make these changes, can anyone point
me in the right direction where to start with this?
I am running SQL Server 2005 on my local machine with linked servers to all
the other servers, though the other servers are all running under 2005.

Thanks for the help.

[Balaji]

Hi Phil,

In my view you should write a script that will connect to each remote server
and execute your change script. Since you already have added all the remote
server(s) as linked servers in your SQL Server this should not be tough
task.

Regards, Balaji

"Phil" <Ph...@discussions.microsoft.com> wrote in message
news:EF8A4C79-86D3-407C...@microsoft.com...

[Dan Guzman]

One method is to create a text file with your list of target instances and
then use a command-prompt FOR command to invoke SQLCMD for each. The
example command below can be saved to a BAT file and run to execute
MyScript.sql for each instance listed in the SQLInstance.txt file.

FOR /F %%f IN (SQLInstances.txt) DO SQLCMD -E -S
"%%f" -d"MyDatabase" -i"MyScript.sql"

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

[Jay]

Dan,

Wouldn't SSIS be good for something like this?

Jay

"Dan Guzman" <guzm...@nospam-online.sbcglobal.net> wrote in message
news:2D801A5F-F345-4085...@microsoft.com...

[Dan Guzman]

> Wouldn't SSIS be good for something like this?

Sure, SSIS can do this. There are many other ways to accomplish this task
too. For example, you can right-click on a server group in SQL Server 2008
SSMS and select new query to run the script against all servers in the
group.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Jay" <sp...@nospam.org> wrote in message
news:uN5M2Hga...@TK2MSFTNGP04.phx.gbl...

[Phil]

Hi all,

Thanks for all the valuable replies, I will have a look at them over the
next couple of day, its nice to have confirmation that I would not be wasting
my time trying to do this.

Thank again, Phil