Run stored procedure on query notification without SqlDependency

Question

Greeting everyone!

In SqlDependency you can easy subscribe to data change using Query Notification mechanism. (or by setting odbc attributes)

 SqlDependency dependency = new SqlDependency(
      new SqlCommand("SELECT [ID], [Name] FROM [dbo].[tbl_Contact]", this.CurrentConnection)
 );
 dependency.OnChange += this.dependency_OnChange;

On the other hand, using native sql you can execute stored procedure on some DMV event. (like user logout)

create queue [myEventQueue] with activation (
   status = on,
   procedure_name = dbo.QueueProcessing,
   max_queue_readers = 2,
   execute as self
)

create service [myNotifications] on queue [myEventQueue]
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);

CREATE EVENT NOTIFICATION [myEvent]
ON server
FOR AUDIT_LOGOUT
TO SERVICE 'myNotifications', 'current database'

My question is:

Can we create and subcribe some event query to data change without SqlDependency (using native t-sql in Managment Studio)?
Can we execute stored procedure when "some data modified"?

Thank you for any help!

P.S. Why i can't use triggers?

I have about 200 "events" wich are dependent on multiple tables with different predicates (filters). Unfortunately, users can change it.

Yes, you can. SqlDependency uses SQL Server's Service Broker. You can use the messages from the Service broker's queue directly in SQL. [read this.](https://msdn.microsoft.com/en-us/bb522893.aspx) — Zohar Peled, Jun 07 '15 at 12:53
@ZoharPeled, i know that they work on same technology, but i can't find how to subcribe to data change. Link no help :( — alex7z, Jun 08 '15 at 06:56
a little help from google returned [this tutorials list](https://technet.microsoft.com/en-us/library/bb839489(v=sql.105).aspx) on MSDN. I think the first one should help you. — Zohar Peled, Jun 08 '15 at 07:19
@ZoharPeled, stil no info for subscribing. Only send/recv by hands. — alex7z, Jun 08 '15 at 12:05
Actually, after reading your question again, you might not even need to use service broker. what about using a simple trigger for after update on the relevant tables? — Zohar Peled, Jun 08 '15 at 12:12
[Also, check this link](http://stackoverflow.com/a/1457226/3094533) — Zohar Peled, Jun 08 '15 at 12:14
It seems to me that it should answer your second question: execute a stored procedure when some data changed... — Zohar Peled, Jun 08 '15 at 18:06
@ZoharPeled, yes, it is. But i still can't subcribe to data change without c# (on native t-sql) — alex7z, Jun 09 '15 at 07:24
But if you can execute a stored procedure as a response to the data change, what's keeping you from using that stored procedure to decide what actions you want to take when that data is changed? — Zohar Peled, Jun 09 '15 at 07:29
@ZoharPeled, i can execute procedure on new event in query arrived, but i can't subcribe query to auto-event on datachange (only on dmv event like user login/logoff) — alex7z, Jun 09 '15 at 10:16
I see. well, seems to me like you are going to need an outside service after all. I'm not an expert in any way on the subject of Service broker, I'm just good at googling :-) — Zohar Peled, Jun 09 '15 at 10:29

score 2 · Accepted Answer · answered Jun 10 '15 at 19:18

Instead of using SqlDependency you can use the SqlNotificationRequest class.

From MSDN article Enabling Query Notifications:

...SqlNotificationRequest requires you to implement the entire listening infrastructure yourself. In addition, all the supporting Service Broker objects such as the queue, service, and message types supported by the queue must be defined. This manual approach is useful if your application requires special notification messages or notification behaviors, or if your application is part of a larger Service Broker application.

But this still does not let you subscribe to a data change notification with native T-SQL code. I suppose it could be possible to create a CLR function to submit a notification subscription though.

Also, MS SQL Server has "Change Tracking" features that maybe be of use to you. You enable the database for change tracking and configure which tables you wish to track. SQL Server then creates change records on every update, insert, delete on a table and then lets you query for changes to records that have been made since the last time you checked. This is very useful for syncing changes and is more efficient than using triggers. It's also easier to manage than making your own tracking tables. This has been a feature since SQL Server 2005.

How to: Use SQL Server Change Tracking

Change tracking only captures the primary keys of the tables and let's you query which fields might have been modified. Then you can query the tables join on those keys to get the current data. If you want it to capture the data also you can use Change Capture, but it requires more overhead and at least SQL Server 2008 enterprise edition.

Change Data Capture

Using these features, you would still have to create some service or SQL Agent job that periodically looks at the change tables and sends off the appropriate Service Broker messages to your services.

Run stored procedure on query notification without SqlDependency

1 Answers1