none
Alert message content with query result RRS feed

  • Question

  • Hi all,

    i set an alert to notify our team via mail in case of a mirror database state change. I set a WMI event alert with this query:
    SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE WHERE State=8.

    Ther's the possibility to include into mail body DatabaseName and TextData field returns by the query?

    Thank you 

    Monday, October 22, 2018 9:16 AM

All replies

  • here is an example

    https://www.brentozar.com/archive/2014/10/send-query-results-sql-server-agent-job/


    Ramesh Babu Vavilla MCTS,MSBI

    Monday, October 22, 2018 9:20 AM
  • USE [msdb]

    GO

    EXEC msdb.dbo.sp_add_alert @name=N'DBM State: Automatic Failover (database_name)',

    @message_id=0,

    @severity=0,

    @enabled=1,

    @delay_between_responses=0,

    @include_event_description_in=1,

    @category_name=N'Database Mirroring',

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

    @wmi_query=N'SELECT * from DATABASE_MIRRORING_STATE_CHANGE

    WHERE State = 8 AND DatabaseName = ''Database_name''',

    @job_id=N'00000000-0000-0000-0000-000000000000


    Ramesh Babu Vavilla MCTS,MSBI

    Monday, October 22, 2018 9:22 AM
  • I've change query just to try if mail arrive. the result is that

    DATE/TIME:      22/10/2018 12:05:37

    DESCRIPTION:    SELECT * from DATABASE_MIRRORING_STATE_CHANGE

    WHERE State <>0

    COMMENT:        (None)

    JOB RUN:        (None)

    I want to have into DESCRIPTION 'DatabaseName' and 'TextData'

    Monday, October 22, 2018 10:04 AM
  • EXEC msdb.dbo.sp_add_alert @name=N'DBM State: Automatic Failover', 
    @message_id=0, 
    @severity=0, 
    @enabled=1, 
    @delay_between_responses=0, 
    @include_event_description_in=1, 
    @category_name=N'[Uncategorized]', 
    @wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER', 
    @wmi_query=N'SELECT * from DATABASE_MIRRORING_STATE_CHANGE WHERE State <>0', 
    @job_id=N'00000000-0000-0000-0000-000000000000'
    GO
    Monday, October 22, 2018 10:06 AM
  • SOLVED WITH THIS METHOD:

    send mail for each status changed in mirror:

    Alert

    USE [msdb]
    GO

    /****** Object:  Alert [Mirroring Alert]    Script Date: 22/10/2018 15:50:07 ******/
    EXEC msdb.dbo.sp_add_alert @name=N'Mirroring Alert', 
    @message_id=0, 
    @severity=0, 
    @enabled=1, 
    @delay_between_responses=0, 
    @include_event_description_in=0, 
    @category_name=N'[Uncategorized]', 
    @wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER', 
    @wmi_query=N'SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE WHERE State<>0', 
    @job_id=N'6a86ce53-5947-4692-a02c-12ea7dfa5884'
    GO

    Job to send mail

    declare @Msg  varchar(1500) 

    set @Msg = 'State of $(ESCAPE_SQUOTE(WMI(DatabaseName))) database changed to
     $(ESCAPE_SQUOTE(WMI(State)))  on $(ESCAPE_SQUOTE(WMI(StartTime))) message: $(ESCAPE_SQUOTE(WMI(TextData))) '

    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'invia mail',
    @recipients = 'nicola.pozzebon.01@delonghigroup.com',
    @subject = 'Mirror State changed',
    @body = @Msg


    Monday, October 22, 2018 1:48 PM