SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Email Automation


Email Automation

Author
Message
arunmoulic
arunmoulic
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 2
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 .
Elliott Whitlow
Elliott Whitlow
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24320 Visits: 5314
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
Dan.Humphries
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2557 Visits: 1212
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 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 = 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 =
'<font face="Verdana" size="4">Server Info</font>
<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"><b>
<font face="Verdana" size="2" color="#FFFFFF">Server Name</font></b></td>
</tr>
<tr>
<td width="39%" height="27"><font face="Verdana" size="2">' + @OriServer +'</font></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"><b>
<font face="Verdana" color="#ffffff" size="1">Version</font></b></td>
<td width="17%" bgColor="#000080" height="15"><b>
<font face="Verdana" color="#ffffff" size="1">Edition</font></b></td>
<td width="18%" bgColor="#000080" height="15"><b>
<font face="Verdana" color="#ffffff" size="1">Service Pack</font></b></td>
<td width="93%" bgColor="#000080" height="15"><b>
<font face="Verdana" color="#ffffff" size="1">Collation</font></b></td>
<td width="93%" bgColor="#000080" height="15"><b>
<font face="Verdana" color="#ffffff" size="1">LicenseType</font></b></td>
<td width="30%" bgColor="#000080" height="15"><b>
<font face="Verdana" color="#ffffff" size="1">SingleUser</font></b></td>
<td width="93%" bgColor="#000080" height="15"><b>
<font face="Verdana" color="#ffffff" size="1">Clustered</font></b></td>
</tr>
<tr>
<td width="50%" height="27"><font face="Verdana" size="1">'+@version +'</font></td>
<td width="17%" height="27"><font face="Verdana" size="1">'+@edition+'</font></td>
<td width="18%" height="27"><font face="Verdana" size="1">'+@SP+'</font></td>
<td width="17%" height="27"><font face="Verdana" size="1">'+@ServerCollation+'</font></td>
<td width="25%" height="27"><font face="Verdana" size="1">'+@LicenseType+'</font></td>
<td width="25%" height="27"><font face="Verdana" size="1">'+@SingleUser+'</font></td>
<td width="93%" height="27"><font face="Verdana" size="1">'+@isclustered+'</font></td>
</tr>
</table>
<p style="margin-top: 0; margin-bottom: 0"> </p>'

SELECT
@TableHTML = @TableHTML +
'</table><p style="margin-top: 1; margin-bottom: 0"> </p>
<font face="Verdana" size="4">Error Log</font><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"><b>
<font face="Verdana" color="#ffffff" size="1">Error Date</font></b></td>
<td width="18%" bgColor="#000080" height="15"><b>
<font face="Verdana" color="#ffffff" size="1">Process Info</font></b></td>
<td width="50%" bgColor="#000080" height="15"><b>
<font face="Verdana" color="#ffffff" size="1">Error Message</font></b></td>
</tr>'

SELECT
@TableHTML = @TableHTML +

'<tr><td width="15%" height="15"><font face="Verdana" size="1">'+CONVERT(varchar(25),LogDate)+'</font></td>'+
'<td width="18%" height="27"><font face="Verdana" size="1">'+ProcessInfo+'</font></td>'+
'<td width="50%" height="27"><font face="Verdana" size="1">'+Text+'</font></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
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search