I've been "kicking the tyres" of Service Broker myself on and off over the past few months as its an area of SQL Server which I don't often seen used in anger. I've put together a sample here which demonstrates how you may use Service Broker to alert your DBAs to any application errors in the database using Database Mail (specifically dbo.sp_send_dbmail).
1) Error gets logged to an central application error table
2) The error is passed to Service Broker for delivery
3) Service Broker delivers error via email in a nice format.
/*
#################################################################
-- configure Service Broker Objects
#################################################################
*/
ALTER DATABASE GlobalDB
SET ENABLE_BROKER
WITH ROLLBACK IMMEDIATE
GO
USE GlobalDB
GO
-- initially a messagetype
CREATE MESSAGE TYPE [ApplicationError]
VALIDATION = NONE;
GO
-- and a contract to send messages of that type
CREATE CONTRACT [ApplicationErrorContract]
(
[ApplicationError] SENT BY INITIATOR
);
GO
-- a queue to send the messages to
CREATE QUEUE [ApplicationError_Snd_Q];
GO
-- and a service that manages that queue
CREATE SERVICE [ApplicationError_Snd_Svc]
ON QUEUE [ApplicationError_Snd_Q];
GO
-- we also need a queue to receive messages into
CREATE QUEUE [ApplicationError_Rcv_Q];
GO
-- and a service that manages that queue
CREATE SERVICE [ApplicationError_Rcv_Svc]
ON QUEUE [ApplicationError_Rcv_Q]([ApplicationErrorContract]);
GO
/*
#################################################################
-- we'll need to configure database mail here
-- to use this ASIS you'll need a default profile configured
#################################################################
*/
/*
#################################################################
-- configure database objects
#################################################################
*/
USE GlobalDB
GO
-- a generic table for holding error logs
CREATE TABLE [dbo].[tblErrorLog]
(
[LogID] [int] IDENTITY(1,1) NOT NULL,
[ErrorMessage] [nvarchar](2000) NULL,
[ErrorNumber] [int] NULL,
[ErrorSeverity] [int] NULL,
[ErrorState] [int] NULL,
[ErrorLine] [int] NULL,
[ErrorProcedure] [nvarchar](555) NULL,
[DatabaseName] [nvarchar](555) NULL,
[Date_Created] [datetime] NULL,
[Created_By] [nvarchar](555) NULL
)
GO
-- this function will take the error message from SB, parse it and return it in
-- HTML format for the send mail task
CREATE FUNCTION dbo.GetErrorEmailBody(@Msg XML)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @Errors TABLE (
LogID INT,
ErrorMessage NVARCHAR(1000),
ErrorProcedure NVARCHAR(1000),
Created_By NVARCHAR(1000),
Date_Created DATETIME
)
INSERT INTO @Errors
SELECT tbl.col.value('LogID[1]','int'),
tbl.col.value('ErrorMessage[1]','nvarchar(1000)'),
tbl.col.value('ErrorProcedure[1]','nvarchar(1000)'),
tbl.col.value('Created_By[1]','nvarchar(1000)'),
tbl.col.value('Date_Created[1]','datetime')
FROM @Msg.nodes('//Errors') AS tbl(col)
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H1>Application Errors</H1>' +
N'<table border="1">' +
N'<tr><th>Log ID</th><th>Error Message</th>' +
N'<th>Procedure</th><th>Date Created</th>' +
N'<th>Created By</th></tr>' +
CAST ( ( SELECT TOP 50 td = LogID, '',
td = ISNULL(ErrorMessage,''), '',
td = ISNULL(ErrorProcedure,''), '',
td = Date_Created, '',
td = Created_By, ''
FROM @Errors
ORDER BY Date_Created DESC
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
RETURN @tableHTML;
END
GO
-- a stored procedure to send data to the send queue
CREATE PROCEDURE dbo.usp_SB_Snd_ApplicationError
(
@LogID INT
)
AS
BEGIN
DECLARE @MessageBody XML
DECLARE @ConfigurationID INT
DECLARE @CalibrationID INT
SELECT @MessageBody = (
SELECT DISTINCT LogID, ErrorProcedure, ErrorNumber, ErrorMessage, Date_Created, Created_By
FROM dbo.tblErrorLog
WHERE LogID = @LogID
FOR XML PATH ('Errors'), TYPE
)
DECLARE @Handle UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @Handle
FROM SERVICE [ApplicationError_Snd_Svc] TO SERVICE 'ApplicationError_Rcv_Svc'
ON CONTRACT [ApplicationErrorContract]
WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @Handle
MESSAGE TYPE [ApplicationError](@MessageBody);
END
GO
-- this procedure will receive messages and process them
CREATE PROCEDURE dbo.usp_SB_Rcv_ApplicationErrorr
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Handle UNIQUEIDENTIFIER;
DECLARE @MessageType SYSNAME;
DECLARE @Message XML
DECLARE @BodyTable NVARCHAR(MAX);
RECEIVE TOP (1) @Handle = conversation_handle,
@MessageType = message_type_name,
@Message = message_body
FROM [ApplicationError_Rcv_Q];
IF(@Handle IS NOT NULL AND @Message IS NOT NULL)
BEGIN
-- parse the message xml to get the error details
-- build the HTML table for send_dbmail
SET @BodyTable = (SELECT dbo.GetErrorEmailBody(@Message));
-- send the mail on the default profile
EXEC msdb.dbo.sp_send_dbmail
@recipients='richard.brown@barrhibb.com',
@subject = 'Errors',
@body = @BodyTable,
@body_format = 'HTML' ;
END CONVERSATION @Handle;
END
END
GO
-- this procedure will receive messages and process them
CREATE PROCEDURE dbo.[usp_SB_Snd_ProcessResponses]
AS
DECLARE @conversation_handle UNIQUEIDENTIFIER
DECLARE @message_body XML
DECLARE @message_type_name SYSNAME
DECLARE @error_message NVARCHAR(3000)
WHILE 1 = 1
BEGIN
SET @message_type_name = NULL;
WAITFOR (
RECEIVE TOP (1)
@conversation_handle = conversation_handle,
@message_body = CAST(message_body AS XML),
@message_type_name = message_type_name
FROM dbo.[ApplicationError_Snd_Q]), TIMEOUT 1000;
IF @message_type_name IS NULL BREAK; --no more messages
IF @message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
END CONVERSATION @conversation_handle;
END
ELSE
BEGIN
IF @message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
BEGIN
-- should log expected errors here
END CONVERSATION @conversation_handle;
END
ELSE
BEGIN
--All other messages types are unexpected here - log error
SET @error_message = N'<Error>Unexpected message type</Error>';
END CONVERSATION @conversation_handle
WITH ERROR = 1
DESCRIPTION = @error_message;
END
END
END
GO
-- a generic logging procedure
CREATE PROCEDURE [dbo].[usp_LogError]
AS
DECLARE @LogID INT
INSERT INTO dbo.tblErrorLog
(
ErrorMessage, ErrorNumber, ErrorSeverity, ErrorState,
ErrorLine, ErrorProcedure, DatabaseName, Date_Created, Created_By
)
SELECT
ERROR_MESSAGE() AS ErrorMessage
,ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_LINE () AS ErrorLine
,ERROR_PROCEDURE() AS ErrorProcedure
,DB_NAME()
,GETDATE()
,SUSER_SNAME()
SET @LogID = SCOPE_IDENTITY();
EXEC dbo.usp_SB_Snd_ApplicationError @LogID;
GO
/*
#################################################################
-- active the queues
#################################################################
*/
-- lets active the queue
ALTER QUEUE [ApplicationError_Rcv_Q] WITH ACTIVATION
(
STATUS = ON,
MAX_QUEUE_READERS = 1,
PROCEDURE_NAME = dbo.usp_SB_Rcv_ApplicationError,
EXECUTE AS OWNER
);
GO
ALTER QUEUE [ApplicationError_Snd_Q] WITH ACTIVATION
(
STATUS = ON,
MAX_QUEUE_READERS = 1,
PROCEDURE_NAME = dbo.[usp_SB_Snd_ProcessResponses],
EXECUTE AS OWNER
);
GO
-- TEST --
CREATE PROCEDURE dbo.TestSB
AS
-- force an error
BEGIN TRY
SELECT 1/0
END TRY
BEGIN CATCH
EXEC dbo.usp_LogError
END CATCH
GO
EXEC dbo.TestSB
GO
