Inform Clients of Table Changes

[faccorp]

Hello all,

What is the perferred method of informing a client that a table has changed. If I have multiple clients viewing the same data, and one client changes data in the view, what's the best way to let everyone know that they should update their views.

I'm using SQL 2005. I've been searching the forum but can't find too much info on this.

Thanks,

 

[SQLBill]

Send them an email?

I guess it would depend on how your system/network is setup. Can you use Sendmail? Can you use NetSend?

-SQLBill

Posting advice: FAQ481-4875

 

[SQLDenis]

Here is a story I have about notifying people by email
We implemented a system and had a trigger on a table that would send out an email to a group of 15 people or so when there was an error in one of the fields

So we started 'stress testing' created 2000 or so of these errors with a nice insert/update statement

result 30000 emails within 1 minute
all email went down for the next 3 hours ;-)

Just be carefull what you implement because you never know......

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[faccorp]

Bill,

Thanks for the response. Sounds interesting. Surely there has to be a more direct method of doing this. Especially if there are changes being made all of the time.

Thanks again.

 

[faccorp]

The way I have done this in the past is to create another table that holds an integer value for each table. Every time a table row is updated/deleted/inserted, the integer gets incremented. Then, each client polls this table every 5 seconds. It retains the last value for each table and if the value is different then somebody must have done an update.

This works but is rather inefficient.

There must be a better way.