SQL Dependancy VB.NET

Other SQL Server Topics
1

Morning all,

I'm reaching out on here as I'm struggeling with using SQL Dependany from a VB.NET application. I have eventually go it to work but the user I log on as needs to have db_owner permissions but I'm not overly comfortable giving all the users db_owner permissions.

I'm a little confused, I noticed i can create a queues & services via SQL Server Managment Studio but the app won't use them and creates its own service & queue on the fly, and rather annoyingly these services & queues then remain until I delete them manually...

Am i missing something here? Any help or explanations are greatly recieved...

Thanks

Dave

EDIT: Test server is SQL Server 2008 R2.

2

please post here the script you used to create you queues & services ?

3 
USE testdb;
CREATE QUEUE testQueue;
CREATE SERVICE testService ON QUEUE testQueue http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO dave;
ALTER DATABASE testdb SET ENABLE_BROKER;
4

is it a domain user or a sql user? either way I do not see you giving permission to a user when creating the Q.
Also please show how you are calling the Q and service from vb.net

5

Not using domain users on the testbox all users are SQL Users. The permissions were modified from: SSMS Security>Logins>dave>Usermapping.

I do not know how to add specific user permissions to a queue?

VB Code:

Option Strict On

Imports System.Data.SqlClient

Public Class Form1

Private Delegate Sub UICallback()

Private Const CS As String = "Data Source=192.168.10.2\test_instance;" +
                            "User ID=dave;Password=dave;" +
                            "Initial Catalog=testdb;" +
                            "Network Library=dbmssocn;" +
                            "Application Name=DemoDep;"

Private Const QuerySelect As String = "SELECT A_TID, A_Num, A_AvailDate FROM dbo.Availability WHERE A_AvailDate >= CONVERT(date, getdate()) ORDER BY A_TID ASC, A_AvailDate ASC"
Private Const QueryDependency As String = "SELECT A_ID FROM dbo.Availability"

Private Sub StartChecking()
    'Create a connection
    Dim cn As SqlClient.SqlConnection = Nothing
    cn = New SqlClient.SqlConnection(CS)

    'Create a DataAdapter with a SELECT query
    Dim dad As New SqlClient.SqlDataAdapter(QueryDependency, cn)

    'Instantiate a SqlDependency object
    Dim dependency As New SqlClient.SqlDependency(dad.SelectCommand)
    'Add a handler for te OnChange event
    AddHandler dependency.OnChange, AddressOf Me.OnNotificationChange
    'Start the SqlDependency object
    SqlClient.SqlDependency.Start(CS)

    'We ne to use the DataAdapter once
    Dim ds As New DataSet
    dad.Fill(ds)
End Sub

Private Sub OnNotificationChange(ByVal sender As Object, ByVal e As SqlClient.SqlNotificationEventArgs)
    Console.WriteLine("Triggered")
    Me.Invoke(New UICallback(AddressOf FillGrid))
    StartChecking()
End Sub

Private Sub FillGrid()
    Using conn As New SqlConnection(CS)
        conn.Open()
        Using cm As New SqlCommand(QuerySelect, conn)
            Using adapter As New SqlDataAdapter(cm)
                Dim ds As New DataSet
                adapter.Fill(ds)
                grdData.DataSource = ds.Tables(0)
            End Using
        End Using
    End Using
End Sub

Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    FillGrid()
    StartChecking()
End Sub

Private Sub Form1_FormClosing(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles MyBase.FormClosing
    SqlClient.SqlDependency.Stop(CS)
End Sub

End Class

I think one of the problems I'm having is that I'm not calling the 'testQueue' specifically, to be honest i'm not sure how.....

6

What is your aim in using service broker? Have you thoroughly read the documentation?

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-queue-transact-sql?view=sql-server-2017

it details exactly how you can setup queues and services with the needed permissions.

7

I have a number of clients that update a table, I want to create a function that keeps an eye on the table so I can get the latest information from it when its updated by another user...

To be completely honest I have glanced over the doumentation but find MS documentation really hard going...

8

:grin: I know how you feel. but if you want to make a 4 course meal, you cant just glance. how about using a trigger or in the entity that manipulates the underlying data make it dump what you need into a log table ? Service broker sounds like a sledge hammer (https://www.youtube.com/watch?v=OJWJE0x7T4Q) to drive a needle, unless you are using it for something else also

9

Ah... I thought using SQL Broker / dependancies was the correct way to get 'real time' updates from a table.

From a VB point of view it allows me to create a trigger or event that tells me the data has changed which in turn can be handled however i see fit, in this case by updating the GUI from a database table.

To be prefectly honest (as you've probably guessed :smiley: ) I've never used it before so was sort of looking forward to learning something new, I might be mis-understanding you here, but is SQL Broker Service considered difficult to configure and/or manage?

10

it is one way to get real time updates for sure. but there are other ways. I would say go for it. Configuring it is not simple nor is it complicated imo.