none
SQL Notifications using SqlDependency not working RRS feed

  • Question

  • Hello.

    I have implemented and tested a SqlDependency in a Windows Application example on MSDN: https://msdn.microsoft.com/en-us/library/a52dhwx7(v=vs.80).aspx

    Unfortunately the example is not running.

    I tried to set up all the necessary options on SQL Server as well which I found on:

    http://www.codeproject.com/Articles/144344/Query-Notification-using-SqlDependency-and-SqlCach#heading0003

    I enclose source code for both Watcher and Updater application and also SQL Script which should enable all the necessary settings on SQL Server to allow SQL Notifications using SqlDependency.

    I will be glad for any help. Is there something I missed?

    Thank you very much in advance.

    Best Regards,

    Zdeněk Neustupa

    -----------------------------------  Watcher Application  --------------------------------------------

    public partial class MainWindow : Window
        {
    
            private int changeCount = 0;
            private const string tableName = "Technician";
            private const string statusMessage = "{0} changes have occurred.";
                    
            private DataSet dataToWatch = null;
            private SqlConnection connection = null;
            private SqlCommand command = null;
    
            public MainWindow()
            {
                InitializeComponent();
                bGetData.IsEnabled = CanRequestNotifications();
            }
    
            private bool CanRequestNotifications()
            {            
                try
                {
                    SqlClientPermission perm = new SqlClientPermission(PermissionState.Unrestricted);
                    perm.Demand();
                    return true;
                }
                catch
                {
                    return false;
                }
            }
    
            private string GetConnectionString()
            {            
                return @"Server=server08\SQLSERVER2008;Database=emistar;User Id=terminal01;Password=TinT434041,;";
            }
    
            private string GetSQL()
            {
                return "SELECT TechnicianName FROM Technician ORDER BY TechnicianName ASC";
            }
    
            private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
            {            
                ISynchronizeInvoke i = (ISynchronizeInvoke)this;            
                if (i.InvokeRequired)
                {               
                    OnChangeEventHandler tempDelegate =
                        new OnChangeEventHandler(dependency_OnChange);
                    object[] args = { sender, e };                
                    i.BeginInvoke(tempDelegate, args);
                    return;
                }
                
                SqlDependency dependency = (SqlDependency)sender;
                dependency.OnChange -= dependency_OnChange;
                            
                ++changeCount;
                lChanges.Content = String.Format(statusMessage, changeCount);
                            
                lbSelect.Items.Clear();
                lbSelect.Items.Add("Info:   " + e.Info.ToString());
                lbSelect.Items.Add("Source: " + e.Source.ToString());
                lbSelect.Items.Add("Type:   " + e.Type.ToString());            
                GetData();
            }
    
            private void GetData()
            {            
                dataToWatch.Clear();           
                command.Notification = null;            
                SqlDependency dependency = new SqlDependency(command, "Service='ChangeNotifications'", 0);
                dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);            
    
                using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                {
                    adapter.Fill(dataToWatch, tableName);
                    dgMain.ItemsSource = dataToWatch.Tables["Technician"].DefaultView;                
                }
            }
    
            private void bGetData_Click(object sender, EventArgs e)
            {
                changeCount = 0;
                lChanges.Content = String.Format(statusMessage, changeCount);
                
                SqlDependency.Stop(GetConnectionString(), "ChangeMessages");
                SqlDependency.Start(GetConnectionString(), "ChangeMessages");
    
                if (connection == null)
                {
                    connection = new SqlConnection(GetConnectionString());
                }
    
                if (command == null)
                {                
                    command = new SqlCommand("SelectTechnicians", connection);
                    command.CommandType = CommandType.StoredProcedure;                
                }
                if (dataToWatch == null)
                {
                    dataToWatch = new DataSet();
                }
                GetData();
            }
        }

    ---------------------------------------------  Updater Application  --------------------------------------------------

    public partial class MainWindow : Window
        {   
            private SqlConnection connection = null;
            private SqlCommand command = null;
    
            public MainWindow()
            {
                InitializeComponent();
            }
    
            private string GetConnectionString()
            {            
                return @"Server=server08\SQLSERVER2008;Database=emistar;User Id=terminal01;Password=TinT434041,;";
            }
    
            private string GetSQL()
            {            
                return "UPDATE emistar.dbo.Technician SET TechnicianName=@TechName WHERE TechnicianID=1";
            }
    
            private void bInsertClick(object sender, EventArgs e)
            {            
                string techName = txtName.Text;
    
                if (connection == null)
                {
                    connection = new SqlConnection(GetConnectionString());
                    connection.Open();
                }
    
                if (command == null)
                {               
                    command = new SqlCommand(GetSQL(), connection);
                    SqlParameter param = new SqlParameter("@TechName", SqlDbType.NVarChar);
                    param.Direction = ParameterDirection.Input;
                    param.DbType = DbType.String;
                    command.Parameters.Add(param);
                }
    
                command.Parameters["@TechName"].Value = techName;
                int rowsAffected = command.ExecuteNonQuery();
                txtName.Clear();
                MessageBox.Show(rowsAffected + " records updated.", "Update");
            }
    
            private void windowClosing(object sender, EventArgs e)
            {
                if (connection != null)
                {
                    connection.Close();
                }
            }
    
        }

    ---------------------------------------------   SQL Server Settings  ---------------------------------------------

    ALTER DATABASE emistar SET ENABLE_BROKER;
    
    CREATE QUEUE ChangeMessages;
    
    CREATE SERVICE ChangeNotifications ON QUEUE ChangeMessages([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);
    
    --DBA creates a new role
    EXEC sp_addrole 'sql_dependency_subscriber'
    
    ---------------------    User terminal01   -----------------------------
    GRANT SUBSCRIBE QUERY NOTIFICATIONS TO terminal01
    
    --Permission needed for startUser
    GRANT CREATE PROCEDURE to terminal01
    GRANT CREATE QUEUE to terminal01
    GRANT CREATE SERVICE to terminal01
    GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to terminal01
    GRANT VIEW DEFINITION TO terminal01
    --Permissions needed for executeUser
    GRANT SELECT to terminal01
    GRANT SUBSCRIBE QUERY NOTIFICATIONS TO terminal01
    GRANT RECEIVE ON QueryNotificationErrorsQueue TO terminal01
    GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to terminal01
    EXEC sp_addrolemember 'sql_dependency_subscriber', 'terminal01'
    
    GRANT RECEIVE ON ChangeMessages TO terminal01;
    GRANT SEND ON SERVICE::ChangeNotifications TO terminal01;
    
    GRANT CREATE PROCEDURE to terminal01;
    GRANT CREATE QUEUE to terminal01;
    GRANT CREATE SERVICE to terminal01;
    
    GRANT SUBSCRIBE QUERY NOTIFICATIONS TO terminal01;
    
    GRANT INSERT ON SCHEMA::dbo TO terminal01;
    GRANT SELECT ON SCHEMA::dbo TO terminal01;
    
    GRANT ALTER ON SCHEMA::dbo TO terminal01;
    GRANT CONTROL ON SCHEMA::dbo TO terminal01;
    --GRANT CREATE SEQUENCE ON SCHEMA::dbo TO terminal01;
    GRANT DELETE ON SCHEMA::dbo TO terminal01;
    GRANT EXECUTE ON SCHEMA::dbo TO terminal01;
    GRANT INSERT ON SCHEMA::dbo TO terminal01;
    GRANT REFERENCES ON SCHEMA::dbo TO terminal01;
    GRANT SELECT ON SCHEMA::dbo TO terminal01;
    GRANT TAKE OWNERSHIP ON SCHEMA::dbo TO terminal01;
    GRANT UPDATE ON SCHEMA::dbo TO terminal01;
    GRANT VIEW CHANGE TRACKING ON SCHEMA::dbo TO terminal01;
    GRANT VIEW DEFINITION ON SCHEMA::dbo TO terminal01;
    
    ---------------------    User terminal02   -----------------------------
    
    GRANT SUBSCRIBE QUERY NOTIFICATIONS TO terminal02
    
    --Permission needed for startUser
    GRANT CREATE PROCEDURE to terminal02
    GRANT CREATE QUEUE to terminal02
    GRANT CREATE SERVICE to terminal02
    GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to terminal02
    GRANT VIEW DEFINITION TO terminal02
    --Permissions needed for executeUser
    GRANT SELECT to terminal02
    GRANT SUBSCRIBE QUERY NOTIFICATIONS TO terminal02
    GRANT RECEIVE ON QueryNotificationErrorsQueue TO terminal02
    GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to terminal02
    EXEC sp_addrolemember 'sql_dependency_subscriber', 'terminal02'
    
    GRANT RECEIVE ON ChangeMessages TO terminal02;
    GRANT SEND ON SERVICE::ChangeNotifications TO terminal02;
    
    GRANT CREATE PROCEDURE to terminal02;
    GRANT CREATE QUEUE to terminal02;
    GRANT CREATE SERVICE to terminal02;
    
    GRANT SUBSCRIBE QUERY NOTIFICATIONS TO terminal02;
    
    GRANT INSERT ON SCHEMA::dbo TO terminal02;
    GRANT SELECT ON SCHEMA::dbo TO terminal02;
    
    GRANT ALTER ON SCHEMA::dbo TO terminal02;
    GRANT CONTROL ON SCHEMA::dbo TO terminal02;
    --GRANT CREATE SEQUENCE ON SCHEMA::dbo TO terminal02;
    GRANT DELETE ON SCHEMA::dbo TO terminal02;
    GRANT EXECUTE ON SCHEMA::dbo TO terminal02;
    GRANT INSERT ON SCHEMA::dbo TO terminal02;
    GRANT REFERENCES ON SCHEMA::dbo TO terminal02;
    GRANT SELECT ON SCHEMA::dbo TO terminal02;
    GRANT TAKE OWNERSHIP ON SCHEMA::dbo TO terminal02;
    GRANT UPDATE ON SCHEMA::dbo TO terminal02;
    GRANT VIEW CHANGE TRACKING ON SCHEMA::dbo TO terminal02;
    GRANT VIEW DEFINITION ON SCHEMA::dbo TO terminal02;



    Zdeněk Neustupa

    Monday, May 4, 2015 6:13 AM

Answers

All replies

  • Hello Zdeněk Neustupa,

    For using the sqldependency, there are a lot of restricts you need to pay attention to, on the database side, please make sure you enable Service Broker:

    ALTER DATABASE DFDB SET ENABLE_BROKER

    For the select statement: yours needs to meet below requirements:

    Supported SELECT Statements: https://msdn.microsoft.com/library/ms181122.aspx, the first one shows that SELECT statement must be explicitly stated, and table names must be qualified with two-part names as “SELECT <field> FROM dbo.Table” rather than “SELECT <field> FROM Table”, in your case, please add the schema part to check if it works.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, May 5, 2015 3:48 AM
    Moderator
  • Hello Fred Bao.

    Firstly thank you very much for your response.

    Unfortunately, the proposed solution is not working for me.

    As you can see in the script attached to an original question I have already enabled the broker function.

    ALTER DATABASE emistar SET ENABLE_BROKER;

    And according to your answer, I altered the SQL statements like this:

    SELECT emistar.dbo.Technician.TechnicianName FROM emistar.dbo.Technician ORDER BY emistar.dbo.Technician.TechnicianName ASC;

    and

    UPDATE emistar.dbo.Technician SET emistar.dbo.Technician.TechnicianName=@TechName WHERE emistar.dbo.Technician.TechnicianID=1;

    Still no change. The update is performed but the Watcher Application never automatically updates the dataset connected to the table.

    I will be glad for any other suggestions from anybody.

    Again thank you in advance.

    Best regards,

    Zdeněk Neustupa



    Zdeněk Neustupa

    Thursday, May 14, 2015 5:42 AM
  • Hello Zdeněk Neustupa,

    After checking that link you provided, I made a demo, however, it could work as expected, here is the demo code, you could have a check, since I am not experienced with WPF, I created a windows form project instead:

    The SQLNotifier class(I copied it completely from the link, just modified the connection string and the select statement):

    The query:

    public DataTable RegisterDependency()
    
            {
    
    
                this.CurrentCommand = new SqlCommand("select [OrderID], [OrderName] from [dbo].[Order] ORDER BY [dbo].[Order].[OrderID] ", this.CurrentConnection);
    
                this.CurrentCommand.Notification = null;
    
    
                SqlDependency dependency = new SqlDependency(this.CurrentCommand);
    
                dependency.OnChange += this.dependency_OnChange;
    
    
                if (this.CurrentConnection.State == ConnectionState.Closed)
    
                    this.CurrentConnection.Open();
    
                try
    
                {
    
    
                    DataTable dt = new DataTable();
    
                    dt.Load(this.CurrentCommand.ExecuteReader(CommandBehavior.CloseConnection));
    
                    return dt;
    
                }
    
                catch { return null; }
    
            }
    


    The form.cs:

    using System;
    
    using System.Collections.Generic;
    
    using System.ComponentModel;
    
    using System.Data;
    
    using System.Drawing;
    
    using System.Linq;
    
    using System.Text;
    
    using System.Threading.Tasks;
    
    using System.Windows.Forms;
    
    
    namespace P20150515
    
    {
    
        public partial class Form1 : Form
    
        {
    
            protected SQLNotifier _SQLNotifier = new SQLNotifier();
    
            public Form1()
    
            {
    
                InitializeComponent();
    
            }
    
    
            private void Form1_Load(object sender, EventArgs e)
    
            {
    
                this.dataGridView1.DataSource = _SQLNotifier.RegisterDependency();
    
                _SQLNotifier.NewMessage += _SQLNotifier_NewMessage;
    
            }
    
    
            void _SQLNotifier_NewMessage(object sender, System.Data.SqlClient.SqlNotificationEventArgs e)
    
            {
    
    
            }
    
        }
    
    }
    

    And as soon as I modified the data in database, the dependency_OnChage method is fired:

    You could try to create window form application to check if this could be related with WPF project.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, May 15, 2015 6:20 AM
    Moderator
  • Hello Fred.

    Again, thank you very much for your response.

    As you suggested I implemented Windows Forms application. I didn’t make any custom queue or service for SQL notifications and everything seems to be working as expected.

    The exactly same code is not working in WPF application. The event which triggers the On_Change handler is never fired (at least it seems like it).

    I can use Windows Forms instead of WPF but there is still one issue which is bothering me.

    In Windows Forms application the counter of changes in the database is constantly increasing. After few seconds of running the Watcher Application the number of changes (presented in Label1) is over a thousand and keeps going even the Watcher Application is running solo and no other application is changing anything in the watched table.

    DataSet and subsequently also the GridView are constantly refreshing. The refresh causes reset of ordering and filtering of the GridView. Also the Watcher Application consumes about 13% of processor time (Intel Core i7-4702MQ @2.20GHz 2.20GHz), which I find very unsatisfying.

    I thought that use of SqlDependency will enhance the performance, but I would have nearly same results using a special thread which would refresh the dataset in infinite loop in the background of the application. I thought that the application is somehow idling and only if there is a change which affects the result of the SQL SELECT statement connected to SqlDependency object the event is fired and the DataSet is updated.

    Please can you let me know if you have similar results (behaviour) in your implementation of the Watcher Application?

    Thank you.

    Best regards,

     Zdeněk Neustupa


    Zdeněk Neustupa

    Saturday, May 16, 2015 9:32 AM
  • Hello Zdeněk Neustupa,

    >>As you suggested I implemented Windows Forms application. I didn’t make any custom queue or service for SQL notifications and everything seems to be working as expected.

    This sounds like the WPF project is not compatible with the database event, however, I am not sure, you could confirm it one WPF forum.

    >> Please can you let me know if you have similar results (behaviour) in your implementation of the Watcher Application?

    I do not have a watcher application, however, as far as I know, when using the SqlDependency, it would impact the performance of application. From your description, your database seems to be updated frequently, in this scenario, I suggest nstead of updating the database after every change using OnChanged events, you could rather have an event e.g. an 'Update Database' button or update on closing the application that goes through and does all database updates in a batch. Constantly talking to the database is very noisy and leads to slow performance.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, May 18, 2015 3:32 AM
    Moderator
  • Hello Fred.

    Thanks again.

    My database is not updated very frequently, but it is essential the grid view displays to date data in any time. Unfortunately it is not possible for me to let the users update the view for themselves. We are developing very specific application integration barcode scanners and most of the terminals won’t have keyboards to interact with the application.

    I just don’t understand why the notifications are firing in such a manner. As I have written in previous post. There are no changes being made in the watched table and the events never stop firing.

    Is there any setting I can change either on SQL Server or on SqlDependency object to really receive notifications about changes in the watched table?

    Because otherwise, I don’t see how could be SQL Notifications used for this kind of applications.

    Thank you for your time and patience.

    Best regards,

    Zdeněk Neustupa


    Zdeněk Neustupa

    Tuesday, May 19, 2015 5:48 AM
  • Hello Zdeněk Neustupa,

    >> I just don’t understand why the notifications are firing in such a manner

    I did some more research, and found someone encountered this issue, you could have a check which might be helpful:

    https://social.msdn.microsoft.com/forums/sqlserver/en-US/3a750a5c-b133-4ba8-bbab-82d1fbd3818e/sqldependency-onchange-event-fires-repeatedly

    http://stackoverflow.com/questions/5980448/

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, May 20, 2015 3:47 AM
    Moderator
  • Hello Fred.

    After testing many possibilities of how to write the SQL Statement, I finally find the correct one.

    SELECT [dbo].[Technician].[TechnicianId], [dbo].[Technician].[TechnicianName] FROM [dbo].[Technician] ORDER BY [dbo].[Technician].[TechnicianName] ASC;

    The variation with database name was not working, neither the one without the brackets [ ].

    Thank you very much for your help.

    Best regards,

    Zdeněk Neustupa


    Zdeněk Neustupa

    Wednesday, May 20, 2015 6:50 AM