none
Dependency Event not firering though queue is created on the SQL server

    Question

  • I am trying to get my VB.net to execute some code when there is changes in a specific table.

    Rights on my SQL user should be right, the queue is created on the server when the Dependency.Start is run, but the event is not being caught when I make any kind of changes in the table.

    Here are alle the code bits that apply to the problem, I hope someone can see what I am doing wrong before I loose all my hair :-(

        Dim ConnectionString As String = "Data Source=nef-sql.nef.local;User ID=SmileSQL;Password=Password;Initial Catalog=Smile;Network Library=dbmssocn;Application Name=WaooSmile;"
        Dim QueryUpdate As String = "SELECT * FROM SmileDansam WHERE TransactionID = 0"
        Dim QueryDependency As String = "SELECT DansamID FROM dbo.SmileDansam WHERE TransactionID = 0"
    
    
        Public Sub StartDependency()
            If Not DoesUserHavePermission() Then
                PrintDebug("Not correct userrights")
            End If
            Dim cn As SqlClient.SqlConnection = Nothing
            cn = New SqlClient.SqlConnection(ConnectionString)
            PrintDebug(SqlClient.SqlDependency.Stop(ConnectionString))
            
            Dim cmd As SqlCommand = cn.CreateCommand()
    
            Using cmd
                cmd.CommandType = CommandType.Text
                cmd.CommandText = QueryDependency
                cmd.Notification = Nothing
            End Using
            Dim dependency As New SqlClient.SqlDependency(cmd)
            AddHandler dependency.OnChange, AddressOf Me.OnNotificationChange
            PrintDebug("Starting dependency")
            PrintDebug(SqlClient.SqlDependency.Start(ConnectionString))
            PrintDebug("Dependency startet")
    
            cn.Open()
            Dim command As New SqlCommand(QueryUpdate, cn)
            Dim reader As SqlDataReader
            reader = command.ExecuteReader()
            If reader.HasRows Then
                While reader.Read()
                    PrintDebug(reader("Request").ToString & " - " & reader("DansamID"))
                End While
            End If
        End Sub
    
      Private Sub OnNotificationChange(ByVal sender As Object, ByVal e As SqlClient.SqlNotificationEventArgs)
            Dim cn As New SqlClient.SqlConnection(ConnectionString)
            Dim command As New SqlCommand(QueryUpdate, cn)
            Dim reader As SqlDataReader
            reader = command.ExecuteReader()
            If reader.HasRows Then
                While reader.Read()
                    PrintDebug(reader("Request").ToString & " - " & reader("DansamID"))
                End While
            End If
    
        End Sub
    
        Private Function DoesUserHavePermission() As Boolean
            Try
                Dim clientPermission As SqlClientPermission = New SqlClientPermission(Security.Permissions.PermissionState.Unrestricted)
                ' this will throw an error if the user does not have the permissions  
                clientPermission.Demand()
                Return True
            Catch ex As Exception
                Return False
            End Try
            Return True
        End Function

       


    Saturday, July 06, 2013 3:12 PM

Answers

  • Execute the cmd query in order to establish the dependency:

            PrintDebug("Starting dependency")
            PrintDebug(SqlClient.SqlDependency.Start(ConnectionString))
            PrintDebug("Dependency started")
    
            cn.Open()
            Dim r As SqlDataReader = cmd.ExecuteReader
            While r.Read
            End While
            r.Close()
            cn.Close()

    Also, you need to open and close the connection and reader in your OnNotificationChange handler (and restart the dependency if desired).


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, July 06, 2013 6:07 PM

All replies

  • Execute the cmd query in order to establish the dependency:

            PrintDebug("Starting dependency")
            PrintDebug(SqlClient.SqlDependency.Start(ConnectionString))
            PrintDebug("Dependency started")
    
            cn.Open()
            Dim r As SqlDataReader = cmd.ExecuteReader
            While r.Read
            End While
            r.Close()
            cn.Close()

    Also, you need to open and close the connection and reader in your OnNotificationChange handler (and restart the dependency if desired).


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, July 06, 2013 6:07 PM
  • Thank you. I knew it was something stupid I was missing.
    Saturday, July 06, 2013 7:24 PM