-- Housekeeping
use master
drop event notification ServerEventNotification on server
go
drop database en_test
create database en_test
use en_test
go
create master key encryption by password='jackthecat'
alter database en_test set new_broker
go
-- Ideally, the events will be processed out of the queue and into this table
create table dbo.CaughtEvents(recorded dateTime default getdate() primary key,eventdata xml);
go
-- Queue Reader
create procedure dbo.ProcessEvent as begin
	set nocount on
	declare @msg varbinary(max),@cgid uniqueidentifier
	begin try
		begin tran;
		while(1=1)
		begin
			set @cgid = null;
			waitfor(get conversation group @cgid from dbo.EventQueue), timeout 10000;
			if @cgid is null
			begin
				rollback;
				break;
			end
			;receive top(1) 
				@msg = message_body
			from dbo.EventQueue
			where conversation_group_id = @cgid;
			insert into dbo.caughtevents(eventdata) values (@msg);
			commit;
		end
	end try
	begin catch
		declare @en int,@em nvarchar(max),@est int,@esv int,@el int,@ep nvarchar(128)
		set @en = error_number();
		set @em = error_message();
		set @est =  error_state();
		set @esv = error_severity();
		set @el = error_line();
		set @ep = error_procedure();
		if not(xact_state() = 0)
			rollback;
		raisError('%s:%d %s (%d)',@esv,@est,@ep,@el,@em,@en) with log;
	end catch
end
go
-- The queue
create queue dbo.EventQueue with status=off,activation(procedure_name=dbo.ProcessEvent,max_queue_readers=5,execute as self);
create service [http://staff.develop.com/ktegels/examples/auditEvent] on queue dbo.EventQueue([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
go
-- see login activity as a view
create view dbo.vwLoginActivity as select eventdata.value('(/EVENT_INSTANCE/PostTime)[1]','datetime') as Recorded, eventdata.value('(/EVENT_INSTANCE/EventType)[1]','varchar(128)') as Type, eventdata.value('(/EVENT_INSTANCE/LoginName)[1]','varchar(128)') as LoginName from dbo.caughtevents where eventdata.exist('/EVENT_INSTANCE[contains(EventType[1],"AUDIT_LOG")]')=1
go
-- Server event notification
create event notification ServerEventNotification on server for AUDIT_LOGIN,AUDIT_LOGOUT,AUDIT_LOGIN_FAILED to service 'http://staff.develop.com/ktegels/examples/auditEvent','current database'
go
-- Database event notification
create event notification DatabaseEventNotification on database for DDL_TABLE_EVENTS to service 'http://staff.develop.com/ktegels/examples/auditEvent','current database'
go
alter queue dbo.EventQueue with status=on
go

/*
select * from dbo.vwLoginActivity
select * from dbo.caughtevents
select * from sys.transmission_queue
select * from dbo.eventqueue
*/
-- build the route...
/*
declare @s nvarchar(max),@bid nvarchar(max)
SELECT @bid = cast(service_broker_guid as nvarchar(max)) FROM sys.databases WHERE database_id = DB_ID()
set @s = 'create route NotificationRoute with service_name = N''http://staff.develop.com/ktegels/examples/auditEvent'',broker_instance = N''' + @bid +  ''',address = N''LOCAL'''
exec sp_executesql @s
go
*/