I would like to propagate an event signal to an external application
when a table in my MSSql2000 server is updated.

Prog A; I have an external application adding records to a table.
Prog B; I have another external application using this table as well.

When Prog A creates a new record in the Table, how can I have Prog B be
notified of the event without polling the table or creating a link
between Prog A and Prog B.

Thanks.

> When Prog A creates a new record in the Table, how can I have Prog B be
> notified of the event

Depends what you mean by "notified". If you want to invoke some code within
the current process (B) then the obvious way is to have B read a table or
watch for some other event and then act accordingly. What exactly do you
want to achieve? Is this simply about optimistic locking?

--
David Portas
SQL Server MVP
--

dubian (collatz@bigtexansoftware.com) writes:
> I would like to propagate an event signal to an external application
> when a table in my MSSql2000 server is updated.
>
> Prog A; I have an external application adding records to a table.
> Prog B; I have another external application using this table as well.
>
> When Prog A creates a new record in the Table, how can I have Prog B be
> notified of the event without polling the table or creating a link
> between Prog A and Prog B.

By far, the easiest way is to poll.

The other way would be to have a trigger on the table, that fires of an
extended stored procedure or OLE object to somehow send a singal to
Process B. Since extended stored procedures and local OLE objects in
the same memory space as the rest of SQL Server, they are somewhat
dangerous: if they crash on an access violation, the entire server
goes belly-up.

I believe that you also can use the sp_OAxxx routiens to start an
OLE object on a remote server. In this there is less risk for crashes.
But this is like to take time, and when you are in a trigger you are
in a transaction hold locks. If the update frequency is high, this
trigger can kill your throughput.

A variation is to have the trigger to write to a table, and then
run a job from SQL Server Agent that reads the table and alerts the
other process. Such a job would run once a minute or so. Since this job
could be an ActiveX task you could signal with XP:s and that.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

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