Send alert from job

[Stephanie]

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 BlockMonitorInf o
(
spid smallint,
blocked smallint,
waittype binary,
InfoTime datetime
)
go
/*********create s a control table that lets you turn the background
process on and off**********/
CREATE TABLE BlockMonitorCon trol
(
BlockMonitorOn tinyint
)
go
/**********popul ates the control table and initially turns on the
background process******/
INSERT INTO BlockMonitorCon trol
VALUES (1)
go

/*
StartBlockMonit or accepts one datetime paramter that controls the
sampling delay for capturing blocking information
*/
drop PROC StartBlockMonit or
go
CREATE PROC StartBlockMonit or
@DelayTime char(9)
AS

DECLARE @CurrentInfoTim e datetime

-- Set the control flag in BlockMonitorCon trol = On
update BlockMonitorCon trol set BlockMonitorOn = 1

-- Capture blocking lock info until the control flag is set = false
-- We set it off by running StopBlockMonito r which we'll create soon.

WHILE (SELECT BlockMonitorOn from BlockMonitorCon trol) = 1
BEGIN

SELECT @CurrentInfoTim e = getdate()

INSERT INTO BlockMonitorInf o (spid, blocked, waittype, InfoTime )
SELECT
spid, blocked, waittype, @CurrentInfoTim e
FROM
master..sysproc esses (nolock)
WHERE
blocked <> 0

WAITFOR delay @DelayTime
END
go

/*
StopBlockMonito r turns off the capture of blocking info by setting the
control flag in BlockMonitorCon trol = off
*/

DROP PROC StopBlockMonito r
go

CREATE PROC StopBlockMonito r
AS
UPDATE BlockMonitorCon trol SET BlockMonitorOn = 0
go

SELECT
BlockMonitorInf o.*
FROM
BlockMonitorInf o,
(SELECT distinct InfoTime FROM BlockMonitorInf o)
BlockChainTime
WHERE
BlockMonitorInf o.InfoTime =
BlockChainTime. InfoTime
and Blocked not in
(SELECT spid
FROM BlockMonitorInf o
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!

[Simon Hayes]

"Stephanie" <st*****@centen e.com> wrote in message
news:11******** **************@ g14g2000cwa.goo glegroups.com.. .

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:

<snip>

Assuming you have SQL Mail set up, then you can use xp_sendmail to send
email from within your procedure. If you don't have SQL Mail, then this is
one common alternative:

http://www.sqldev.net/xp/xpsmtp.htm

Simon