Example for Starting a Microsoft SQL Server Alerter

The following example shows how to execute the stored procedure on a Microsoft SQL Server database. A service of Publication Service with the TIBCO Rendezvous transport type is configured with the alerter method selected. It is assumed that the rvpub.tra configuration has its data source name, user account name (demo) and password defined in the repository.

Prerequisites

Ensure that the following two conditions are met:
  • The create_user70.sql and alerter_ms.sql statements are executed on the database server to initialize the database environment.
  • The service broker function is enabled by running the Alter database dbname set enable_broker with rollback immediate statement on the database server.

    To check whether the service broker function is enabled, execute the following statement: SELECT is_broker_enabled FROM sys.databases WHERE database_id = DB_ID()

    If the returned value is 1, the service broker function is enabled.

Procedure

  1. Start the adapter configuration by typing the following command:
    adbagent --propFile rvpub.tra
  2. Insert a message and then execute the commit_and_notify_table stored procedure with the publishing table monitored by the adapter.
    For example, if the adapter is monitoring the PUB_ORDER table, execute the stored procedure as follows: 
    isql -Udemo -Pdemo
SQL> insert into ORDER_TABLE values(111,'Oak Table',499.95);
SQL> execute commit_and_notify_table ’PUB_ORDER’;
    The procedure puts a message in the Service Broker queue, which is retrieved by the adapter. The publisher endpoint then reads its publishing table and sends a message containing the changed data on its configured subject.

Result

This example shows how to notify an adapter configuration to poll a single table, that is, PUB_ORDER, for changes. You can use the commit_and_notify stored procedure to poll all publishing tables for changes.