RE: Email Automation – SQLServerCentral

SSChampion

Points: 12125

May 13, 2010 at 10:02 am

Here is a script I use that you can get the concept from. It sends out a nicely formated html e-mail.

While I have made quite a few modifications to it I do not take credit for the script as I got this from a web site all though I do not recall were. This runs as a job and sends out notification of any errors in the sql server errorlog.

DECLARE @Error_Message varchar(75)

DECLARE @Error_Date datetime

DECLARE @SPID varchar(15)

DECLARE @Cnt1 int

DECLARE @Row_Count int

DECLARE @MxDate Datetime

DECLARE @ErrorMsg_Message varchar(255)

DECLARE @TableHTML varchar(8000)

DECLARE @StrSubject VARCHAR(100)

DECLARE @Oriserver VARCHAR(100)

DECLARE @Version VARCHAR(250)

DECLARE @Edition VARCHAR(100)

DECLARE @ISClustered VARCHAR(100)

DECLARE @sp-2 VARCHAR(100)

DECLARE @ServerCollation VARCHAR(100)

DECLARE @SingleUser VARCHAR(5)

DECLARE @LicenseType VARCHAR(100)

DECLARE @StartDate DATETIME

DECLARE @EndDate DATETIME

DECLARE @Cnt int

DECLARE @URL varchar(1000)

DECLARE @STR varchar(1000)

SELECT @Version = @@version

SELECT @Edition = CONVERT(VARCHAR(100), serverproperty('Edition'))

SELECT @StartDate = CAST(CONVERT(VARCHAR(4), DATEPART(yyyy, GETDATE())) + '-' + CONVERT(VARCHAR(2), DATEPART(mm, GETDATE())) + '-01' AS DATETIME)

SELECT @StartDate = @StartDate - 1

SELECT @EndDate = CAST(CONVERT(VARCHAR(5),DATEPART(yyyy, GETDATE() + 1)) + '-' + CONVERT(VARCHAR(2),DATEPART(mm, GETDATE() + 1)) + '-' + CONVERT(VARCHAR(2), DATEPART(dd, GETDATE() + 1)) AS DATETIME)

SET @Cnt = 0

IF serverproperty('IsClustered') = 0

BEGIN

SELECT @ISClustered = 'No'

END

ELSE

BEGIN

SELECT @ISClustered = 'YES'

END

SELECT @sp-2 = CONVERT(VARCHAR(100), SERVERPROPERTY ('productlevel'))

SELECT @ServerCollation = CONVERT(VARCHAR(100), SERVERPROPERTY ('Collation'))

SELECT @LicenseType = CONVERT(VARCHAR(100), SERVERPROPERTY ('LicenseType'))

SELECT @SingleUser = CASE SERVERPROPERTY ('IsSingleUser')

WHEN 1 THEN 'Yes'

WHEN 0 THEN 'No'

ELSE

'null' END

SELECT @OriServer = CONVERT(VARCHAR(50), SERVERPROPERTY('servername'))

SELECT @strSubject = 'DB Server Daily Error Log Checks ('+ CONVERT(VARCHAR(50), SERVERPROPERTY('servername')) + ')'

CREATE TABLE ##Errors1 (ERRORLOG varchar(8000),continuationRow varchar(5))

INSERT into ##Errors1

(ERRORLOG,continuationRow)

Exec master..xp_readerrorlog

CREATE TABLE ##Errors (LogDate DateTime,ProcessInfo varchar(15),errortxt varchar(4000),[text] varchar(4000))

INSERT into ##Errors

(LogDate,ProcessInfo,errortxt)

select substring(ERRORLOG,0,23) as LogDate,

substring(ERRORLOG,24,8) as ProcessInfo,

substring(ERRORLOG,34,Len(ERRORLOG)-34) as [Text]

from ##Errors1

where ERRORLOG LIKE '%Error:%' AND ERRORLOG NOT LIKE '%15457%' AND ERRORLOG NOT LIKE '%1073759%'

Update ##Errors

set Text=

(select description from master..sysmessages

where error=Substring(errortxt,charindex('Error:',errortxt)+6,(charindex(',',errortxt)-1)-(charindex('Error:',errortxt)+6)+1)

and msglangid=1033)

WHERE errortxt LIKE '%Error:%'

AND errortxt NOT LIKE '%15457%'

AND errortxt NOT LIKE '%1073759%'

AND errortxt NOT LIKE '%0x%'

