Skip to Content
Author's profile photo Former Member

Configuring Auto-Notifications for SAP jobs scheduled though SQL server agent in SQL Server 2005

For configuring email alerts for Backup Jobs first we will need to configure either SQL Mail or Database Mail in SQL Server 2005.

Database mail is newly introduced concept in SQL Server 2005 and it is replacement of SQLMail of SQL Server earlier version. Database Mail has many enhancement over SQLMail. Database Mail is based on SMTP (Simple Mail Transfer Protocol) and also very fast and reliable where as SQLMail is based on MAPI (Messaging Application Programming Interface). Additionally, for MAPI to be enabled for SQLMail it will require Outlook to be installed. All this leads to potential security threat to database server.So we will be using Database mail

There are two ways that we can setup Database Mail, either by using the stored procedures that are included with SQL Server 2005 or by using SQL Server Management Studio. I have configured the Database Mail by using the GUI

Log on to SQL server Management studio and expand the Management node of the tree


Right click in Database mail and then select configure Database mail


Click on Next to continue


By default the option is set for “Set up Database mail by performing the following tasks”.We will use this

We will be setting up a profile and account for database alerts

Click on Next to continue


Here you will be asked to enable Database mail feature if it is not already enabled.

You have two options here: either click on yes to continue or there is another option through a SQL statement.

I clicked on yes to continue. This will enable the database mail feature.


Give the name of the profile and the description for that.

Click on Add to add an account for sending mails


Give the account name and description.

Under Outgoing Mail server, you have to give

E-mail address- the email id from which the mails will come

Display name – the mail will come in your mailbox with this name

Reply e-mail – we will not be using this

Server Name – It is the mail server which will be used to send mails, by default the port will be 25

Under SMTP Authentication I have used Basic authentication, the password is for the user sidadm


After completing this click on Ok and proceed

The following screen will appear.This shows that the Account has been added to the profile.


Click on Next to continue.

Here we can configure the following system parameters of Database Mail system for sending mails.

I have used the default ones.


Click On Next to continue

The following screen will appear.Click on Finish to perform the action mentioned here.


When you click “‘Finish” the next screen will appear that shows you the status of installing Database Mail. When this has finished just click “Close” to close this screen.Make sure everything is in green.


Now we will be configuring the operator to whom the mail is send when a job fails.Expand the tree SQL server agent; right click on operators and then New Operator



In this give the name of the operator.

I have used IBM Basis(the operator for our purpose).In email-name give the email id of the operator who you want to be notified; I have given the email id of our group.Click on Ok to complete the task.


Thus in case of any job failure a mail notification will be send out to the operator at the mail id specified above

Now we have to configure SQL server agent to send the auto notifications mail.Right click on SQL server agent and then go to properties


Go to Alert System


 Check on Enable mail profile and then click on Ok


Now we have configured the Database mail to send us auto notifications. 

Now we have to configure the background job to send auto alerts.

Go to Notifications Tab.Right click on the job and then properties

Check on E-Mail box and select the operator to whom we should send mails.

In our case it is IBM Basis. Also choose this for notification form when the job fails.We can also configure this when the job completes successfully


The notifications has been configured successfully in case of job failures

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Lars Breddemann
      Lars Breddemann
      ... why not just use the alert functionality of CCMS for that?


      Author's profile photo Former Member
      Former Member
      Hello Lars,

      well CCMS can be configured for jobs that are scheduled through SM37 I suppose but what about the jobs that are scheduled through SQL server.
      Also suppose our SAP is down but database is still up,then in that case jobs scheduled through SQL server will still run and then in that case CCMS wont work but still we will get auto-notifications from Database if job failures...
      how about it,we are using this in our setup and its working fine without any extra load on database server

      Author's profile photo Former Member
      Former Member
      Hi Lars how are you?

      I was looking this explanation and I look your question and the funny of this is that I want to configure that alert functionality of CCMS... can you explain how I can setup that?
      I´m newest in CCMS monitoring, so I ask you to explain me from the beginning when you´ll try to explain this functionality, OK?

      Thanks in advance
      Best regards,
      Joao Dimas

      Author's profile photo Former Member
      Former Member

      Thanks for the valuable information