none
sp_send_dbmail Queues but never sends

    Question

  • I've recently configured Database Mail, which went well and sent a test message which also works.

    Because I plan to send an email from within another stored procedure, I thought I'd first compose the call to sp_send_dbmail in a blank query window, make sure it works, then move it into the sp I actually want to send an email from.

    The following, when pasted into a new query window, works perfectly.

    EXEC msdb..sp_send_dbmail @profile_name = 'Default Mail Profile', @recipients = 'stevenjamesfrank@gmail.com', @from_address = 'helpdesk@sharecorp.com', @reply_to = 'helpdesk@sharecorp.com', @subject = 'Test', @body = 'This email is to notify you that order XXXXXXXX has errored out.'

    I get the following response, followed shortly by actual delivery of the message.

    Mail (Id: 29) queued.

    Obviously, the ID increments with each send.

    Step 2; I paste EXACTLY the code above into the actual sp I want to send an email from. It is in a Catch block.

    When I run my sp (from the query window) I can look at the Messages tab and I see the response from the sp_send_dbmail call that a new message has been queued (including a now-incremented Id) so the call to sp_send_dbmail IS OCCURING and the message (at least according to SQL Server is getting queued (see below).  The only problem is, no delivery.

    I can reproduce this at will, simply running the call to sp_send_dbmail works perfectly, try the sp below, get the queued message with no delivery, then go back an run sp_send_dbmail per the code above and it works perfectly.

    Friday, July 12, 2013 8:40 PM

All replies

  • The following threads are on similar topic:

    http://stackoverflow.com/questions/7168648/msdb-dbo-sp-send-dbmail-does-not-execute-within-a-stored-procedure

    http://forums.asp.net/t/1680235.aspx/1

    Can you post the entire code?


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: SQL Server 2012 Administration



    Friday, July 12, 2013 8:49 PM
  • Thanks for the response, but none of that helps.

    I'm just looking to send an email when a procedure completes or errors.

    The steps detailed in the link are 10 times more complicated than the entire procedure I'm working on.

    Friday, July 12, 2013 9:20 PM
  • Hi Steven,

    What is @@version?

    What is the execution context for the stored procedure? Same as for the query?

    Can you post code?


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: SQL Server 2012 Administration


    Saturday, July 13, 2013 12:27 AM
  • Please see the section "To determine if problems with Database Mail affect all accounts in a profile or only some accounts" in:  http://msdn.microsoft.com/en-us/library/ms187540(v=sql.105).aspx

    Monday, July 15, 2013 2:48 PM
  • Here is the value of @@version:

    Microsoft SQL Server 2012 - 11.0.2332.0 (X64)

    Aug 22 2012 17:05:49

    Copyright (c) Microsoft Corporation

    Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

    To give you a little more context on the process;

    I have a sproc called sp_order_import, it is fired as necessary from a powershell script.  It's function is to grab an xml file and bring it into the database.  This all works, no problems at all.

    I would like to send email notifications on success of failure of the import, hence my interest in Database Mail.

    My initial attempt was to simply put a call to sp_send_dbmail into sp_order_import at the appropriate point(s).  The syntax for the call is good (I can paste it into a new query window and run it with success) but it will not send the email when invoked from sp_order_import.  When I run sp_order_import, I know it's calling sp_send_dbmail, the message is returned that a new message was queued and the Id of the message increments.

    Also, this is a very basic install of Database Mail.  It has a single profile and a single account within that profile.

    Any additional suggestions would be greatly appreciated.

    Monday, July 15, 2013 4:04 PM
  • I do not believe this applies. 

    I only have one profile and one account.

    I CAN send from that account, it just does work when sp_send_dbmail is invoked from my main sproc (see above for additional details on the process as a whole).

    Monday, July 15, 2013 4:05 PM
  • Run the queries in that section:

    SELECT sent_account_id, sent_date FROM msdb.dbo.sysmail_sentitems;

    SELECT * FROM msdb.dbo.sysmail_event_log;

    Monday, July 15, 2013 4:11 PM
  • sent_account_id	sent_date
    1	2013-07-10 13:17:34.000
    1	2013-07-10 13:18:57.000
    1	2013-07-12 14:27:04.000
    1	2013-07-12 14:53:12.000
    1	2013-07-12 14:55:35.000
    1	2013-07-12 15:01:25.000
    1	2013-07-12 15:07:01.000
    1	2013-07-12 15:10:33.000
    1	2013-07-12 15:13:14.000
    1	2013-07-12 15:20:15.000
    log_id	event_type	log_date	description	process_id	mailitem_id	account_id	last_mod_date	last_mod_user
    1	information	2013-07-12 14:53:12.127	DatabaseMail process is started	5080	NULL	NULL	2013-07-12 14:53:12.127	NT Service\MSSQLSERVER
    2	information	2013-07-12 15:30:15.977	DatabaseMail process is shutting down	5080	NULL	NULL	2013-07-12 15:30:15.977	NT Service\MSSQLSERVER
    3	success	2013-07-12 15:30:28.903	Activation successful.	NULL	NULL	NULL	2013-07-12 15:30:28.903	sa
    4	information	2013-07-12 15:30:29.250	DatabaseMail process is started	348	NULL	NULL	2013-07-12 15:30:29.250	NT Service\MSSQLSERVER
    5	success	2013-07-12 15:30:34.097	Activation successful.	NULL	NULL	NULL	2013-07-12 15:30:34.097	sa
    6	success	2013-07-12 15:30:38.910	Activation successful.	NULL	NULL	NULL	2013-07-12 15:30:38.910	sa
    7	success	2013-07-12 15:30:43.910	Activation successful.	NULL	NULL	NULL	2013-07-12 15:30:43.910	sa
    8	success	2013-07-12 15:30:48.910	Activation successful.	NULL	NULL	NULL	2013-07-12 15:30:48.910	sa
    9	success	2013-07-12 15:30:53.910	Activation successful.	NULL	NULL	NULL	2013-07-12 15:30:53.910	sa
    10	success	2013-07-12 15:30:58.910	Activation successful.	NULL	NULL	NULL	2013-07-12 15:30:58.910	sa
    11	success	2013-07-12 15:31:03.910	Activation successful.	NULL	NULL	NULL	2013-07-12 15:31:03.910	sa
    12	success	2013-07-12 15:31:08.910	Activation successful.	NULL	NULL	NULL	2013-07-12 15:31:08.910	sa
    13	success	2013-07-12 15:31:13.913	Activation successful.	NULL	NULL	NULL	2013-07-12 15:31:13.913	sa
    14	success	2013-07-12 15:31:18.917	Activation successful.	NULL	NULL	NULL	2013-07-12 15:31:18.917	sa
    15	success	2013-07-12 15:31:23.913	Activation successful.	NULL	NULL	NULL	2013-07-12 15:31:23.913	sa
    16	success	2013-07-12 15:31:28.913	Activation successful.	NULL	NULL	NULL	2013-07-12 15:31:28.913	sa
    17	success	2013-07-12 15:31:33.913	Activation successful.	NULL	NULL	NULL	2013-07-12 15:31:33.913	sa
    18	success	2013-07-12 15:31:38.917	Activation successful.	NULL	NULL	NULL	2013-07-12 15:31:38.917	sa
    19	success	2013-07-12 15:31:43.917	Activation successful.	NULL	NULL	NULL	2013-07-12 15:31:43.917	sa
    20	success	2013-07-12 15:31:48.917	Activation successful.	NULL	NULL	NULL	2013-07-12 15:31:48.917	sa
    21	success	2013-07-12 15:31:53.917	Activation successful.	NULL	NULL	NULL	2013-07-12 15:31:53.917	sa
    22	success	2013-07-12 15:31:58.920	Activation successful.	NULL	NULL	NULL	2013-07-12 15:31:58.920	sa
    23	success	2013-07-12 15:32:03.920	Activation successful.	NULL	NULL	NULL	2013-07-12 15:32:03.920	sa
    24	success	2013-07-12 15:32:08.920	Activation successful.	NULL	NULL	NULL	2013-07-12 15:32:08.920	sa
    25	success	2013-07-12 15:32:13.920	Activation successful.	NULL	NULL	NULL	2013-07-12 15:32:13.920	sa
    26	success	2013-07-12 15:32:18.920	Activation successful.	NULL	NULL	NULL	2013-07-12 15:32:18.920	sa
    27	success	2013-07-12 15:32:23.920	Activation successful.	NULL	NULL	NULL	2013-07-12 15:32:23.920	sa
    28	success	2013-07-12 15:32:28.920	Activation successful.	NULL	NULL	NULL	2013-07-12 15:32:28.920	sa
    29	success	2013-07-12 15:32:33.920	Activation successful.	NULL	NULL	NULL	2013-07-12 15:32:33.920	sa
    30	success	2013-07-12 15:32:38.923	Activation successful.	NULL	NULL	NULL	2013-07-12 15:32:38.923	sa
    31	success	2013-07-12 15:32:43.923	Activation successful.	NULL	NULL	NULL	2013-07-12 15:32:43.923	sa
    32	success	2013-07-12 15:32:48.923	Activation successful.	NULL	NULL	NULL	2013-07-12 15:32:48.923	sa
    33	success	2013-07-12 15:32:53.923	Activation successful.	NULL	NULL	NULL	2013-07-12 15:32:53.923	sa
    34	success	2013-07-12 15:32:58.927	Activation successful.	NULL	NULL	NULL	2013-07-12 15:32:58.927	sa
    35	success	2013-07-12 15:33:03.927	Activation successful.	NULL	NULL	NULL	2013-07-12 15:33:03.927	sa
    36	success	2013-07-12 15:33:08.927	Activation successful.	NULL	NULL	NULL	2013-07-12 15:33:08.927	sa
    37	success	2013-07-12 15:33:13.927	Activation successful.	NULL	NULL	NULL	2013-07-12 15:33:13.927	sa
    38	success	2013-07-12 15:33:18.937	Activation successful.	NULL	NULL	NULL	2013-07-12 15:33:18.937	sa
    39	success	2013-07-12 15:33:23.930	Activation successful.	NULL	NULL	NULL	2013-07-12 15:33:23.930	sa
    40	success	2013-07-12 15:33:28.930	Activation successful.	NULL	NULL	NULL	2013-07-12 15:33:28.930	sa
    41	success	2013-07-12 15:33:33.930	Activation successful.	NULL	NULL	NULL	2013-07-12 15:33:33.930	sa
    42	success	2013-07-12 15:33:38.973	Activation successful.	NULL	NULL	NULL	2013-07-12 15:33:38.973	sa
    43	success	2013-07-12 15:33:43.933	Activation successful.	NULL	NULL	NULL	2013-07-12 15:33:43.933	sa
    44	success	2013-07-12 15:33:48.930	Activation successful.	NULL	NULL	NULL	2013-07-12 15:33:48.930	sa
    45	success	2013-07-12 15:33:53.933	Activation successful.	NULL	NULL	NULL	2013-07-12 15:33:53.933	sa
    46	success	2013-07-12 15:33:58.933	Activation successful.	NULL	NULL	NULL	2013-07-12 15:33:58.933	sa
    47	success	2013-07-12 15:34:03.933	Activation successful.	NULL	NULL	NULL	2013-07-12 15:34:03.933	sa
    48	success	2013-07-12 15:34:08.937	Activation successful.	NULL	NULL	NULL	2013-07-12 15:34:08.937	sa
    49	success	2013-07-12 15:34:13.933	Activation successful.	NULL	NULL	NULL	2013-07-12 15:34:13.933	sa
    50	success	2013-07-12 15:34:18.937	Activation successful.	NULL	NULL	NULL	2013-07-12 15:34:18.937	sa
    51	success	2013-07-12 15:34:24.063	Activation successful.	NULL	NULL	NULL	2013-07-12 15:34:24.063	sa
    52	success	2013-07-12 15:34:29.000	Activation successful.	NULL	NULL	NULL	2013-07-12 15:34:29.000	sa
    53	success	2013-07-12 15:34:34.000	Activation successful.	NULL	NULL	NULL	2013-07-12 15:34:34.000	sa
    54	success	2013-07-12 15:34:39.000	Activation successful.	NULL	NULL	NULL	2013-07-12 15:34:39.000	sa
    55	success	2013-07-12 15:34:44.000	Activation successful.	NULL	NULL	NULL	2013-07-12 15:34:44.000	sa
    56	success	2013-07-12 15:34:49.040	Activation successful.	NULL	NULL	NULL	2013-07-12 15:34:49.040	sa
    57	success	2013-07-12 15:34:54.003	Activation successful.	NULL	NULL	NULL	2013-07-12 15:34:54.003	sa
    58	success	2013-07-12 15:34:59.003	Activation successful.	NULL	NULL	NULL	2013-07-12 15:34:59.003	sa
    59	success	2013-07-12 15:35:04.003	Activation successful.	NULL	NULL	NULL	2013-07-12 15:35:04.003	sa
    60	success	2013-07-12 15:35:09.043	Activation successful.	NULL	NULL	NULL	2013-07-12 15:35:09.043	sa
    61	success	2013-07-12 15:35:14.007	Activation successful.	NULL	NULL	NULL	2013-07-12 15:35:14.007	sa
    62	success	2013-07-12 15:35:19.007	Activation successful.	NULL	NULL	NULL	2013-07-12 15:35:19.007	sa
    63	success	2013-07-12 15:35:24.010	Activation successful.	NULL	NULL	NULL	2013-07-12 15:35:24.010	sa
    64	success	2013-07-12 15:35:29.010	Activation successful.	NULL	NULL	NULL	2013-07-12 15:35:29.010	sa
    65	success	2013-07-12 15:35:34.010	Activation successful.	NULL	NULL	NULL	2013-07-12 15:35:34.010	sa
    66	success	2013-07-12 15:35:39.010	Activation successful.	NULL	NULL	NULL	2013-07-12 15:35:39.010	sa
    67	success	2013-07-12 15:35:44.010	Activation successful.	NULL	NULL	NULL	2013-07-12 15:35:44.010	sa
    68	success	2013-07-12 15:35:49.013	Activation successful.	NULL	NULL	NULL	2013-07-12 15:35:49.013	sa
    69	success	2013-07-12 15:35:54.163	Activation successful.	NULL	NULL	NULL	2013-07-12 15:35:54.163	sa
    70	success	2013-07-12 15:35:59.023	Activation successful.	NULL	NULL	NULL	2013-07-12 15:35:59.023	sa
    71	success	2013-07-12 15:36:04.023	Activation successful.	NULL	NULL	NULL	2013-07-12 15:36:04.023	sa
    72	success	2013-07-12 15:36:09.023	Activation successful.	NULL	NULL	NULL	2013-07-12 15:36:09.023	sa
    73	success	2013-07-12 15:36:14.027	Activation successful.	NULL	NULL	NULL	2013-07-12 15:36:14.027	sa
    74	success	2013-07-12 15:36:19.023	Activation successful.	NULL	NULL	NULL	2013-07-12 15:36:19.023	sa
    75	success	2013-07-12 15:36:24.027	Activation successful.	NULL	NULL	NULL	2013-07-12 15:36:24.027	sa
    76	success	2013-07-12 15:36:29.027	Activation successful.	NULL	NULL	NULL	2013-07-12 15:36:29.027	sa
    77	success	2013-07-12 15:36:34.027	Activation successful.	NULL	NULL	NULL	2013-07-12 15:36:34.027	sa
    78	success	2013-07-12 15:36:39.027	Activation successful.	NULL	NULL	NULL	2013-07-12 15:36:39.027	sa
    79	success	2013-07-12 15:36:44.030	Activation successful.	NULL	NULL	NULL	2013-07-12 15:36:44.030	sa
    80	success	2013-07-12 15:36:49.030	Activation successful.	NULL	NULL	NULL	2013-07-12 15:36:49.030	sa
    81	success	2013-07-12 15:36:54.030	Activation successful.	NULL	NULL	NULL	2013-07-12 15:36:54.030	sa
    82	success	2013-07-12 15:36:59.030	Activation successful.	NULL	NULL	NULL	2013-07-12 15:36:59.030	sa
    83	success	2013-07-12 15:37:04.030	Activation successful.	NULL	NULL	NULL	2013-07-12 15:37:04.030	sa
    84	success	2013-07-12 15:37:09.030	Activation successful.	NULL	NULL	NULL	2013-07-12 15:37:09.030	sa
    85	success	2013-07-12 15:37:14.030	Activation successful.	NULL	NULL	NULL	2013-07-12 15:37:14.030	sa
    86	success	2013-07-12 15:37:19.030	Activation successful.	NULL	NULL	NULL	2013-07-12 15:37:19.030	sa
    87	success	2013-07-12 15:37:24.033	Activation successful.	NULL	NULL	NULL	2013-07-12 15:37:24.033	sa
    88	success	2013-07-12 15:37:29.033	Activation successful.	NULL	NULL	NULL	2013-07-12 15:37:29.033	sa
    89	success	2013-07-12 15:37:34.033	Activation successful.	NULL	NULL	NULL	2013-07-12 15:37:34.033	sa
    90	success	2013-07-12 15:37:39.033	Activation successful.	NULL	NULL	NULL	2013-07-12 15:37:39.033	sa
    91	success	2013-07-12 15:37:44.037	Activation successful.	NULL	NULL	NULL	2013-07-12 15:37:44.037	sa
    92	success	2013-07-12 15:37:49.037	Activation successful.	NULL	NULL	NULL	2013-07-12 15:37:49.037	sa
    93	success	2013-07-12 15:37:54.037	Activation successful.	NULL	NULL	NULL	2013-07-12 15:37:54.037	sa
    94	success	2013-07-12 15:37:59.067	Activation successful.	NULL	NULL	NULL	2013-07-12 15:37:59.067	sa
    95	success	2013-07-12 15:38:04.037	Activation successful.	NULL	NULL	NULL	2013-07-12 15:38:04.037	sa
    96	success	2013-07-12 15:38:09.040	Activation successful.	NULL	NULL	NULL	2013-07-12 15:38:09.040	sa
    97	success	2013-07-12 15:38:14.110	Activation successful.	NULL	NULL	NULL	2013-07-12 15:38:14.110	sa
    98	success	2013-07-12 15:38:19.043	Activation successful.	NULL	NULL	NULL	2013-07-12 15:38:19.043	sa
    99	success	2013-07-12 15:38:24.043	Activation successful.	NULL	NULL	NULL	2013-07-12 15:38:24.043	sa
    100	success	2013-07-12 15:38:29.043	Activation successful.	NULL	NULL	NULL	2013-07-12 15:38:29.043	sa
    101	success	2013-07-12 15:38:34.043	Activation successful.	NULL	NULL	NULL	2013-07-12 15:38:34.043	sa
    102	success	2013-07-12 15:38:39.043	Activation successful.	NULL	NULL	NULL	2013-07-12 15:38:39.043	sa
    103	success	2013-07-12 15:38:44.043	Activation successful.	NULL	NULL	NULL	2013-07-12 15:38:44.043	sa
    104	success	2013-07-12 15:38:49.047	Activation successful.	NULL	NULL	NULL	2013-07-12 15:38:49.047	sa
    105	success	2013-07-12 15:38:54.047	Activation successful.	NULL	NULL	NULL	2013-07-12 15:38:54.047	sa
    106	success	2013-07-12 15:38:59.047	Activation successful.	NULL	NULL	NULL	2013-07-12 15:38:59.047	sa
    107	success	2013-07-12 15:39:04.050	Activation successful.	NULL	NULL	NULL	2013-07-12 15:39:04.050	sa
    108	success	2013-07-12 15:39:09.050	Activation successful.	NULL	NULL	NULL	2013-07-12 15:39:09.050	sa
    109	success	2013-07-12 15:39:14.050	Activation successful.	NULL	NULL	NULL	2013-07-12 15:39:14.050	sa
    110	success	2013-07-12 15:39:19.050	Activation successful.	NULL	NULL	NULL	2013-07-12 15:39:19.050	sa
    111	success	2013-07-12 15:39:24.050	Activation successful.	NULL	NULL	NULL	2013-07-12 15:39:24.050	sa
    112	success	2013-07-12 15:39:29.050	Activation successful.	NULL	NULL	NULL	2013-07-12 15:39:29.050	sa
    113	success	2013-07-12 15:39:34.050	Activation successful.	NULL	NULL	NULL	2013-07-12 15:39:34.050	sa
    114	success	2013-07-12 15:39:39.050	Activation successful.	NULL	NULL	NULL	2013-07-12 15:39:39.050	sa
    115	success	2013-07-12 15:39:44.050	Activation successful.	NULL	NULL	NULL	2013-07-12 15:39:44.050	sa
    116	success	2013-07-12 15:39:49.053	Activation successful.	NULL	NULL	NULL	2013-07-12 15:39:49.053	sa
    117	success	2013-07-12 15:39:54.053	Activation successful.	NULL	NULL	NULL	2013-07-12 15:39:54.053	sa
    118	success	2013-07-12 15:39:59.053	Activation successful.	NULL	NULL	NULL	2013-07-12 15:39:59.053	sa
    119	success	2013-07-12 15:40:04.087	Activation successful.	NULL	NULL	NULL	2013-07-12 15:40:04.087	sa
    120	success	2013-07-12 15:40:09.060	Activation successful.	NULL	NULL	NULL	2013-07-12 15:40:09.060	sa
    121	success	2013-07-12 15:40:14.060	Activation successful.	NULL	NULL	NULL	2013-07-12 15:40:14.060	sa
    122	success	2013-07-12 15:40:19.060	Activation successful.	NULL	NULL	NULL	2013-07-12 15:40:19.060	sa
    123	success	2013-07-12 15:40:24.060	Activation successful.	NULL	NULL	NULL	2013-07-12 15:40:24.060	sa
    124	success	2013-07-12 15:40:29.060	Activation successful.	NULL	NULL	NULL	2013-07-12 15:40:29.060	sa
    125	information	2013-07-12 15:40:29.370	DatabaseMail process is shutting down	348	NULL	NULL	2013-07-12 15:40:29.370	NT Service\MSSQLSERVER
    126	success	2013-07-12 15:40:39.067	Activation successful.	NULL	NULL	NULL	2013-07-12 15:40:39.067	sa
    127	information	2013-07-12 15:40:39.407	DatabaseMail process is started	4556	NULL	NULL	2013-07-12 15:40:39.407	NT Service\MSSQLSERVER
    128	success	2013-07-12 15:40:44.063	Activation successful.	NULL	NULL	NULL	2013-07-12 15:40:44.063	sa
    129	success	2013-07-12 15:40:49.063	Activation successful.	NULL	NULL	NULL	2013-07-12 15:40:49.063	sa
    130	success	2013-07-12 15:40:54.063	Activation successful.	NULL	NULL	NULL	2013-07-12 15:40:54.063	sa
    131	success	2013-07-12 15:40:59.063	Activation successful.	NULL	NULL	NULL	2013-07-12 15:40:59.063	sa
    132	success	2013-07-12 15:41:04.063	Activation successful.	NULL	NULL	NULL	2013-07-12 15:41:04.063	sa
    133	success	2013-07-12 15:41:09.063	Activation successful.	NULL	NULL	NULL	2013-07-12 15:41:09.063	sa
    134	success	2013-07-12 15:41:14.067	Activation successful.	NULL	NULL	NULL	2013-07-12 15:41:14.067	sa
    135	success	2013-07-12 15:41:19.073	Activation successful.	NULL	NULL	NULL	2013-07-12 15:41:19.073	sa
    136	success	2013-07-12 15:41:24.067	Activation successful.	NULL	NULL	NULL	2013-07-12 15:41:24.067	sa
    137	success	2013-07-12 15:41:29.067	Activation successful.	NULL	NULL	NULL	2013-07-12 15:41:29.067	sa
    138	success	2013-07-12 15:41:34.067	Activation successful.	NULL	NULL	NULL	2013-07-12 15:41:34.067	sa
    139	success	2013-07-12 15:41:39.067	Activation successful.	NULL	NULL	NULL	2013-07-12 15:41:39.067	sa
    140	success	2013-07-12 15:41:44.070	Activation successful.	NULL	NULL	NULL	2013-07-12 15:41:44.070	sa
    141	success	2013-07-12 15:41:49.070	Activation successful.	NULL	NULL	NULL	2013-07-12 15:41:49.070	sa
    142	success	2013-07-12 15:41:54.070	Activation successful.	NULL	NULL	NULL	2013-07-12 15:41:54.070	sa
    143	success	2013-07-12 15:41:59.070	Activation successful.	NULL	NULL	NULL	2013-07-12 15:41:59.070	sa
    144	success	2013-07-12 15:42:04.183	Activation successful.	NULL	NULL	NULL	2013-07-12 15:42:04.183	sa
    145	success	2013-07-12 15:42:09.073	Activation successful.	NULL	NULL	NULL	2013-07-12 15:42:09.073	sa
    146	success	2013-07-12 15:42:14.073	Activation successful.	NULL	NULL	NULL	2013-07-12 15:42:14.073	sa
    147	success	2013-07-12 15:42:19.073	Activation successful.	NULL	NULL	NULL	2013-07-12 15:42:19.073	sa
    148	success	2013-07-12 15:42:24.077	Activation successful.	NULL	NULL	NULL	2013-07-12 15:42:24.077	sa
    149	success	2013-07-12 15:42:29.077	Activation successful.	NULL	NULL	NULL	2013-07-12 15:42:29.077	sa
    150	success	2013-07-12 15:42:34.077	Activation successful.	NULL	NULL	NULL	2013-07-12 15:42:34.077	sa
    151	success	2013-07-12 15:42:39.077	Activation successful.	NULL	NULL	NULL	2013-07-12 15:42:39.077	sa
    152	success	2013-07-12 15:42:44.077	Activation successful.	NULL	NULL	NULL	2013-07-12 15:42:44.077	sa
    153	success	2013-07-12 15:42:49.273	Activation successful.	NULL	NULL	NULL	2013-07-12 15:42:49.273	sa
    154	success	2013-07-12 15:42:54.083	Activation successful.	NULL	NULL	NULL	2013-07-12 15:42:54.083	sa
    155	success	2013-07-12 15:42:59.087	Activation successful.	NULL	NULL	NULL	2013-07-12 15:42:59.087	sa
    156	success	2013-07-12 15:43:04.087	Activation successful.	NULL	NULL	NULL	2013-07-12 15:43:04.087	sa
    157	success	2013-07-12 15:43:09.087	Activation successful.	NULL	NULL	NULL	2013-07-12 15:43:09.087	sa
    158	success	2013-07-12 15:43:14.090	Activation successful.	NULL	NULL	NULL	2013-07-12 15:43:14.090	sa
    159	success	2013-07-12 15:43:19.090	Activation successful.	NULL	NULL	NULL	2013-07-12 15:43:19.090	sa
    160	success	2013-07-12 15:43:24.090	Activation successful.	NULL	NULL	NULL	2013-07-12 15:43:24.090	sa
    161	success	2013-07-12 15:43:29.090	Activation successful.	NULL	NULL	NULL	2013-07-12 15:43:29.090	sa
    162	success	2013-07-12 15:43:34.090	Activation successful.	NULL	NULL	NULL	2013-07-12 15:43:34.090	sa
    163	success	2013-07-12 15:43:39.090	Activation successful.	NULL	NULL	NULL	2013-07-12 15:43:39.090	sa
    164	success	2013-07-12 15:43:44.090	Activation successful.	NULL	NULL	NULL	2013-07-12 15:43:44.090	sa
    165	success	2013-07-12 15:43:49.090	Activation successful.	NULL	NULL	NULL	2013-07-12 15:43:49.090	sa
    166	success	2013-07-12 15:43:54.093	Activation successful.	NULL	NULL	NULL	2013-07-12 15:43:54.093	sa
    167	success	2013-07-12 15:43:59.093	Activation successful.	NULL	NULL	NULL	2013-07-12 15:43:59.093	sa
    168	success	2013-07-12 15:44:04.093	Activation successful.	NULL	NULL	NULL	2013-07-12 15:44:04.093	sa
    169	success	2013-07-12 15:44:09.093	Activation successful.	NULL	NULL	NULL	2013-07-12 15:44:09.093	sa
    170	success	2013-07-12 15:44:14.097	Activation successful.	NULL	NULL	NULL	2013-07-12 15:44:14.097	sa
    171	success	2013-07-12 15:44:19.097	Activation successful.	NULL	NULL	NULL	2013-07-12 15:44:19.097	sa
    172	success	2013-07-12 15:44:24.120	Activation successful.	NULL	NULL	NULL	2013-07-12 15:44:24.120	sa
    173	success	2013-07-12 15:44:29.100	Activation successful.	NULL	NULL	NULL	2013-07-12 15:44:29.100	sa
    174	success	2013-07-12 15:44:34.103	Activation successful.	NULL	NULL	NULL	2013-07-12 15:44:34.103	sa
    175	success	2013-07-12 15:44:39.100	Activation successful.	NULL	NULL	NULL	2013-07-12 15:44:39.100	sa
    176	success	2013-07-12 15:44:44.103	Activation successful.	NULL	NULL	NULL	2013-07-12 15:44:44.103	sa
    177	success	2013-07-12 15:44:49.103	Activation successful.	NULL	NULL	NULL	2013-07-12 15:44:49.103	sa
    178	success	2013-07-12 15:44:54.103	Activation successful.	NULL	NULL	NULL	2013-07-12 15:44:54.103	sa
    179	success	2013-07-12 15:44:59.103	Activation successful.	NULL	NULL	NULL	2013-07-12 15:44:59.103	sa
    180	success	2013-07-12 15:45:04.103	Activation successful.	NULL	NULL	NULL	2013-07-12 15:45:04.103	sa
    181	success	2013-07-12 15:45:09.107	Activation successful.	NULL	NULL	NULL	2013-07-12 15:45:09.107	sa
    182	success	2013-07-12 15:45:14.110	Activation successful.	NULL	NULL	NULL	2013-07-12 15:45:14.110	sa
    183	success	2013-07-12 15:45:19.110	Activation successful.	NULL	NULL	NULL	2013-07-12 15:45:19.110	sa
    184	success	2013-07-12 15:45:24.110	Activation successful.	NULL	NULL	NULL	2013-07-12 15:45:24.110	sa
    185	success	2013-07-12 15:45:29.113	Activation successful.	NULL	NULL	NULL	2013-07-12 15:45:29.113	sa
    186	success	2013-07-12 15:45:34.113	Activation successful.	NULL	NULL	NULL	2013-07-12 15:45:34.113	sa
    187	success	2013-07-12 15:45:39.113	Activation successful.	NULL	NULL	NULL	2013-07-12 15:45:39.113	sa
    188	success	2013-07-12 15:45:44.113	Activation successful.	NULL	NULL	NULL	2013-07-12 15:45:44.113	sa
    189	success	2013-07-12 15:45:49.113	Activation successful.	NULL	NULL	NULL	2013-07-12 15:45:49.113	sa
    190	success	2013-07-12 15:45:54.173	Activation successful.	NULL	NULL	NULL	2013-07-12 15:45:54.173	sa
    191	success	2013-07-12 15:45:59.140	Activation successful.	NULL	NULL	NULL	2013-07-12 15:45:59.140	sa
    192	success	2013-07-12 15:46:04.143	Activation successful.	NULL	NULL	NULL	2013-07-12 15:46:04.143	sa
    193	success	2013-07-12 15:46:09.140	Activation successful.	NULL	NULL	NULL	2013-07-12 15:46:09.140	sa
    194	success	2013-07-12 15:46:14.143	Activation successful.	NULL	NULL	NULL	2013-07-12 15:46:14.143	sa
    195	success	2013-07-12 15:46:19.153	Activation successful.	NULL	NULL	NULL	2013-07-12 15:46:19.153	sa
    196	success	2013-07-12 15:46:24.143	Activation successful.	NULL	NULL	NULL	2013-07-12 15:46:24.143	sa
    197	success	2013-07-12 15:46:29.147	Activation successful.	NULL	NULL	NULL	2013-07-12 15:46:29.147	sa
    198	success	2013-07-12 15:46:34.147	Activation successful.	NULL	NULL	NULL	2013-07-12 15:46:34.147	sa
    199	success	2013-07-12 15:46:39.230	Activation successful.	NULL	NULL	NULL	2013-07-12 15:46:39.230	sa
    200	success	2013-07-12 15:46:44.150	Activation successful.	NULL	NULL	NULL	2013-07-12 15:46:44.150	sa
    201	success	2013-07-12 15:46:49.150	Activation successful.	NULL	NULL	NULL	2013-07-12 15:46:49.150	sa
    202	success	2013-07-12 15:46:54.150	Activation successful.	NULL	NULL	NULL	2013-07-12 15:46:54.150	sa
    203	success	2013-07-12 15:46:59.153	Activation successful.	NULL	NULL	NULL	2013-07-12 15:46:59.153	sa
    204	success	2013-07-12 15:47:04.153	Activation successful.	NULL	NULL	NULL	2013-07-12 15:47:04.153	sa
    205	success	2013-07-12 15:47:09.153	Activation successful.	NULL	NULL	NULL	2013-07-12 15:47:09.153	sa
    206	success	2013-07-12 15:47:14.157	Activation successful.	NULL	NULL	NULL	2013-07-12 15:47:14.157	sa
    207	success	2013-07-12 15:47:19.157	Activation successful.	NULL	NULL	NULL	2013-07-12 15:47:19.157	sa
    208	success	2013-07-12 15:47:24.290	Activation successful.	NULL	NULL	NULL	2013-07-12 15:47:24.290	sa
    209	success	2013-07-12 15:47:29.160	Activation successful.	NULL	NULL	NULL	2013-07-12 15:47:29.160	sa
    210	success	2013-07-12 15:47:34.160	Activation successful.	NULL	NULL	NULL	2013-07-12 15:47:34.160	sa
    211	success	2013-07-12 15:47:39.160	Activation successful.	NULL	NULL	NULL	2013-07-12 15:47:39.160	sa
    212	success	2013-07-12 15:47:44.163	Activation successful.	NULL	NULL	NULL	2013-07-12 15:47:44.163	sa
    213	success	2013-07-12 15:47:49.163	Activation successful.	NULL	NULL	NULL	2013-07-12 15:47:49.163	sa
    214	success	2013-07-12 15:47:54.163	Activation successful.	NULL	NULL	NULL	2013-07-12 15:47:54.163	sa
    215	success	2013-07-12 15:47:59.200	Activation successful.	NULL	NULL	NULL	2013-07-12 15:47:59.200	sa
    216	success	2013-07-12 15:48:04.167	Activation successful.	NULL	NULL	NULL	2013-07-12 15:48:04.167	sa
    217	success	2013-07-12 15:48:09.370	Activation successful.	NULL	NULL	NULL	2013-07-12 15:48:09.370	sa
    218	success	2013-07-12 15:48:14.167	Activation successful.	NULL	NULL	NULL	2013-07-12 15:48:14.167	sa
    219	success	2013-07-12 15:48:19.167	Activation successful.	NULL	NULL	NULL	2013-07-12 15:48:19.167	sa
    220	success	2013-07-12 15:48:24.167	Activation successful.	NULL	NULL	NULL	2013-07-12 15:48:24.167	sa
    221	success	2013-07-12 15:48:29.170	Activation successful.	NULL	NULL	NULL	2013-07-12 15:48:29.170	sa
    222	success	2013-07-12 15:48:34.170	Activation successful.	NULL	NULL	NULL	2013-07-12 15:48:34.170	sa
    223	success	2013-07-12 15:48:39.170	Activation successful.	NULL	NULL	NULL	2013-07-12 15:48:39.170	sa
    224	success	2013-07-12 15:48:44.170	Activation successful.	NULL	NULL	NULL	2013-07-12 15:48:44.170	sa
    225	success	2013-07-12 15:48:49.170	Activation successful.	NULL	NULL	NULL	2013-07-12 15:48:49.170	sa
    226	success	2013-07-12 15:48:54.173	Activation successful.	NULL	NULL	NULL	2013-07-12 15:48:54.173	sa
    227	success	2013-07-12 15:48:59.173	Activation successful.	NULL	NULL	NULL	2013-07-12 15:48:59.173	sa
    228	success	2013-07-12 15:49:04.170	Activation successful.	NULL	NULL	NULL	2013-07-12 15:49:04.170	sa
    229	success	2013-07-12 15:49:09.173	Activation successful.	NULL	NULL	NULL	2013-07-12 15:49:09.173	sa
    230	success	2013-07-12 15:49:14.177	Activation successful.	NULL	NULL	NULL	2013-07-12 15:49:14.177	sa
    231	success	2013-07-12 15:49:19.177	Activation successful.	NULL	NULL	NULL	2013-07-12 15:49:19.177	sa
    232	success	2013-07-12 15:49:24.177	Activation successful.	NULL	NULL	NULL	2013-07-12 15:49:24.177	sa
    233	success	2013-07-12 15:49:29.180	Activation successful.	NULL	NULL	NULL	2013-07-12 15:49:29.180	sa
    234	success	2013-07-12 15:49:34.180	Activation successful.	NULL	NULL	NULL	2013-07-12 15:49:34.180	sa
    235	success	2013-07-12 15:49:39.180	Activation successful.	NULL	NULL	NULL	2013-07-12 15:49:39.180	sa
    236	success	2013-07-12 15:49:44.180	Activation successful.	NULL	NULL	NULL	2013-07-12 15:49:44.180	sa
    237	success	2013-07-12 15:49:49.180	Activation successful.	NULL	NULL	NULL	2013-07-12 15:49:49.180	sa
    238	success	2013-07-12 15:49:54.180	Activation successful.	NULL	NULL	NULL	2013-07-12 15:49:54.180	sa
    239	success	2013-07-12 15:49:59.183	Activation successful.	NULL	NULL	NULL	2013-07-12 15:49:59.183	sa
    240	success	2013-07-12 15:50:04.180	Activation successful.	NULL	NULL	NULL	2013-07-12 15:50:04.180	sa
    241	success	2013-07-12 15:50:09.183	Activation successful.	NULL	NULL	NULL	2013-07-12 15:50:09.183	sa
    242	success	2013-07-12 15:50:14.183	Activation successful.	NULL	NULL	NULL	2013-07-12 15:50:14.183	sa
    243	success	2013-07-12 15:50:19.183	Activation successful.	NULL	NULL	NULL	2013-07-12 15:50:19.183	sa
    244	success	2013-07-12 15:50:24.183	Activation successful.	NULL	NULL	NULL	2013-07-12 15:50:24.183	sa
    245	success	2013-07-12 15:50:29.300	Activation successful.	NULL	NULL	NULL	2013-07-12 15:50:29.300	sa
    246	success	2013-07-12 15:50:34.187	Activation successful.	NULL	NULL	NULL	2013-07-12 15:50:34.187	sa
    247	success	2013-07-12 15:50:39.187	Activation successful.	NULL	NULL	NULL	2013-07-12 15:50:39.187	sa
    248	information	2013-07-12 15:50:39.437	DatabaseMail process is shutting down	4556	NULL	NULL	2013-07-12 15:50:39.437	NT Service\MSSQLSERVER


    Monday, July 15, 2013 6:10 PM
  • According to the DBMail log you posted, there are no errors.  This would mean, either the email was never queued or was sent to the SMTP server and not delivered.  Do those times corrospond to you running the stored proc?  Did you check the SMTP server log? 

    Monday, July 15, 2013 6:30 PM
  • As stated in my initial post, I can reproduce at will.  This is regular and 100% consistent behavior.

    If I send and email by just pasting a call to sp_send_dbmail into a query window, I get the "Mail (Id:##) queued." response and the Id goes up as expected and delivery follows shortly thereafter.

    When I invoke sp_send_dbmail from within my sproc (sp_order_import) I again get the "Mail (Id:##) queued." response and again, the Id increments as I would expect (see screen shot in original post).  The only difference is no delivery.

    I can bounce between these two methods, watching the Ids increment while only getting delivery when the call to sp_send_dbmail is invoked from a blank query window.

    I agree this makes no sense.  If it is a permissions problem with invoking sp_send_dbmail, I would not expect the system to report that the message is queued.  But once queued, I can't imagine how the SMTP server would have a problem with one and not the other.

    Monday, July 15, 2013 7:38 PM
  • As an aside, when I invoke sp_send_dbmail, I get the response the that message has been queued.

    Where can I look at this queue?

    Monday, July 15, 2013 7:46 PM
  • Run this query to see what is in the queue.

    select top 100
    *
    from msdb.dbo.sysmail_allitems  
    order by mailitem_id desc


    See: http://msdn.microsoft.com/en-us/library/ms190630(v=sql.105).aspx
    Monday, July 15, 2013 7:47 PM
  • Here it is.  It is clear now that although SQL Server is telling me that these messages are getting queued, in fact, they are not. 

    If you look at my original post, it clearly shows Id 30 being queued and there is no Id 30 in the table below.

    mailitem_id	profile_id	recipients	copy_recipients	blind_copy_recipients	subject	body	body_format	importance	sensitivity	file_attachments	attachment_encoding	query	execute_query_database	attach_query_result_as_file	query_result_header	query_result_width	query_result_separator	exclude_query_output	append_query_error	send_request_date	send_request_user	sent_account_id	sent_status	sent_date	last_mod_date	last_mod_user
    29	1	stevenjamesfrank@gmail.com	NULL	NULL	Test	This email is to notify you that order XXXXXXXX has errored out.	TEXT	NORMAL	NORMAL	NULL	MIME	NULL	NULL	0	1	256	 	0	0	2013-07-12 15:20:15.730	AMAZONA-1IUH76P\Administrator	1	sent	2013-07-12 15:20:15.000	2013-07-12 15:20:16.000	sa
    27	1	stevenjamesfrank@gmail.com	NULL	NULL	Test	This email is to notify you that order XXXXXXXX has errored out.	TEXT	NORMAL	NORMAL	NULL	MIME	NULL	NULL	0	1	256	 	0	0	2013-07-12 15:13:13.780	AMAZONA-1IUH76P\Administrator	1	sent	2013-07-12 15:13:14.000	2013-07-12 15:13:14.007	sa
    26	1	stevenjamesfrank@gmail.com	NULL	NULL	Test	This email is to notify you that order XXXXXXXX has errored out.	TEXT	NORMAL	NORMAL	NULL	MIME	NULL	NULL	0	1	256	 	0	0	2013-07-12 15:10:33.150	AMAZONA-1IUH76P\Administrator	1	sent	2013-07-12 15:10:33.000	2013-07-12 15:10:33.390	sa
    24	1	stevenjamesfrank@gmail.com	NULL	NULL	Test	This email is to notify you that order XXXXXXXX has errored out.	TEXT	NORMAL	NORMAL	NULL	MIME	NULL	NULL	0	1	256	 	0	0	2013-07-12 15:07:01.727	AMAZONA-1IUH76P\Administrator	1	sent	2013-07-12 15:07:01.000	2013-07-12 15:07:02.000	sa
    21	1	stevenjamesfrank@gmail.com	NULL	NULL	Test	This email is to notify you that order XXXXXXXX has errored out.	TEXT	NORMAL	NORMAL	NULL	MIME	NULL	NULL	0	1	256	 	0	0	2013-07-12 15:01:25.020	AMAZONA-1IUH76P\Administrator	1	sent	2013-07-12 15:01:25.000	2013-07-12 15:01:25.357	sa
    17	1	stevenjamesfrank@gmail.com	NULL	NULL	Test	This email is to notify you that order XXXXXXXX has errored out.	TEXT	NORMAL	NORMAL	NULL	MIME	NULL	NULL	0	1	256	 	0	0	2013-07-12 14:55:35.050	AMAZONA-1IUH76P\Administrator	1	sent	2013-07-12 14:55:35.000	2013-07-12 14:55:35.290	sa
    15	1	stevenjamesfrank@gmail.com	NULL	NULL	Test	This email is to notify you that order XXXXXXXX has errored out.	TEXT	NORMAL	NORMAL	NULL	MIME	NULL	NULL	0	1	256	 	0	0	2013-07-12 14:53:11.350	AMAZONA-1IUH76P\Administrator	1	sent	2013-07-12 14:53:12.000	2013-07-12 14:53:12.463	sa
    3	1	stevenjamesfrank@gmail.com	NULL	NULL	Order XXXXXXXX awaiting processing	This email is to notify you that your order, XXXXXXXX, has been received and is awaiting further processing by Share Corporation	TEXT	NORMAL	NORMAL	NULL	MIME	NULL	NULL	0	1	256	 	0	0	2013-07-12 14:27:02.950	AMAZONA-1IUH76P\Administrator	1	sent	2013-07-12 14:27:04.000	2013-07-12 14:27:04.597	sa
    2	1	ahayes@digicorp-inc.com	NULL	NULL	Database Mail Test	Please confirm your receipt to my email address - Steve
    
    This is a test e-mail sent from Database Mail on AMAZONA-1IUH76P.	TEXT	NORMAL	NORMAL	NULL	MIME	NULL	NULL	0	1	256	 	0	0	2013-07-10 13:18:57.410	AMAZONA-1IUH76P\Administrator	1	sent	2013-07-10 13:18:57.000	2013-07-10 13:18:57.493	sa
    1	1	stevenjamesfrank@gmail.com	NULL	NULL	Database Mail Test	This is a test e-mail sent from Database Mail on AMAZONA-1IUH76P.	TEXT	NORMAL	NORMAL	NULL	MIME	NULL	NULL	0	1	256	 	0	0	2013-07-10 13:17:32.907	AMAZONA-1IUH76P\Administrator	1	sent	2013-07-10 13:17:34.000	2013-07-10 13:17:35.043	sa

    Monday, July 15, 2013 8:05 PM
  • The table msdb.dbo.sysmail_allitems   is the message queue.

    My guess is your process is in a transaction and the transaction is failing and rolling back, which also rolls back the send mail process.

    Monday, July 15, 2013 8:31 PM
  • I don't think that's the case, I am not using transactions.

    However, something has changed.  My messages Ids seem to have reset to 1000 and things appear to be actually working.

    Perhaps there was some configuration glitch?  Perhaps I'm just an idiot, who knows.

    Monday, July 15, 2013 8:48 PM
  • That is a symptom of the server restarting and using SQL 2012 RTM.

    Please post the results of SELECT @@VERISON

    Monday, July 15, 2013 8:58 PM
  • Here is the value of @@version:

    Microsoft SQL Server 2012 - 11.0.2332.0 (X64)

    Aug 22 2012 17:05:49

    Copyright (c) Microsoft Corporation

    Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

    Monday, July 15, 2013 9:04 PM