Email Automation

Question

Post reply

Email Automation

arunmoulic

Grasshopper

Points: 23
More actions
May 13, 2010 at 5:43 am

#140272

I am trying to automate Email form SQL Server .I am a newbie to DB activities .Actually i am using a Testing Tool connected to SQL Server .
I want to mail the results to the management at frequent intervals ;is there any way through which it can be automated .
Else in simple words i just want to send an automated email at scheduled hours .
Thanks in Advance .

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

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

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 1

You can use database mail to send emails from the database.

You can use SQL Agent to schedule jobs.

However, the ease of making all this possible is dependent upon what you want to send and how easy it is to get that information.

CEWII

Dan.Humphries SSChampion Points: 12125 More actions · Answer 2

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.