Hi,

I did set Service Broker (SB) to insert into a table all the changes on my
user objects.

I have created the Queue, the service, the event notification and the
activation stored procedure as described below

My problem is that the activation procedure can not capture the client
hostname (this is very usefull to get who changed an object)

Any idea????
ALTER PROCEDURE dbo.SP_DBA_NS_Audit

AS

BEGIN

DECLARE @eventDataXML XML, @hostname nvarchar(128), @spid smallint ;

RECEIVE TOP(1) @eventDataXML=message_body

FROM NS90.dbo.AuditQueue

IF CAST(@eventDataXML as XML) is not null

BEGIN

SELECT @spid = @eventDataXML.value('(/EVENT_INSTANCE/SPID)[1]','int')

SELECT @hostname = host_name FROM sys.dm_exec_sessions where session_id =
@spid

INSERT INTO NS90.dbo.DBA_NS_Audit (LogTime, ObjectName,spid,hostname,
eventXMLData)

VALUES
(getdate(),@eventDataXML.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(100)'),@spid,@hostname,cast(@eventDataXML
as XML));

END

END;

CREATE QUEUE NS90.dbo.AuditQueue

WITH STATUS = ON,

ACTIVATION (

procedure_name = NS90.dbo.SP_DBA_NS_Audit,

MAX_QUEUE_READERS = 1,

execute as self )

ON [DEFAULT] ;

CREATE SERVICE

AuditService ON QUEUE dbo.AuditQueue

([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);

CREATE EVENT NOTIFICATION ENDBAudit

ON SERVER

FOR

CREATE_FUNCTION,

ALTER_FUNCTION,

DROP_FUNCTION,

CREATE_INDEX,

ALTER_INDEX,

DROP_INDEX,

CREATE_PROCEDURE,

ALTER_PROCEDURE,

DROP_PROCEDURE,

CREATE_TABLE,

ALTER_TABLE,

DROP_TABLE,

CREATE_TRIGGER,

ALTER_TRIGGER,

DROP_TRIGGER,

CREATE_VIEW,

ALTER_VIEW,

DROP_VIEW

TO SERVICE 'AuditService', 'current database';