automatic data update from SERVER to all CLIENTS connected

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"Filippo" <rude_Fil@.yahoo.it> wrote in message
news:c2vhb.26030$e6.883003@.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|||Filippo (rude_Fil@.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, sommar@.algonet.se

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

automated restore

Is it possible to do an automatic/daily restore of a
database to a test box that is being backed up as part of
a maintenence plan? Since the backup file contains a
timestamp, I'm wondering if the file name is stored in one
of the msdb system tables where I can select it into the
restore script. thanks.Yes it is. Here is a bit of code to identify the last backup for a
database. You can then build a process around this to automate the restore.
Keep in mind if you do multiple types of backups (tran, diff, and full) then
you might have to restore from multiple files. Also if the file is on your
prod box, you might need to create a network share or something to get
access to the backup from test.
select physical_device_name
from msdb..backupset a join msdb..backupmediaset b on a.media_set_id =
b.media_set_id
join msdb..backupmediafamily c on a.media_set_id = c.media_set_id
where backup_start_date =
(select top 1 backup_start_date from msdb..backupset
where database_name = 'YourBaseNameHere'
order by backup_start_date desc)
Here is an article that will show you how to even build the restore script:
http://www.databasejournal.com/feat...cle.php/2174411
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"rob" <anonymous@.discussions.microsoft.com> wrote in message
news:2367001c45ed3$30411ae0$a601280a@.phx
.gbl...
> Is it possible to do an automatic/daily restore of a
> database to a test box that is being backed up as part of
> a maintenence plan? Since the backup file contains a
> timestamp, I'm wondering if the file name is stored in one
> of the msdb system tables where I can select it into the
> restore script. thanks.

automated restore

Is it possible to do an automatic/daily restore of a
database to a test box that is being backed up as part of
a maintenence plan? Since the backup file contains a
timestamp, I'm wondering if the file name is stored in one
of the msdb system tables where I can select it into the
restore script. thanks.
Yes it is. Here is a bit of code to identify the last backup for a
database. You can then build a process around this to automate the restore.
Keep in mind if you do multiple types of backups (tran, diff, and full) then
you might have to restore from multiple files. Also if the file is on your
prod box, you might need to create a network share or something to get
access to the backup from test.
select physical_device_name
from msdb..backupset a join msdb..backupmediaset b on a.media_set_id =
b.media_set_id
join msdb..backupmediafamily c on a.media_set_id = c.media_set_id
where backup_start_date =
(select top 1 backup_start_date from msdb..backupset
where database_name = 'YourBaseNameHere'
order by backup_start_date desc)
Here is an article that will show you how to even build the restore script:
http://www.databasejournal.com/featu...le.php/2174411
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"rob" <anonymous@.discussions.microsoft.com> wrote in message
news:2367001c45ed3$30411ae0$a601280a@.phx.gbl...
> Is it possible to do an automatic/daily restore of a
> database to a test box that is being backed up as part of
> a maintenence plan? Since the backup file contains a
> timestamp, I'm wondering if the file name is stored in one
> of the msdb system tables where I can select it into the
> restore script. thanks.