I'm a newby and I have a quick question. I have set up this procedure
to run as a job. I can create a block and have it populate the table,
but I need to be alerted via email when the table is being populated. I
do not know what step/s am I missing. Here is the scripts I ran:
CREATE TABLE BlockMonitorInfo
(
spid smallint,
blocked smallint,
waittype binary,
InfoTime datetime
)
go
/*********creates a control table that lets you turn the background
process on and off**********/
CREATE TABLE BlockMonitorControl
(
BlockMonitorOn tinyint
)
go
/**********populates the control table and initially turns on the
background process******/
INSERT INTO BlockMonitorControl
VALUES (1)
go
/*
StartBlockMonitor accepts one datetime paramter that controls the
sampling delay for capturing blocking information
*/
drop PROC StartBlockMonitor
go
CREATE PROC StartBlockMonitor
@DelayTime char(9)
AS
DECLARE @CurrentInfoTime datetime
-- Set the control flag in BlockMonitorControl = On
update BlockMonitorControl set BlockMonitorOn = 1
-- Capture blocking lock info until the control flag is set = false
-- We set it off by running StopBlockMonitor which we'll create soon.
WHILE (SELECT BlockMonitorOn from BlockMonitorControl) = 1
BEGIN
SELECT @CurrentInfoTime = getdate()
INSERT INTO BlockMonitorInfo (spid, blocked, waittype, InfoTime )
SELECT
spid, blocked, waittype, @CurrentInfoTime
FROM
master..sysprocesses (nolock)
WHERE
blocked <> 0
WAITFOR delay @DelayTime
END
go
/*
StopBlockMonitor turns off the capture of blocking info by setting the
control flag in BlockMonitorControl = off
*/
DROP PROC StopBlockMonitor
go
CREATE PROC StopBlockMonitor
AS
UPDATE BlockMonitorControl SET BlockMonitorOn = 0
go
SELECT
BlockMonitorInfo.*
FROM
BlockMonitorInfo,
(SELECT distinct InfoTime FROM BlockMonitorInfo)
BlockChainTime
WHERE
BlockMonitorInfo.InfoTime =
BlockChainTime.InfoTime
and Blocked not in
(SELECT spid
FROM BlockMonitorInfo
WHERE InfoTime =
BlockChainTime.InfoTime)
This last query is what I have set up in a job, but I need the job to
alert us when the table gets new information added.
Thank you for the assistance!
