July 27, 2009 at 9:00 pm
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**********
:w00t:
August 4, 2009 at 11:24 am
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