GD - TPB LUD: 2022-09-06 CS: #

SQLJob-VBS-SendMail Create Calling cmd file - ACS_email.cmd Create VB Script ACS_email.vbs  to get the parameter and execute Create SQL Agent Job - schedule to trigger

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." '