Enabling the Service Broker

I'm trying to enable the Service Broker for Sql Server 2005 because I want to be able to use a SqlDependency object.

I ran the following query to see if my local sql server service broker was enabled:
SELECT is_broker_enabled FROM sys.databases WHERE name = 'dbname';

It came back with a value of 0 (which means it is not enabled).

I tried executing the following sql command to enable it:
ALTER DATABASE dbname SET ENABLE_BROKER;

The query has been running for over 5 mins and just keeps spinning (should it take this long to enable the Service brfoker), so I cancel it.

I even try to issue the command to see if the service broker is enabled after I cancel the query and it is not enabled.

How can I properly enable the Service Broker?

You need exclusive access to the database. See http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx

HTH,
~ Remus

|||

Thanks for the tip. It resolved my issue of enabling the service broker.

Now that I have it enabled, I am testing an application, and have code set up which is supposed to test whether a table got updated or not and am not haveing any luck with the Haschanges method changing to TRUE.

I have the following sql set up in my stored procedure:

<code>

set ANSI_NULLS ON
set ANSI_PADDING ON
set ANSI_WARNINGS ON
set CONCAT_NULL_YIELDS_NULL ON
set QUOTED_IDENTIFIER ON
set NUMERIC_ROUNDABORT OFF
set ARITHABORT ON
GO
ALTER PROCEDURE [dbo].[SelectAllQCCSwitch]
AS
SET NOCOUNT ON;
SELECT GateWay, Description, CDRTable, Priority
FROM QCCSwitch

</code>

Here is my vb.net code that I have inside a class:

<code>

Public blnQCCSwitch As Boolean = False

Dim depQCCSwitch As New SqlDependency()

.

Public Function LoadQCCSwitch() As dstFindBatch

Dim clsSelectAllQCCSwitch As New SelectAllQCCSwitchTableAdapter

Try

If blnQCCSwitch = False Then

depQCCSwitch.AddCommandDependency(clsSelectAllQCCSwitch.cmdSelectQCCSwitch)

SqlDependency.Start(clsSelectAllQCCSwitch.Connection.ConnectionString)

blnQCCSwitch = True

End If

If (depQCCSwitch.HasChanges) OrElse (blnQCCSwitch = False) Then

clsSelectAllQCCSwitch.dapQCCSwitch.SelectCommand = clsSelectAllQCCSwitch.cmdSelectQCCSwitch

SQLServerDataAccess.GetSQLData(clsSelectAllQCCSwitch.Connection, clsSelectAllQCCSwitch.dapQCCSwitch, DstFindBatch2.SelectAllQCCSwitch)

Else

If DstFindBatch2.SelectAllQCCSwitch.Rows.Count = 0 Then

clsSelectAllQCCSwitch.dapQCCSwitch.SelectCommand = clsSelectAllQCCSwitch.cmdSelectQCCSwitch

SQLServerDataAccess.GetSQLData(clsSelectAllQCCSwitch.Connection, clsSelectAllQCCSwitch.dapQCCSwitch, DstFindBatch2.SelectAllQCCSwitch)

End If

End If

Return DstFindBatch2

Catch ex As Exception

Throw ex

Finally

clsSelectAllQCCSwitch.dapQCCSwitch.Dispose()

clsSelectAllQCCSwitch.Dispose()

End Try

End Function

.</code>

Notice the BOLDFACED text above which is what specifically concerns this post.

During debugging, I simply manually added a record to the table which is included in the "clsSelectAllQCCSwitch.cmdSelectQCCSwitch" Select command object.

I was expecting the HasChanges method of the Dependency object to change to TRUE and read the DB again to refresh the data.

What am I missing that I need to do?

.

|||

After you started the subscription, you are going to receive the notifications on the OnChangeEventHandler callback. You shouldn't explictily check for changes, you should simply continue and rely on the callback to notify you when a change occured.

HTH,
~ Remus

|||

Remus, thanks for the info...

