Need to capture alert error text.

Question

Post reply

Need to capture alert error text.

Adam Wilbur

SSCertifiable

Points: 7001
More actions
June 30, 2004 at 11:41 am

#164207

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
July 5, 2004 at 8:00 am

This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47794 More actions · Answer 1

philcart

SSC-Forever

Points: 47794

July 5, 2004 at 9:26 pm

#513369

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

Daljit S. Saini SSC Enthusiast Points: 189 More actions · Answer 2

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 : '

SET @body = @body + '<table border=1 cellspacing = 1 cellpadding =1><tr><td align=center>Failed Job List</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>'+@jobname +' </td><td valign=top no wrap></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