Enable\Disable alerts with script

• GoofyKC

  SSCommitted

  Points: 1586

  More actions

  October 9, 2009 at 8:37 am

  #136274

  I have a need to disable an alert and then enable it after a process. I can do it manually via Management Studio but need to be able to do it programmatically. Anyone have any ideas?

• hi_abhay78

  SSCertifiable

  Points: 6265

  More actions

  October 9, 2009 at 9:50 am

  #1064205

  You need to use sp_update_alert and set the enabled value to 0

  USE [msdb]

  GO

  EXEC msdb.dbo.sp_update_alert @name=N'Respond to DEADLOCK_GRAPH',

  @message_id=0,

  @severity=0,

  @enabled=0,

  @delay_between_responses=0,

  @include_event_description_in=5,

  @database_name=N'',

  @notification_message=N'',

  @event_description_keyword=N'',

  @performance_condition=N'',

  @wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',

  @wmi_query=N'SELECT * FROM DEADLOCK_GRAPH',

  @job_id=N'23196807-e886-4d50-9407-295b32f68105'

  GO

  Abhay Chaudhary
  Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)