I am attempting to use some knowledge I got from Insights 2018. I want to create a SQL job that will capture data at the time of the alert or perform an action at the time of an alert event. (page 417 in Epicor work book). I got this script from Epicor. I run it and it creates the jobs and alerts however, if I start the job I get an error. Error is “Unable to start execution of step 1 (reason: variable WMI(TextData) not found. The step failed.” Any suggestions?
--JOBS AND ALERTS IF OBJECT_ID('DeadlockEvents', 'U') IS NOT NULL BEGIN DROP TABLE DeadlockEvents ; END ; GO CREATE TABLE DeadlockEvents (AlertTime DATETIME, DeadlockGraph XML) ; GO -- Add a job for the alert to run. EXEC msdb.dbo.sp_add_job @job_name=N'Capture Deadlock Graph', @enabled=1, @description=N'Job for responding to DEADLOCK_GRAPH events' ; GO -- Add a jobstep that inserts the current time and the deadlock graph into -- the DeadlockEvents table. EXEC msdb.dbo.sp_add_jobstep @job_name = N'Capture Deadlock Graph', @step_name=N'Insert graph into LogEvents', @step_id=1, @on_success_action=1, @on_fail_action=2, @subsystem=N'TSQL', @command= N'INSERT INTO DeadlockEvents (AlertTime, DeadlockGraph) VALUES (getdate(), N''$(ESCAPE_SQUOTE(WMI(TextData)))'')', @database_name=N'EpicorLive10' ; GO -- Set the job server for the job to the current instance of SQL Server. EXEC msdb.dbo.sp_add_jobserver @job_name = N'Capture Deadlock Graph' ; GO -- Add an alert that responds to all DEADLOCK_GRAPH events for -- the default instance. To monitor deadlocks for a different instance, -- change MSSQLSERVER to the name of the instance. EXEC msdb.dbo.sp_add_alert @name=N'Respond to DEADLOCK_GRAPH', @wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER', @wmi_query=N'SELECT * FROM DEADLOCK_GRAPH', @job_name='Capture Deadlock Graph' ; GO