errorlog monitoring & job outputs

Considering email is setup, how would you send emails by scanning errorlog for any issues. Also, how to send program outputs using email.
Thanks...nobody has this kind of setup?|||Which mail, setup how?

-PatP|||email setup to send emails from SQL Server.|||Need more info from ya, Mkalsi...you can use xp_sendmail to do it...I have things setup to do what I THINK you are trying to do...

Although, this is probably more convoluted than you need...I wanted to try to make my messages a little more readable than just inserting the error message row columns from the EventLog table...

Anyway...check out the sendmail code near the bottom...that's the gist of what you'll need.

Keep in mind though, that there's a whole 'nother arm-wrestling match about permissions and user id stuff that must be in place to send mail from SQL Server 2000 through outlook - it was a tad more complex than just point-n-shoot.

*================================================= ====================================
| Name: Send_EventLog_Mail_By_Level
| Description: Performs a select against the EventLog table based on input parameters for target date and
| target error level and inserts the selected rows into an email which is then sent to a static
| mail address list. No email is sent if selection results in no rows selected for that date/error level.
|
| Created: 04/29/2004 by Yo_Mama
| Modified: 09/20/2004 by Yo_Mama - Added Server Name to message subject/message body.
| Inputs: TDate (smalldatetime) - The date for which any errors are to be selected/reported via email.
| TLevel (int) - The relative error level for which rows are to be selected/reported via email.
|================================================= =====================================*/
CREATE PROCEDURE [dbo].[Send_EventLog_Mail_By_Level]
@.TDate smalldatetime = NULL,
@.TLevel int = 0
AS

DECLARE @.SQLCmd varchar(256)
DECLARE @.Message varchar(256)
DECLARE @.Subject varchar(128)
DECLARE @.TargetDate smalldatetime
DECLARE @.LargestEventSize int
DECLARE @.SvrName varchar(30)

/*-- Capture Server Name and build string to use in WaitForFile request */
SET @.SvrName = CONVERT(varchar, SERVERPROPERTY ('ServerName'))

IF (@.TDate IS NULL)
SET @.TargetDate = CONVERT(varchar(10), GETDATE(), 101)
ELSE
SET @.TargetDate = CONVERT(varchar(10), @.TDate, 101)

/*-- Drop the temporary table and recreate it */
IF EXISTS (SELECT *
FROM dbo.sysobjects
WHERE ID = OBJECT_ID(N'[dbo].[EventLogTbl]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[EventLogTbl]

CREATE TABLE [dbo].[EventLogTbl] (EvtID int, EventDesc varchar(1200))

/*-- Build rows in the temporary table by selecting the date and message level desired and formatting row */
INSERT INTO EventLogTbl
SELECT [ID] as EvtID, ('---> ID=' + LTRIM(CONVERT(varchar(10), [ID])) +
', Date=' + CONVERT(varchar(20), [Date]) + ', Level=' +
LTRIM(CONVERT(varchar(2), MsgLevel)) + ', APP=' + RTRIM(App) +
', Function=' + RTRIM([Function]) + ', Message=' + RTRIM(Message) +
', Rows=' + LTRIM(CONVERT(varchar(5), [Rows])) + ', Host=' +
RTRIM(Host)) AS EventDesc
FROM EventLog
WHERE (CONVERT(varchar(10), [Date], 101) = CONVERT(varchar(10), @.TargetDate, 101)) AND
MsgLevel = @.TLevel

/*-- If our dragnet caught anything, then format subject line, message intro, and detail the message rows */
IF (@.@.ROWCOUNT > 0)
BEGIN
SET @.Subject = @.SvrName + '.IBDIndex - Level ' +
CONVERT(varchar(3), @.TLevel) + ' Processing Events were found for ' +
CONVERT(varchar(10), @.TargetDate,101)

SET @.Message = 'The following events (Message Level ' + CONVERT(varchar(3), @.TLevel) +
') occurred in ' + @.SvrName + '.IBDIndex processing on ' +
CONVERT(varchar(12), @.TargetDate,101) + CHAR(13) + CHAR(10) +
CHAR(9) + '- Check EventLog table for details and context' +
CHAR(13) + CHAR(10)

/*-- Capture the longest message in the temp table...need it to cast varchar below */
SELECT @.LargestEventSize = Max(Len(EventDesc))
FROM EventLogTbl

/*-- Set the SQL command for xp_sendmail - need to recast varchar to avoid trailing blanks */
SET @.SqlCmd = 'SELECT Cast(EventDesc AS varchar(' +
CONVERT(varchar(5), @.LargestEventSize) +
')) FROM EventLogTbl ORDER BY EvtID'

/*-- Send of the email!!! */
EXECUTE Master.dbo.xp_sendmail
'Yo_Mama@.AngieDaddyToo.com',
@.query = @.SQLCmd,
@.no_header= 'TRUE',
@.dbuse = 'MyDatabase',
@.subject=@.Subject,
@.message=@.message
END

DROP TABLE EventLogTbl

RETURN

GO