dubian (co*****@bigtex ansoftware.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,
es****@sommarsk og.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp 
