InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 19:23:58 GMT20http://www.sqlservercentral.com/Forums/Topic837753-1365-1.aspxJonathan,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 . ;) Sat, 10 Nov 2012 22:24:46 GMTJohn Esraelo-498130http://www.sqlservercentral.com/Forums/Topic837753-1365-1.aspxHey 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.Sat, 10 Nov 2012 22:16:44 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic837753-1365-1.aspxJonathan,OMG, how embarrassing this is for me.. hehe ;) it is working great.. thx again Sat, 10 Nov 2012 22:05:07 GMTJohn Esraelo-498130http://www.sqlservercentral.com/Forums/Topic837753-1365-1.aspxBTW:You also need to CAST the @Success variable to VARCHAR in you concatenation code for the email body:[code="sql"] + char(10) + 'Success ' + CAST(@Success AS VARCHAR)[/code]Sat, 10 Nov 2012 20:28:03 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic837753-1365-1.aspxYour 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 47Cannot 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 47Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.The problem is the code below:[code="sql"] 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)')[/code]It should be the code below:[code="sql"] 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)')[/code]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.Sat, 10 Nov 2012 19:26:54 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic837753-1365-1.aspxJonathan, 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_EVENTand 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 &lt;X86&gt; (Build 3790: Service Pack 2)[code="sql"]-- Using msdb prevents the need for certificate signing the -- activation procedure to execute sp_send_dbmail across-- databasesUSE [msdb];GO-- Drop the notification if it existsIF EXISTS ( SELECT * FROM sys.server_event_notifications WHERE name = N'CaptureAuditDatabaseManagementEvents' ) BEGIN DROP EVENT NOTIFICATION CaptureAuditDatabaseManagementEvents ON SERVER; END-- Drop the route if it existsIF EXISTS ( SELECT * FROM sys.routes WHERE name = N'AuditDatabaseManagementEventRoute' ) BEGIN DROP ROUTE AuditDatabaseManagementEventRoute; END-- Drop the service if it existsIF EXISTS ( SELECT * FROM sys.services WHERE name = N'AuditDatabaseManagementEventService' ) BEGIN DROP SERVICE AuditDatabaseManagementEventService; END-- Drop the queue if it existsIF EXISTS ( SELECT * FROM sys.service_queues WHERE name = N'AuditDatabaseManagementEventQueue' ) BEGIN DROP QUEUE AuditDatabaseManagementEventQueue; END-- Create a service broker queue to hold the eventsCREATE QUEUE [AuditDatabaseManagementEventQueue]WITH STATUS=ON;GO-- Create a service broker service receive the eventsCREATE SERVICE [AuditDatabaseManagementEventService]ON QUEUE [AuditDatabaseManagementEventQueue] ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);GO-- Create a service broker route to the serviceCREATE ROUTE [AuditDatabaseManagementEventRoute]WITH SERVICE_NAME = 'AuditDatabaseManagementEventService',ADDRESS = 'LOCAL';GO-- Create the event notification to capture the eventsCREATE EVENT NOTIFICATION [CaptureAuditDatabaseManagementEvents]ON SERVERWITH FAN_INFOR AUDIT_DATABASE_MANAGEMENT_EVENTTO SERVICE 'AuditDatabaseManagementEventService', 'current database';GO-- ========================================================================================== USE [msdb];GOSELECT 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 QueueIF EXISTS ( SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[USP_ProcessAuditDatabaseManagementEvents]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1 ) DROP PROCEDURE [dbo].[USP_ProcessAuditDatabaseManagementEvents];GOcreate PROCEDURE [dbo].[USP_ProcessAuditDatabaseManagementEvents] WITH EXECUTE AS OWNERAS 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; ENDGO-- Alter the queue to use the activation procedureALTER QUEUE [AuditDatabaseManagementEventQueue] WITH STATUS=ON, ACTIVATION (STATUS=ON, PROCEDURE_NAME = [dbo].[USP_ProcessAuditDatabaseManagementEvents], MAX_QUEUE_READERS = 1, EXECUTE AS OWNER);GO[/code]Sat, 10 Nov 2012 18:59:24 GMTJohn Esraelo-498130http://www.sqlservercentral.com/Forums/Topic837753-1365-1.aspxI 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 OWNERAS 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 awaysure, 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.. Sat, 10 Nov 2012 17:44:42 GMTJohn Esraelo-498130http://www.sqlservercentral.com/Forums/Topic837753-1365-1.aspxSend 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?Sat, 10 Nov 2012 17:26:01 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic837753-1365-1.aspxso 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.aspxalright, the search of other possible areas continues.. Sat, 10 Nov 2012 17:14:28 GMTJohn Esraelo-498130http://www.sqlservercentral.com/Forums/Topic837753-1365-1.aspxFYII wonder if my AUDIT_DATABASE_MANAGEMENT_EVENT statement in the paragraph in below does not exist or typed incorrectly.. so, I am going to search on internet / msdn and see what I can find out .. CREATE EVENT NOTIFICATION [CaptureAuditDatabaseManagementEvents]ON SERVERWITH FAN_INFOR AUDIT_DATABASE_MANAGEMENT_EVENTTO SERVICE 'AuditDatabaseManagementEventService', 'current database';GOSat, 10 Nov 2012 17:08:58 GMTJohn Esraelo-498130http://www.sqlservercentral.com/Forums/Topic837753-1365-1.aspx[quote][b]Jonathan Kehayias (11/10/2012)[/b][hr][quote][b]John Esraelo-498130 (11/10/2012)[/b][hr]Hi Jonathan,Before I ask a question I wanted to say that I really like your articles, especially in the events area.[b]Here is the question:[/b]I have create a queue for auditing database management on alter, create, drop, etc.everything looks fine, I can query the queue and displaying the records.. But, for some reason this queue becomes "disabled" within a minute after re-enabling and I have to manually or programmatically re-enable it.I have created couple of other ones that work fine, such as audit login failed, and one adapted from your awesome code on AutoGrowFile.. Any advise would be grandthx again Jonathan[/quote]Hey John,My first guess would be that you have a poison message in the queue that is causing the activation procedure to rollback and after 5 rollbacks it will automatically disable the queue. To troubleshoot this, I would disable activation and then enable the queue and look at the next message on it to see what is wrong with it. I'd also see why the activation procedure has to rollback during processing of the message. To do this:[code="sql"] -- Turn activation offALTER QUEUE [YourQueue]WITH ACTIVATION (STATUS = OFF,EXECUTE AS OWNER);-- Enable the queueALTER QUEUE [YourQueue] WITH STATUS = ON;[/code]Then just do a standard SELECT against the queue to view it's contents. [code="sql"]SELECT *FROM [YourQueue];[/code]You can also clear the items in the queue by using RECEIVE:[code="sql"]DECLARE @message_body XML;-- Get the top message from the queueRECEIVE TOP (1) @message_body = CAST([message_body] AS XML)FROM [YourQueue]);[/code]Hopefully that helps you resolve the problems.Cheers,[/quote]Thank you Jonathan, I will give it a shot in a minute and keep you posted of the results .thx againSat, 10 Nov 2012 15:29:30 GMTJohn Esraelo-498130http://www.sqlservercentral.com/Forums/Topic837753-1365-1.aspx[quote][b]John Esraelo-498130 (11/10/2012)[/b][hr]Hi Jonathan,Before I ask a question I wanted to say that I really like your articles, especially in the events area.[b]Here is the question:[/b]I have create a queue for auditing database management on alter, create, drop, etc.everything looks fine, I can query the queue and displaying the records.. But, for some reason this queue becomes "disabled" within a minute after re-enabling and I have to manually or programmatically re-enable it.I have created couple of other ones that work fine, such as audit login failed, and one adapted from your awesome code on AutoGrowFile.. Any advise would be grandthx again Jonathan[/quote]Hey John,My first guess would be that you have a poison message in the queue that is causing the activation procedure to rollback and after 5 rollbacks it will automatically disable the queue. To troubleshoot this, I would disable activation and then enable the queue and look at the next message on it to see what is wrong with it. I'd also see why the activation procedure has to rollback during processing of the message. To do this:[code="sql"] -- Turn activation offALTER QUEUE [YourQueue]WITH ACTIVATION (STATUS = OFF,EXECUTE AS OWNER);-- Enable the queueALTER QUEUE [YourQueue] WITH STATUS = ON;[/code]Then just do a standard SELECT against the queue to view it's contents. [code="sql"]SELECT *FROM [YourQueue];[/code]You can also clear the items in the queue by using RECEIVE:[code="sql"]DECLARE @message_body XML;-- Get the top message from the queueRECEIVE TOP (1) @message_body = CAST([message_body] AS XML)FROM [YourQueue]);[/code]Hopefully that helps you resolve the problems.Cheers,Sat, 10 Nov 2012 14:22:13 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic837753-1365-1.aspxHi Jonathan,Before I ask a question I wanted to say that I really like your articles, especially in the events area.[b]Here is the question:[/b]I have create a queue for auditing database management on alter, create, drop, etc.everything looks fine, I can query the queue and displaying the records.. But, for some reason this queue becomes "disabled" within a minute after re-enabling and I have to manually or programmatically re-enable it.I have created couple of other ones that work fine, such as audit login failed, and one adapted from your awesome code on AutoGrowFile.. Any advise would be grandthx again JonathanSat, 10 Nov 2012 13:53:24 GMTJohn Esraelo-498130http://www.sqlservercentral.com/Forums/Topic837753-1365-1.aspx[quote][b]vinay.kumar (7/6/2012)[/b][hr]HI,Thanks For the Post.I tried the sample Event Notification given in the blog. And it looks all fine.For the each Event(ERRORLOG) it is using one Conversation Group for all the raised errors. SO i manually ended the conversation and then i raised a custom exception. This time no messaes sent to any Queue.Please let me know your thoughtThanks,Vinay K[/quote]Why would you do that? The conversation being reused by the Event Notification is normal behavior, and reuse of conversations can be a a major performance improvement.[url=http://rusanu.com/2007/04/25/reusing-conversations/] http://rusanu.com/2007/04/25/reusing-conversations/[/url]Fri, 06 Jul 2012 16:42:07 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic837753-1365-1.aspxHI,Thanks For the Post.I tried the sample Event Notification given in the blog. And it looks all fine.For the each Event(ERRORLOG) it is using one Conversation Group for all the raised errors. SO i manually ended the conversation and then i raised a custom exception. This time no messaes sent to any Queue.Please let me know your thoughtThanks,Vinay KFri, 06 Jul 2012 14:45:44 GMTvinay.kumarhttp://www.sqlservercentral.com/Forums/Topic837753-1365-1.aspxHi,I am trying to create notification in server A and want the read the queue data using stored procedure from server B. Can you help me with sample article of how to achieve this? I am struck with creating notification in server A and making it to pass the data to server B. Where do i mention that the data should go to server B. Greatly appreciate your help.Thu, 24 Dec 2009 10:19:16 GMTBaskar B.Vhttp://www.sqlservercentral.com/Forums/Topic837753-1365-1.aspx[quote][b]philcart (12/23/2009)[/b][hr]Great article Jonathan, looking forward to more. Especially interested in extended events.[/quote]Event Notifications and Extended Events are completely different animals in SQL Server. Extended Events are new in 2008 while Event Notifications exist in 2005, and the eventing for the two is very different. If you'd like more information on Extended Events, you can read my whitepaper on MSDN,[url=http://msdn.microsoft.com/en-us/library/dd822788.aspx]Using SQL Server 2008 Extended Events[/url]as well as Paul Randal's article on Technet:[url=http://technet.microsoft.com/en-us/magazine/2009.01.sql2008.aspx]Advanced Troubleshooting with Extended Events[/url]If you'd like some other references I have a tag on my blog for Extended Events posts I write:[url]http://sqlblog.com/blogs/jonathan_kehayias/archive/tags/Extended+Events/default.aspx[/url]and so does Paul:[url]http://www.sqlskills.com/BLOGS/PAUL/category/Extended-Events.aspx[/url]Wed, 23 Dec 2009 20:54:23 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic837753-1365-1.aspxlouis.young, One thing I can think of is to make sure you are in the same database that you created the queue in.Great article Jonathan, looking forward to more. Especially interested in extended events.Wed, 23 Dec 2009 20:05:36 GMTphilcarthttp://www.sqlservercentral.com/Forums/Topic837753-1365-1.aspxGreat Job Jonathan! I look forward to part 2.Wed, 23 Dec 2009 11:24:28 GMTAdam Haineshttp://www.sqlservercentral.com/Forums/Topic837753-1365-1.aspxI've copied the T-SQL from this article exactly and executed it. All works fine, but when I view the queue:SELECT *FROM EventNotificationQueuethere are no records. What am I doing wrong?Tue, 22 Dec 2009 22:05:50 GMTlouis.younghttp://www.sqlservercentral.com/Forums/Topic837753-1365-1.aspxExcellent article. Looking forward to more like it !!!!! :-)Tue, 22 Dec 2009 16:45:12 GMTMark_Pratthttp://www.sqlservercentral.com/Forums/Topic837753-1365-1.aspxThanks for the info! Great stuff!Tue, 22 Dec 2009 15:40:56 GMTggoblehttp://www.sqlservercentral.com/Forums/Topic837753-1365-1.aspx[quote][b]Stan_Segers (12/22/2009)[/b][hr]You only need to set up the Event Notification for the database (or server). The stored procedure is only needed in the database where the queue resides. You could send the events to another database or even another server. Also, you are not required to use signed procedures.I did a blog-post some time ago to catch deadlocks with event notification. This shows you how to handle server level events. [url=http://resquel.com/ssb/2009/03/02/DeadlockAlertsThroughEventNotification.aspx]Deadlock Alerts Trough Event Notification[/url] and doesn't use signed procedures.[/quote]You can create the server level items in msdb to get around the signed procedure but since that isn't a recommended practice, I don't offer ideas like that in online articles. It is one way to go about it though. I keep these kinds of things in DBA_DATA database on my servers personally, but everyone has their own methods.This is the first in a series I have written on using Event Notifications and there is an article on capturing Deadlock Graphs pending publication, though I take things a bit further in my article than you did on your blog post by parsing the XML and retrieving additional information regarding the executing statements like their query plans which are important in deadlock analysis.Tue, 22 Dec 2009 15:00:29 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic837753-1365-1.aspx[quote][b]greggoble2 (12/22/2009)[/b][hr]So if I want to implement this on another databases, I would have to create the stored procedure in each database and then sign the procedure with the cert correct?Also if I am currently using db mail in other stored procedures will I have to sign the certificate to all of those as well for them to continue to work?[/quote]Trace events are server scoped, so you don't need multiple Notifications per database for this. If you used DDL Events at the database level, it would have to be created per database, and to send email through queue activation from that database with DatabaseMail would require signing it with a certificate as the most secure method. I covered the purpose behind using certificate signing to access DatabaseMail from a non-msdb database on my article last week [url=http://www.sqlservercentral.com/articles/Security/68873/]Using a Certificate Signed Stored Procedure to Execute sp_send_dbmail [/url].Tue, 22 Dec 2009 14:55:31 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic837753-1365-1.aspxYou only need to set up the Event Notification for the database (or server). The stored procedure is only needed in the database where the queue resides. You could send the events to another database or even another server. Also, you are not required to use signed procedures.I did a blog-post some time ago to catch deadlocks with event notification. This shows you how to handle server level events. [url=http://resquel.com/ssb/2009/03/02/DeadlockAlertsThroughEventNotification.aspx]Deadlock Alerts Trough Event Notification[/url] and doesn't use signed procedures.Tue, 22 Dec 2009 14:25:08 GMTStan_Segershttp://www.sqlservercentral.com/Forums/Topic837753-1365-1.aspxSo if I want to implement this on another databases, I would have to create the stored procedure in each database and then sign the procedure with the cert correct?Also if I am currently using db mail in other stored procedures will I have to sign the certificate to all of those as well for them to continue to work?Tue, 22 Dec 2009 13:13:51 GMTggoblehttp://www.sqlservercentral.com/Forums/Topic837753-1365-1.aspxNice ArticleTue, 22 Dec 2009 11:32:57 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic837753-1365-1.aspxVery nice work. Well written and very useful. Thank youTue, 22 Dec 2009 06:02:27 GMTScott Abrantshttp://www.sqlservercentral.com/Forums/Topic837753-1365-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/Event+Notifications/68831/">Getting Started with SQL Server Event Notifications</A>[/B]Tue, 22 Dec 2009 00:08:27 GMTJonathan Kehayias