Sql server 2000 - Automatically email alert for sql agent log

Problem: Automatically sending error log and sql agent log

Prequisite: SQL Server SMTP Mail XP, download this dll here.

This procedure will send the sql agent log .

create procedure sp_EmailSQLAgentlog @charfrom
varchar(50), @charsubject varchar(100), @charemailserver
varchar(15), @intresults int OUTPUTAS /* This sp is to query the Sql
agent history and email it to operator*/
Declare c1 scroll cursor
forSELECT endTime = DATEADD (
SECOND,
jh.run_duration,
CAST
(

CONVERT(VARCHAR, jh.run_date)
+ ' ' +
STUFF(STUFF(RIGHT('000000'
+
CONVERT(VARCHAR,jh.run_time),6),5,0,':'),3,0,':')
AS DATETIME
) )
,
replace(replace(jh.run_status,1,'Successful'),0,'Failure'),
j.name
FROM msdb..sysjobhistory jh INNER JOIN
msdb..sysjobs j ON j.job_id = jh.job_id
WHERE jh.step_name = '(Job outcome)' and
jh.run_date = cast(year(getdate())as varchar) + right('0'+
cast(month(getdate())as varchar),2) + right('0'+ cast(day(getdate()) as
varchar),2) Declare @msg varchar(3000)Declare @tmpmsg1
varchar(500)Declare @tmpmsg2 varchar(500)Declare @tmpmsg3 varchar(500)
set
@msg =''
open C1 fetch next from C1 into @tmpmsg1, @tmpmsg2,
@tmpmsg3 While (@@Fetch_Status <> - 1)
begin set @msg=
@msg + @tmpmsg1 + ' ' + @tmpmsg2 + ' ' + @tmpmsg3 + ' '+ char(13)
fetch next from C1 into @tmpmsg1, @tmpmsg2,
@tmpmsg3 endclose C1
deallocate C1
if len(@msg) > 0
begin ---- get all the operator email Declare c2 scroll
cursor for SELECT email_address FROM
msdb.dbo.sysoperators where enabled=1 Declare @email
varchar(3000) Declare @tmpemail varchar(500) set @email
='' open c2 fetch next from c2 into @tmpemail
While (@@Fetch_Status <> - 1)
begin
set @email= @email + @tmpemail + ','
fetch next from c2 into @tmpemail end close c2 deallocate
c2 --- end of getting operator email --- send out the
mail exec @intresults =
master.dbo.xp_smtp_sendmail
@from = @charfrom, @to =
@email, @server =
@charemailserver, @subject = @charsubject
, @message = @msg
end

Scheduled this using the sql agent.

declare @rc intset

@rc=0exec
sp_EmailSQLAgentlog @charfrom = N'test@tests.com', @charemailserver
= N'18.18.0.1', @charsubject = N'SQL Agent Log: (Server:
"xxxx") (Job: "Daily sp_EmailSQLAgentlog")
', @intresults
= @rc
select @rc