Overview | Contents

SQL Server 2005 Mail Database Mail

Database Mail in SQL Server 2005

IMP Please Follow this - Download: Step by Step Screenshots

Help LInk: http://www.db-staff.com/index.php/microsoft-sql-server/90-configure-database-mail

In SQL Server 2005, the mail feature is called Database Mail and it supports and uses SMTP email. How to configure Database Mail and send email from SQL Server is explains as fallows.

How to set up SQL Server 2000's SQL Mail to use SMTP/POP

First, here's a link to a Microsoft Support article that provides some good background information: http://support.microsoft.com/default.aspx?scid=kb;en-us;897349

Before you configure SQL Mail, you'll need to locate a copy of Outlook 2000 (not Outlook Express) and install it on your SQL Server. Newer versions of Outlook won't work as they use a different method of processing messages that is not compatible with SQL Server.

Install Outlook using the domain account under which the SQL Server and SQL Server Agent services will run and specify the "Corporate or Workgroup" installation. This is important as Workgroup mode includes additional processing functionality that is necessary for sending messages from SQL Mail.

Verify that there are copies of the MAPI32.dll in both the C:\windows\system32 directory and the c:\program files\common files\system\MSMAPI\NT directory. The timestamp on the files should be 1/26/1999 or older.

Once Outlook 2000 has been installed, download and install service pack 3 for Outlook 2000.

Now, configure SQL Mail. There are three locations where SQL Mail needs to be configured. In Enterprise Manager they are as follows:

1. Under the SQL Server Properties Server Settings tab. To view SQL Server properties, right-click on the name of the server, then select "View Properties". Click on the "Server Settings" tab and click on the "Change" button in the SQL Mail group box. Select the default profile (the one you just created) then click on the "Test" button to verify that SQL Mail can communicate with Outlook.

2. Under the SQL Server Agent Properties General tab. To view SQL Server Agent properties, expand the server folder, expand the management folder then right-click on the SQL Server Agent icon and select "View Properties". The "General" tab will be displayed by default. Select the Outlook profile from the drop-down list and click the "Test" button to verify that SQL Server Agent can communicate with Outlook. Warning: The SQL Server Agent service may need to restart.

3. Under the SQL Mail Configuration tab. To view SQL Mail properties, right-click on "SQL Mail" then select "Properties". Select the Outlook profile from the drop-down list and click the "Test" button to verify that SQL Mail can communicate with Outlook.

Use the xp_SendMail extended stored procedure in the master database to send messages, and use xp_startmail and xp_stopmail to start and stop SQL Mail. Note that xp_sendmail automatically starts SQL Mail if it hasn't already been started.

Database Mail has four components

1. Configure the OutLook.

2. Configuration Component
   Configuration component has two sub components. One is the Database Mail account, which contains information such as the SMTP server login, Email account, Login and password for SMTP mail.

   The Second sub component is Database Mail Profile. Mail profile can be Public, meaning members of DatabaseMailUserRoleinMSDBdatabasecan send email. For private profile, a set of users should be defined.

3. Messaging Component
   Messaging component is basically all of the objects related to sending email stored in the MSDB database.

4. Database Mail Executable
   Database Mail uses the DatabaseMail90.exe executable to send email. (C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn)

5. Logging and Auditing component
   Database Mail stores the log information on MSDB database and it can be queried using sysmail_event_log.

 

Understanding the SQL Mail Queries

Step 1 :
Before setting up the Database Mail profile and accounts, we have to enable the Database Mail feature on the server. The fallowing Transact SQL will enable the Database Mail.

use master
go
sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'Database Mail XPs',1
--go
--sp_configure 'SQL Mail XPs',0
go
reconfigure
go

Step 2 :
The Configuration Component Database account can be enabled by using the sysmail_add_account procedure. In this example, we are going create the account, "MyMailAccount," using mail.versantinc.net as the mail server and vishnums@versantinc.com as the e-mail account.

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'MyMailAccount',
@description = 'Mail account for Database Mail',
@email_address = ‘vishnums@versantinc.com’,
@display_name = 'MyAccount',
@username=’ vishnums@versantinc.com’,
@password='abc123',
@mailserver_name = 'mail.versantinc.net'

Step 3 :
The second sub component of the configuration requires us to create a Mail profile.
In this example, we are going to create "MyMailProfile" using the sysmail_add_profile procedure to create a Database Mail profile.

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'MyMailProfile',
@description = 'Profile used for database mail'

Step 4 :
The sysmail_add_profileaccount procedure is to add the Database Mail account we created in step 2, to the Database Mail profile you created in step 3.

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'MyMailProfile',
@account_name = 'MyMailAccount',
@sequence_number = 1

Step 5 :
Use the sysmail_add_principalprofile procedure to grant the Database Mail profile access to the msdb public database role and to make the profile the default Database Mail profile.

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'MyMailProfile',
@principal_name = 'public',
@is_default = 1

Step 6 : Check Points

Step 7 :
To send a test email from SQL Server.

declare @body1 varchar(100)
set @body1 = 'Server :'+@@servername+ ' My First Database Email '
EXEC msdb.dbo.sp_send_dbmail @recipients='mak_999@yahoo.com',
@copy_recipients='Teat1@yahoo.com;Teat2@yahoo.com',
@subject = 'My Mail Test',
@body = @body1,
@body_format = 'HTML'

Send a SQL Mail In Single Click
All the above T-SQL Queries are integrated in the fallowing Query, copy the Query and past in the SQL Query Analyzer and give the appropriate information at the commented areas and execute.

use master
go

sp_configure 'show advanced options',1

go

reconfigure with override

go

sp_configure 'Database Mail XPs',1

--go

--sp_configure 'SQL Mail XPs',0

go

reconfigure

go

 

 

EXECUTE msdb.dbo.sysmail_add_account_sp

@account_name = 'MyMailAccount',

@description = 'Mail account for Database Mail',

@email_address = 'vishnums@versantinc.com',-- User Mail Address

@display_name = 'MyAccount',

@username='vishnums@versantinc.com',-- User Mail Address

@password='********',-- User Mail Address Password

@mailserver_name = 'mail.versantinc.com'--OutGoing SMTP Mail Address

go

EXECUTE msdb.dbo.sysmail_add_profile_sp

@profile_name = 'MyMailProfile',

@description = 'Profile used for database mail'

go

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

@profile_name = 'MyMailProfile',

@account_name = 'MyMailAccount',

@sequence_number = 1

go

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

@profile_name = 'MyMailProfile',

@principal_name = 'public',

@is_default = 1

go

DECLARE @body1 VARCHAR(100)

SET @body1 = 'Server :'+@@servername+ ' My First Database Email '

EXEC msdb.dbo.sp_send_dbmail @recipients='vishnums@versantinc.com',

@copy_recipients='Teat1@yahoo.com;Teat2@yahoo.com',

@subject = 'My Mail Test',

@body = @body1,

@body_format = 'HTML'

Fig 1.1

Moreover, in a few moments you will receive the email message shown in Fig 1.2.

Fig 1.2

• Please make sure port 25 is not blocked by a firewall or anti virus software etc.
• Windows login user ID should have Admin Permissions to send the SQL Mail.

Fig 1.3

Fig 1.4

Fig 1.5