sp_send_dbmail return Command(s) completed successfully. instead of mailsent id
-
i have configure the database mail for send the mail creating the profile and sp_configure 'Database Mail XPs', 1; and other settings after execute the sp_send_dbmail it will be return me Command(s) completed successfully. insted of Mail (Id: #####) queued.
-
sp_send_dbmail returns an int output parameter, which you can capture, but you have to do so explicitly.
[highlight="#ffff11"]declare @TheMailID int[/highlight]
EXEC msdb.dbo.sp_send_dbmail
@profile_name='A Profile Specific To Database Mail',
@recipients='Lowell Izaguirre<lizaguirre@somedomain.com>;',
@subject = 'Finance SSIS Package Alerts:EDCA to BI360DW',
@body = @HTMLBody,
@body_format = 'HTML'[highlight="#ffff11"],
@mailitem_id = @TheMailID OUTPUT
PRINT @TheMailID[/highlight]
/*--results
Mail queued.
9005
*/
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Hello SSChampion
thanks a for your answer , from your query i can get the id but mail was not received in my inbox
also it is not listed in select * from sysmail_allitems
at what position i can show that mailid which is return after execute sp_send_dbmail
-
Chirag# (11/30/2015)
Hello SSChampionthanks a for your answer , from your query i can get the id but mail was not received in my inbox
also it is not listed in select * from sysmail_allitems
at what position i can show that mailid which is return after execute sp_send_dbmail
all items sent successfully to the service broker would be in sysmail_allitems. If its not, your email is not set up correctly.
have you EVER sent a mail before? maybe you need to start and stop the service? did you send a test email to yourself to prove the service is working?
if you did not get an explicit error in TSQL when you sent it, ie it said "mail queued", then your code is fine, and the issue is downstream and outside of TSQL.
First I'd check if there is an error from the mail server itself in the msdb logs, and look for errors connecting to the server, connection actively refused,or relaying prohibited. i know in my network, i had to make the network admins add my servers explicitly to the white lists by IP address so i could send automated emails.
SELECT top 100
mail.send_request_date As SentDate,
sent_status As Reason,
err.[description],
mail.*
FROM [msdb].[dbo].[sysmail_allitems] mail
inner join [msdb].[dbo].[sysmail_event_log] err
ON err.mailitem_id = mail.mailitem_id
WHERE mail.sent_status <> 'sent'
order by mailitem_id desc
if it's not in the fail mail, then you need to dig deeper into the spam filtering properties of your mail and network;
i'd expect your email was rejected or it is going into a spam filter for whatever you use for mail.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Is your mail profile listed as public or private profile?
Do you have port 25 open for SQL Server to send mail on your network?
You can also view the logs of database mail and see if there are any errors on sending mail.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply