SQL dependency event fires on Alter Database but n

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

SQL dependency event fires on Alter Database but n

Author

Topic

mattt
Posting Yak Master

194 Posts

Posted - 2010-12-02 : 06:32:09

Hi,

Working on someone else's code where they've instituted a SQL Dependency event (SQL 2008). Not something I'm terribly familiar with so was just doing some reading and some testing.

The event is called CustomerServicesTable_OnChange and so is presumably meant to fire whenever the customerServices table is changed. However when the DB is changed through code (we're using Entity Framework) the event is not firing.

Out of curiosity I fired up SQL Server Manager and tried running queries against the table directly to see what would happen. Running UPDATE, INSERT or ALTER queries against the table didn't cause the event to fire.

To check that SQL dependency had been enabled I then ran the following commands against the database, supposedly the first step in enabling the service:

ALTER DATABASE customers SET SINGLE_USER with rollback immediate
alter database customers set enable_broker
ALTER DATABASE customers SET MULTI_USER

To my surprise running these commands caused the event to fire!

So it looks as though the dependency is working against either the database as a whole, or table(s) in the DB other than CustomerServices. Can anyone suggest a way forward to help me determine exactly what the problem is?

Cheers, Matt

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-12-02 : 07:15:21

Not sure if I understand correct but I think you should read about the difference between DML and DDL triggers...

No, you're never too old to Yak'n'Roll if you're too young to die.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-02 : 07:42:38

You can look at the extended event definition, instead of "presuming" what it is.

See these:

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

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

mattt
Posting Yak Master

194 Posts

Posted - 2010-12-02 : 08:50:20

quote:
Originally posted by russell

You can look at the extended event definition, instead of "presuming" what it is.

Thanks - not immediately obvious how these will help but I'll read through them and work it out!

I may be back with more questions shortly

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-02 : 08:53:02

You're guessing what the extended events are.

These links will show you how to know, rather than guess

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-02 : 08:56:08

Might also look at these articles by SQL Server MVP and SQL Team expert Spirit1:

http://www.sqlteam.com/article/introduction-to-sql-server-2008-extended-events

http://www.sqlteam.com/article/advanced-sql-server-2008-extended-events-with-examples

mattt
Posting Yak Master

194 Posts

Posted - 2010-12-02 : 09:39:59

Hmm. I'm not sure we're not talking at cross-purposes here. I'm talking about SQLDependency / SQLNotification - the ability to set-up SQL Server to notify something (such as a listening .dll) that the data in the table has changed.

Are these services built on Extended Events?

(Sorry if I'm being dense - my forte is web development & front-ends, less so databases)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-02 : 09:42:31

quote:
The event is called CustomerServicesTable_OnChange

Where do you see this event?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-02 : 09:47:27

also, the "event" you're talking about doesn't do that. as you've demonstrated yourself.

a trigger is what you need to capture data changes in real time

mattt
Posting Yak Master

194 Posts

Posted - 2010-12-02 : 10:11:59

The "event" is in some c# code I'm working on ...


        static void CustomerServicesTable_OnChange(object sender, System.Data.SqlClient.SqlNotificationEventArgs e)
        {
            RouteCollection routes = RouteTable.Routes;

            using (routes.GetReadLock())
            {
                RouteTable.Routes.Clear();
                RegisterRoutes(RouteTable.Routes, new CustomersRepository());
            }
        }

This code only gets hit when the notification service throws an error. Theoretically it should get hit every time the data in the DB changes.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-02 : 10:17:56

Ok, I initially thought you meant SQL Server Events.

I'm no expert in C#, but there isn't enough in the code snippet you posted to see what's going on.

What are you trying to accomplish once some data changes?

mattt
Posting Yak Master

194 Posts

Posted - 2010-12-03 : 04:18:36

quote:
Originally posted by russell

Ok, I initially thought you meant SQL Server Events.

I'm no expert in C#, but there isn't enough in the code snippet you posted to see what's going on.

What are you trying to accomplish once some data changes?

Sorry, it doesn't look like I'm explaining myself very well.

SQL Server 2005 is supposed to provide a number of services which can "notify" external applications when data changes have been made. They seem to have a variety of names, but the most common is SQL Notification Service and they're based on the SQL Service Broker. See:

http://msdn.microsoft.com/en-us/library/ms171626(v=SQL.100).aspx

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

So this isn't a C# problem. Nor is it a SQL events problem. It's a problem with the Notification Service. From what I can gather, making it work seems to be something of a black art - lot of people are asking a lot of questions and there's not a lot of answers. I thought this forum might be a good place to look for an expert to help me :)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-03 : 10:56:53

Notification Services was removed in SQL 2008.

Service Broker will do nothing to help you by itself. But it is an asynchronous messaging system (similar to MSMQ). You could have it deliver an XML message (or any message for that matter) to anyplace you want. You have to write the code for it though.

For example, you can have all your stored procedures drop a message in a queue, and have your C# application monitor that queue.

SQL Server doesn't raise any event (as far as I know) that can be captured externally when data is modified. You need to implement that yourself. Triggers are the most common way, but Service Broker can be used too -- and there are many benefits to doing it asynchronously.

What do you intend to do when your application "detects" data changes? This in part will help determine the best way to do it.

If you're breaking off recordsets and just wanting to make sure the underlying data hasn't changed before writing back to the database, timestamps and checksums are the way. If you're updating one table based on actions performed against another, triggers are your best bet. If you're notifying one system about changes to another, SB or SQL Agent jobs.

If you're refreshing a users screen when data changes, then you can have the app periodically poll the table for checksum, or poll a SB queue.

mattt
Posting Yak Master

194 Posts

Posted - 2010-12-03 : 11:51:48

quote:
Originally posted by russell

What do you intend to do when your application "detects" data changes? This in part will help determine the best way to do it.

Thanks for the clarification about the removal in 2008. I had started to suspect that this was the case but the name difference between "SQL Deployment" and "SQL Notification" confused me. As did the fact that notification still seemed to work for errors. But there you go.

The application is an MVC website which has routing constraints based on some occasionally updated data. So ideally what we wanted was for the route table to update when the data changed. A poll wasn't the ideal solution because it seemed a lot of overhead for something that only changed fairly rarely, and we wanted fairly instantaneous feedback. However under the circumstances it's probably the best way to go about this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources