Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Getting Started with SQL Server Event Notifications Expand / Collapse
Author
Message
Posted Saturday, November 10, 2012 5:14 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 2:58 PM
Points: 531, Visits: 965
so much for my findings ;) this tells me that my audit argument in the paragraph may be correct.

http://msdn.microsoft.com/en-us/library/ms182454%28v=sql.90%29.aspx

alright, the search of other possible areas continues..


Cheers,
John Esraelo
Post #1383403
Posted Saturday, November 10, 2012 5:26 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 11:15 AM
Points: 1,708, Visits: 1,790
Send me the scripts for the Queue, Service, Event Notification, and Activation Procedure by email through my blog. Let me know what specific version of SQL Server you are running, and the Edition, or just copy/paste the output of SELECT @@VERSION into the message so that I can match your environment and I'll see if I can repro this and offer a complete fix.

Did you dump all of the messages from the queue and look at them to see if there is a problem with one?


Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Post #1383404
Posted Saturday, November 10, 2012 5:44 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 2:58 PM
Points: 531, Visits: 965
I used your TEST DB creation method, generated 3 statements in the queue.. viewed the content of the queue table did not look any strange or anything like that.. also the queue captured some statements from another DB's transactional backup..
then I placed couple of email notifications as an echo print for debugging in the USP..
and I was just checking the email to see how far in the USP the process go in..

create PROCEDURE [dbo].[USP_ProcessAuditDatabaseManagementEvents]
WITH EXECUTE AS OWNER
AS
DECLARE @message_body XML;
DECLARE @message_sequence_number INT;
DECLARE @dialog UNIQUEIDENTIFIER;
DECLARE @email_message NVARCHAR(MAX);
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'LocalServer', -- your defined email profile
@recipients = 'JohnE.SQL@gmail.com', -- your email
@subject = 'Audit Database Management Event Notification',
@body = 'echo print to the message_body in USP';

WHILE ( 1 = 1 )
BEGIN
BEGIN TRANSACTION;

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'LocalServer', -- your defined email profile
@recipients = 'JohnE.SQL@gmail.com', -- your email
@subject = 'Audit Database Management Event Notification',
@body = 'after reading a records ';

-- Receive the next available message FROM the queue
WAITFOR
(
RECEIVE TOP(1) -- just handle one message at a time
@message_body=CAST(message_body AS XML)
FROM dbo.AuditDatabaseManagementEventQueue
), TIMEOUT 1000; -- if queue empty for 1 sec, give UPDATE AND GO away




sure, no problem, I will send you the code..
it is basically like the other ones that were adapted from yours and they work fabulously ..
that's why my suspicion was on audit_database_management...etc.. class / event name..



Cheers,
John Esraelo
Post #1383407
Posted Saturday, November 10, 2012 6:59 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 2:58 PM
Points: 531, Visits: 965
Jonathan,
Wasn't able to find your email link on the blog.. but here is the code..
as you notice that I have commented out most majority of the elements to be emailed ..
I have a suspicion, as I mentioned before, on the AUDIT_DATABASE_MANAGEMENT_EVENT

and here is the version

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
Jul 9 2008 14:43:34
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)





-- Using msdb prevents the need for certificate signing the
-- activation procedure to execute sp_send_dbmail across
-- databases
USE [msdb];
GO

-- Drop the notification if it exists
IF EXISTS ( SELECT *
FROM sys.server_event_notifications
WHERE name = N'CaptureAuditDatabaseManagementEvents' )
BEGIN
DROP EVENT NOTIFICATION CaptureAuditDatabaseManagementEvents ON SERVER;
END

-- Drop the route if it exists
IF EXISTS ( SELECT *
FROM sys.routes
WHERE name = N'AuditDatabaseManagementEventRoute' )
BEGIN
DROP ROUTE AuditDatabaseManagementEventRoute;
END

-- Drop the service if it exists
IF EXISTS ( SELECT *
FROM sys.services
WHERE name = N'AuditDatabaseManagementEventService' )
BEGIN
DROP SERVICE AuditDatabaseManagementEventService;
END

-- Drop the queue if it exists
IF EXISTS ( SELECT *
FROM sys.service_queues
WHERE name = N'AuditDatabaseManagementEventQueue' )
BEGIN
DROP QUEUE AuditDatabaseManagementEventQueue;
END

-- Create a service broker queue to hold the events
CREATE QUEUE [AuditDatabaseManagementEventQueue]
WITH STATUS=ON;
GO

-- Create a service broker service receive the events
CREATE SERVICE [AuditDatabaseManagementEventService]
ON QUEUE [AuditDatabaseManagementEventQueue] ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
GO

-- Create a service broker route to the service
CREATE ROUTE [AuditDatabaseManagementEventRoute]
WITH SERVICE_NAME = 'AuditDatabaseManagementEventService',
ADDRESS = 'LOCAL';
GO

-- Create the event notification to capture the events
CREATE EVENT NOTIFICATION [CaptureAuditDatabaseManagementEvents]
ON SERVER
WITH FAN_IN
FOR AUDIT_DATABASE_MANAGEMENT_EVENT
TO SERVICE 'AuditDatabaseManagementEventService', 'current database';
GO


-- ==========================================================================================
USE [msdb];
GO
SELECT

ApplicationName = message_body.value('(/EVENT_INSTANCE/ApplicationName )[1]','nvarchar(32)')
, DatabaseName = message_body.value('(/EVENT_INSTANCE/DatabaseName )[1]','nvarchar(32)')
, DBUserName = message_body.value('(/EVENT_INSTANCE/DBUserName )[1]','nvarchar(32)')
, EventSubClass = message_body.value('(/EVENT_INSTANCE/EventSubClass )[1]','int' )
, (case message_body.value('(/EVENT_INSTANCE/EventSubClass )[1]','int' ) when 1 then 'Create' when 2 then 'Alter' when 3 then 'Drop' when 4 then 'Dump' when 11 then 'Load' else 'Undefined' end) SubClassNameDesc
, HostName = message_body.value('(/EVENT_INSTANCE/HostName )[1]','nvarchar(32)')
, LoginName = message_body.value('(/EVENT_INSTANCE/LoginName )[1]','nvarchar(32)')
, NTDomainName = message_body.value('(/EVENT_INSTANCE/NTDomainName )[1]','nvarchar(32)')
, NTUserName = message_body.value('(/EVENT_INSTANCE/NTUserName )[1]','nvarchar(32)')
, ObjectName = message_body.value('(/EVENT_INSTANCE/ObjectName )[1]','nvarchar(32)')
, OwnerName = message_body.value('(/EVENT_INSTANCE/OwnerName )[1]','nvarchar(32)')
, ServerName = message_body.value('(/EVENT_INSTANCE/ServerName )[1]','nvarchar(32)')
, SessionLoginName = message_body.value('(/EVENT_INSTANCE/SessionLoginName)[1]','nvarchar(32)')
, StartTime = message_body.value('(/EVENT_INSTANCE/StartTime )[1]','datetime2' )
, Success = message_body.value('(/EVENT_INSTANCE/Success )[1]','int' )
, TextData = message_body.value('(/EVENT_INSTANCE/TextData )[1]','nvarchar(32)')

FROM ( SELECT CAST(message_body AS XML) AS message_body
FROM [AuditDatabaseManagementEventQueue]
) AS Tab;
-- ==========================================================================================
-- Create the Activation Stored Procedure to Process the Queue
IF EXISTS ( SELECT *
FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[dbo].[USP_ProcessAuditDatabaseManagementEvents]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )
DROP PROCEDURE [dbo].[USP_ProcessAuditDatabaseManagementEvents];
GO

create PROCEDURE [dbo].[USP_ProcessAuditDatabaseManagementEvents]
WITH EXECUTE AS OWNER
AS
DECLARE @message_body XML;
DECLARE @message_sequence_number INT;
DECLARE @dialog UNIQUEIDENTIFIER;
DECLARE @email_message NVARCHAR(MAX);

WHILE ( 1 = 1 )
BEGIN
BEGIN TRANSACTION;

-- Receive the next available message FROM the queue
WAITFOR
(
RECEIVE TOP(1) -- just handle one message at a time
@message_body=CAST(message_body AS XML)
FROM dbo.AuditDatabaseManagementEventQueue
), TIMEOUT 1000; -- if queue empty for 1 sec, give UPDATE AND GO away


-- If we didn't get anything, bail out
IF ( @@ROWCOUNT = 0 )
BEGIN
ROLLBACK TRANSACTION;
BREAK;
END

declare
@ApplicationName nvarchar(32) -- nvarchar
, @DatabaseName nvarchar(32) -- nvarchar
, @DBUserName nvarchar(32) -- nvarchar
, @EventSubClass int -- int
, @HostName nvarchar(32) -- nvarchar
, @LoginName nvarchar(32) -- nvarchar
, @NTDomainName nvarchar(32) -- nvarchar
, @NTUserName nvarchar(32) -- nvarchar
, @ObjectName nvarchar(32) -- nvarchar
, @OwnerName nvarchar(32) -- nvarchar
, @ServerName nvarchar(32) -- nvarchar
, @SessionLoginName nvarchar(32) -- nvarchar
, @StartTime nvarchar(32) -- datetime
, @Success int -- int
, @TextData nvarchar(32) -- ntext

select
@ApplicationName = message_body.value('(/EVENT_INSTANCE/ApplicationName )[1]','nvarchar(32)')
, @DatabaseName = message_body.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(32)')
, @DBUserName = message_body.value('(/EVENT_INSTANCE/DBUserName)[1]','nvarchar(32)')
, @EventSubClass = message_body.value('(/EVENT_INSTANCE/EventSubClass)[1]','int' )
, @HostName = message_body.value('(/EVENT_INSTANCE/HostName)[1]','nvarchar(32)')
, @LoginName = message_body.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(32)')
, @NTDomainName = message_body.value('(/EVENT_INSTANCE/NTDomainName)[1]','nvarchar(32)')
, @NTUserName = message_body.value('(/EVENT_INSTANCE/NTUserName)[1]','nvarchar(32)')
, @ObjectName = message_body.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(32)')
, @OwnerName = message_body.value('(/EVENT_INSTANCE/OwnerName)[1]','nvarchar(32)')
, @ServerName = message_body.value('(/EVENT_INSTANCE/ServerName)[1]','nvarchar(32)')
, @SessionLoginName = message_body.value('(/EVENT_INSTANCE/SessionLoginName)[1]','nvarchar(32)')
, @StartTime = message_body.value('(/EVENT_INSTANCE/StartTime)[1]','nvarchar(32)')
, @Success = message_body.value('(/EVENT_INSTANCE/Success)[1]','int' )
, @TextData = message_body.value('(/EVENT_INSTANCE/TextData)[1]','nvarchar(32)')

-- Generate formatted email message
select
@email_message = 'Audit DB Mgmt Event Occured::'
--+ char(10) + 'ApplicationName ' + @ApplicationName
--+ char(10) + 'DatabaseName ' + @DatabaseName
--+ char(10) + 'DBUserName ' + @DBUserName
--+ char(10) + 'EventSubClass ' + (case @EventSubClass when 1 then 'Create' when 2 then 'Alter' when 3 then 'Drop' when 4 then 'Dump' when 11 then 'Load' else 'Undefined' end)
--+ char(10) + 'HostName ' + @HostName
--+ char(10) + 'LoginName ' + @LoginName
--+ char(10) + 'NTDomainName ' + @NTDomainName
--+ char(10) + 'NTUserName ' + @NTUserName
--+ char(10) + 'ObjectName ' + @ObjectName
--+ char(10) + 'OwnerName ' + @OwnerName
--+ char(10) + 'ServerName ' + @ServerName
--+ char(10) + 'SessionLoginName ' + @SessionLoginName
--+ char(10) + 'StartTime ' + @StartTime
--+ char(10) + 'Success ' + @Success
--+ char(10) + 'TextData ' + @TextData

-- Send email using Database Mail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'LocalServer', -- your defined email profile
@recipients = 'JohnE.SQL@gmail.com', -- your email
@subject = 'Audit Database Management Event Notification',
@body = @email_message;

-- Commit the transaction. At any point before this, we could roll
-- back. The received message would be back on the queue AND the
-- response wouldn't be sent.
COMMIT TRANSACTION;
END
GO

-- Alter the queue to use the activation procedure
ALTER QUEUE [AuditDatabaseManagementEventQueue]
WITH STATUS=ON,
ACTIVATION
(STATUS=ON,
PROCEDURE_NAME = [dbo].[USP_ProcessAuditDatabaseManagementEvents],
MAX_QUEUE_READERS = 1,
EXECUTE AS OWNER);
GO





Cheers,
John Esraelo
Post #1383409
Posted Saturday, November 10, 2012 7:26 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 11:15 AM
Points: 1,708, Visits: 1,790
Your activation stored procedure is the problem. Before you configured the queue for activation did you test the procedure by executing it? I am going to bet not in this case. If you run the procedure as written it will provide the following error:

Msg 4121, Level 16, State 1, Procedure USP_ProcessAuditDatabaseManagementEvents, Line 47
Cannot find either column "message_body" or the user-defined function or aggregate "message_body.value", or the name is ambiguous.
Msg 266, Level 16, State 2, Procedure USP_ProcessAuditDatabaseManagementEvents, Line 47
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

The problem is the code below:

			select 													
