Enabling Database Mail on SQL Server Express

Sun Jul 1, 2007 by Mladen Prajdić in sql-server

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 :)

Legacy Comments


Dhirendra Singh Jadon 2007-07-23	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

david 2007-09-21	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

Rania Magdy 2008-03-11	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

re 2008-06-20	re: Enabling Database Mail on SQL Server Express hi, any news about this?

John hunter 2008-06-20	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).

kiran 2008-07-16	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

Ted 2008-11-08	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

Sam Marsden 2009-03-05	re: Enabling Database Mail on SQL Server Express did anyone have an answer for this as currently i have the same issue

Tina Hudak 2009-04-17	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.

BC 2009-08-26	re: Enabling Database Mail on SQL Server Express Can express be configured to send mail? I have followed the instructions but no luck..

Tammera 2009-08-27	re: Enabling Database Mail on SQL Server Express No luck here either - I've added the three files, ran all the config scripts. I get the response back that the broker is enabled, and sysmail is started but everything just ends up being queued. Now I have 6 emails waiting in the queue. Has anyone got this to work?

Keith Fletcher 2009-09-21	re: Enabling Database Mail on SQL Server Express Works for me... Beautiful!

Keith Fletcher 2009-09-21	re: Enabling Database Mail on SQL Server Express Of course, the only trick is administering the mail accounts, as the Service doesn't show up in Management Studio...

Rozsko 2009-09-21	re: Enabling Database Mail on SQL Server Express I had a problem also but finally got this working fine for me. So thank you very much for the post. My only mistake was that I copied the above mentioned three files to a wrong MSSQL\binn folder because I have several instances installed on the same server with MSSQL.x name where x is a number. An other issue may be (I suspect this from the Windows application event log after running databasemail.exe manually) that named pipes are disabled in the protocols for that particular database instance.

Shorty 2009-10-29	re: Enabling Database Mail on SQL Server Express Any more on this... I also get mails in the que waiting to be sent... all the errors I get point to the DatabaseMail90.exe not running... it is n=in correct location and full rights for the SQL user.

paul clavering 2009-11-16	re: Enabling Database Mail on SQL Server Express we are also getting so far as to being able to send emails - but they just sit in the queue. The status I have for the server is also inactive and not receives_occurring. Is there anyway to force a change in the status perhaps as all my other config looks good! Paul

Ell 2009-12-07	re: Enabling Database Mail on SQL Server Express May i know where can i download these files? DatabaseMail90.exe, DatabaseMailEngine.dll and DatabaseMailProtocols.dll Hope this will work for me as well. Ell

Mladen 2009-12-07	re: Enabling Database Mail on SQL Server Express @Eli: you can't download them. you have to have a non express sql server install and copy them from there.

ROB 2009-12-09	re: Enabling Database Mail on SQL Server Express I've tried this, it looks like the databasemail90.exe starts, then stops after about 10 seconds. Is there a way of logging the Databasemail90.exe start up so I can see what's going on?

Rozsko 2010-03-05	re: Enabling Database Mail on SQL Server Express Earlier I already stated that this works fine (well, that was last year). This year I tried to send a test mail and unfortunately it was not working. The symptoms were the same as ROB had. Databasemail90.exe was started (the mail log also acknowledged that process activated) but after few seconds it stopped running and nothing happened. No email, no error in the log (msdb.dbo.sysmail_event_log) neither in the windows application log. I spent exactly three days on Google and read through everything I could but no relevant information at all. Then I asked myself the big question what has changed since last year??? Well, I installed SP3 on SQL Server Express because that solved some reporting services issues for me. That was the cause of the problem and let me explain why: Because I did not find any solution on the net I tried to trace the problem myself. The process should work the way that SQL server activates the external mail program (databasemail90.exe) by invoking an extended procedure called xp_sysmail_activate (xpstar90.dll in the binn folder) and it inserts a line into the database mail log saying "Activation successful" (first step). Once databasemail90.exe is started it should write back to the mail log the process id with a message saying "DatabaseMail process is started" (2nd step). Then "Mail successfully sent" (3rd step) messages should be inserted into the log for every mail item. Finally (after some time) the "DatabaseMail process is shutting down" (4th step) message should be inserted by databasemail90.exe and that's it (of corse only in case if everything is working fine). As I mentioned the first step was done but the rest not. What happens? Why the mail program can't write back to the log? Why does the mail program shuts down so fast (by default it should be running for 10 mins)??? I started databasemail90.exe manually, nothing happened. Then I thought I should monitor the system how SQL server starts databasemail90.exe so I downloaded the processexplorer from sysinternals. With that little tool I could figure out that there are some parameters passed to the exe (like server/instance, msdb, ...) so I tried to execute databasemail90.exe with exactly the same parameters. Now I got a message saying something like incorrect parameters. What??? That is how the server invokes databasemail90.exe, what's wrong??? At this point I stopped and was totally confused and could not get any further in the resolution. After a sleep (or two) the spark has come (SP3 install !!!). So I checked the file version of databasemail90.exe and xpstar90.dll and they were different. Oops, has MS replaced the dll what calls databasemail90.exe with a new version??? and the new dll sends the parameters in different order (or it send some other parameters) to databasemail90.exe??? and that is why I get the error on the parameters??? Yes, MS has replaced xpstar90.dll with a new version in SP3 and the databasemail.exe (and the 2 other dlls from the original article), but unfortunately SP3 for Express does not include the exe + the 2 dlls it only contains the new xpstar90.dll. So I had to get the 3 files from a full SP3 to see if it helps and yes IT HELPS. Hope this helps to some folks even if this was a bit long and probably not 100% precise at some points. Regards

Mahya 2010-05-05	re: Enabling Database Mail on SQL Server Express This worked for me. thank you all. 1- I copied the three mentioned file. 2- executed : dbo.sysmail_start_sp and dbo.sysmail_stop_sp 3- Configured "Database Mail XPs' using (refering the comment by "John Hunter"): 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 4- created the a profile and set it as default: [http://www.sqltipsandtricks.com/2009/09/16/sql-server-2005-database-mail-setup/] -- Create new profile EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = 'DoNotReply', @description = 'Profile to send the notification emails' GO -- Set the New Profile as the Default EXECUTE msdb.dbo.sysmail_add_principalprofile_sp @profile_name = 'DoNotReply', @principal_name = 'public', @is_default = 1 ; -- Make this the default profile GO 5- created the account as: EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = 'DoNotReply', @description = 'my email description', @email_address = 'DoNotReply@mydomain.com', @display_name = 'My email display name', @replyto_address = null, @mailserver_name = 'mail.mydomain.com', @username = 'DoNotReply@mydomain.com', @password = 'my_password', @port = 25, -- Check with your admin for correct port @enable_ssl = False -- Enable ssl communication 6- Added account to the profile: EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = 'DoNotReply', @account_name = 'DoNotReply', @sequence_number = 1 GO 7-Made sure the define port (25 in my case) is allowed in the server. 8- Test : EXECUTE msdb.dbo.sp_send_dbmail @recipients='receipient@recipientdomain.com', @subject = 'Test e-mail sent from database mail', @body = 'This is a test message sent from the newly created database mail account', @reply_to = 'info@mydomain.com' GO BINGO!

Uttam Rawat 2010-08-19	re: Enabling Database Mail on SQL Server Express This is feature should add on Sql Express edtion