locked
Problem with sending mail via Database Mail RRS feed

  • Question

  • Hi every body


    I want to send a simple mail using DATABASE MAIL feature in SQL SERVER 2005.

    I've defined a public profile.
    I've enabled Database Mail stored procedures through the Surface Area Configuration .

    but   I can't send a mail with sp_send_dbmail stored procedure in 'msdb' database .


    when I execute sp_send_dbmail in the Managment Studio  the message is
    "Mail queued"  but the mail is not sent.

    Could it be related to Service Broker?Because  the Surface Area Configuration indicates:'this inctance does not have a Service Broker endpoint'.If so, how should I make an endpoint?

    here is the log file after executing sp_send_dbmail:


    1)  "DatabaseMail process is started"

    2)   "The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2007-03-08T00:49:29). Exception Message: Could not connect to mail server. (No connection could be made because the target machine actively refused it)."

     The DatabaseMail90.exe is triggred ,so the mail is transfered to the mail queue but  DatabaseMail90.exe couldn't give the mail to SMTP server.The promlem is what should I do to make DatabaseMail90.exe able to connect to  the server?



    please help me.

    POUYAN

    Friday, March 9, 2007 11:23 AM

Answers

All replies

  • Hi POUYAN,

    you must have enabled the Service Broker in the msdb database.

    BOL: How to: Activate Service Broker Message Delivery in Databases (Transact-SQL) 

    To see if the message was sent successfully, you can run a query on the sysmail_allitems system view.

    SELECT * FROM sysmail_allitems
     
    Friday, March 9, 2007 1:21 PM
  • Hi

    By default service broker is enabled in each database except 'master' & 'tempdb'

    any way again I enabled service broker.

    where as DatabaseMail90.exe is triggerd whene I execute sp_send_dbmail,the mail should be given to the queue.but when I execute

    sysmail_help_queue_sp to see the queue the 'length' column is 0 which says there is no mail in the queue.I'm confused.

     

    More over I think another problem is that DatabaseMail90.exe can not communicate with the machine(server) .

    do u know  how I can check communication settings between SQL SERVER and the server to see what's going on?

     

    thanks.

    POUYAN.

    Saturday, March 10, 2007 6:49 AM
  • Hi POUYAN,

    plz check:

    SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb' ;

    A value of 0 indicates that Service Broker message delivery is not activated in the msdb database

    EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole': --- check members of the DatabaseMailUserRole

    EXEC msdb.dbo.sysmail_help_principalprofile_sp

    EXECUTE dbo.sysmail_help_status_sp ; --- check status of Database Mail

    EXECUTE dbo.sysmail_start_sp: ---start Database Mail in a mail host database

    EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail'

     

     

     

     

    Tuesday, March 13, 2007 8:23 AM
  •  Pouyan wrote:

    Could it be related to Service Broker?Because  the Surface Area Configuration indicates:'this inctance does not have a Service Broker endpoint'.If so, how should I make an endpoint?

    You do not need an endpoint for database mail.

     Pouyan wrote:

    The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2007-03-08T00:49:29). Exception Message: Could not connect to mail server. (No connection could be made because the target machine actively refused it).

    The problem has nothing to do with Service Broker. Your mailer process gets the mail to send and attempt to deliver, but ot cannot. The problems seems to be with yoyr @mailserver_name or @port parameters passed to sysmail_add_account_sp.

    HTH,
    ~ Remus

    Tuesday, March 13, 2007 8:14 PM
  • Hi

    I 've just installed SMTP services from the "add windows component"

    (I hadn't done !!!!)

     

    but this time I'm facing a new error type:

    "

    Message
    The mail could not be sent to the recipients because of the mail server failure.
     (Sending Mail using Account 1 (2007-03-14T03:55:26).
     Exception Message: Cannot send mails to mail server
    . (Mailbox unavailable. The server response was: 5.7.1 Unable to relay for pooyan_pdm@yahoo.com).
    )
    "

    and when I execute  sysmail_help_queue_sp this would be the resuls:

    queue_type length      state                    last_empty_rowset_time  last_activated_time
    ---------- ----------- -------------------------------------------
    mail       0           RECEIVES_OCCURRING       2007-03-14 12:44:29.713 2007-03-14 12:38:48.463
    status     0           INACTIVE                 2007-03-14 12:44:29.763 2007-03-14 12:44:29.713

    ------------------------------------------

    *database mail is enabled

    *service broker is ebabed(is_broker_enabled=1  for msdb in the sys.databases)

     

    ------------------

    you said this may be because off port or server name:

    in the account I use to send the mail I have set these vause:

    server name:pouyan(computer name in the system properties)

    port =25

     

    should they be any thing else???

     

     

    thanks

    pouyan.

     

     

     

    Wednesday, March 14, 2007 1:03 PM
  • Hi

    I checked every thing just as u said every thinh was ok

    here is the result of executin sysmail_help_queue_sp:

     

    queue_type length      state                    last_empty_rowset_time  last_activated_time
    ---------- ----------- -------------------------------------------
    mail       0           RECEIVES_OCCURRING       2007-03-14 12:44:29.713 2007-03-14 12:38:48.463
    status     0           INACTIVE                 2007-03-14 12:44:29.763 2007-03-14 12:44:29.713

    -----------------------------------------

    I just today installed SMTP services!!!!

    and the type of error changed to this form:

    "

    Message
    The mail could not be sent to the recipients because of the mail server failure.
     (Sending Mail using Account 1 (2007-03-14T03:55:26).
     Exception Message: Cannot send mails to mail server
    . (Mailbox unavailable. The server response was: 5.7.1 Unable to relay for pooyan_pdm@yahoo.com).
    )

    "

    And there is another thing to tell : starting the database mail external(databasemail90.exe) is not mentioned in sysmail_event_log or Windows Application event log.

    but I can see databasemail90.exe running in the prosses tab of  the Task Manager.

    I'm extremly confused.

    Wednesday, March 14, 2007 1:12 PM
  • You need not only install SMTP service, but you also need to configure it properly. See http://support.microsoft.com/kb/304897 for how to test your SMTP relay.
    Wednesday, March 14, 2007 3:49 PM
  • Every thing is ok

    thanks

    Friday, March 16, 2007 10:18 AM
  • Pouyan,

    This may also be happeneing because of any antivirus installed on your machine. BCoz Antivirus softwares ususally block the ports. For example, Macfee blocks the port no 25 which is used by SQL server to send mails.

    • Proposed as answer by Learning2fly2 Tuesday, December 21, 2010 11:37 PM
    Thursday, March 29, 2007 9:31 AM
  • I have to set up database mail to send emails using .

    I am also getting the same error. I tested for SMTP relay service also.

     

    am still getting this error :

     

    The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2007-04-09T15:24:10). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: 5.7.1 Unable to relay for abc@abc.com

     

    can u please let know where I can find the errors.

    Monday, April 9, 2007 10:00 AM
  • Hi,

     

    can you pls. explain to me in short how did you solve this problem?

    I'm getting the same error message....

    Thnx a lot in advance.

     

    BR,

    Bozo

    Friday, July 27, 2007 1:06 PM
  • This problem was caused for ME because of Anti-Virus.

     

    AV blocks services from sending on TCP-25 (like an email worm). Adjust the AV software accordingly. Add DATABASEMAIL90.EXE to the list of innocent programs in your AV.

     

    Friday, July 27, 2007 7:47 PM
  • This is an old topic, but I'm hoping someone will reply...

    Dml256: I tried what you said and it works.  The problem is that after 1-2 minutes, the list of Excluded Processes gets reset and DATABASEMAIL90.EXE is removed.  I don't know why it's doing this or how to fix it.  Help?
    Thursday, April 17, 2008 5:45 PM
  • Hi.

     

    I had the same problem that you have right now, and the problem is quiet simple to solve if you work with Microsoft Exchange or Lotus. You need to add the Ip Address from your database server in your SMTP Server.

     

    It works for me...

     

    Wednesday, December 10, 2008 4:41 PM
  • Issue:
    I had a similar issue. I could send emails within my domain ex: abc.com but NOT to another domain xyz.com email address. 
    SQL 2005 using DB Mail.
    Error Message was: The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2009-11-30T09:44:39). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: 5.7.1 Unable to relay for 1test23@hotmail.com).

    Resolution:
    I found help on http://support.microsoft.com/kb/304897   (It is already mentioned in the thread earlier)
    Problem was related to Exchange Server not allowing relaying from SQL Server.  Network Administrator added SQL Server ip address to allow to send emails to a different domain taking below steps.

    On Mail server > Exchange Server Manager > SMTP Connector Properties
     > Access Properties > Access Tab > Relay button > added ip address of SQL Server
    Monday, November 30, 2009 4:00 PM
  • Issue:
    I had a similar issue. I could send emails within my domain ex: abc.com but NOT to another domain xyz.com email address. 
    SQL 2005 using DB Mail.
    Error Message was: The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2009-11-30T09:44:39). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: 5.7.1 Unable to relay for 1test23@hotmail.com).

    Resolution:
    I found help on http://support.microsoft.com/kb/304897   (It is already mentioned in the thread earlier)
    Problem was related to Exchange Server not allowing relaying from SQL Server.  Network Administrator added SQL Server ip address to allow to send emails to a different domain taking below steps.

    On Mail server > Exchange Server Manager > SMTP Connector Properties
     > Access Properties > Access Tab > Relay button > added ip address of SQL Server
    Monday, November 30, 2009 4:01 PM
  • Hi POUYAN,

    you must have enabled the Service Broker in the msdb database.

    BOL: How to: Activate Service Broker Message Delivery in Databases (Transact-SQL)

    To see if the message was sent successfully, you can run a query on the sysmail_allitems system view.

    SELECT * FROM sysmail_allitems
     

    Thanks for your sharing!
    Thursday, February 17, 2011 5:46 AM
  • Make shure that the credentials for each instance of SQL Server have "LocalSystem" instead of "Network Session" or other. Have Luck! and Greetings from Mexico

    Wednesday, October 19, 2011 10:19 PM
  • I add the IP address from my database server in the SMTP Server. And i used AD-account as a service account.
    Tuesday, May 22, 2012 9:41 AM
  • change the port to 25. It will work.

    Even i was using port 465 (smtp.gmail.com) but wasnt working. Now its working fine.

    Monday, July 29, 2013 3:12 PM