Skip to content

Posts from the ‘Database Mail’ Category

Aug 24

1 Comment

Sending email from SQL Server agent Job using operators

In this article, we will talk about sending email from SQL Server agent jobs by making use of operator and database mail feature. Before we can continue, make sure database mail is already configured. If database mail is not configured, please configure this feature by visiting here.

Below are the steps to be followed to send email from SQL Server agent Jobs: -

1) Connect to the database instance from SQL Server Management Studio and navigate to SQL Server Agent à Operator. Right click on Operator and click on ‘New Operator’

2) In this new window, provide the new operator name that you want to create. Also, provide the email address, which should appear as sender name, when email is sent to recipients. Avoid using net send and pager, as these features will be removed from future release.

3) Here in the Notification tab, you can select the appropriate options if you want to get notified for any alerts. Else click ok to save this new operator.

4) Now navigate to Jobs and go to the properties of SQL Server agent job for which you want to get notified via email.

5) Here go to the Notification tab and then select the check box against e-mail. Then select the operator we have created in previous step. You can also opt for sending this email when job completes or it runs successfully or it fails. Chose the appropriate option as per your requirement and click OK.

6) Just to verify, you can go back to Operators and select the properties of the Operator that we have used in above job.

Here go to notification tab and you will see that Jobs radio button is now enabled. When you select this radio button, you can see all the jobs, which are using this operator.

Also, you can go to the history tab of this operator to check when was the last time, email was sent by this operator.

7) If you have multiple database mails configured on this server and want to make sure that the operators to send email for SQL Server agent jobs use correct profile. Then navigate to SQL Server agent and go to the properties of SQL Server agent.

Here go to the Alert System tab and make sure that Enable mail profile option is enabled. Against mail system, select Database mail and against Mail profile, select the database mail profile that is to be used. Then click OK. SQL Server agent is to be restarted in order for above changes to take effect.

Now making use of the database mail profile selected above will send all emails from SQL Server agent operator.

Aug 21

4 Comments

How to Configure Database Mail in SQL Server 2008 R2?

In this article we will talk about configuration of Database mail feature in SQL Server 2008 R2. This feature was first introduced in SQL Server 2005. So, same steps will work for SQL Server 2005 and 2008 also.

Before we start the configuration make sure we have SMTP server details, which we can use to send emails form SQL Server. Also, we have enabled the database mail feature. In order to do that either we can run the below script: -

sp_configure ‘show advanced options’, 1;

GO

RECONFIGURE;

GO

sp_configure ‘Database Mail XPs’, 1;

GO

RECONFIGURE

GO

sp_configure ‘show advanced options’, 0;

GO

RECONFIGURE;

GO

Or, just start the configuration steps below and at the very first point, configuration wizard will show a window stating Database mail feature is not enabled, click yes to enable the feature.

NOTE: – We have already enabled the database mail feature before we started the below configuration. So, we didn’t get the window stating Database mail feature is not enabled.

STEPS

1) Connect to SQL Server instance from SSMS and navigate to Management a Database Mail. Right click on Database Mail and click on Configure Database Mail option.

2) Here select the first option “ Setup database mail…..” and move to next step.

3) Type the desired profile name and profile description. Then click on Add button to add the SMTP accounts to this profile.

4) Since this is the first profile we are creating, there will not be any Account name available. Here click on New Account to create a new account.

5) Provide the below details: -

Account Name –> Desires account name for this SMTP account

Description –> Description of the account

E-mail address –> Email account from which emails will be sent. When recipient receives email, the sender email is shown as this address.

Display Name –> Name associated with the email address

Reply e-mail –> where reply to the emails will be forwarded.

Server name –> Provide the SMTP, which is to be used to send emails from SQL Server.

Port Number –> Port number to be used by this account. Default is 25.

SMTP Authentication –> Select the desired authentication to be used and then click OK. Then click Next on new window.

6) This screen will show the public profiles available. We can see Test profile which we created above.

7) This screen will show the private profiles available. We can see Test profile which we created above. You can select the account that you want to associate to private profile.

8) Make necessary changes to the Database mail system parameters and move to next step.

9) Database Mail Configuration Wizard will show the summary of the actions to be performed. Click finish and database mail is configured.

10) You can test the newly configured database mail feature by right clicking on Database Mail and click on Test email. Then select the recipient email and send the test email.