May 28, 2010 at 7:34 am
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.
*
*******************************************************************************************/
/* 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
Regards
GURSETHi
May 30, 2010 at 9:14 pm
You need to allow SQL Server Agent to use tokens (option available in SQL Server Agent properties). There is security risk associated with this: http://msdn.microsoft.com/en-us/library/ms175575.aspx.
February 20, 2012 at 9:55 am
Hi
I am working on same error and have check mark the "Replace tokens for all job responses to alert".
but still when getting same error " WMI(SQLInstance) not found".
Any clue or Idea?
Thanks
February 20, 2012 at 10:22 am
Make sure that SQL Server Agent service has been restarted
February 20, 2012 at 11:11 am
Yes i did after i checked mark that option
February 20, 2012 at 11:34 am
Do you use Default or Named instance?
February 20, 2012 at 11:36 am
I am on default Instance
February 20, 2012 at 12:17 pm
Try to create a test alert and see if it works:
EXEC msdb.dbo.sp_add_alert @name=N'Test Alert - Create DB',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=15,
@include_event_description_in=1,
@notification_message=N'WMI Testing',
@category_name=N'[Uncategorized]',
@wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',
@wmi_query=N'select * from create_database'
GO
After you created the alert - set e-mail notification to yourself. Create a database and see if you get notification.
February 20, 2012 at 12:28 pm
I tried but i am not getting any alert when i create the DB.
February 20, 2012 at 12:31 pm
This is script of my first step of Job .
INSERT INTO BLOCKED_PROCESS_REPORT (
[PostTime] ,
[SQLInstance] ,
[Databaseid] ,
[computerName],
[SessionLoginName],
[SPID] ,
[TransactionID] ,
[EventSequence] ,
[objectID] ,
[IndexID] ,
[TextData],
[duration]
)
VALUES (
GETDATE(),
N'$(ESCAPE_NONE(WMI(SQLInstance)))',
N'$(ESCAPE_NONE(WMI(Databaseid)))',
N'$(ESCAPE_NONE(WMI(ComputerName)))',
N'$(ESCAPE_NONE(WMI(SessionLoginname)))',
N'$(ESCAPE_NONE(WMI(SPID)))',
N'$(ESCAPE_NONE(WMI(TransactionID)))',
N'$(ESCAPE_NONE(WMI(EventSequence)))',
N'$(ESCAPE_NONE(WMI(objectid)))',
N'$(ESCAPE_NONE(WMI(indexid)))',
N'$(ESCAPE_SQUOTE(WMI(Textdata)))',
N'$(ESCAPE_NONE(WMI(Duration)))'
)
February 21, 2012 at 3:31 pm
Check also if [NT AUTHORITY\SYSTEM] has sysadmin access on SQL Server (if WMI service runs as SYSTEM account).
July 4, 2012 at 9:09 am
Hi,
I know this is an ld post but I've tried all that has been asked as per post and the [WMI Performance Adapter] service is running under the account which has admin rights on the server and sysadmin rights on the SQL server.
Below is the script being run on the job:-
INSERT INTO DeadlockEvents
(AlertTime, DeadlockGraph)
VALUES (getdate(), N'$(ESCAPE_SQUOTE(WMI(TextData))))')
The script can run when I execute it but I stil get error below:-
Date7/4/2012 3:40:03 PM
LogJob History (Capture Deadlock Graph_Old)
Step ID1
ServerSERVERNAME
Job NameCapture Deadlock Graph_Old
Step NameInsert graph into LogEvents
Duration00:00:00
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Unable to start execution of step 1 (reason: Variable WMI(TextData) not found). The step failed.
July 4, 2012 at 9:10 am
The script can run when running in management studio
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply