I want some Moore

# 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

# 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

# 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: Enabling Database Mail on SQL Server Express

hi, any news about this?

# 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).

# 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

# 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

# re: Enabling Database Mail on SQL Server Express

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

# 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.

# re: Enabling Database Mail on SQL Server Express

Can express be configured to send mail? I have followed the instructions but no luck..

# 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?

# re: Enabling Database Mail on SQL Server Express

Works for me... Beautiful!

# 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...

# 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.

# 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.

# 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

# 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

# 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.

# 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?

# 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

# 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!

# re: Enabling Database Mail on SQL Server Express

This is feature should add on Sql Express edtion