sql server replication: Perform Mirror-Changes to All Databases -- sql server replication

Perform Mirror-Changes to All Databases

Zakir
7/31/2006 12:43:56 PM

sql server replication:

Hi,
We're setting up a mirrored database environment with SQL Server 2005.
[quoted text, click to view]
database seperately. Is it possible to perform these standard actions,
such as failing over the database the other sever, to all the mirrored
databases at once? I started to look into writing a stored procedure
that would perform the action to all the databases listed in a certain
table, but am running into some errors...

CREATE PROCEDURE sp_GentleFailOver

AS

DECLARE @DBO nvarchar(50)

DECLARE DBs_cursor CURSOR FOR

SELECT DatabaseName from MirrorDatabases

OPEN DBs_cursor

FETCH NEXT FROM DBs_cursor INTO @DBO

WHILE @@FETCH_STATUS = 0 BEGIN

ALTER DATABASE @DBO SET PARTNER FAILOVER

FETCH NEXT FROM DBs_cursor INTO @DBO

END

CLOSE IPs_cursor

DEALLOCATE IPs_cursor

GO

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Errors:
Msg 102, Level 15, State 1, Procedure sp_GentleFailOver, Line 12
Incorrect syntax near '@DBO'.

Msg 195, Level 15, State 7, Procedure sp_GentleFailOver, Line 12
'PARTNER' is not a recognized SET option.

I don't receive either of the errors below when I put

ALTER DATABASE TEST SET PARTNER FAILOVER instead of

ALTER DATABASE @DBO SET PARTNER FAILOVER

Thanks,

Zakir

Thanks,

Zakir

RE: Perform Mirror-Changes to All Databases

Joshua
10/12/2006 11:29:02 AM

I'm not sure if the procedure will accomplish its intended task but part of
the problem is coming from trying to pass a database name as a varialble into
the sql statement without using dynamic sql. try something like the
following:

create proc p_Diddy as begin

declare @vTab varchar(25)
declare @vString varchar(500)

set @vTab = 'TEST'

set @vString = 'Create table '+ @vTab+'(id int)'

exec (@vString)

end

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server replication group.