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 ?
thanksYou might want to read about "notification services" on the MS web site for this. I haven't used it
myself, but it is designed for this type of scenario.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Filippo" <rude_Fil@.yahoo.it> wrote in message news:O%uhb.24255$vO5.872426@.twister1.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
>
>

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 Dataimport

Hi everyone,
i'm new to ado.net and need some help. My problem:
got one DB with real bad tablestructure
and one new DB (structured!)
i need do do an automated import from the old to the new DB, with check if the data row is new or updated.
I try to do it this way-> 2 datasets merged together ->Problem: new entries in destination dataset have row state unchanged
other way i tryed add rows from source to destination with add funktion -> problem no check if data exist and maybe is changed
So how can i get the merge working or how can i implemt an effective funktion to solve this problem.
Thanks in advance!

Is this something you need to do just once, or on an ongoing basis?
|||

I have to do it aprox. once per week.
My idea is to write my own merge function. But if there is a faster and simplier way please show me!
Thx!

|||I'd do this completely in SQL Server, with a stored procedure, and use SQL Server Agent to schedule it to run once/week.
The query in the stored procedure would look something like this:
IF NOT EXISTS(SELECT * FROM NewTable WHERE NewTable.Key = OldTable.Key)
BEGIN
INSERT INTO
NewTable
(
Column1,
Column2,
Column3
)
SELECT
Column1,
Column2,
Column3
FROM
OldTable
END
ELSE
UPDATE
NewTable
SET
NewTable.Column1 = OldTable.Column1,
NewTable.Column2 = OldTable.Column2,
NewTable.Column3 = OldTable.Column3,
NewTable.DateModified = GETDATE()
FROM
NewTable
INNER JOIN
OldTable ON NewTable.Key = OldTable.Key
WHERE
NewTable.Column1 <> OldTable.Column1 OR
NewTable.Column2 <> OldTable.Column2 OR
NewTable.Column3 <> OldTable.Column3



|||Thx for your help,
i think thats the better solution. Last Question i have, what is the code to call an other Database on the same Server with Stored Procedures.
|||

Iso wrote:

what is the code to call an other Database on the same Server with Stored Procedures.

Use the 3-part qualified name: database.owner.table. For example:
yourDatabase.dbo.OldTable