ALTER DATABASE using CURSOR. Error: not a valid identifier

• Bjarne Rasmussen

  Right there with Babe

  Points: 714

  More actions

  July 4, 2013 at 8:46 am

  #274931

  Hi all,

  I'm really lost as to why i can't get this to work.

  I have an AlwaysOn AG with 42 databases in the group. The option to suspend data movement is to issue the suspend statement one at a time like this:

  ALTER DATABASE [DBName] SET HADR SUSPEND;

  I like to build a script that can suspend them all at once. My code looks like this:

  Declare @database_name VARCHAR(100)

  Declare @suspendname VARCHAR(100)

  DECLARE db_cursor CURSOR FOR

  select database_name from sys.availability_databases_cluster

  OPEN db_cursor

  FETCH NEXT FROM db_cursor INTO @database_name

  WHILE @@FETCH_STATUS = 0

  BEGIN

  Set @suspendname = 'ALTER DATABASE ['+@database_name+'] SET HADR SUSPEND;'

  execute @suspendname

  --PRINT @suspendname

  FETCH NEXT FROM db_cursor INTO @database_name

  END

  CLOSE db_cursor

  DEALLOCATE db_cursor

  The error i get is this:

  Msg 203, Level 16, State 2, Line 19

  The name 'ALTER DATABASE [Div_Test] SET HADR SUSPEND;' is not a valid identifier.

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  July 4, 2013 at 8:49 am

  #1629845

  Try this:

  ...

  execute (@suspendname)

  ...

  John

• Bjarne Rasmussen

  Right there with Babe

  Points: 714

  More actions

  July 4, 2013 at 8:56 am

  #1629851

  WUPTI 😀

  Great - that worked - got another error, but I know how to fix that.

  Thnx a million.