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.

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" <Phil@discussions.microsoft.com> wrote in message 
news:EF8A4C79-86D3-407C-AAD9-B019D6CDE7CD@microsoft.com...
> 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. 

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/

 

Dan,

Wouldn't SSIS be good for something like this?

Jay

"Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message 
news:2D801A5F-F345-4085-B365-325327D79865@microsoft.com...
> 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/
>
>
> 

> 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" <spam@nospam.org> wrote in message 
news:uN5M2HgaKHA.6028@TK2MSFTNGP04.phx.gbl...
> Dan,
>
> Wouldn't SSIS be good for something like this?
>
> Jay
>
> "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message 
> news:2D801A5F-F345-4085-B365-325327D79865@microsoft.com...
>> 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/
>>
>>
>>
>
> 

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

"Phil" wrote:

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