September 4, 2012 at 8:27 am
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.
September 4, 2012 at 4:52 pm
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.
September 4, 2012 at 5:09 pm
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 5, 2012 at 3:58 am
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
September 5, 2012 at 5:07 am
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:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply