Enabling Database Mail on SQL Server Express

<< Sorting data with row size greater than 8094 works in SQL Server 2005 | Home | Ways to revert a database to pre-Test state after Unit testing >>

Enabling Database Mail on SQL Server Express

Database mail is a completly rewritten mailing system in SQL Server 2005 built on top of the service broker.

This means that it runs asynchrounously. The mails are put in a queue and are sent from there.

However it's not present in SQL Server Express. I wonder why not because Express supports Service Broker just fine.

Well i've found a great blog post that explains how to "enable" Database Mail in SQL Server Express.

But it's in German so i'll explain in English how to get it to work :)

SQL Express holds all necessary stored procedures, service broker queues, etc... for proper mail handling.

However when you try to send an email the processing of queued mails fails with because an external process

called "DatabaseMail90.exe" couldn't be started.

This means that the file is simply missing.

By copying DatabaseMail90.exe, DatabaseMailEngine.dll and DatabaseMailProtocols.dll into the MSSQL\Binn directory

and executing system stored procedures in MSDB database called

dbo.sysmail_start_sp
dbo.sysmail_stop_sp

the queued mails are processed immediatly.

Now the only problem with this is that you have to get those 3 files from either standard or enterprise edition of SQL Server

but that is anoter matter :)

Print | posted on Sunday, July 01, 2007 9:47 PM

Feedback

# re: Enabling Database Mail on SQL Server Express

Actually SQL server 2005 express edition don't support this feature so no adding of these files will solve this problem.
So just forgot to send the mail if you are using express edition.

I also read about microsoft guys removing this mail feature from future SQL releases.
I don't know why they are doing this.

But anyway happy programming...

Dhirendra Singh Jadon
dhirendra.singh@genus.in

7/23/2007 12:25 PM | Dhirendra Singh Jadon

# Did anyone try this? does it work?

I don't see any error when send email, but the email sit in queue and never send out

9/21/2007 9:58 PM | david

# re: Enabling Database Mail on SQL Server Express

Hi Guys,

As for Microsoft, They did not remove the "Database mail" feature, Actually they gonna remove the "SQL mail" feature.
so, They recommended using the Database mail NOT SQL Mail.

about the SQL express mail, I'll try something if it work, i'll be glad to share it with you

Rania

3/11/2008 6:14 PM | Rania Magdy

# re: Enabling Database Mail on SQL Server Express

hi, any news about this?

6/20/2008 10:43 AM | re

# re: Enabling Database Mail on SQL Server Express

This is not enough to get Database mail running. If you start the service you will likely get:

HResult 0x3BB1, Level 16, State 1
SQL Server blocked access to procedure 'dbo.sysmail_start_sp' of component 'Data
base Mail XPs' because this component is turned off as part of the security conf
iguration for this server. A system administrator can enable the use of 'Databas
e Mail XPs' by using sp_configure. For more information about enabling 'Database
Mail XPs', see "Surface Area Configuration" in SQL Server Books Online.

To fix the run the following script:
USE Master
GO
sp_configure 'show advanced options', 1
GO
reconfigure with override
GO
sp_configure 'Database Mail XPs', 1
GO
reconfigure
GO
sp_configure 'show advanced options', 0
GO

This should allow you to run Database mail on SQL Server 2005 Express (assuming you can get those assemblies from somewhere).

6/20/2008 5:02 PM | John hunter

# Error in sending mail

Hi guys,

I was trying to send a mail using Dataase mail option in sql server.I have configures the user account and profle for that database mail.I could able to send the mails to the user who are under our server, but couldn't send to others(gmail or yahoo).I am getting the following error

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 7 (2008-07-16T12:00:54). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: <kirankrapa222@gmail.com> No such user here). )

Help me out please................
Kiran

7/16/2008 8:47 AM | kiran

# re: Enabling Database Mail on SQL Server Express

By copying DatabaseMail90.exe, DatabaseMailEngine.dll and DatabaseMailProtocols.dll into the MSSQL\Binn directory

the above were copied in the Binn directory, i do not see database mail in the Surface Area Configuration. Please assist in how to enable the service in express.

thanks..Ted

11/8/2008 2:25 PM | Ted

# re: Enabling Database Mail on SQL Server Express

did anyone have an answer for this as currently i have the same issue

3/5/2009 6:16 PM | Sam Marsden

# re: Enabling Database Mail on SQL Server Express

Everything seems to be set up fine, but the emails are sitting in the queue as unsent mail. After running through troubleshooting (http://msdn.microsoft.com/en-us/library/ms187540.aspx) I found the following issue:

If the Database Mail external program is started, check the status of the mail queue with the following statement:
EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail';
The mail queue should have the state of RECEIVES_OCCURRING. The status queue may vary from moment to moment. If the mail queue state is not RECEIVES_OCCURRING, try stopping the queue using sysmail_stop_sp and then starting the queue using sysmail_start_sp.

When I run this, the status is inactive. I stop and restart the queue, but there is no change. Does anyone have an idea to why the status of the mail queue is not showing as Receives_Occurring? What can I do to troubleshoot this issue?

Any help would be much appriciated.

4/17/2009 5:08 PM | Tina Hudak

Post Comment

Title
Name
Email
Url
Comment
Remember Me?
Please add 6 and 4 and type the answer here: Enter the code shown above:

I want some Moore

My Links

SQLTeam.com Links

News

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff