August 4, 2008 at 9:33 pm
Gurus
When we create a job in Sql Server 2005 we have a option in properties table,where we can setup a alert by email ,whenever a job fails.
Is this email enabled by using sql mail?Do we need to have that configured?
My requirement is that i should receive a mail wheneve my job fails
Thanks
Nitin
August 4, 2008 at 9:42 pm
First, you need to enable Database Mail (not SQL Mail, which requires a MAPI client be installed on the server - e.g. Outlook). Once you have configured Database Mail, you need to setup SQL Server Agent to use Database Mail (right-click on SQL Server Agent, Properties - select option for alert system).
And finally, once all that is done - create an Operator and you can then setup the job to notify that operator when it fails. Or, you can modify your code to use sp_send_dbmail to send a mail notification.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 4, 2008 at 9:49 pm
Thanks a lot
August 7, 2008 at 12:48 am
SQL Mail - Requires MAPI client like outlook installed on the sql box.
Database Mail - Does not require MAPI client like outlook installed on the sql box.
On SQL 2005, you need to enable Database Mail or SQL Mail via 'Surface Area configurations for features'. Both of them are not enabled by default after the initial sql install.
After you enable Database Mail via 'Surface Area configurations for features', you will now need to configure it.
How to Configure Database Mail
1. Assign the account [The account under which the mail would be sent] to the DatabaseMailUsers role in the msdb database.
2. Using SSMS, choose Configure Database Mail which would be in Management --> Database Mail.
3. Select Setup Database Mail. It is wizard based configuration and hence is easy for configuration.
Once a new Database Mail profile is setup, you need to configure SQL Agent to use the new database mail profile.
Configure SQL Agent to use new mail profile
1. In SSMS --> Under SQL Server Agent create operator
2. In SSMS --> Choose properties on SQL Server Agent
3. In Alert System --> Enable Mail Profile
4. Make sure Mail system = Database Mail, and Mail Profile = 'Profile created in the previous steps'
5. Ensure fail safe operator is enabled, check the operator created above is listed and then notify using email.
6. Restart SQL Agent service
August 7, 2008 at 12:52 am
Thanks Again
August 7, 2008 at 2:27 am
Cant i use the send mail task in SSIS?
August 7, 2008 at 3:13 am
you can use it.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply