Post reply

how to find when email is queued.

  • July 19, 2015 at 11:31 am

    #319110

    hi,

    want to send an email two ice a day, from database.

    so i have planned to make a storedproce which will be called by

    a job (which will select some record from one table and put it in other table based on a flag)

    but i want to run it in a transaction so that if email is send successfully

    then only it should commit else it should rollback.

    q1) how can i find that "Mail queued" now i should commit.

    q2) if there is any other way to solve the problem , then please tel me. (will ssis be better than above one)

    your sincerely

  • July 19, 2015 at 2:57 pm

    #1813552

    the modern database mail uses a service broker, so it's fire and forget; you call sp_send_dbmail with the right parameters, and the proc returns immediately, and the service broker tries to send the email asyncronously...it may be several minutes before it goes out, and the service tries to send three times if it fails the first two, by default, assuming the vagaries of networking and stuff made it fail.

    you would have to catch the returned mail_id, and then check the msdb.dbo.all_mailitems? to see if it really went through, or got an error returned back from the mail server.(no such emailbox, bad email address,box full, no relaying allowed, etc etc)

    why do you want to rollback vs trying to send an email regardless?

    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!

Viewing 2 posts - 1 through 1 (of 1 total)

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