January 21, 2019 at 11:28 am
Hopefully this is an easy question.
In my scenario, I want some events to trigger an e-mail to someone. I have a table of tasks, and when I assign the task, I want to send an e-mail using send_dbMail, but in such a way that the e-mail only has to be queued (instead of sent) before the stored procedure completes. Can I do this with an update trigger? (How would I test this if I'm not on a domain with an e-mail server available?)
If I were using a trigger, I'd just resolve the recipient's ID in the database to his e-mail address and just stuff that into the To... that's trivial.. the part I'm interested in is not having a dead e-mail server stopping the insert of records in my database. Can I just use Service Broker to handle this? (any simple references on SB?)
I read this article, but it doesn't say if you can force dbMail to just queue the message and then carry on..
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql?view=sql-server-2017
Thanks,
Pieter
January 21, 2019 at 11:55 am
pietlinden - Monday, January 21, 2019 11:28 AMHopefully this is an easy question.
In my scenario, I want some events to trigger an e-mail to someone. I have a table of tasks, and when I assign the task, I want to send an e-mail using send_dbMail, but in such a way that the e-mail only has to be queued (instead of sent) before the stored procedure completes. Can I do this with an update trigger? (How would I test this if I'm not on a domain with an e-mail server available?)
If I were using a trigger, I'd just resolve the recipient's ID in the database to his e-mail address and just stuff that into the To... that's trivial.. the part I'm interested in is not having a dead e-mail server stopping the insert of records in my database. Can I just use Service Broker to handle this? (any simple references on SB?)
I read this article, but it doesn't say if you can force dbMail to just queue the message and then carry on..
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql?view=sql-server-2017
Thanks,
Pieter
sp_send_dbmail will send right away so if you want things queued, you'd need to do something on your own.
It may be simpler to have a table for Task notifications and insert into that table, use that for email notifications, poll the table however often, etc.
Sue
January 21, 2019 at 12:13 pm
sp_send_dbmail is asynchronous. the whole process is already a service broker.
it returns success and an mail_od immediately without actually sending anything, and puts it in a queue.
the service broker behind the scenes then sends/tries to send the email.
it would only error if database mail was not set up correctly, or you selected an invalid profile or something.
Lowell
January 21, 2019 at 5:28 pm
Oh, okay. That's what I was wondering. Thanks for the explanation. I was reading the documentation and it wasn't totally clear.
Thanks, Lowell (and everybody). That answers my question.
January 21, 2019 at 11:23 pm
Sue_H - Monday, January 21, 2019 11:55 AMpietlinden - Monday, January 21, 2019 11:28 AMHopefully this is an easy question.
In my scenario, I want some events to trigger an e-mail to someone. I have a table of tasks, and when I assign the task, I want to send an e-mail using send_dbMail, but in such a way that the e-mail only has to be queued (instead of sent) before the stored procedure completes. Can I do this with an update trigger? (How would I test this if I'm not on a domain with an e-mail server available?)
If I were using a trigger, I'd just resolve the recipient's ID in the database to his e-mail address and just stuff that into the To... that's trivial.. the part I'm interested in is not having a dead e-mail server stopping the insert of records in my database. Can I just use Service Broker to handle this? (any simple references on SB?)
I read this article, but it doesn't say if you can force dbMail to just queue the message and then carry on..
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql?view=sql-server-2017
Thanks,
Pietersp_send_dbmail will send right away so if you want things queued, you'd need to do something on your own.
It may be simpler to have a table for Task notifications and insert into that table, use that for email notifications, poll the table however often, etc.Sue
Maybe its a setup issue but that's not what what I've seen. I always get a "message queued" notification. Of course,unless you're going through something like O365, the message arrives almost instantly but that doesn't mean it hasn't seen the queue. And, no, I didn't actually have to do anything special to get it to queue. It seems to be the default or I'm just damned lucky. 😀
{EDIT} ... and now that I've scrolled down (I've got to remember to do that), I see Lowell pretty much confirmed that with some added information.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 22, 2019 at 3:14 am
For me (with 2008 R2) it queues the message (and this makes logical sense to me).
Anyway, when you call send_dbMail, if it all is basically OK you get a mailitem_id back.
If you care if it was successful, you then need to check, afterwards and asynchronously, values for msdb.dbo.sysmail_mailitems.sent_status when it finally becomes 1 or 2. Values have these meanings:
SELECT
CASE sent_status
WHEN 0 THEN 'Unsent'
WHEN 1 THEN 'Sent'
WHEN 2 THEN 'Failed'
WHEN 3 THEN 'Retrying'
END AS sent_status_desc
FROM msdb..sysmail_mailitems
WHERE ...
You will also see fields send_request_date and sent_date (which have a time component), which are different often by several seconds and occasionally several minutes.
Look at https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2012/ms175056(v=sql.110).
Our version of O365 has a limit of 30 messages per minute (after which there is a failure), so you may end up needing to do your own queueing.
Mike
January 22, 2019 at 6:45 am
Jeff Moden - Monday, January 21, 2019 11:23 PMSue_H - Monday, January 21, 2019 11:55 AMpietlinden - Monday, January 21, 2019 11:28 AMHopefully this is an easy question.
In my scenario, I want some events to trigger an e-mail to someone. I have a table of tasks, and when I assign the task, I want to send an e-mail using send_dbMail, but in such a way that the e-mail only has to be queued (instead of sent) before the stored procedure completes. Can I do this with an update trigger? (How would I test this if I'm not on a domain with an e-mail server available?)
If I were using a trigger, I'd just resolve the recipient's ID in the database to his e-mail address and just stuff that into the To... that's trivial.. the part I'm interested in is not having a dead e-mail server stopping the insert of records in my database. Can I just use Service Broker to handle this? (any simple references on SB?)
I read this article, but it doesn't say if you can force dbMail to just queue the message and then carry on..
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql?view=sql-server-2017
Thanks,
Pietersp_send_dbmail will send right away so if you want things queued, you'd need to do something on your own.
It may be simpler to have a table for Task notifications and insert into that table, use that for email notifications, poll the table however often, etc.Sue
Maybe its a setup issue but that's not what what I've seen. I always get a "message queued" notification. Of course,unless you're going through something like O365, the message arrives almost instantly but that doesn't mean it hasn't seen the queue. And, no, I didn't actually have to do anything special to get it to queue. It seems to be the default or I'm just damned lucky. 😀
{EDIT} ... and now that I've scrolled down (I've got to remember to do that), I see Lowell pretty much confirmed that with some added information.
I didn't word my response well - it's not sitting in a queue that can be controlled and not really sitting much at all. The post request was to have it sit in a queue and not send. I merely mentioned it would send right away. It's not a queue you can do anything with even if you get a message of "mail queued" That's just the message when the broker is initiated.
Sorry about any confusion.
January 22, 2019 at 7:01 am
Sue_H - Tuesday, January 22, 2019 6:45 AMJeff Moden - Monday, January 21, 2019 11:23 PMSue_H - Monday, January 21, 2019 11:55 AMpietlinden - Monday, January 21, 2019 11:28 AMHopefully this is an easy question.
In my scenario, I want some events to trigger an e-mail to someone. I have a table of tasks, and when I assign the task, I want to send an e-mail using send_dbMail, but in such a way that the e-mail only has to be queued (instead of sent) before the stored procedure completes. Can I do this with an update trigger? (How would I test this if I'm not on a domain with an e-mail server available?)
If I were using a trigger, I'd just resolve the recipient's ID in the database to his e-mail address and just stuff that into the To... that's trivial.. the part I'm interested in is not having a dead e-mail server stopping the insert of records in my database. Can I just use Service Broker to handle this? (any simple references on SB?)
I read this article, but it doesn't say if you can force dbMail to just queue the message and then carry on..
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql?view=sql-server-2017
Thanks,
Pietersp_send_dbmail will send right away so if you want things queued, you'd need to do something on your own.
It may be simpler to have a table for Task notifications and insert into that table, use that for email notifications, poll the table however often, etc.Sue
Maybe its a setup issue but that's not what what I've seen. I always get a "message queued" notification. Of course,unless you're going through something like O365, the message arrives almost instantly but that doesn't mean it hasn't seen the queue. And, no, I didn't actually have to do anything special to get it to queue. It seems to be the default or I'm just damned lucky. 😀
{EDIT} ... and now that I've scrolled down (I've got to remember to do that), I see Lowell pretty much confirmed that with some added information.
I didn't word my response well - it's not sitting in a queue that can be controlled and not really sitting much at all. The post request was to have it sit in a queue and not send. I merely mentioned it would send right away. It's not a queue you can do anything with even if you get a message of "mail queued" That's just the message when the broker is initiated.
Sorry about any confusion.
And that wording is incorrect - I should have said when the conversation is initiated not when broker is initiated.
I was just responding to the "queue and not send" aspect of the stored procedure mentioned.
But I'm done - I'll leave it to everyone else to come up with how to not send it since it's been queued 🙂
January 22, 2019 at 11:09 am
pietlinden - Monday, January 21, 2019 11:28 AMHopefully this is an easy question.
In my scenario, I want some events to trigger an e-mail to someone. I have a table of tasks, and when I assign the task, I want to send an e-mail using send_dbMail, but in such a way that the e-mail only has to be queued (instead of sent) before the stored procedure completes. Can I do this with an update trigger? (How would I test this if I'm not on a domain with an e-mail server available?)
If I were using a trigger, I'd just resolve the recipient's ID in the database to his e-mail address and just stuff that into the To... that's trivial.. the part I'm interested in is not having a dead e-mail server stopping the insert of records in my database. Can I just use Service Broker to handle this? (any simple references on SB?)
I read this article, but it doesn't say if you can force dbMail to just queue the message and then carry on..
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql?view=sql-server-2017
Thanks,
Pieter
In order to format your mail(s) and 'hold' them until you are ready to send them (at the end of your stored procedure), you can do your own queuing by creating a MAIL_QUEUE table and insert a record for every piece of mail that needs to be sent. Then process the MAIL_QUEUE table with a cursor at the end of your stored procedure to execute sp_send_dbmail. So, even if sp_send_dbmail will 'hold' your mail request for you, you will eventually need to tell it to release the mail, right? Isn't that what you are trying to achieve?
January 22, 2019 at 7:28 pm
no. my requirement was super simple. Essentially, when an employee is assigned a task/drug protocol/whatever, send him an e-mail advising him of it. I had asked about this maybe pre-Service Broker where if you included the stored procedure to send mail, it could cause the insert to fail (could be wrong, but that's what I remember from the conversation.)
January 22, 2019 at 9:15 pm
Sue_H - Tuesday, January 22, 2019 6:45 AMJeff Moden - Monday, January 21, 2019 11:23 PMSue_H - Monday, January 21, 2019 11:55 AMpietlinden - Monday, January 21, 2019 11:28 AMHopefully this is an easy question.
In my scenario, I want some events to trigger an e-mail to someone. I have a table of tasks, and when I assign the task, I want to send an e-mail using send_dbMail, but in such a way that the e-mail only has to be queued (instead of sent) before the stored procedure completes. Can I do this with an update trigger? (How would I test this if I'm not on a domain with an e-mail server available?)
If I were using a trigger, I'd just resolve the recipient's ID in the database to his e-mail address and just stuff that into the To... that's trivial.. the part I'm interested in is not having a dead e-mail server stopping the insert of records in my database. Can I just use Service Broker to handle this? (any simple references on SB?)
I read this article, but it doesn't say if you can force dbMail to just queue the message and then carry on..
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql?view=sql-server-2017
Thanks,
Pietersp_send_dbmail will send right away so if you want things queued, you'd need to do something on your own.
It may be simpler to have a table for Task notifications and insert into that table, use that for email notifications, poll the table however often, etc.Sue
Maybe its a setup issue but that's not what what I've seen. I always get a "message queued" notification. Of course,unless you're going through something like O365, the message arrives almost instantly but that doesn't mean it hasn't seen the queue. And, no, I didn't actually have to do anything special to get it to queue. It seems to be the default or I'm just damned lucky. 😀
{EDIT} ... and now that I've scrolled down (I've got to remember to do that), I see Lowell pretty much confirmed that with some added information.
I didn't word my response well - it's not sitting in a queue that can be controlled and not really sitting much at all. The post request was to have it sit in a queue and not send. I merely mentioned it would send right away. It's not a queue you can do anything with even if you get a message of "mail queued" That's just the message when the broker is initiated.
Sorry about any confusion.
Ah... now I get what you were saying. Thanks for the feedback, Sue.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 23, 2019 at 4:22 pm
pietlinden - Tuesday, January 22, 2019 7:28 PMno. my requirement was super simple. Essentially, when an employee is assigned a task/drug protocol/whatever, send him an e-mail advising him of it. I had asked about this maybe pre-Service Broker where if you included the stored procedure to send mail, it could cause the insert to fail (could be wrong, but that's what I remember from the conversation.)
That was true with SQL Mail (which was before Database Mail). No longer the case with Database Mail
There was an obnoxious benefit though in that you would know the mail failed. In your case, you may want to think about how you are going to track if the mail does fail though. I wouldn't be a happy employee if I was assigned a whatever task but was never notified. That's what I was thinking when I mentioned another table for notifications. There are lots of other options or approaches...just a thought.
Sue
January 23, 2019 at 11:15 pm
I think the following link may have the answers to all of this...
https://docs.microsoft.com/en-us/sql/relational-databases/database-mail/database-mail-log-and-audits?view=sql-server-2017
--Jeff Moden
Change is inevitable... Change for the better is not.
January 24, 2019 at 12:04 am
I'll have a read. From a quick scan, I'd basically have to use a unique subject and search for that and the recipient's e-mail in the table, and if not found, the mail went through.
January 24, 2019 at 1:12 am
db_send_dbmail returns a mailitem_id immediately (the email is queued to be sent later). Use that to read msdb.dbo.sysmail_mailitems and check sent_status at a later time (values 0-3 in my previous posting), rather than worrying about a unique subject. On our system it usually sends (or fails) within a second or two, but when there are problems can take up to a couple of minutes.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply