Post reply

AUDITING

  • July 27, 2009 at 9:00 pm

    #385066

    Ok guys here it goes...

    I am trying to utilize SQL Server 2005 Auditing capabilities and I am trying to achieve the following:

    Create Notification Service to trigger server scope events and triggers for database scope events. Since I am stuck on the server scope I will start there.

    1. Created queue, service, route and event notification

    2. Tested successfuly by creating database and the queue does pick up on the event

    3. Wrote a store procedure to extract data from the xml column and I want it to insert the events in another table in another server. The link connection and queries have been tested successfully, however the store procedure that is being activated in the queue is not bringing in the entire dataset. I trucate the auditing table and select * and see nulls all aross as expected. Then I trigger a server scope event and check to see what happends to the target table. I then get the datetime data populated (not the format I want but populated) but the other columns no longer have nulls but rather just no data.

    I tried to print out the store variable data and when I look at the sql logs I see nothing

    Here is the code:

    CREATE PROCEDURE usp_Insert_Audit_Info

    AS

    BEGIN

    SET NOCOUNT ON;

    DECLARE @Handle UNIQUEIDENTIFIER;

    --DECLARE @MessageType SYSNAME;

    DECLARE @Message XML

    DECLARE @LoginName varchar(max)

    DECLARE @PostTime varchar(max) ;

    DECLARE @SQLInstance varchar(max)

    DECLARE @Computername varchar(max);

    RECEIVE TOP (1)

    @Handle = conversation_handle,

    --@MessageType = message_type_name,

    @Message = message_body

    FROM AuditQueue;

    IF(@Handle IS NOT NULL AND @Message IS NOT NULL)

    BEGIN

    SELECT @LoginName = CAST(@Message.query('/Params/LoginName/text()') AS VARCHAR(MAX))

    SELECT @PostTime = CAST(CAST(@Message.query('/Params/PostTime/text()') AS VARCHAR(MAX)) AS DATETIME)

    SELECT @SQLInstance = CAST(@Message.query('/Params/SQLInstance/text()') AS VARCHAR(MAX))

    SELECT @Computername = CAST(@Message.query('/Params/Computername/text()') AS VARCHAR(MAX));

    INSERT RemoteServer.DBAAUDIT.DBO.DDL_EVENT_DATA (LoginName,PostTime,SQLInstance,ComputerName)

    VALUES(@LoginName,@PostTime,@SQLInstance,@Computername);

    END

    END

    GO

    ************Here is the actual elements of the event I have triggered**********

    DROP_DATABASE 2009-07-24T19:14:48.240 58

    Server

    company\rrahyab

    test

    drop database test

    :w00t:

  • August 4, 2009 at 11:24 am

    #1034431

    I see a lot of readers but no replies? 😛

    I the issue with this statement. It had to do with my casting, too many partenthesis and I changed from query() to value(). Also I had retention on in my queue so it would not pick up any old events so I had to use select top (1) instead of receive top (1)

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply