Enterprise Application Logging 
by Jim Maglione

Example 1:

<ROOT>
   <CentralLogMessage>
      <Application_Name>APP_TEST</Application_Name> 
      <Severity_Cd>INFO</Severity_Cd>
      <MessageDateTime>1/1/2005 12:12:12</MessageDateTime>
      <Message>This is an INFORMATIONAL TEST message!</Message>
   </CentralLogMessage>
</ROOT>


Example 2:

CREATE MESSAGE TYPE [LogRequest] VALIDATION = 
   WELL_FORMED_XML
CREATE CONTRACT [LogServiceContract] 
  AUTHORIZATION [dbo] ([LogRequest] SENT BY ANY)
CREATE QUEUE [dbo].[CentralLogQueue]
CREATE SERVICE [AppEventLogService]  
  AUTHORIZATION [dbo]  
  ON QUEUE [AppEventDestinationQueue] 
    ([LogServiceContract])



Listing One
-- This procedure will receive an XML log record and submit to the Central
-- Queue via the Log Service.

CREATE PROCEDURE [dbo].[SPLogPublisher]
     @msgXML [nvarchar](max)
WITH EXECUTE AS CALLER
AS
declare @msgHandle  uniqueidentifier

begin transaction

begin dialog conversation @msgHandle
     from service [LogService]     to service 'LogService'
     on contract [LogServiceContract];
send on conversation @msgHandle message type [LogRequest] (@msgXML)
end conversation @msgHandle
commit
Listing Two
    CREATE PROCEDURE [dbo].[SPRouter]
WITH EXECUTE AS CALLER
AS
-- This is a Service Program that will receive Log Messages as xml from the
-- Central Queue
-- (LogService) and route them depending on the Log_Configuration metadata to
-- the appropriate
-- Destination Queues (App, DB and/or Email Services).
--
-- Workflow:
--   a) receive an xml log message from the CentralLogQueue
--   b) for each  destination_id that is setup in the Log Configuration for the
--      Application_Name and Severity_Cd tags in the message log, post the
--      xml log message to that destination queue.
--
declare @conversation_handle         uniqueidentifier
declare @message_body                nvarchar(MAX)
declare @message_type_name           sysname

declare @xml_doc_handle  int; -- used by XPath to create a doc in memory
declare @xml_doc         varchar(MAX); -- receives xml doc from the
sp_preparedoc call

-- database ID for these services (assume they all exist in same database)
declare @LogServiceGUID uniqueidentifier;

-- store the results of this xml log into a temp table for querying to find
-- destinations
declare @xml_log_app_and_severity table ( application_name varchar(32),
                                          severity_cd varchar(32) )

-- will store each destination and service that this log is going to goto
declare @destinations table ( destination_name varchar(32),
                              service_name varchar(32) )

-- used for working on individual rows from the destinations table above
declare @destination varchar(32)
declare @service varchar(32)
declare @msgHandle  uniqueidentifier

-- cache this for use when publishing the messages to destination queues
SELECT @LogServiceGUID = service_broker_guid
FROM sys.databases
WHERE database_id = DB_ID('LogService');

-- Keep this alive. Else the broker will call this procedure for every message.
while ( 1=1 )
begin

         -- receive the next message off the queue
         waitfor
         (
                  receive top(1)
                           @message_body=message_body,
                           @message_type_name=message_type_name,
                           @conversation_handle=conversation_handle
                  from [CentralLogQueue]
         )

         -- insure the message is of the proper type (should ALWAYS be)
         if @message_type_name = 'LogRequest'
         begin

                  -- setup an internal representation of this XML doc
                  exec sp_xml_preparedocument @xml_doc_handle output, @message_body

                  -- get the destination names based on application and severity of this log
                  -- and load into temp destination table
                     insert into @xml_log_app_and_severity
                     select application_name, severity_cd
                     from OpenXML(@xml_doc_handle, '/ROOT/CentralLogMessage',2)
                     with (Application_Name varchar(64),
                                    Severity_Cd varchar(32))

                  -- lookup the destination(s) via the configuration
                  insert into @destinations
                  select d.destination_name, d.service_name
                  from destination d, central_log_configuration clc,
                           application a,
                           severity s,
                           @xml_log_app_and_severity x
                  where x.application_name = a.application_name and
                           x.severity_cd = s.severity_cd and
                           clc.application_id = a.application_id and
                           clc.severity_id = s.severity_id and
                           clc.destination_id = d.destination_id and
                           a.logging_level >= s.severity_id

             -- publish the log to each destination by iterating through the table
             declare cDestinations cursor fast_forward for select * from @destinations
                  open cDestinations
                  fetch next from cDestinations into @destination, @service

                  while @@fetch_status = 0
                  begin

                           -- publish message to designated service
                           -- @destination name is only needed for debugging purposes
                           begin transaction
                           begin dialog conversation @msgHandle
                                    from service @service
                                    to service @service, @LogServiceGUID
                                    on contract [LogServiceContract];
                           send on conversation @msgHandle message type [LogRequest]
                                    (@message_body)
                           end conversation @msgHandle
                           commit transaction

                           -- get the next row (service) to publish this message too
                           fetch next from cDestinations into @destination, @service

                  end
                  close cDestinations
                  deallocate cDestinations

                  -- clean up temp tables
                  delete from @xml_log_app_and_severity
                  delete from @destinations

                  -- clean up
                  exec sp_xml_removedocument @xml_doc_handle

         end
         else
         begin
         -- wrong type of message, or an error occured.
         print N'Central Logging: Invalid message type received for DIALOG HANDLE: ' +
             cast( @conversation_handle as varchar(256) ) + N' XML MESSAGE: ' +
                           cast( cast( @message_body as XML ) as varchar(MAX));
         end

end -- loop