SELECT @Row_Count = count(*) FROM ##Errors where logDate>GetDate()-1

If @Row_Count<>0

BEGIN

SET @TableHTML =

'Server Info

<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="47%" id="AutoNumber1" height="50">

<tr>

<td width="39%" height="22" bgcolor="#000080">

Server Name</td>

</tr>

<tr>

<td width="39%" height="27">' + @OriServer +'</td>

</tr>

</table>

<table id="AutoNumber1" style="BORDER-COLLAPSE: collapse" borderColor="#111111" height="40" cellSpacing="0" cellPadding="0" width="933" border="1">

<tr>

<td width="50%" bgColor="#000080" height="15">

Version</td>

<td width="17%" bgColor="#000080" height="15">

Edition</td>

<td width="18%" bgColor="#000080" height="15">

Service Pack</td>

<td width="93%" bgColor="#000080" height="15">

Collation</td>

<td width="93%" bgColor="#000080" height="15">

LicenseType</td>

<td width="30%" bgColor="#000080" height="15">

SingleUser</td>

<td width="93%" bgColor="#000080" height="15">

Clustered</td>

</tr>

<tr>

<td width="50%" height="27">'+@version +'</td>

<td width="17%" height="27">'+@edition+'</td>

<td width="18%" height="27">'+@SP+'</td>

<td width="17%" height="27">'+@ServerCollation+'</td>

<td width="25%" height="27">'+@LicenseType+'</td>

<td width="25%" height="27">'+@SingleUser+'</td>

<td width="93%" height="27">'+@isclustered+'</td>

</tr>

</table>

 '

SELECT

@TableHTML = @TableHTML +

'</table> 

Error Log<table id="AutoNumber1" style="BORDER-COLLAPSE: collapse" borderColor="#111111" height="40" cellSpacing="0" cellPadding="0" width="933" border="1">

<tr>

<td width="15%" bgColor="#000080" height="15">

Error Date</td>

<td width="18%" bgColor="#000080" height="15">

Process Info</td>

<td width="50%" bgColor="#000080" height="15">

Error Message</td>

</tr>'

SELECT

@TableHTML = @TableHTML +

'<tr><td width="15%" height="15">'+CONVERT(varchar(25),LogDate)+'</td>'+

'<td width="18%" height="27">'+ProcessInfo+'</td>'+

'<td width="50%" height="27">'+Text+'</td></tr>'

from ##Errors

WHERE Text LIKE '%Error:%' AND

Text NOT LIKE '%15457%' AND Text NOT LIKE '%1073759%'

and logDate>GetDate()-1

DECLARE @From varchar(50)

DECLARE @To varchar(50)

DECLARE @cc varchar(50)

DECLARE @Subject as varchar(200)

DECLARE @vcBody varchar(8000)

DECLARE @CurrDate datetime

DECLARE @MailServerName VARCHAR(100)

DECLARE @BodyType varchar(100)

DECLARE @iMsg int

DECLARE @hr int

DECLARE @source varchar(255)

DECLARE @description varchar(500)

DECLARE @output varchar(1000)

SET @From = 'Bud@weiser.com'

SET @To = 'Billy@goat.com'

--SET @cc = 'cc@yahoo.com'

SET @MailServerName = 'vrtintex02'

SET @Subject = @strSubject

SET @BodyType ='HTMLBODY'

SET @vcBody = @TableHTML

EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT

EXEC @hr = sp_OASetProperty @iMsg,'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'

-- Replace MailServerName by the name or IP of your SMTP Server.

EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @MailServerName

EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

EXEC @hr = sp_OASetProperty @iMsg, 'To', @To

EXEC @hr = sp_OASetProperty @iMsg, 'From', @From

EXEC @hr = sp_OASetProperty @iMsg, 'CC', @cc

EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.

EXEC @hr = sp_OASetProperty @iMsg, @BodyType , @vcBody

EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

--PRINT @hr

-- Sample error handling.

IF @hr <>0

BEGIN

select @hr

EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT

IF @hr = 0

BEGIN

SELECT @output = ' Source: ' + @source

PRINT @output

SELECT @output = ' Description: ' + @description

PRINT @output

END

ELSE

BEGIN

PRINT ' sp_OAGetErrorInfo failed.'

RETURN

END

-- Do some error handling after each step if you need to.

-- Clean up the objects created.

EXEC @hr = sp_OADestroy @iMsg

END

Drop table ##Errors

Drop table ##Errors1

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.