Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2002

    Unanswered: 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.


  2. #2
    Join Date
    Nov 2002
    ...nobody has this kind of setup???

  3. #3
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    Which mail, setup how?


  4. #4
    Join Date
    Nov 2002
    email setup to send emails from SQL Server.

  5. #5
    Join Date
    Feb 2004
    Burbank & Santa Cruz de la Sierra
    Need more info from ya, 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
    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)
    	SET @TargetDate = CONVERT(varchar(10), @TDate, 101)
    /*----- Drop the temporary table and recreate it								*/
    	     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)
    		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
    				@query = @SQLCmd,
    				@no_header= 'TRUE', 
    				@dbuse = 'MyDatabase', 
    DROP TABLE EventLogTbl
    Last edited by TallCowboy0614; 11-08-04 at 18:58.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts