Question

X. Perignon on Wed, 26 Sep 2012 13:55:20


Hello

I tried for hours to get my notifications working, without success, on SQL Server 2008R2 Web Edition 64b

I send mail with the test :

USE msdb

GO

EXEC sp_send_dbmail @profile_name='ARRIERE',@recipients='mai@mail.tld',

@subject='Bonjour ',@body='Congrates Database Mail Received By you Successfully.'


But this mail seems to be never sent.

With:

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.

With

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.

Regards



Sponsored



Replies

Iric Wen on Thu, 27 Sep 2012 08:19:00


Hi X.Perignon,

Please refer to this thread to troubleshoot your problem:

http://social.msdn.microsoft.com/Forums/en/sqlservicebroker/thread/e9aea42f-0382-4cee-b3b4-8aa62842d44e

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


Best Regards,
Iric
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


Hello,

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

Regads

Iric Wen on Fri, 28 Sep 2012 07:36:54


X.Perignon,

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.
EXECUTE dbo.sysmail_help_status_sp

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


Hello,

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

Regards

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


Hello,

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

Regards.

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


Hello,

I can send successfully a mail test from telnet command.

I tried to send a mail test from management - Database Mail but not working.

In: 

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.

Regards.

N.Van on Tue, 09 Oct 2012 09:31:21


Hello,

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.

Regards.

Marcelo E. Rodrigues on Wed, 16 Jul 2014 14:47:10


Hello everyone,

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?

best regards,

Marcelo.

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


Hi X.Perignon,

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.

Kind regards,

Mohammad

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

Paolo

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.

HTH

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.