SQL Server WMI Alert Fails to Insert Data Into a Table

Question

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 :[e-mail] and [e-mail]
*******************************************************************************************/


/* 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


 

 

 

Answer 1

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