I took that example logic and applied it to my app. I'm still expecting the Onchange event to occur (which only happens the first time the table is hit in my code) after I manually change a record in the table. However, the OnChange event is not firing when I do this. Is this because of the ConnectionString (meaning the table would have to be changed via the same connectionstring other than me doing it manually in order for it to fire)?

Here is my middle tier logic code. If you can verify that is seems okay, I would appreciate it very much. Or if I need to change something else.....

<code>

Public Function LoadQCCSwitch() As dstFindBatch

Dim clsSelectAllQCCSwitch As New SelectAllQCCSwitchTableAdapter

Try
SqlDependency.Stop(clsSelectAllQCCSwitch.Connection.ConnectionString)
SqlDependency.Start(clsSelectAllQCCSwitch.Connection.ConnectionString)
Dim depQCCSwitch As New SqlDependency(clsSelectAllQCCSwitch.cmdSelectQCCSwitch)
AddHandler depQCCSwitch.OnChange, AddressOf QCCSwitchDependency_OnChange
If DstFindBatch2.SelectAllQCCSwitch.Rows.Count = 0 Then
clsSelectAllQCCSwitch.dapQCCSwitch.SelectCommand = clsSelectAllQCCSwitch.cmdSelectQCCSwitch
SQLServerDataAccess.GetSQLData(clsSelectAllQCCSwitch.Connection, clsSelectAllQCCSwitch.dapQCCSwitch, DstFindBatch2.SelectAllQCCSwitch)
End If
Return DstFindBatch2
Catch ex As Exception
Throw ex
Finally
clsSelectAllQCCSwitch.dapQCCSwitch.Dispose()
clsSelectAllQCCSwitch.Dispose()
End Try

End Function

Private Sub QCCSwitchDependency_OnChange(ByVal sender As Object, ByVal e As SqlNotificationEventArgs)

Dim clsSelectAllQCCSwitch As New SelectAllQCCSwitchTableAdapter

Try
clsSelectAllQCCSwitch.dapQCCSwitch.SelectCommand = clsSelectAllQCCSwitch.cmdSelectQCCSwitch
SQLServerDataAccess.GetSQLData(clsSelectAllQCCSwitch.Connection, clsSelectAllQCCSwitch.dapQCCSwitch, DstFindBatch2.SelectAllQCCSwitch)
Catch ex As Exception
Throw ex
Finally
clsSelectAllQCCSwitch.dapQCCSwitch.Dispose()
clsSelectAllQCCSwitch.Dispose()
Dim dependency As SqlDependency = CType(sender, SqlDependency)
RemoveHandler dependency.OnChange, AddressOf QCCSwitchDependency_OnChange
End Try

End Sub
</code>

|||

Have a look at the MSDN SqlDependency sample at http://msdn2.microsoft.com/en-us/a52dhwx7.aspx

The updates that trigger the notification can happen on any session and under any settings, it is not related whatsoever to the connection string of the SqlDependency.

Each time the OnChange is fired, the underlying Query Notification gets torn down and you have to subscribe again to get a new notification next time a change occurs. In the MSDN sample, the client calls again GetData at the end of it's dependency_OnChange method. Inside the GetData method, it sets up again a notification.

If you believe you've set up the notification correctly and applied an update yet the notification does not fire here are some steps to investigate:

- make sure the notification is created, see it in select * from sys.dm_qn_subscriptions
- make sutre the notification is fired. The Profiler will show this as an Broker:Broker Conversation event with the subclass SEND.
- make sure the notification message is delivered. Look in the database sys.transmission_queue, if the notification is still pending there it should have a transmission_status explaining why it cannot be delivered. My blog has a troubleshooting guide, at http://blogs.msdn.com/remusrusanu/archive/2005/12/20/506221.aspx. While is generic for Service Broker, it applies just as well to Query Notification messages.

If the notification is successfuly delivered, then the OnChange method should fire.

HTH,
~ Remus