database mail sending error

  • hi,

    i am getting following error

    Msg 14641, Level 16, State 1, Procedure sp_send_dbmail, Line 81

    Mail not queued. Database Mail is stopped. Use sysmail_start_sp to start Database Mail.

    as per this i did exec sysmail_start_sp

    that time it's show me status started but when again i start to send it's showing me same error again

    any one has any suggestion or solution then let me know asap

    Raj Acharya

  • Was it working fine earlier?

    Try sending a test mail and see the log for what's happening?

    If the test mail is fine:

    Go to SQL Agent Properties-->Alert System-->Enable Database Mail.

    Restart the SQL Agent.

    If the test mail is throwing an issue:

    Check with the network adminstrators if SMTP service is running fine and is it allowing the mails (sort of).

    Regards,

    Pavan.

  • you don't get my point previously it was working fine

    let me tell you step by step

    for testing purpose i am executing like below

    EXEC msdb..sp_send_dbmail

    @profile_name = 'profilename'

    ,@recipients = 'test@vcmpartners.com'

    ,@subject = 'test mail'

    ,@body_format = 'HTML'

    ,@body = 'test mail'

    ,@from_address = 'prod@vcmpartners.com'

    it's show me error

    Msg 14641, Level 16, State 1, Procedure sp_send_dbmail, Line 81

    Mail not queued. Database Mail is stopped. Use sysmail_start_sp to start Database Mail.

    as per error suggest

    EXEC msdb.dbo.sysmail_start_sp; ----- to start database mail

    also check status through

    EXEC msdb.dbo.sysmail_help_status_sp;

    it's shows me started

    after that if i try to send it's show me

    mail queued

    but i don't get any mail

    again if i start to send test mail it's shows me same error and what's it's relation to sql agent mail enable or disable

    Raj Acharya

  • I have written a couple of blog posts about problems with database mail that may help. You can read them here and here.

  • Hey Raj,

    I had a similar issue, even my test mail was failing and it was giving similar sort of error.

    While setting the db mail, in the SMTP server, I used the loopback address 127.0.0.1./ it can be even your IP address.(Anonymous authentication)

    Even my test mail was failing, then I went to IIS Manager, select the Default SMTP server, and under General tab set the IP address as ALL Assigned.

    Try this and see if it works.

    Cheers,

    Pavan.

  • pavan,

    actually here we are using exchange server so in smtp i need to put that, my problem seems different then you.

    guys when i go deep into this i got one error from mail log error described as following

    Exception Information =================== Exception Type: System.Data.SqlClient.SqlException

    Errors: System.Data.SqlClient.SqlErrorCollection Class: 16

    LineNumber: 1 Number: 6602 Procedure: sp_xml_preparedocument

    Server: SQL2008 State: 2 Source: .Net SqlClient Data Provider

    ErrorCode: -2146232060 Message: The error description is 'Whitespace is not allowed at this location.'.

    Data: System.Collections.ListDictionaryInternal

    TargetSite: Void OnError(System.Data.SqlClient.SqlException, Boolean)

    HelpLink: NULL

    StackTrace Information ===================

    i am going to concentrate on this 'Whitespace is not allowed at this location.'

    i think this is something which making problem

    whatever thanx for your reply pavan i am also trying to find my solution.

    Raj Acharya

  • After starting the service using sysmail_start_sp run the following script. This will solve the issue

    SET NOCOUNT ON

    Declare @ch uniqueidentifier;

    Declare @message_type nvarchar(256);

    Declare @message_body nvarchar(max);

    Declare @ctr bigint;

    Declare @ctr2 bigint;

    While (1=1)

    Begin

    Receive @ch = conversation_handle, @message_type = message_type_name from ExternalMailQueue

    set @ctr2 = @ctr2 + 1

    set @ctr = (select count(*) from ExternalMailQueue)

    if @ctr = 0

    break

    end

    ++++++++++++++++++++++++++

    Allways think Positive

    ++++++++++++++++++++++++++

  • It worked for me....Thanks for the Query James..... can you please tell us what was the reason Database mail was behaving like that.

    Thanks in advance... 🙂

  • Yes, its resolved my problem.

  • This saved my bacon as well. Now, to figure out what it actually does...

    thanks!

  • frick156 (8/6/2013)


    This saved my bacon as well. Now, to figure out what it actually does...

    thanks!

    That query receives (reads) the messages form the mail service broker queue and essentially clears them out.

  • Thanks Jack. I read up on RECEIVE before running and assumed that was the case.

    My only question would be if a mail item's status was marked as "sent", would that be truly sent, or is there a possibility that one of these "sent" messages were removed by this queue clearing, essentially, not sent?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply