When you want to start using SqlDependency for QueryNotification in Microsoft SQL Server 2012, there are some steps you need to take, to make this work.

In development

In development you should take the following steps:

Make "sa" dbo

use [master]
go
alter authorization on database::[Your database name here] to [sa]
go

Client should connect as "sa"

Use "sa" to connect to the database, when subscribing a query for QueryNotification

Check if SqlDependency is enabled on the database

use [master]
go
select name, is_broker_enabled from sys.databases
go

Enable SqlDependency on the database

use [master]
go
alter database [Your database name here] set enable_broker with rollback immediate
go

To disable SqlDependency, see notes below.

Database SET OPTIONS

For more information on the database SET OPTIONS, see the notes below

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

Stored procedure

When using a stored procedure as query for the QueryNotification, make sure the database options above are set before the stored procedure is created:

if object_id(‘Reporting.SqlDependency_GetProcesses’) is not null
begin
    drop procedure Reporting.SqlDependency_GetProcesses
end
go

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

create procedure Reporting.SqlDependency_GetProcesses
as
begin
    select  Id,
            Name,
            StartCheckTime,
            EndCheckTime,
            OranjeAlertTime,
            RedAlertTime,
            LastImportDateTime,
            StatusId
    from    General.Proces
end
go

DO NOT ADD with(nolock), because database isolation level read uncommitted is not allowed in queries that are used in the QueryNotification subscription.

C# code

Always re-register for QueryNotification, in the SqlDependency OnChange eventhandler.

The object containing the SqlDependency OnChange eventhandler, should be alive, in other words, when you are using SqlDependency in a WebApi or MVC 4 controler, save the object containing the SqlDependency OnChange eventhandler between request aka make it static

Use Schema.StoredProcedureName, in the registration call

Some example code for registring a stored procedure for QueryNotification:

public class Process
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public TimeSpan StartCheckTime { get; set; }
        public TimeSpan EndCheckTime { get; set; }
        public TimeSpan OrangeAlertTime { get; set; }
        public TimeSpan RedAlertTime { get; set; }
        public DateTime LastImportDateTime { get; set; }
        public int StatusId { get; set; }
    }

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

private List<Process> _data = null;

/// <summary>
        /// Refreshes the cached data.
        /// </summary>
        public void RefreshData()
        {
            try
            {
                // To prevent errors, when database connection is lost, always stop SqlDependency subscription, before starting one.
                SqlDependency.Stop(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);
                SqlDependency.Start(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);

                using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
                {
                    // Define query.
                    string storedProcedureName = "Reporting.SqlDependency_GetProcesses";

                    using (SqlCommand command = new SqlCommand(storedProcedureName, connection))
                    {
                        command.CommandType = System.Data.CommandType.StoredProcedure;

                        // Make sure the command object does not already have a notification object associated with it.
                        command.Notification = null;

                        // Hookup sqldependency eventlistener (re-register for change events).
                        var dependency = new SqlDependency(command);
                        dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);

                        // Open connection to database.
                        connection.Open();

                        // Get the data from the database and convert to a list of DTO's.
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            if (reader.HasRows)
                            {
                                var data = new List<Process>();
                                while (reader.Read())
                                {
                                    var da = new Process
                                    {
                                        Id = reader.GetInt32(0),
                                        Name = reader.GetString(1),
                                        StartCheckTime = reader.GetTimeSpan(2),
                                        EndCheckTime = reader.GetTimeSpan(3),
                                        OrangeAlertTime = reader.GetTimeSpan(4),
                                        RedAlertTime = reader.GetTimeSpan(5),
                                        LastImportDateTime = reader.GetDateTime(6),
                                        StatusId = reader.GetInt32(7)
                                    };
                                    data.Add(da);
                                }

                                _data = data;
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                _logger.Error("Error.", ex);

                // Exception can be caused by a database connection los, so try to hookup sqldependency eventlistener again.
                RefreshData();
            }
        }

        /// <summary>
        /// Fires, when the data in the database changes.
        /// </summary>
        private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
        {
            if (e.Type == SqlNotificationType.Change)
            {
                // Re-register for query notification SqlDependency Change events.
                RefreshData();
            }
        }

In test

When everything is working in development and you are shifting to the test environment, harden the security:

Rights for DBO

Do not make sa dbo owner, use a specific service account login [DOMAIN\ServiceAccountUsername].

Rights for client

Do not connect with SQL authentication, use windows authentication and connect with a specific client account login[DOMAIN\ClientAccountUsername].

grant subscribe query notifications to [You client login]

go

NOTES

To disable SqlDependency on the database

use [master]

go

alter database [Your database name here] set disable_broker with rollback immediate

go

Trouble shooting

When SqlDependency does not fire events, start a standard Microsoft SQL Server profiler trace:

Add the following specific events:

– Enable all Broker events

– Enable all Query Notification

– Enable all Security Audit events

Correct subscriptions

Generate a record in the view sys.dm_qn_subscriptions

select * from sys.dm_qn_subscriptions

Incorrect subscriptions

Incorrect QueryNotification subscriptions records contain:

type="subscribe" source="statement" info="set options" database_id="0"

where the INFO part contains a indication to the problem.

Database SET OPTIONS

SET ANSI_NULLS ON

http://msdn.microsoft.com/en-us/library/ms188048.aspx

• In a future version of Microsoft SQL Server ANSI_PADDING will always be ON and any applications that explicitly set the option to OFF will produce an error.
• When SET ANSI_NULLS is ON, you can’t compare NULL values with the = or <> operators and you should use the IS operator.

SET ANSI_PADDING ON

http://msdn.microsoft.com/en-us/library/ms187403.aspx

• In a future version of Microsoft SQL Server ANSI_PADDING will always be ON and any applications that explicitly set the option to OFF will produce an error.
• Trailing blanks in character values inserted into varchar columns are not trimmed.

SET ANSI_WARNINGS ON

http://msdn.microsoft.com/en-us/library/ms190368.aspx

• When set to ON, the divide-by-zero and arithmetic overflow errors cause the statement to be rolled back and an error message is generated. When set to OFF, the divide-by-zero and arithmetic overflow errors cause null values to be returned.

SET CONCAT_NULL_YIELDS_NULL ON

http://msdn.microsoft.com/en-us/library/ms176056.aspx

• In a future version of SQL Server CONCAT_NULL_YIELDS_NULL will always be ON and any applications that explicitly set the option to OFF will generate an error.
• When SET CONCAT_NULL_YIELDS_NULL is ON, concatenating a null value with a string yields a NULL result. For example, SELECT ‘abc’ + NULL yields NULL. When SET CONCAT_NULL_YIELDS_NULL is OFF, concatenating a null value with a string yields the string itself (the null value is treated as an empty string). For example, SELECT ‘abc’ + NULL yields abc.

SET QUOTED_IDENTIFIER ON

http://msdn.microsoft.com/en-us/library/ms174393.aspx

• Allows for objects to be created with reserved key words, for example a table called "select".

SET NUMERIC_ROUNDABORT OFF

http://msdn.microsoft.com/en-us/library/ms188791.aspx

• When SET NUMERIC_ROUNDABORT is ON, an error is generated after a loss of precision occurs in an expression. When OFF, losses of precision do not generate error messages and the result is rounded to the precision of the column or variable storing the result.

SET ARITHABORT ON

http://msdn.microsoft.com/en-us/library/ms190306.aspx

• You should always set ARITHABORT to ON in your logon sessions. Setting ARITHABORT to OFF can negatively impact query optimization leading to performance issues.