none
Service Broker, I need some help plz!!

    Question

  • Hi,

    I have a service that uses sqlnotificationRequest, it works kinda alright, it received messages from sql on a particular table.

    Okay, I copied that majority of code for another service and this one does not get any messages, timeouts, insert nothing.

    Can somebody please help me out. I will post the script I use to set up the service broker and the sqlnotificationRequest code. Although I think it's on the sql side that the problem is occurring.

    so basically I have two users, cdwf_Notification is the account the service will run under in production.

    and wwprogramming\programming_restricted for testing.

    DECLARE @SPID int
    DECLARE Process_Cursor CURSOR FOR
    		SELECT sp.spid
    		  FROM master..sysprocesses sp
    		 INNER JOIN sys.databases sd
    			ON sd.database_id = sp.DBID
    		 WHERE sd.name = 'CabinetDoor'
    		   AND NOT sp.spid = @@spid
    	
    	OPEN Process_Cursor
    	
    	FETCH NEXT FROM Process_Cursor
    	 INTO @SPID
    	
    	WHILE @@FETCH_STATUS = 0
    	BEGIN
    		PRINT 'Killing spid = ' + LTRIM(STR(@SPID))
    		EXEC('KILL ' + @SPID )
    			
    		FETCH NEXT FROM Process_Cursor
    		 INTO @SPID
    	END
    	
    	CLOSE Process_Cursor
    	DEALLOCATE Process_Cursor
    
    USE [master]
    GO
    IF NOT EXISTS (SELECT name FROM Master.sys.server_principals WHERE name = 'WWPROGRAMMING\Programming_Restricted')
    BEGIN
    	CREATE LOGIN [WWPROGRAMMING\Programming_Restricted] FROM WINDOWS WITH DEFAULT_DATABASE=[CabinetDoor]
    
    END
    GO
    
    USE [CabinetDoor]
    IF NOT EXISTS (SELECT name FROM sys.database_principals WHERE name = 'WWPROGRAMMING\Programming_Restricted')
    BEGIN
    	CREATE USER [WWPROGRAMMING\Programming_Restricted] FOR LOGIN [WWPROGRAMMING\Programming_Restricted]
    END
    GO
    
    
    IF NOT EXISTS (SELECT name FROM Master.sys.server_principals WHERE name = 'WWPROGRAMMING\CDWF_NotificationSer')
    BEGIN
    	CREATE LOGIN [WWPROGRAMMING\CDWF_NotificationSer] FROM WINDOWS WITH DEFAULT_DATABASE=[CabinetDoor]
    
    END
    GO
    USE [CabinetDoor]
    IF NOT EXISTS (SELECT name FROM sys.database_principals WHERE name = 'WWPROGRAMMING\CDWF_NotificationSer')
    BEGIN
    	CREATE USER [WWPROGRAMMING\CDWF_NotificationSer] FOR LOGIN [WWPROGRAMMING\CDWF_NotificationSer]
    END
    GO
    
    ALTER DATABASE [CabinetDoor] SET  ENABLE_BROKER WITH NO_WAIT
    GO
    
    USE CabinetDoor
    
    CREATE QUEUE WFMessages
    
    CREATE SERVICE WFNotifications
    ON QUEUE WFMessages
    --([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);
    
    CREATE ROUTE WFRoute
    WITH SERVICE_NAME = 'WFNotifications',
    	 ADDRESS = 'LOCAL';
    
    GRANT DELETE TO [WWPROGRAMMING\CDWF_NotificationSer]
    GO
    GRANT EXECUTE TO [WWPROGRAMMING\CDWF_NotificationSer]
    GO
    GRANT INSERT TO [WWPROGRAMMING\CDWF_NotificationSer]
    GO
    GRANT SELECT TO [WWPROGRAMMING\CDWF_NotificationSer]
    GO
    GRANT UPDATE TO [WWPROGRAMMING\CDWF_NotificationSer]
    GO
    
    
    GRANT DELETE TO [WWPROGRAMMING\Programming_Restricted]
    GO
    GRANT EXECUTE TO [WWPROGRAMMING\Programming_Restricted]
    GO
    GRANT INSERT TO [WWPROGRAMMING\Programming_Restricted]
    GO
    GRANT SELECT TO [WWPROGRAMMING\Programming_Restricted]
    GO
    GRANT UPDATE TO [WWPROGRAMMING\Programming_Restricted]
    GO
    
    
    --Security for Service Broker...
    GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [WWPROGRAMMING\CDWF_NotificationSer]
    GO
    GRANT VIEW DEFINITION TO [WWPROGRAMMING\CDWF_NotificationSer]
    GO
    
    GRANT RECEIVE ON [dbo].[WFMessages] TO [WWPROGRAMMING\CDWF_NotificationSer]
    GO
    GRANT REFERENCES ON [dbo].[WFMessages] TO [WWPROGRAMMING\CDWF_NotificationSer]
    GO
    
    GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [WWPROGRAMMING\Programming_Restricted]
    GO
    GRANT VIEW DEFINITION TO [WWPROGRAMMING\Programming_Restricted]
    GO
    
    GRANT RECEIVE ON [dbo].[WFMessages] TO [WWPROGRAMMING\Programming_Restricted]
    GO
    GRANT REFERENCES ON [dbo].[WFMessages] TO [WWPROGRAMMING\Programming_Restricted]
    GO
    
    --May not be required*****************************************************
    GRANT SEND ON service::[http://schemas.microsoft.com/SQL/Notifications/QueryNotificationService] TO [WWPROGRAMMING\CDWF_NotificationSer]
    GO
    
    GRANT SEND ON service::[http://schemas.microsoft.com/SQL/Notifications/EventNotificationService] TO [WWPROGRAMMING\CDWF_NotificationSer]
    GO
    
    GRANT SEND ON service::[http://schemas.microsoft.com/SQL/ServiceBroker/ServiceBroker] TO [WWPROGRAMMING\CDWF_NotificationSer]
    GO
    
    GRANT SEND ON service::[http://schemas.microsoft.com/SQL/Notifications/QueryNotificationService] TO [WWPROGRAMMING\Programming_Restricted]
    GO
    
    GRANT SEND ON service::[http://schemas.microsoft.com/SQL/Notifications/EventNotificationService] TO [WWPROGRAMMING\Programming_Restricted]
    GO
    
    GRANT SEND ON service::[http://schemas.microsoft.com/SQL/ServiceBroker/ServiceBroker] TO [WWPROGRAMMING\Programming_Restricted]
    GO
    --***********************************************************************
    
    GRANT SEND ON service::[WFNotifications] TO [WWPROGRAMMING\CDWF_NotificationSer]
    GO
    
    GRANT RECEIVE ON [dbo].[QueryNotificationErrorsQueue] TO [WWPROGRAMMING\CDWF_NotificationSer]
    GO
    GRANT REFERENCES ON [dbo].[QueryNotificationErrorsQueue] TO [WWPROGRAMMING\CDWF_NotificationSer]
    GO
    
    GRANT SEND ON service::[WFNotifications] TO [WWPROGRAMMING\Programming_Restricted]
    GO
    
    GRANT RECEIVE ON [dbo].[QueryNotificationErrorsQueue] TO [WWPROGRAMMING\Programming_Restricted]
    GO
    GRANT REFERENCES ON [dbo].[QueryNotificationErrorsQueue] TO [WWPROGRAMMING\Programming_Restricted]
    GO
    
    --****************************************************************************************************
    
    --REVOKE RECEIVE ON [dbo].[EventNotificationErrorsQueue] TO [WWPROGRAMMING\CDWF_NotificationSer]
    --GO
    --REVOKE REFERENCES ON [dbo].[EventNotificationErrorsQueue] TO [WWPROGRAMMING\CDWF_NotificationSer]
    --GO
    
    --REVOKE RECEIVE ON [dbo].[ServiceBrokerQueue] TO [WWPROGRAMMING\CDWF_NotificationSer]
    --GO
    --REVOKE REFERENCES ON [dbo].[ServiceBrokerQueue] TO [WWPROGRAMMING\CDWF_NotificationSer]
    --GO
    
    --GRANT RECEIVE ON [dbo].[EventNotificationErrorsQueue] TO [WWPROGRAMMING\Programming_Restricted]
    --GO
    --GRANT REFERENCES ON [dbo].[EventNotificationErrorsQueue] TO [WWPROGRAMMING\Programming_Restricted]
    --GO
    
    --GRANT RECEIVE ON [dbo].[ServiceBrokerQueue] TO [WWPROGRAMMING\Programming_Restricted]
    --GO
    --GRANT REFERENCES ON [dbo].[ServiceBrokerQueue] TO [WWPROGRAMMING\Programming_Restricted]
    --GO
    

    Now the SqlNotificationRequest code:

    Private m_dependency_options As String = "Service=WFNotifications"
    Private m_dependency_SelectText As String = "Notification_Select"
    Private m_callback As New AsyncCallback(AddressOf dependency_onchange)
    
    #Region "   setup_connection"
    
        Private Function setup_connection() As Boolean
    
            Try
                WriteEntry("In setup_connection")
                'first time through, 
                'keep a class level sqlconnection and sqlcommand object
                m_dependency_conn = New SqlConnection(m_connectionstring)
                m_dependency_cmd = m_dependency_conn.CreateCommand
                m_dependency_cmd.CommandType = CommandType.StoredProcedure
                m_dependency_cmd.CommandText = m_dependency_SelectText
                m_dependency_conn.Open()
                m_dependency_cmd.Notification = Nothing
            Catch ex As Exception
                Return False
            End Try
            Return True
        End Function
    
    #End Region
    
    #Region "   init_notificationrequest"
    
        Private Function init_notificationrequest(options As String) As Boolean
            Try
                If m_dependency_cmd.Notification Is Nothing Then
                    Dim request As New SqlNotificationRequest()
                    request.UserData = Guid.NewGuid().ToString()
                    request.Options = options
                    request.Timeout = m_dependency_timeout
                    m_dependency_cmd.Notification = request
                End If
                If m_dependency_conn.State = ConnectionState.Open Then
                    m_dependency_conn.Close()
                End If
                m_dependency_conn.Open()
                m_dependency_cmd.ExecuteNonQuery()
            Catch ex As Exception
                Return False
            End Try
            Return True
        End Function
    
    #End Region
    
    #Region "   configure_dependency_cmd"
    
        Private Sub configure_dependency_cmd()
    
            Try
                Using command As New SqlCommand(m_dependencyCommandText, m_dependency_conn)
                    If Not m_dependency_conn.State = ConnectionState.Open Then
                        m_dependency_conn.Open()
                    End If
                    command.CommandTimeout = m_dependency_timeout
                    command.BeginExecuteReader(m_callback, command)
                End Using
            Catch ex As Exception
                m_exceptionmsg = String.Format(m_exceptionmsg, ex.Message, ex.InnerException)
                WriteEntry(String.Format("Error In configure_dependency_cmd {0}", ex.Message), EventLogEntryType.Error)
                Throw
            End Try
    
        End Sub
    
    #End Region
    
    #Region "   dependency_onchange"
    
        Private Sub dependency_onchange(asynresult As IAsyncResult)
    
            Try
                WriteEntry("In Dependency_OnChange")
                Dim qn As QueryNotification = QueryNotification.Parse(asynresult)
    
                WriteEntry(String.Format("{0} : {1}", qn.ToString(), Now.ToString("hh:mm:ss")), EventLogEntryType.Warning)
                If qn.Type = SqlNotificationType.Change Then
                    Select Case qn.Source
                        Case SqlNotificationSource.Data
                            Select Case qn.Info
                                Case SqlNotificationInfo.Insert
                                    If m_servicehost.State = CommunicationState.Faulted Then
                                        RemoveHandler DirectCast(m_servicehost, ICommunicationObject).Faulted, AddressOf Faulted
                                        m_servicehost = New ServiceHost(New MultiWCFNotificationRequestService(Me), New Uri() {m_serviceuri})
                                        AddHandler DirectCast(m_servicehost, ICommunicationObject).Faulted, AddressOf Faulted
                                        m_servicehost.Open()
                                    End If
    
                                    If m_servicehost.State = CommunicationState.Opened Then
                                        Try
                                            'Go ahead and call select, process records
                                            m_servicehost.SingletonInstance.NotifySubscriber(loaddata(m_dependency_cmd))
                                        Catch ex As Exception
                                            WriteEntry(String.Format("dependency_onchange: NotifySubscriber caused an exception. {0}", ex.Message), EventLogEntryType.Error)
                                        End Try
                                    End If
                            End Select
                    End Select
                End If
                If m_running Then
                    If init_notificationrequest(m_dependency_options) Then
                        configure_dependency_cmd()
                    End If
                End If
            Catch ex As Exception
    
            End Try
    
        End Sub
    
    #End Region


    • Edited by garusher Tuesday, March 25, 2014 6:55 PM
    Tuesday, March 25, 2014 6:53 PM

Answers

All replies