X. Perignon on Wed, 26 Sep 2012 13:55:20
I tried for hours to get my notifications working, without success, on SQL Server 2008R2 Web Edition 64b
I send mail with the test :
EXEC sp_send_dbmail @profile_name='ARRIERE',@email@example.com',
@subject='Bonjour ',@body='Congrates Database Mail Received By you Successfully.'
But this mail seems to be never sent.
EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail';
The queue state is always INACTIVE, and length is growing everytime I send a email.
I tried to restart the mail system some times :
EXEC msdb.dbo.sysmail_stop_sp; EXEC msdb.dbo.sysmail_start_sp;
The states goes RECEIVE OCURRING, but after some seconds and without intervention, it goes back to INACTIVE.
I tried to rester SQL Agent, SQL Server, the server himself, but no luck.
The broker is activated (SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb' ;)
I disabled every firewall, antivirus and others (by the way, I can do "telnet smtp.domain.tld 25" and send a mail with telnet.
SELECT * FROM msdb.dbo.sysmail_allitems;
I see all my mails with state "unsent".
Nothing logged in Event Viewer, SQL Events, and in sysmail_event_log I only get "Activation successful".
I configured the local IIS SMTP as a relay as said here : http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/f9744284-a9b6-4bfb-81b9-c4833f282ad0
I cleaned the mails with:
DECLARE @GETDATE datetime SET @GETDATE = GETDATE() EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @GETDATE; GO
=> Nothing more.
Can I do something more?
Thans for your help.
Iric Wen on Thu, 27 Sep 2012 08:19:00
Please refer to this thread to troubleshoot your problem:
The solution is turn on the SQL Server Browser service.
And one thing need to be noticed, service broker in SQL Server web edition is only supported for client. Client-only mode is that you can program a single instance of SQL Server with Service Broker however you like. However, when communicating between multiple instances that use Service Broker, you must have at least one, "non-client-mode" instance in the mix (e.g. you could not do cross-instance messaging using only two Express instances).
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
X. Perignon on Thu, 27 Sep 2012 08:49:18
Thanks you for your answer. I already seen this thread, but I forgot to mention it in my post, apologies.
the Browser was already started... restarting it didn't change anything.
Indeed, I have two instances on this server. The only one purpose of sql mail on this server is to send a mail after each work finished.
No communication between these two instances.
Have a nice day
Iric Wen on Fri, 28 Sep 2012 07:36:54
Please check these items:
1. Checked the SQL Server agent alert sytem configuraion to understand if the Database mail is enable or not. Right click SQL Server agent -> properties -> Alert system.
2. Run sp_configure, check Database mail is enabled. Sample is: Database Mail XPs 0 1 1 1
3. Checked the SQL Server MSDB database for service broker as below:
select is_broker_enabled,* from sys.databases
4. Checked the status of the database mail and it is started.
If all above is already configured, please try to:
1. Create a mail account (domain account) dedicated for DB Mail
2. Change the SQL service account to the mail account
N.Van on Tue, 02 Oct 2012 13:17:41
I replace X.Perignon for the issue.
Iric, I tested all step but same issue, all mail stuck in queue at "unsent" status :(
Any idea ?Have a nice day
RohitGarg on Wed, 03 Oct 2012 13:42:29
have you seen any errors in database mail logs ?
Is it working before or its new setup?
I know its not a good solution but I stuck with this one time :-
1) I clear all mail profiles, pending mails & disable database mails
2) restart the server having sql server installed
3) Create new profile from start & it works
There are several things that cause issue.
N.Van on Thu, 04 Oct 2012 16:38:14
it's working very well before. :(
Nothing logged in Event Viewer, SQL Events, and in sysmail_event_log I only get "Activation successful"
I created a new profile with an another mail SMTP but no luck.
I will try your solution.
If you are any suggestion, you are welcome :).
RohitGarg on Fri, 05 Oct 2012 13:54:41
can you check the connectity with smtp server. ping & telnet on port 25.
RahulKapoor on Fri, 05 Oct 2012 21:39:03
Try sending a test mail from Management - Database Mail and see if that reaches you or not.
if not then check this table for the reason for it.
select * from sysmail_sentitems order by sent_date desc
N.Van on Mon, 08 Oct 2012 14:59:41
I can send successfully a mail test from telnet command.
I tried to send a mail test from management - Database Mail but not working.
select * from sysmail_sentitems
order by sent_date desc
- > I have nothing :-(
But at SELECT * FROM msdb.dbo.sysmail_allitems; I have my mail at "unsent" status.
N.Van on Tue, 09 Oct 2012 09:31:21
The probleme was solved.
For test, I granted the service account running SQL Engine to Administrators Group then restarted the service MSSQL and it worked.
With process Monitor, I detected some Access denied in registry.
It's seem that sysadmin role was not enough.
Marcelo E. Rodrigues on Wed, 16 Jul 2014 14:47:10
First of all, thanks for the attention.
I have the same issue here in my SQL 2008 server, the queue state of all e-mail sent are INACTIVE. But the service account running MSSQLSERVER is the local system account.
I think that this account already has administrative properties. Must also put it in the administrators group? How should I proceed?
edit: i used the process monitor, not detected "access denied"...detected only "name not found" and "path not found"
Att, Marcelo E. Rodrigues
M.Hatami on Fri, 09 Sep 2016 06:16:54
I know this is an old thread, but I had the very same problem with Windows Server 2012 R2 and SQL Server 2016. And after a lot of efforts and tracking down the root cause of the issue, I found the issue is the absence of .Net Framework 3.5.
SQL server 2016 needs not only .Net framwork 4.6 that is in the Microsoft website as a pre-requisite for SQL server 2016, but you have to enable .Net framework 3.5 as well. This is how to if you don't know already:
1- Enable/Install .Net Framework 3.5 https://technet.microsoft.com/en-us/library/dn482071.aspx
2- After installation if the emails in the queue are not sent out, you need to restart SQL Server Engine. For doing so again if you don't know: Open "SQL Server Configuration Manager > SQL Server Services > right-click on "SQL Server (yourInstanceName)" >Restart
I hope this help those who have the same problem.
Adrian Ciocan on Mon, 12 Sep 2016 22:46:00
Thank you Mohammad! You saved me from a lot of additional grief! That was the fix for my exact problem, as well.
Paul23 on Fri, 23 Sep 2016 13:44:32
THANK YOU Mohammad! I've searched for hours on this problem, and you are the only person I've seen explain the need for .Net 3.5. That fixed it, running SQL Server 2016 on Win 10.
Paolo Amerio on Wed, 05 Oct 2016 08:22:19
Thanks, saved me hours of headache!! That does the trick on windows server 2012 and sql server 2016...
Hilary Cotter on Mon, 17 Oct 2016 16:38:40
I bumped into a similar issue. it turned out the DatbaseMail.exe had crashed and could not be restarted by SQL Server broker.
It would work when I started it from the command prompt. Until we could restart SQL Server I needed to schedule it.
Greg Wilkerson on Wed, 14 Dec 2016 22:57:45
Mohammad, thanks. And, someone probably needs to clear the rest of these answers. It would certainly safe time for others running into this.
Sultanuddin Siddiqui on Tue, 11 Apr 2017 04:56:24
Thanks Mohammed for this solution, it worked well.
thecoat on Tue, 02 May 2017 20:43:36
I realize this is an old thread, but I found it troubleshooting this problem and the solution I found was a bit different from some of those listed here.
SQL 2008 R2 Standard
Windows Server 2008 R2 Standard.
I was able to run DatabaseMail.exe manually from an administrative login and the mail messages that were queued up were sent.
The windows account under which SQL Server was running (Network Services) did not have security access to 'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\Config\machine.config'. When SQL server tried to run DatabaseMail.exe it threw an exception which appeared to get swallowed somewhere along the line as I didn't find the exception messages in any of the database logs etc.
How I did end up finding this was to try and run DatabaseMail.exe From the query windows using xp_cmdshell, which illuminated the stack trace.
Once I added the Network Services account granting it read access to the machine.config file, SQL server mail test sends started working.