Verifying sp_send_dbmail results

Question

Verifying sp_send_dbmail results

DoubleEx

SSCommitted

Points: 1523
More actions
December 6, 2010 at 12:20 pm

#230159

I have a stored procedure that loops through a table sending emails based on the values in the table. I send through a call to "EXEC msdb..sp_send_dbmail...". It works perfectly.
After an email is sent I update the original sql table record with the "sent date" and this is what prohibits me sending it again on a pass at a later time.
I want to add the protection of not updating these records if the EXEC fails for any reason in the future. MSDN points out that a Return Code of 0 indicates success and a Return Code of 1 indicates failure. http://msdn.microsoft.com/en-us/library/ms190307.aspx
How do I capture and evaluate the return code within the stored procedure making the call?
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

I'm not sure the return code will help; it signifies whether the procedure executed without errors or not...not whether the email was sent successfully. the email gets sent asynchronously, when it gets processed by the service broker, and can be found in one of the views on the msdb database after teh fact.

it's possible that the proc returns 0, but the email can be found in the msdb.dbo.sysmail_faileditems for a large number of reasons.

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!

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 2

declare @retcode int

exec @retcode = msdb..sp_send_dbmail ...proc parameters...

if @retcode <> 0

begin

...code to handle error ...

end

DoubleEx SSCommitted Points: 1523 More actions · Answer 3

Thanks - I understand that the email may not have actually made it through all points. I'm trying to at least catch any of the "local" problems that are indicated by the sp_send_dbmail results. Thanks for the info in your response

DoubleEx SSCommitted Points: 1523 More actions · Answer 4

Michael Valentine Jones (12/6/2010)
declare @retcode int exec @retcode = msdb..sp_send_dbmail ...proc parameters... if @retcode <> 0 begin ...code to handle error ... end

That's what I was looking for. I have lots of programming experience outside of T-SQL and I know what I wanted I just didn't know the syntax. It didn't help that this is probably T-SQL 101 so none of the examples bother to show it. I coded to your example and it works great.

Thanks

DoubleEx SSCommitted Points: 1523 More actions · Answer 5

Lowell (12/6/2010)
I'm not sure the return code will help; it signifies whether the procedure executed without errors or not...not whether the email was sent successfully. the email gets sent asynchronously, when it gets processed by the service broker, and can be found in one of the views on the msdb database after teh fact.
it's possible that the proc returns 0, but the email can be found in the msdb.dbo.sysmail_faileditems for a large number of reasons.

Actually Lowell, your reference to msdb.dbo.sysmail_faileditems is quite useful and is going in my notes! Thanks again.