database mail sending error

Question

Post reply

database mail sending error

raj acharya

SSCrazy

Points: 2805
More actions
November 20, 2009 at 2:43 am

#139577

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

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

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

dba_pkashyap Hall of Fame Points: 3336 More actions · Answer 1

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.

raj acharya SSCrazy Points: 2805 More actions · Answer 2

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

Jack Corbett SSC Guru Points: 184393 More actions · Answer 3

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

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

dba_pkashyap Hall of Fame Points: 3336 More actions · Answer 4

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.

raj acharya SSCrazy Points: 2805 More actions · Answer 5

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

James Varghese SSC Veteran Points: 283 More actions · Answer 6

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

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

khandelwal.deep Old Hand Points: 355 More actions · Answer 7

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

jerminlouis SSC Rookie Points: 47 More actions · Answer 8

jerminlouis

SSC Rookie

Points: 47

February 6, 2013 at 4:14 am

#1584859

Yes, its resolved my problem.

frick156 SSC Enthusiast Points: 139 More actions · Answer 9

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

thanks!

Jack Corbett SSC Guru Points: 184393 More actions · Answer 10

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.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

frick156 SSC Enthusiast Points: 139 More actions · Answer 11

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?