-- Включаем Service Broker
-- alter database db2_log set enable_broker with rollback immediate; 

use DB2_LOG
go

if exists (select 1 from sysobjects where id = object_id('SendLogMessage'         ) and type in ('P','PC')) drop procedure SendLogMessage
if exists (select 1 from sysobjects where id = object_id('AsyncLogQueueActivation') and type in ('P','PC')) drop procedure AsyncLogQueueActivation
if exists (select 1 from sysobjects where id = object_id('XmlMessageToTable'      ) and type in ('P','PC')) drop procedure XmlMessageToTable
if exists (select 1 from sysobjects where id = object_id('SimpleAuditData'        ) and type in ('U'     )) drop table SimpleAuditData
if exists (select 1 from sys.services                  where name = 'AsyncLogService'    ) drop service AsyncLogService
if exists (select 1 from sys.service_queues            where name = 'AsyncLogQueue'      ) drop queue AsyncLogQueue;
if exists(select * from sys.server_event_notifications where name = 'asyncLogEventNotify') drop event notification asyncLogEventNotify on server
-------------------------------------------------
go

-- Создаем очередь и сервисы

if object_id('AsyncLogQueueActivation', 'P') is null
 exec('create procedure AsyncLogQueueActivation as begin set nocount on; end;');
go

create queue AsyncLogQueue with
 status = on,
 retention = off,
 activation (status = on, procedure_name = AsyncLogQueueActivation, max_queue_readers = 5, execute as owner),
 poison_message_handling (status = on);
go

create service AsyncLogService authorization dbo  
on queue AsyncLogQueue (
    [http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]
);

go
create table SimpleAuditData
(
 ad_id         int identity,
 ad_OccurredAt datetime not null,
 ad_Login       sysname,
 ad_Application nvarchar(128),
 ad_Host        nvarchar(128),
 ad_ShortText   nvarchar(128),
 ad_LongText    nvarchar(4000),
 Stamp          timestamp,
 constraint PK_SimpleAuditData primary key (ad_id)
);
go

-- ХП которая разбирает XML и пишет его в таблицу
create procedure XmlMessageToTable @message xml
as
begin
    with s as
    (
            select
              n.value('EventType[1]'       , 'sysname'        ) as EventType
            , n.value('PostTime[1]'        , 'datetime'       ) as PostTime
            , n.value('TextData[1]'        , 'sysname'        ) as TextData
            , n.value('BinaryData[1]'      , 'varbinary(8000)') as BinaryData
            , n.value('DatabaseID[1]'      , 'int'            ) as DatabaseID
            , n.value('DatabaseName[1]'    , 'sysname'        ) as DatabaseName
            , n.value('ApplicationName[1]' , 'sysname'        ) as ApplicationName
            , n.value('HostName[1]'        , 'sysname'        ) as HostName
            , n.value('SessionLoginName[1]', 'sysname'        ) as SessionLoginName
            from
            @message.nodes('/EVENT_INSTANCE') x(n)
    )
    insert into SimpleAuditData
    (ad_OccurredAt, ad_Login, ad_Application, ad_Host, ad_ShortText, ad_LongText)
    select
        s.PostTime, s.SessionLoginName, s.ApplicationName, s.HostName, s.TextData, cast(s.BinaryData as nvarchar(4000))
    from s 
    --where DatabaseID = db_id();
end

-- Процедура которая будет принимать сообщения и обрабатывать их
go
alter procedure AsyncLogQueueActivation with execute as owner
as
begin
    set nocount on;

    declare @handle uniqueidentifier;
    declare @message xml;
    declare @message_type sysname;
 
    while (1=1)
    begin
        begin transaction;
 
            waitfor
            (
              receive top (1)
                @handle = conversation_handle,
                @message =  CAST(message_body AS XML),
                @message_type = message_type_name
              from AsyncLogQueue
            ), timeout 500;
 
            if (@@rowcount = 0)
            begin
              rollback transaction;
              print 'Error - нет данных в очереди'
              break;
            end
            if @message_type = N'http://schemas.microsoft.com/SQL/Notifications/EventNotification'
            begin
                exec XmlMessageToTable @message
            end
            else if @message_type = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
            begin
                end conversation @handle with cleanup;
            end
            else if @message_type = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
            begin
                --TODO
                --log errors
                end conversation @handle with cleanup;
            end 
        commit transaction;
    end
end
go

-- привязываем пользовательское событие к службе
create event notification asyncLogEventNotify
    on server
    for USERCONFIGURABLE_0 -- event_id = 82
    to service 'AsyncLogService', 'current database';
go

-- Процедура общего вида, с помощью которой можно отправлять сообщения
go
create procedure SendLogMessage @logMsg nvarchar(128), @logData nvarchar(1024)
as
begin
    set nocount on;
    -- sp_trace_generateevent 
    --      [     @eventid  = ]  event_id   
    --      [ , [ @userinfo = ] 'user_info' ]  
    --      [ , [ @userdata = ]  user_data  ]  
    -- event_id - numbers from 82 through 91
    declare @b varbinary(8000) = cast(@logData as varbinary(8000));
    exec sp_trace_generateevent 82, @logMsg, @b;
end
go


exec  SendLogMessage 'message1', 'data1'
exec  SendLogMessage 'message2', 'data2'
exec  SendLogMessage 'message3', 'data3'
exec  SendLogMessage 'message4', 'data4'
waitfor delay '00:00:01'
select * from SimpleAuditData