Configuration of Database Mail

Has anyone been able to configure Database Mail and successfully send an email message? I set it up this afternoon, but all my messages are sitting in the sys_mailitems table of the msdb database.

We use Novell GroupWise as our email client, but we have not installed GroupWise on this server. Can I configure the IIS SMTP server to send the email messages?

These are the steps I have taken thus far:

ALTER DATABASE AdventureWorks SET ENABLE_BROKER;

GO

EXECUTE msdb.dbo.sysmail_start_sp;

GO

EXEC msdb.dbo.sp_send_dbmail
@.profile_name = 'CLG_SQLMail',
@.recipients = 'username@.domain.com',
@.body = 'This is only a test. Had this been a real system problem you would already be pulling your hair out. ',
@.subject = 'SQL Server 2005 - Database Mail System Test'

GO

CREATE ENDPOINT SQLMail_Endpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 25 )
FOR SERVICE_BROKER (
AUTHENTICATION = WINDOWS NEGOTIATE,
ENCRYPTION = REQUIRED
)

GO

Thanks in advance,
Ken

After a few weeks of frustrating trial and error, I was able to successfully configure Database Mail. Here are the steps I followed:

Prerequisites:
1) Make sure that IIS is installed
2) Make sure that the SMTP service has been installed. (This service runs within IIS.)
3) Create a valid email account for Database Mail to use

Configuration Steps:
1) Go to the Object Browser and expand the "Management" tree
2) Right-click on "Database Mail" and select "Configure Database Mail" to start the "Database Mail Configuration Wizard"
3) Navigate through the screens of the wizard, entering the appropriate information for your environment. In our case, I use an SMTP relay server as the server for the Database Mail Account.
4) When you are finished with the wizard, go to the Management Studio and connect to the instance.
5) Open a query window and connect to the master database
6) Run the following script:

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE
GO
EXEC sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO

7) Run the following script:

EXEC msdb..sysmail_start_sp;

8) Now go to the "SQL Server Configuration Manager"
9) Stop and restart both the SQL Server service and the SQL Server Agent service.

When I first tried to set up Database Mail, I did not restart these services. That was the reason my mail was stuck in the queue. The Books Online article on "sp_configure" needs to specify which of the options requires a restart of the services!

10) Go back to the Management Studio and create a test message using the sp_send_dbmail stored procedure.
11) The message should appear in your Inbox within a minute or two

NOTE: In our environment, we have an alternate email domain. I could only send messages using the primary domain, which I assume is due to the configuration of the SMTP relay server.|||One follow up in regard to restarting services. The following is the order in which you must execute the stored procedures and restart the services whenever you make a change to the Database Mail configuration:

1) Execute the following script in the query window:

EXECUTE msdb..sysmail_stop_sp;

GO

2) When the script completes, stop the following services, in the order listed below:
a. SQL Server Agent
b. SQL Server
c. SMTP Service

3) Restart the following services in the order listed below:
a. SMTP Service
b. SQL Server
c. SQL Server Agent

4) Execute the following script in the query window:

EXECUTE msdb..sysmail_start_sp;

GO

5) Send your test message again to confirm that everything is working correctly

|||Hi,

I have followed all these steps. I still can't send mail. The error message from the table: sysmail_log is:
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2005-10-14T10:48:48). Exception Message: Cannot send mails to mail server. (The message could not be sent to the SMTP server. The transport error code was 0x80040217. The server response was not available ). )

My profile is set to use my Comcast account (cable modem).
Any ideas? Are there any public smtp servers I could use to test database mail?

Regards,
Asim.
|||

Hi Ken,

I was having this same issue.

I resolved my issue by putting in the correct groupwise mail server name and pointing it to use port 25 (default). Be sure that you are using the right mail host server name in your account profile.

I did not have to jump through any extra hoops, it worked right after I made change.

Let me know if that helps.

Thanks,

*T*