@ApplicationName = message_body.value('(/EVENT_INSTANCE/ApplicationName )[1]','nvarchar(32)')
, @DatabaseName = message_body.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(32)')
, @DBUserName = message_body.value('(/EVENT_INSTANCE/DBUserName)[1]','nvarchar(32)')
, @EventSubClass = message_body.value('(/EVENT_INSTANCE/EventSubClass)[1]','int' )
, @HostName = message_body.value('(/EVENT_INSTANCE/HostName)[1]','nvarchar(32)')
, @LoginName = message_body.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(32)')
, @NTDomainName = message_body.value('(/EVENT_INSTANCE/NTDomainName)[1]','nvarchar(32)')
, @NTUserName = message_body.value('(/EVENT_INSTANCE/NTUserName)[1]','nvarchar(32)')
, @ObjectName = message_body.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(32)')
, @OwnerName = message_body.value('(/EVENT_INSTANCE/OwnerName)[1]','nvarchar(32)')
, @ServerName = message_body.value('(/EVENT_INSTANCE/ServerName)[1]','nvarchar(32)')
, @SessionLoginName = message_body.value('(/EVENT_INSTANCE/SessionLoginName)[1]','nvarchar(32)')
, @StartTime = message_body.value('(/EVENT_INSTANCE/StartTime)[1]','nvarchar(32)')
, @Success = message_body.value('(/EVENT_INSTANCE/Success)[1]','int' )
, @TextData = message_body.value('(/EVENT_INSTANCE/TextData)[1]','nvarchar(32)')

It should be the code below:

			select 													
@ApplicationName = @message_body.value('(/EVENT_INSTANCE/ApplicationName )[1]','nvarchar(32)')
, @DatabaseName = @message_body.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(32)')
, @DBUserName = @message_body.value('(/EVENT_INSTANCE/DBUserName)[1]','nvarchar(32)')
, @EventSubClass = @message_body.value('(/EVENT_INSTANCE/EventSubClass)[1]','int' )
, @HostName = @message_body.value('(/EVENT_INSTANCE/HostName)[1]','nvarchar(32)')
, @LoginName = @message_body.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(32)')
, @NTDomainName = @message_body.value('(/EVENT_INSTANCE/NTDomainName)[1]','nvarchar(32)')
, @NTUserName = @message_body.value('(/EVENT_INSTANCE/NTUserName)[1]','nvarchar(32)')
, @ObjectName = @message_body.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(32)')
, @OwnerName = @message_body.value('(/EVENT_INSTANCE/OwnerName)[1]','nvarchar(32)')
, @ServerName = @message_body.value('(/EVENT_INSTANCE/ServerName)[1]','nvarchar(32)')
, @SessionLoginName = @message_body.value('(/EVENT_INSTANCE/SessionLoginName)[1]','nvarchar(32)')
, @StartTime = @message_body.value('(/EVENT_INSTANCE/StartTime)[1]','nvarchar(32)')
, @Success = @message_body.value('(/EVENT_INSTANCE/Success)[1]','int' )
, @TextData = @message_body.value('(/EVENT_INSTANCE/TextData)[1]','nvarchar(32)')

You referenced a column name and not the variable that you set the message_body to so it errors out and you get the rollback, so 5 times and your queue disables. Anytime you are building an activation procedure, you need to run it to test that it works without error before configuring the queue for activation using the procedure or you can have this problem.


Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Post #1383413
Posted Saturday, November 10, 2012 8:28 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 11:15 AM
Points: 1,708, Visits: 1,790
BTW:

You also need to CAST the @Success variable to VARCHAR in you concatenation code for the email body:

					+ char(10) + 	'Success			'	+	CAST(@Success AS VARCHAR)



Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Post #1383415
Posted Saturday, November 10, 2012 10:05 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 2:58 PM
Points: 531, Visits: 965
Jonathan,
OMG, how embarrassing this is for me.. hehe ;)
it is working great..
thx again



Cheers,
John Esraelo
Post #1383417
Posted Saturday, November 10, 2012 10:16 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 11:15 AM
Points: 1,708, Visits: 1,790
Hey John,

It's an easy mistake to make, but I bet it will never happen again. Ask me how I know this, I made a very similar mistake 4-5 years ago when I first worked with Event Notifications. This is why I suggested removing the activation from the queue and testing whether it would go back to disabled. If it doesn't the problem is within the activation procedure, so executing it separately will tell you where the problem is. Always test the procedure before binding it to the queue so that you know if it will be a problem or not.

Have a good weekend.


Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Post #1383418
Posted Saturday, November 10, 2012 10:24 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 2:58 PM
Points: 531, Visits: 965
Jonathan,
You are right, will not occur again. I can see how that happened to me. First of all I should have run the USP and check.. secondly, I had copied and pasted from the select statement in the script that the message_body was an Alias and not the actual object ;)

Thank you again and 2 heads always work better than mine, I mean better than 1 . ;)



Cheers,
John Esraelo
Post #1383420
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse