How to replicate Oracle DBMS_Alert functionality

[email protected] · Post by **[email protected]** » Tue 23 Sep 2008 16:09

I have an application that runs against Oracle that we are porting to MS SQL Server 2005. In the oracle version we have a trigger when a particular table is updated that calls the DBMS_Alert package, which broadcasts the Record ID to all oracle connections so that the individual clients can check whether they are current displaying that record and update as necessary. What would be the best way of replicating that functionality in SDAC?
Thanks in advance,
Mark.

Dimon · Post by **Dimon** » Wed 24 Sep 2008 12:58

SDAC includes the TMSServiceBroker component similar to Alerter in Oracle.
You can find more detailed information about this component in the SDAC Help.

[email protected] · Post by **[email protected]** » Wed 24 Sep 2008 21:29

Thank you. The help is somewhat less than illuminating. I can get the demo to run, and it creates a bunch of new tables in the database, but do you have any samle code as to how to use a database trigger to create the 'message'?
Presumably I need to have each client act as an initiator when then are started up, but really they all need to be a part of the same 'conversation', so that an update to the bookings table calls the trigger which pushes the message. In the demo, I have to explicitly retrieve the message from the queue, whereas really i just want to trigger an event when my client app receives a message.

Dimon · Post by **Dimon** » Mon 29 Sep 2008 09:23

There is an example in SDACDemo, that demonstrates work with the TMSServiceBroker component.
You can find more information about SQL Server Service Broker by the link:
http://msdn.microsoft.com/en-us/library/bb522893.aspx

To send message with SQL you should use the SEND ON statement. For more information refer to:
http://msdn.microsoft.com/en-us/library/ms188407.aspx