automatic data update from SERVER to all CLIENTS connected ???

[Filippo]

Hi,
i have a problem about the CLIENT-SERVER architecture procedure.
Well , i have an application in VB with ADO connection to a table in a
database on a SQLSERVER 7.0 .
Is possible to do that when a client updates a data in a field of my table ,
the SERVER communicates to all clients connected to my table that this data
are updated , without the client do anything , for example without a
client-timer to control the data in the server ???
thanks

[Simon Hayes]

"Filippo" <ru******@yahoo.it> wrote in message
news:c2*******************@twister2.libero.it...

Hi,
i have a problem about the CLIENT-SERVER architecture procedure.
Well , i have an application in VB with ADO connection to a table in a
database on a SQLSERVER 7.0 .
Is possible to do that when a client updates a data in a field of my table , the SERVER communicates to all clients connected to my table that this data are updated , without the client do anything , for example without a
client-timer to control the data in the server ???
thanks

In theory, you could use a trigger with xp_cmdshell to call some sort of
program to notify the clients, but in practice that wouldn't be a very good
solution. It would have serious performance implications, and if the
external program failed or hung, you could block access from other clients.

A better option is probably to poll the table to see if the data has
changed, either based on a datetime column, or perhaps a 'ModifiedFlag'
column. Clients could poll directly, or use a scheduled job at regular
intervals - the job could then call your notification program, and that
would not impact the database in case of communications or other issues.

Simon

[Erland Sommarskog]

Filippo (ru******@yahoo.it) writes:

i have a problem about the CLIENT-SERVER architecture procedure.
Well , i have an application in VB with ADO connection to a table in a
database on a SQLSERVER 7.0 .
Is possible to do that when a client updates a data in a field of my
table , the SERVER communicates to all clients connected to my table
that this data are updated , without the client do anything , for
example without a client-timer to control the data in the server ???

As Simon said, there is no direct support for this in SQL Server.

For a simple solution, polling is probably best. Note here that you
could make use of a timestamp column. Such a column is automatically
updated each time you update the row, and the value is monotonically
increasing on a database-wide basis. Thus, a client can save the last
fecthed timestamp value, and then get the new one.

A more sophisticated solution would be to write an extended stored
procedure to alert the clients. As Simon pointed out, such an operation
could be detrimental to performance, if you are not careful. Best is
to alert a local process, and this process then alerts the clients
asynchronusly.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp