Need to capture alert error text.
-
I am using SMTP mail (rather than SQL Mail) for my alerts. Does anyone know of a good way to capture the Alert Error Text so I can then add that to the SMTP email I send.
I know I can get the basic message from the sysmessages table, but I'd like it to include the arguements as well. That way I get the specific error.
Thanks,
A.J.
DBA with an attitude -
This was removed by the editor as SPAM
-
Check out the use of tokens for SQL Agent in BOL.
There is a good example of their use here,
http://www.sqldev.net/sqlagent/SQLAgentStepTokens.htm
--------------------
Colt 45 - the original point and click interface
-
for extracting the error log file you can use script like following:
CREATE TABLE [dbo].[Errors] (
[vchMessage] [varchar] (400) NULL ,
[ID] [int] NULL
) ON [PRIMARY]
GO
INSERT dba.dbo.Errors EXEC master..xp_readerrorlog
DELETE FROM ERRORS WHERE vchMessage LIKE '%Log backed up%' OR
vchMessage LIKE '%.TRN%' OR vchMessage LIKE '%Database backed up%' OR
vchMessage LIKE '%.BAK%' OR vchMessage LIKE '%Run the RECONFIGURE%' OR
vchMessage LIKE '%Copyright (c)%'
and to send a mail through SMTP mail, you can use the similar logic as follows
(I am including the sample proc, you can modify it to be used for sending mail for error messages)
BEGIN
--DECLARE @job_cnt INTEGER
-- print 'FailedJobs'
SET @subject ='Job Failures on \\'+@@servername
CREATE TABLE #failedjobs
( jobname VARCHAR(100))
INSERT INTO #failedjobs
SELECT convert(varchar(50),name) Failed_Job_List
FROM msdb.dbo.sysjobs A, msdb.dbo.sysjobservers B
WHERE A.job_id = B.job_id AND B.last_run_outcome = 0
SET @body = @body + 'The Jobs that failed today : <br><br>'
SET @body = @body + '<table border=1 cellspacing = 1 cellpadding =1><tr><td align=center><font face=arial size=3><b>Failed Job List</b></font></td></tr>'
SET @body = @body + '<tr><td bgcolor=red colspan=1></td></tr>'
DECLARE cur_failed_jobs CURSOR FOR SELECT jobname FROM #failedjobs
OPEN cur_failed_jobs
FETCH NEXT FROM cur_failed_jobs INTO @jobname
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @body = @body + '<tr><td align=left><font face=arial size=2>'+@jobname +' </font></td><td valign=top no wrap><font face=arial size=2></font></td></tr>'
FETCH NEXT FROM cur_failed_jobs INTO @jobname
END
CLOSE cur_failed_jobs
DEALLOCATE cur_failed_jobs
SET @body = @body + '</table><BODY></html>'
--GOTO send_mail
END
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply