0

I am creating an application with Sql cached dependency.

For this i enabled the broker by 

Alter database test ENABLE_BROKER

After i add two new tables to the database.

But i found that i am not able to get any notification services for the new tables that i added.

Even i use

Alter datatbase test New_Broker

THis command keeps on running so i cancel it without any success.

Can you please specify what i am missing

Any help is apprciated

Moons
  • 3,833
  • 4
  • 49
  • 82
  • 1
    These ALTER DATABASE commands require a database lock. Use the activity monitor to check there are no other open connections on the database before running them, otherise they will just wait for the other connections to close. – Stephen Turner Nov 15 '11 at 13:07
  • @webturner This is something that i really dont have any knowledge. Can you also specify how can i lock the Database – Moons Nov 16 '11 at 05:29
  • The command itself does the locking, you don't need to. But it won't be able to get a lock (it'll wait) if anything else is running on the database, so use Activity monitor (msdn.microsoft.com/en-us/library/ms175518.aspx) to check. – Stephen Turner Nov 16 '11 at 10:58

1 Answers1

1

It seems like you're asking two questions:

  1. How to prevent SET ENABLE_BROKER from hanging

    ALTER DATABASE [YourDBName] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE

  2. Can you get notified by Service Broker when the schema changes?

Notifications are for existing tables only. This might lead you to consider trying to register a dependency against sys.tables, but system views and tables are not allowed in a query for notifications.

You should be able to work around this by setting up a DDL trigger that inserts into a special table, and then issue queries against that table. For example:

CREATE TRIGGER [MyTrigger] ON DATABASE AFTER CREATE_TABLE AS ...
RickNZ
  • 18,448
  • 3
  • 51
  • 66