Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


E-Mail notification after a scheduled task completes


E-Mail notification after a scheduled task completes

Author
Message
kaplan71
kaplan71
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 324
Hello --

I have created a script within a SQLExpress 2008 installation that will do full backups, with overwrites, of three databases to a local as well as network drive. I am currently testing it via the Task Scheduler of the host system, Windows 7 64-bit, with it scheduled to run at night.

I want to set up e-mail notifications so I will know if the job failed or was successful. Looking through the Task Scheduler log, I know from testing that a successful completion
resulted in a 102 code while that of a failure is a 202 code. However, there are other scheduled jobs that run on the system, and they also have similar success and failure codes.

How can I configure the script or Task Scheduler to accurately notify me in either case?

Thanks.
SQLmountain
SQLmountain
SSC-Addicted
SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)

Group: General Forum Members
Points: 481 Visits: 782
Task scheduler can be a real problem. Do you have the Standard/Enterprise edition available? The scheduling and email notification features are so valuable for keeping tabs on the status of backups and other maintenance jobs.
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5697 Visits: 7660
Add another step to task scheduler that will call db_sendmail from the server and shoot you an email. You won't see failures (which is when it's important to get that email) but you can send yourself success emails that way. Either that or integrate it directly into the scripts as the last task as mentioned above.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
KumarVelayutham
KumarVelayutham
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 1281
You can add one more step in that job and add the following script in the command textbox.Before doing this,you should configure database mail profile('DB_GROUPMAIL') in SQL.

EXEC sp_send_dbmail @Profile_name='DB_GROUPMAIL',
@recipients='kumar.velayuthum@gmail.com',
@Copy_recipients='Phaneesh.Balachandra@xys.com',
@Subject='Backup Job Status',
@Body=' Daily Backup Plan for All Production Databases completed successfully!!!!!!!!'

Regards,
Kumar
derek.colley
derek.colley
Mr or Mrs. 500
Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)

Group: General Forum Members
Points: 520 Visits: 603
If I had the limitations of Express, then personally I would use the CDO SMTP methods of VBScript, called using CScript (Windows Scripting Host), based on the successful / failed outcome of a script. The batch file envelope can be called using EXEC xp_cmdshell('c:\go.bat').

In English...

1. Create a batch file to launch your VBScript script.

go.bat

@echo off
cls
cscript /nologo myscript.vbs


2. Create your script file.

myscript.vbs

' Declare the object
Set Msg = CreateObject("CDO.Message")

' Set SMTP configuration here. You only need to point to your SMTP server and port number.
Msg.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
Msg.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.foo.com"
Msg.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
Msg.Configuration.Fields.Update

' Set e-mail details here.
Msg.Subject = "A problem has occurred with your script."
Msg.From = "mailbox@foo.com"
Msg.To = "you@foo.com"
Msg.TextBody = "Script has failed. Please check error log."

' Send the e-mail.
Msg.Send


3. Amend your SP to use the scripts.


ALTER PROCEDURE myProc (@name VARCHAR(100) )
AS BEGIN
SELECT [database_id] FROM sys.databases WHERE [name] = @name -- example query
END



becomes...


ALTER PROCEDURE myProc (@name VARCHAR(100) )
AS BEGIN
BEGIN TRY
SELECT [database_id] FROM sys.databases WHERE [name] = @name -- example query
END TRY
BEGIN CATCH
PRINT 'Something went wrong!'
EXEC xp_cmdshell 'c:\go.bat'
END CATCH



You can amend the VBScript / batch file scripts to take parameters if you like, e.g. define an error number / message and call go.bat as 'go.bat ' + @errorNumber, then amend go.bat to call the VBScript file with the %1 parameter passed in, so the VBScript script can put it in the subject of the e-mail, perhaps.

I've used this method before and yes, it's very 90s, but works absolutely fine.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!Crazy

Want to get the best help? Click here http://www.sqlservercentral.com/articles/Best+Practices/61537/ (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

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