Geeking out at 36,000 feet

So, I’ve now spent the last couple of hours playing with Database Mail and HTML formatted messages being sent to the SQLskills subscribers. It’s been a fun learning experience as I think I’ve found a bug with the email account name length…let’s just put it this way – don’t be too descriptive with your account names.


Outside of that – it’s amazing how well queue based email works. The old mapi based mail would take a LONG time to complete the batch mail processing but now – with queue based mail it’s done in seconds.


The best part is that I’m on a flight over the Atlantic right now…on my way to Frankfurt. I think this is the MOST productive flight I’ve ever had!


Have a great weekend,
kt

3 thoughts on “Geeking out at 36,000 feet

  1. I just noticed that I haven’t received the newsletter, although registered….did something go wrong? ;)

    -Magne

  2. Hey there Magne – Well, I certainly learned a lot about database mail, the internet at 36,000 feet AND my SMTP mail server’s SPAM prevention techniques… now I know how to work through them all ;).

    So, in sending the mail messages they go to a sysmail_mailitems where you can see ALL messages. For each mail sent it is also logged to the Database Mail Log (which is part of the new unified log viewer under Management in SSMS). After all of the errors, I found the status of the mail was either 0 = has not yet been sent = sysmail_unsentitems, 1 = sent = sysmail_sentitems, 2 = failure = sysmail_faileditems and 3 = in some form of retrying state and these eventually became 1 or 2. When I originally sent the mail (from the plane), there were a lot timeouts that caused about half of the emails to fail. The good part is that sending the mail from the plane slowed them down quite a bit and as a result actually HELPED because when I tried to resend from my hotel in Geneva, I started having problems with the mail server shutting down (I believe this is due to SPAM prevention). But through all of this I was able to reselect the mail addresses (recipients) of the failed messages and re-run my batch processes from there (building in a delay between mail messages).

    So, in the end, I’m not sure you could do full blown batch processing (aka SPAM) with Database Mail unless you have a much more robust mail server (hmmm, I bet there are a few things I could do to the config of the SMTP mail host/gateway??) and a better connection (ok, the 36,000 feet thing was a bit ambitious). SQL Server seemed to do it’s part very well. I wonder if I should chat with Bob on the comparison(s) between Database Mail and Notification Services (something I really never got into all that much but Bob has a two day class on it).

    Regardless, thanks for subscribing and you should have that newsletter by now!

    Cheers,
    Kimberly

  3. Hi Kim

    here is the definition of the ‘sysmail_allitems’ that confirms your trial and error approach. I happened to be working on a similar issue in SQL2000 and was looking at these system tables in order to get some ideas…

    Cheers,

    Michel

    CREATE VIEW sysmail_allitems
    AS
    SELECT 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,
    CASE sent_status
    WHEN 0 THEN ‘unsent’
    WHEN 1 THEN ‘sent’
    WHEN 3 THEN ‘retrying’
    ELSE ‘failed’
    END as sent_status,
    sent_date,
    last_mod_date,
    last_mod_user
    FROM msdb.dbo.sysmail_mailitems
    WHERE (send_request_user = SUSER_SNAME()) OR (ISNULL(IS_SRVROLEMEMBER(N’sysadmin’), 0) = 1)

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.