2

Is there an elegant way for sending e-mails using a Service Broker service/queue?

I want to setup an EVENT NOTIFICATION for SQL Server, and I'd like to send e-mails to operators for each message on the queue.

According to the book SQL Server 2005 DBA Street Smarts: A Real World Guide to SQL Server 2005 Certification Skills, Database Mail uses the Service Broker:

Additionaly, Database Mail is scalable because it uses the Service Broker queue, which allows the requests to be handled asynchronously and even saves the request if the server goes down before it can be handled.

Maybe I'm too off base here, but, is there a way I can send messages directly to the Database Mail queue using Service Broker?

If I have to set up a stored procedure for sending an e-mail on behalf of Service Broker, I think it should be easier to just setup a job on the SQL Server Agent to check for the events I'm interested in inside the error log and send the operators an e-mail.

I'm open to suggestions.

Improve this question

1 Answer 1

Reset to default
3

is there a way I can send messages directly to the Database Mail queue using Service Broker

When you invoke sp_send_dbmail that is exactly what is happening.

I want to setup an EVENT NOTIFICATION for SQL Server, and I'd like to send e-mails to operators for each message on the queue

Then do just that. Create an activated stored procedure on the queue of your service that receives the notifications and have it RECEIVE the message and invokes sp_send_dbmail.

I think you are trying to 'cut out the middleman' and have the EVENT NOTIFICATION send the notification directly to the DB Mail queue. That is not possible because the code that handles the messages arriving in the DB Mail queue expects the messages to have a certain format (ie. the one created by sp_send_dbmail). The messages sent by Event Notifications would have a completely different format and the DB Mail processor would choke as it does not understand them. In order to prevent exactly such situation Service Broker services are bound to specific contracts and DB Mail service does not accept the http://schemas.microsoft.com/SQL/Notifications/PostEventNotification contract used by Event Notifications.

Improve this answer
3
  • Yes, indeed I was thinking of something like that. In fact, I was under the false impression that Service Broker offered some functionality like that (directly sending e-mails using Database Mail). Well, I guess Amazon AWS (SNS in particular) is making me too comfortable. Thank you for your answer ;)
    – ivanmp
    Mar 26, 2012 at 22:53
  • Nothing prevents you from writing your queue processing code that does that: RECEIVE from queue, place a call to SNS::Publish, rinse cycle and repeat. Transactional semantics though are difficult to achieve.
    – Remus Rusanu
    Mar 26, 2012 at 23:02
  • Sure, nothing prevents me from doing anything once I have to write the glue processing code I was looking to avoid writing. But hey, looks like there's no alternative.
    – ivanmp
    Mar 27, 2012 at 12:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.