Thursday, 21 July 2011

T-SQL: Service Broker to alert errors

I read this interesting blog post by Jamie Thomson a few weeks back and it inspired me to write up this article.

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

No comments:

Post a comment

/* add this crazy stuff in so i can use syntax highlighter