Service Broker/Event Notification Problem; Event Notification stopping unexpectedly

• mstjean

  Hall of Fame

  Points: 3310

  More actions

  June 28, 2010 at 7:14 pm

  #222685

  I am running SQL 2005 Enterprise Edition; I am SA on this server. I am using EVENT NOTIFICATION for DDL_DATABASE_LEVEL_EVENTS to track DDL changes. If I do "USE TRACKED_DB; DROP TABLE TRACKED_DB.DBO.SOMETABLE" it works without problems. But if I do "USE OTHER_DB; DROP TABLE TRACKED_DB.DBO.SOMETABLE" it appears to do a DROP EVENT NOTIFICATION-- at any rate, I have to re-do my CREATE EVENT NOTIFICATION to continue tracking DDL changes.

  Is this expected behavior? (err... It wasn't for me!) ...Am I doing something grossly wrong? Thanks for any help.

  You need 2 dbs to test this; TestAlerts and Admin. There are no other special requirements needed for them.

  --------------------------------------------------------------------------

  --PART 1

  -- this db will hold a permanent table for the events tracked

  -- In my environ I may do the PART 2 CODE for several dbs on a server

  -- This table will periodically RECEIVE rows from the queue-- from all tracked dbs on the server

  -- ...And eventually send THOSE to a central server fpr reporting

  -- I planned to do the RECEIVE and the send to central via periodic SQL JOBs.

  --------------------------------------------------------------------------

  USE ADMIN

  CREATE TABLE ReceivedDdlDbEvents(

  rowid bigint identity,

  servername varchar(200),

  dbname varchar(200),

  service_instance_id UNIQUEIDENTIFIER,

  handle UNIQUEIDENTIFIER,

  message_sequence_number BIGINT,

  service_name NVARCHAR(512),

  service_contract_name NVARCHAR(256),

  message_type_name NVARCHAR(256),

  validation NCHAR,

  message_body XML) ;

  --------------------------------------------------------------------------

  --PART 2

  -- Set it up

  --------------------------------------------------------------------------

  ALTER DATABASE TestAlerts SET ENABLE_BROKER

  USE TestAlerts

  go

  CREATE QUEUE queueDdlDbEvents WITH STATUS = ON--SBQ/SERVICE BROKER QUEUE: holds the events

  go

  CREATE SERVICE svcDdlDbEvents ON QUEUE queueDdlDbEvents ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])--SBS/SERVICE BROKER SERVICE: receives the incoming messages (events) and stores them in the SBQ

  go

  CREATE EVENT NOTIFICATION notify_DdlDbEvents ON DATABASE WITH fan_in FOR DDL_DATABASE_LEVEL_EVENTS TO SERVICE 'svcDdlDbEvents', 'current database';--SBN/SERVICE BROKER NOTIFICATION: writes to the SBS (which then takes rcvd messages/evts and sends to SBQ

  go

  --------------------------------------------------------------------------

  --PART 3

  -- Here are a series of DDL commands;

  -- Run a group of lines from PART 3 and check by running PART 4 after

  --------------------------------------------------------------------------

  USE TestAlerts

  CREATE TABLE FOO1(bar char(1))

  CREATE TABLE FOO2(bar char(1))

  CREATE TABLE FOO3(bar char(1))

  CREATE TABLE FOO4(bar char(1))

  CREATE TABLE FOO5(bar char(1))

  CREATE TABLE FOO6(bar char(1))

  -- if you run part 4 after the 7 lines above you should see 6 rows in

  --SELECT * FROM ADMIN.dbo.ReceivedDdlDbEvents

  USE TestAlerts

  DROP TABLE FOO1

  DROP TABLE DBO.FOO2

  DROP TABLE TestAlerts.DBO.FOO3

  -- if you run part 4 after the 4 lines above you should see 4 MORE (10 total) rows in

  --SELECT * FROM ADMIN.dbo.ReceivedDdlDbEvents

  USE ADMIN

  DROP TABLE TestAlerts.DBO.FOO4-- uhoh; didn't get into queue

  go

  SELECT * FROM TESTALERTS.dbo.queueDdlDbEvents -- do this line or run part 4; same result

  USE TestAlerts

  DROP TABLE TestAlerts.DBO.FOO5-- uhoh; neither did this

  go

  SELECT * FROM TESTALERTS.dbo.queueDdlDbEvents -- do this line or run part 4; same result

  -- what the...? WHY do I need to re-CREATE EVENT??

  USE TestAlerts

  CREATE EVENT NOTIFICATION notify_DdlDbEvents ON DATABASE WITH fan_in FOR DDL_DATABASE_LEVEL_EVENTS TO SERVICE 'svcDdlDbEvents', 'current database';--SBN/SERVICE BROKER NOTIFICATION: writes to the SBS (which then takes rcvd messages/evts and sends to SBQ

  go

  DROP TABLE TestAlerts.DBO.FOO6-- and now it works again:

  SELECT * FROM TESTALERTS.dbo.queueDdlDbEvents -- do this line or run part 4; same result

  --------------------------------------------------------------------------

  --PART 4

  -- This should get queued data into the ADMIN table

  --------------------------------------------------------------------------

  USE TestAlerts

  DECLARE @ReceivedDdlDbEvents TABLE(

  service_instance_id UNIQUEIDENTIFIER,

  handle UNIQUEIDENTIFIER,

  message_sequence_number BIGINT,

  service_name NVARCHAR(512),

  service_contract_name NVARCHAR(256),

  message_type_name NVARCHAR(256),

  validation NCHAR,

  message_body XML) ;

  -- RECEIVE it into @TV ----------------------------------------

  RECEIVE

  conversation_group_id,

  conversation_handle,

  message_sequence_number,

  service_name,

  service_contract_name,

  message_type_name,

  validation,

  message_body

  FROM TESTALERTS.dbo.queueDdlDbEvents

  INTO @ReceivedDdlDbEvents

  -- THEN save it to a real table -------------------------------

  INSERT INTO ADMIN.DBO.ReceivedDdlDbEvents SELECT @@servername,db_name(),* FROM @ReceivedDdlDbEvents

  -- see?

  SELECT * FROM ADMIN.dbo.ReceivedDdlDbEvents

  SELECT * FROM TESTALERTS.dbo.queueDdlDbEvents

  ---

  Cursors are useful if you don't know SQL

• mstjean

  Hall of Fame

  Points: 3310

  More actions

  June 29, 2010 at 5:24 pm

  #1186779

  A little more fiddling and I've got the (one-line) fix:

  Changed

  CREATE EVENT NOTIFICATION notify_DdlDbEvents ON [highlight]DATABASE[/highlight] WITH fan_in FOR DDL_DATABASE_LEVEL_EVENTS TO SERVICE 'svcDdlDbEvents', 'current database';

  To

  CREATE EVENT NOTIFICATION notify_DdlDbEvents ON [highlight]SERVER[/highlight] WITH fan_in FOR DDL_DATABASE_LEVEL_EVENTS TO SERVICE 'svcDdlDbEvents', 'current database';

  ---

  Cursors are useful if you don't know SQL