6

I want to check if Broker Service is running using code and depending on the status, start sqldependency or not. How can I do that?

IamDeveloper
  • 5,156
  • 6
  • 34
  • 50
  • 1
    I'm not familiar with them, but there are a few APIs available which wrap the service broker - I'm sure one or more of them will have something to do what you want to do. http://stackoverflow.com/questions/2089184/net-api-for-sql-service-broker – FinnNk Aug 10 '10 at 08:06

1 Answers1

12

You can do a simple query:

SELECT is_broker_enabled FROM sys.databases WHERE Name = 'mydatabasename'

Alternatively you can just start the SqlDependency and trap the error you get if it hasn't been enabled, but the first method is simpler and better:

  try {
      SqlDependency.Start();
  } catch (InvalidOperationException ex) {
      // If broker hasn't been enabled, you'll get the following exception:
      //
      // The SQL Server Service Broker for the current database is not enabled, and
      // as a result query notifications are not supported.  Please enable the Service
      // Broker for this database if you wish to use notifications.
  }
Richard
  • 29,854
  • 11
  • 77
  • 120
  • hmm, I want to use c# for that, but ideally would like to skip selecting something. Isn't there some API for that? – IamDeveloper Aug 10 '10 at 08:34
  • 1
    There is no official API method as far as I'm aware. Any API you used would simply be calling the SQL query I indicated anyway. If you really don't want to execute a simple SQL query from your code, then you can just trap the exception raised by SqlDependency.Start() - simple code added to my answer. – Richard Aug 10 '10 at 09:38