I'm going to need to be able to have my app be aware of data changes to a table. Specifically, the app is sensitive to changes in state of "projects". (My app is used by consultants who work on projects.) If a project manager updates the project table, any current users need to be informed of this. More specifically, any apps that are logged into that project need to be notified so they can update UIs, apply new business rules, etc.



Currently we are using a low tech solution...the project managers are trained to communicate with their team, get everybody out of the project, then make the change. This is an infrequent task, so it works OK.



Currently, we are on SQL 2000, but we'll be moving to SQL 2005 soon (that's the plan in any case). So, the question is what is the best/scalable/least hit on client performance to implement this? Are SQL 2005 notification services a good idea? The only other thought I've had is a timer in each client to run a query to check for changes...but that seems like a bad idea as this occurs rarely (the changes just have HUGE ramifications) and most of the clients are connecting remotely.

Hi Greg,

Not that I've ever done this but what about using a trigger on the table then e-mailing the updates (directly out of SQL) to a distribution list. You can, I believe, also send SMS messages.

Cheers, Peter

Hi Greg,

Sorry I don't have anything to offer as a solution, but I am also intered in the replies since I do have the need to handle this kind of auto-update feature. 

And one of the situation is when sending an update out to a multi-user location, that update should check the database to see if it needs to be updated and then update all clients as well.

On my VFP projects I have logic in place using StoneField Toolkit to update the metadata and used the .EXE version to keep track of those updates.

Edhy Rijo

When we first played with notification services I was quite excited and thought that this would be a great solution for a lot of things...but there are a number of potential issues that come with using notification services.  The first is, you must have a direct connection to the server (i.e. ES will not support notification services, at least for the moment).  Secondly, you have to create a subscription for each query...so if you were to use this throughout the system as a blanket solution, then it could get "sticky." 

Let me give you a couple of ideas on how to address this.  The way we get around a lot of this is through our application server...which I would suspect that most of the applications that get created will not have this, but you can still use the same techniques that we did, just through the client side.

Let's take our Appointment Scheduling software.  In this case, we have a number of front desk people who may be booking and posting appointments.  Additionally, if patients are checking in from more than one location, the "signed-in" displays need to be updated, even if it didn't happen from the computer that I am on.  So we create threaded timers.  These have an incredibly small footprint and are asynchronous (you will want to do this versus using the component timer).  To create a timer it would look like this:

The above declaration would call the method MyCallbackMethod every 1 second...clearly this would be too often for performing some type of scalar method, but you get the idea.  The MyCallbackMethod would look like this:

OK...so once you get this setup and working the way that you like in regards to the timer, then you can begin to test on a state or count, etc.  You could also create a sproc that you call that performs a fair bit of logic if it is required and returns a single value telling you if something has changed and requires an update.  We have been doing this for a while and it works really well and with very little overhead.

Thanks for the idea, Peter. Unfortunately this isn't what I'm looking for. I don't really need to contact the users, but rather the user's application. I'll just let the project manager contact the users.



I believe Trent's reply is more of what I'm after...



Thanks for the ideas though!

Thanks Trent!



It is good to know that ES doesn't support query notifications. Since most of my users are remote using ES, query notifications are pretty much shot.



I'm really leery of using a timer at the client as I'm fighting the performance vs. getting the info in a timely enough manner to be useful. Some users will be on slow connections, where I'd really have to be careful about the timing.



For this reason, I'd really like the clients to just be notified when there is a change, at which point the client can just go get the info. Not that I have any clear idea of how this might be done. Your reply suggests some ideas:

- you mention an application server. I'm assuming this is some sort of server based component. Does it use remoting? Can remoting be used to raise events between computers? (>clueless about remoting ) Also, what sort of access do you need to an application server? Virtually none of the user are inside of the internal network while using the app.

- I suppose something like a web service could also be used, but as far as I know, they don't raise events. Could a web service be used?

- If I end up having to use the polling method, via a threaded timer, what is likely faster: scalar method, call to a web service, call to an application server (whatever that means)?

- I was thinking about using a "notification" table. I.e. if an action/data change occurs that results in any clients needing to be updated, a new record is written to this table, which would "trigger" or raise an event that is broadcast to clients (although I don't see how to do this at the moment) or it would be this table that clients would poll via the threaded timer using a scalar method (either a query or a sproc that would check the table) or this table that the web service/application server would query. Do you seen any problems with this?



Thanks for your thoughts!

No.  Remoting is really slow...so don't try to create anything that uses remoting as a base.  We actually created our own request and response objects (which you could just use HttpResponse and requests for that matter).

Correct...you would have to create subscriptions, etc...this could get hairy for async traffic.

They could really be one in the same in this respect.  You can even create a service that runs as your service (this is what we do).  We just have a service (not web service) that runs as our server.  You could create a TcpIP listener and server on a port that allows traffic to flow both ways.  This is a way to create a tunnel between your server and clients and gives you total control.

This type of approach generally presents a lot of issues...applications may die, power go out, etc. and then you have "junk" and orphan records lying around.  If you are going to try this approach, you are WAY better off creating sprocs and calling scalar methods.

FWIW we have somewhere in the neighborhood of 20 threaded timers in our server and application and it hasn't slown down a thing.  This includes the deployments in the field where we have some clients running on really crappy equipment and connections as well.  So this really comes down to how you design the query.  If you are calling a sproc on the SQL Server, the scalar method isn't going to choke anything down...it should be really fast, especially if you are polling on a 30+ second interval.

Thanks Trent. Lots to chew on.

No problem