Alter Database across linked server

We have approximately 30 servers with about 700 databases.
We are running SQL Server 2005, SP1, on Windows 2003
We need to set Quoted Identifiers ON, as the default setting on all databases.
Linked servers are set up on all servers.
I have created a statement using dynamic sql to loop through all servers and
databases to set QUOTED_IDENTIFIER ON.
This is a sample output of a statement to be executed:
ALTER DATABASE SQL02.AdventureWorks
SET QUOTED_IDENTIFIER OFF
When the statement is executed I get an error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.
To verify my linked server is set up properly, I run the following
successfully:
select * from SQL02.AdventureWorks.Person.Address
Is it possible to run an alter database command across a linked server, and
if so, how do you let SQL Server know which database server is to be used?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200701/1"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6c17852baa692@.uwe...
> We have approximately 30 servers with about 700 databases.
> We are running SQL Server 2005, SP1, on Windows 2003
> We need to set Quoted Identifiers ON, as the default setting on all
> databases.
>
> Linked servers are set up on all servers.
> I have created a statement using dynamic sql to loop through all servers
> and
> databases to set QUOTED_IDENTIFIER ON.
> This is a sample output of a statement to be executed:
> ALTER DATABASE SQL02.AdventureWorks
> SET QUOTED_IDENTIFIER OFF
> When the statement is executed I get an error:
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near '.'.
> To verify my linked server is set up properly, I run the following
> successfully:
> select * from SQL02.AdventureWorks.Person.Address
> Is it possible to run an alter database command across a linked server,
> and
> if so, how do you let SQL Server know which database server is to be used?
>
Yes. In 2005 you can execute arbitrary batches, including stored procedures
and DDL, at remote servers with the EXEC ... AT statement.
eg:
exec ( '
ALTER DATABASE AdventureWorks SET QUOTED_IDENTIFIER OFF
' ) at SQL02
David