SQL dependency in MAIN

Sometimes you want database changes pushed to a screen without interaction of the user. This is possible but before you can do this you need to make a few updates to your database.

Preparation

The following updates must be done on the database where you want to activate the SQL Dependency:

--Alter database-----------------------------------------------------------
ALTER DATABASE [MA!N DatabaseName] SET ENABLE_BROKER WITH no_wait;
ALTER DATABASE [MA!N DatabaseName] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
ALTER AUTHORIZATION ON DATABASE::[MA!N] TO [sa];
CREATE ROLE [SQLDependency] AUTHORIZATION [dbo];

    --Service Broker permissions-----------------------------------------------
GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification 1] TO [SQLDependency];
GRANT RECEIVE ON QueryNotificationErrorsQueue TO [SQLDependency];
EXEC sp_addrolemember N’SQLDependency’, N’Company\ADGroupOrUsers’’;

    -- Database level permissions----------------------------------------------
GRANT CREATE PROCEDURE TO [SQLDependency];
GRANT CREATE QUEUE TO [SQLDependency];
GRANT CREATE SERVICE TO [SQLDependency];
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [SQLDependency];
GRANT VIEW DEFINITION TO [SQLDependency];

Notice that there can be no open connections to the database if you want to alter the database (as done here in step 1).

If the database is altered like show above, we can make a screen that uses this dependency. The screen will be updated without user interaction when the data in the database changes. Do note that there are quite a few restrictions on the query that is used to retrieve the data shown on the screen.

For example: The query cannot contain ‘TOP’, ‘DISTICNT’, ‘UNION’ or COUNT(*). Neither can there be a GETDATE() in the WHERE clause. All restrictions can be found here.

In the command where we want the SQL Dependency, we need to add the following:

<Dependency>
    <PrimaryKey>
        <Column>ID</Column>
    </PrimaryKey>
</Dependency>

In this example, ID corresponds with the column that should change when it is updated in the database.