none
SQL Server WMI Alert Fails to Insert Data Into a Table

    General discussion

  • Hi,

    I created a WMI alert which actually monitor any DDL event on a database. Like when ever a Database got Created/Altered/Deleted its entry will be written to a table in Master database. I am reading this class thru WMI.

     

    SELECT * FROM AUDIT_LOGIN_CHANGE_PASSWORD_EVENT

     

    So once such event occurs this alert got triggered and kicks a job which interns insert data to a table. Now if I run a Create/Alter/Delete database Alert does got fired but SQL Job not able to insert data into table and fails with this error.

     

    Date        5/28/2010 6:14:26 PM
    Log        Job History (capture pwd_change_EVENTS)

    Step ID        1
    Server        GURPSETH
    Job Name        capture pwd_change_EVENTS
    Step Name        Insert data into LogEvents
    Duration        00:00:00
    Sql Severity        0
    Sql Message ID        0
    Operator Emailed       
    Operator Net sent       
    Operator Paged       
    Retries Attempted        0

    Message
    Unable to start execution of step 1 (reason: Variable WMI(HostName) not found).  The step failed.

     

    In SQL Server Error Logs we have these errors.

     

    2010-05-28 18:13:21.14 spid57      Error: 17003, Severity: 16, State: 1.
    2010-05-28 18:13:21.14 spid57      Closed event notification conversation endpoint with handle '{F143118A-3B6A-DF11-B9E1-0022FAD17E6A}', due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8470</Code><Description>Remote service has been dropped.</Description></Error>'.
    2010-05-28 18:13:55.14 spid12s     Error: 17001, Severity: 16, State: 1.
    2010-05-28 18:13:55.14 spid12s     Failure to send an event notification instance of type 'AUDIT_LOGIN_CHANGE_PASSWORD_EVENT' on conversation handle '{F143118A-3B6A-DF11-B9E1-0022FAD17E6A}'. Error Code = '8429'.
    2010-05-28 18:13:58.08 spid14s     Error: 17005, Severity: 16, State: 1.
    2010-05-28 18:13:58.08 spid14s     Event notification 'SQLWEP_DDD57A95_4218_4830_AC03_1127C9AABB1C' in database 'master' dropped due to send time service broker errors. Check to ensure the conversation handle, service broker contract, and service specified in the event notification are active.

     

    Need help on this.

    Below is the script which I am using which is creating table, job and alert.

     

    /*******************************************************************************************
    *  This script will create 1 Alert to Monitor Create database, Alter Database &
    *  Drop database events. The alert will run a job and the job will enter data in a table.
    *
    * For any suggestion contact :gur.sethi@in.ibm.com and abhay.chaudhary@in.ibm.com
    *******************************************************************************************/


    /* Step 1: creating the table to capture the DDL information */

    USE Master
    GO

    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[DDl_DATABASE_EVENTS]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
    DROP TABLE [dbo].[DDL_DATABASE_EVENTS]
    GO

    CREATE TABLE [dbo].[DDL_DATABASE_EVENTS] (
    [computerName] Varchar(20),
    [DatabaseName] varchar(20),
    [Loginname] Varchar(20),
    [PostTime] [datetime] NOT NULL ,
    [SQLInstance] Varchar(20),
    [TSqlcommand] Varchar (500),
    [RecordID] [int] IDENTITY (1,1) NOT FOR REPLICATION NOT NULL,
    [Flag] [int] NOT NULL CONSTRAINT [DF_DDL_DATABASE_EVENTS_Flag]  DEFAULT ((0))
    ) ON [PRIMARY]
    GO

    CREATE INDEX [DDL_DATABASE_EVENTS_IDX01] ON [dbo].[DDL_DATABASE_EVENTS]([Posttime]) WITH FILLFACTOR = 100 ON [PRIMARY]
    GO

    /*Step 2 : Creating the Job that will enter values into the DDL_DATABASE_EVENTS table created above*/
    /*         Service account and sql operator option are optional*/
    /*         Error handling is also added and we are running it in a transaction*/

    USE [msdb]
    GO

    IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'capture DDL_DATABASE_EVENTS')
    EXEC msdb.dbo.sp_delete_job @job_name = N'capture DDL_DATABASE_EVENTS', @delete_unused_schedule=1

    GO

    --DECLARE @ServiceAccount varchar(128)
    --SET @ServiceAccount = N'<job_owner_account>'
    --DECLARE @SQLOperator varchar(128)
    --SET @SQLOperator = N'<sql_agent_operator>'

    BEGIN TRANSACTION
    DECLARE @ReturnCode INT
    SELECT @ReturnCode = 0

    IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
    BEGIN
    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    END

    DECLARE @jobId BINARY(16)
    EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'capture DDL_DATABASE_EVENTS',
    @enabled=1,
    @notify_level_eventlog=2,
    @notify_level_email=3,
    @notify_level_netsend=0,
    @notify_level_page=0,
    @delete_level=0,
    @description=N'Job for responding to SP change events',
    @category_name=N'[Uncategorized (Local)]',
    --@owner_login_name=@ServiceAccount,
    --@notify_email_operator_name=@SQLOperator,
    @job_id = @jobId OUTPUT

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    /*Step 3: Insert values into DDL_DATABASE_EVENTS*/

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Insert data into LogEvents',
    @step_id=1,
    @cmdexec_success_code=0,
    @on_success_action=1,
    @on_success_step_id=0,
    @on_fail_action=2,
    @on_fail_step_id=0,
    @retry_attempts=0,
    @retry_interval=0,
    @os_run_priority=0, @subsystem=N'TSQL',
    @command=N'
    INSERT INTO DDL_DATABASE_EVENTS (
    Computername,
    DatabaseName,
    LoginName,
    PostTime,
    SQLInstance,
    TSqlCommand
    )
    VALUES (
    N''$(ESCAPE_NONE(WMI(ComputerName)))'',
    N''$(ESCAPE_NONE(WMI(DatabaseName)))'',
    N''$(ESCAPE_NONE(WMI(Loginname)))'',
    GETDATE(),
    N''$(ESCAPE_NONE(WMI(SQLInstance)))'',
    N''$(ESCAPE_NONE(WMI(TSQLCommand)))''
    )',
    @database_name=N'master',
    @flags=0

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    COMMIT TRANSACTION

    GOTO EndSave

    QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
    EndSave:
    GO

    /*Step 4:Creating ALTER DATABASE alert and associating it with the Job to be fired */

    USE [msdb]
    GO

    IF EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Respond to ALTER_DATABASE_EVENTS')
    EXEC msdb.dbo.sp_delete_alert @name=N'Respond to ALTER_DATABASE_EVENTS'

    GO

    DECLARE @server_namespace varchar(255)
    IF ISNULL(CHARINDEX('\', @@SERVERNAME), 0) > 0
    SET @server_namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\' + SUBSTRING(@@SERVERNAME, ISNULL(CHARINDEX('\', @@SERVERNAME), 0) + 1, LEN(@@SERVERNAME) - ISNULL(CHARINDEX('/', @@SERVERNAME), 0))
    ELSE
    SET @server_namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER'

    EXEC msdb.dbo.sp_add_alert @name=N'Respond to ALTER_DATABASE_EVENTS',
    @enabled=1,
    @notification_message=N'Your Message',
    @wmi_namespace=@server_namespace,
    @wmi_query=N'SELECT * FROM ALTER_DATABASE',
    @job_name='capture DDL_DATABASE_EVENTS' ;

    GO


    /*Step 5: Creating CREATE DATABASE alert and associating it with the Job to be fired */

    USE [msdb]
    GO

    IF EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Respond to CREATE_DATABASE_EVENTS')
    EXEC msdb.dbo.sp_delete_alert @name=N'Respond to CREATE_DATABASE_EVENTS'

    GO

    DECLARE @server_namespace varchar(255)
    IF ISNULL(CHARINDEX('\', @@SERVERNAME), 0) > 0
    SET @server_namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\' + SUBSTRING(@@SERVERNAME, ISNULL(CHARINDEX('\', @@SERVERNAME), 0) + 1, LEN(@@SERVERNAME) - ISNULL(CHARINDEX('/', @@SERVERNAME), 0))
    ELSE
    SET @server_namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER'

    EXEC msdb.dbo.sp_add_alert @name=N'Respond to CREATE_DATABASE_EVENTS',
    @enabled=1,
    @notification_message=N'Your Message',
    @wmi_namespace=@server_namespace,
    @wmi_query=N'SELECT * FROM CREATE_DATABASE',
    @job_name='capture DDL_DATABASE_EVENTS' ;

    GO


    /*Step 6: Creating DROP DATABASE alert and associating it with the Job to be fired */

    USE [msdb]
    GO

    IF EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Respond to DROP_DATABASE_EVENTS')
    EXEC msdb.dbo.sp_delete_alert @name=N'Respond to DROP_DATABASE_EVENTS'

    GO

    DECLARE @server_namespace varchar(255)
    IF ISNULL(CHARINDEX('\', @@SERVERNAME), 0) > 0
    SET @server_namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\' + SUBSTRING(@@SERVERNAME, ISNULL(CHARINDEX('\', @@SERVERNAME), 0) + 1, LEN(@@SERVERNAME) - ISNULL(CHARINDEX('/', @@SERVERNAME), 0))
    ELSE
    SET @server_namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER'

    EXEC msdb.dbo.sp_add_alert @name=N'Respond to DROP_DATABASE_EVENTS',
    @enabled=1,
    @notification_message=N'Your Message',
    @wmi_namespace=@server_namespace,
    @wmi_query=N'SELECT * FROM DROP_DATABASE',
    @job_name='capture DDL_DATABASE_EVENTS' ;

    GO


     



     

     

    • Changed type GURSETHI Friday, May 28, 2010 1:10 PM Its not related to Database Mirroring
    Friday, May 28, 2010 1:09 PM

All replies

  • Hi 

    Did you find any solution on this error . i am working on this same error . 

    please let me know if you have any solution

    bye thanks


    Ankit Shah SQL Server DBA

    Monday, February 20, 2012 3:43 PM