Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

E-Mail notification after a scheduled task completes Expand / Collapse
Author
Message
Posted Tuesday, September 4, 2012 8:27 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 6, 2014 11:55 AM
Points: 77, Visits: 169
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.
Post #1353932
Posted Tuesday, September 4, 2012 4:52 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 17, 2013 3:27 PM
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.
Post #1354248
Posted Tuesday, September 4, 2012 5:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 15, 2014 2:26 PM
Points: 5,466, Visits: 7,647
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
Post #1354257
Posted Wednesday, September 5, 2012 3:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 6:36 AM
Points: 102, Visits: 1,076
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
Post #1354421
Posted Wednesday, September 5, 2012 5:07 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, August 22, 2013 9:13 AM
Points: 496, Visits: 601
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??!

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.
Post #1354435
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse