SQLJob-VBS-SendMail
Create Calling cmd file - ACS_email.cmd
REM Subject and Body text - 2 parameters we can send
cscript F:\SendMaill\ACS_email.vbs %1 %2
Create VB Script ACS_email.vbs to get the parameter and execute
' VBScript source code
' This script is written for firing an email
if WScript.Arguments.count <> 2 Then
WScript.Echo "The parameter you provided is not in the right format. Make sure the"
WScript.Echo "following parameters are there"
WScript.Echo ""
WScript.Echo "eMail subject"
wscript.Echo "eMail TextBody"
WScript.Quit 1
End if
Set objMessage = CreateObject("CDO.Message")
objMessage.From="mcwebdbprod@us.ibm.com"
objMessage.To="abc@domain.com,0123456789@txt.att.net"
objMessage.Subject = WScript.arguments(0)
objMessage.TextBody = WScript.arguments(1)
'==Start remote SMTP server configuration section==
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
'Name or IP of Remote SMTP Server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "10.238.3.177"
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objMessage.Configuration.Fields.Update
'==End remote SMTP server configuration section==
objMessage.Send
Create SQL Agent Job - schedule to trigger
Here use whether the job is failed then send mail - the sample is given.
DECLARE @status BIT
SELECT @status = status
FROM PHASE2..export_log
WHERE Job_datetime = ( SELECT max(Job_datetime)
FROM export_PMF_log)
IF @status = 1
EXEC master..xp_cmdshell 'f:\Mail\ACS_email.cmd "xyzserver - JOB Export JobName is not completed." " Please check f:\log\JobName.log." '
