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.
Please refer to Create a User-Defined Event
By default, user-defined messages of severity lower than 19 are not sent to the Microsoft Windows application log when they occur. User-defined messages of severity lower than 19 therefore do not trigger SQL Server Agent alerts.
So your message should be
EXEC sp_addmessage @msgnum = 50005,
@severity = 1, -- Informational messages that return status information or report errors that are not severe. The Database Engine does not raise system errors with severities of 0 through 9.
@msgtext = N'%s';
GO
When you use THROW
, it defaults to severity 16 which does not fire the alert.
RAISERROR (50005, -- Message id.
1, -- Severity,
1, -- State,
N'My custom message') with log;
USE [msdb]
GO
/****** Object: Alert [Alert DBA on custom errors] Script Date: 1/22/2016 3:17:22 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N'Alert DBA on custom errors',
@message_id=50005,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=0,
@database_name=N'AdventureWorks2012_test',
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
Also, make sure you use @delay_between_responses
to a sensible value so that your inbox is not flooded with emails.
Also, refer to my answer for Monitoring free space in SQL Server data files using user defined message and alerts.
Best Answer
To get the changed records for a table and information about these changes, use the
CHANGETABLE(CHANGES…)
function.Drawback: is that you have to query the tables periodically and then send the notification email
msdb.dbo.sp_send_dbmail
should be used to send an email alert to a specified email recipient, immediately:e.g.
As triggers are fired per batch, not per row, it should be ensured that all batch transactions are captured, and no data is lost.
Therefore as mentioned above both the above solutions are very much helpful, but depends upon how you want to use them and get benefited.
In addition, please read Tracking Data Changes from MSDN and Performance Tuning SQL Server Change Tracking
The method which i have been using for such scenarios is analyzing the changes from default trace file which tracks object level changes using the below query as explained in this great article:The default trace in SQL Server - the power of performance and security auditing