Edit

Receive SQL Query Notifications using BizTalk Server

  • Article

You can configure the SQL adapter to receive notification messages for SQL Server tables or views. You can specify a SQL statement that the adapter uses to register for notifications with SQL Server. The notification statement can be a SELECT statement or a stored procedure that returns a result set. For more information about query notifications, see “Using Query Notifications” at https://go.microsoft.com/fwlink/?LinkId=122159. For information about queries that can be used for query notifications, see “Creating a Query for Notification” at https://go.microsoft.com/fwlink/?LinkId=122160.

Receiving query notifications from SQL Server is similar to polling SQL Server, with a few key differences. For the list of differences, see Considerations Receiving Query Notifications Using the SQL adapter.

Following are some scenarios in which you can configure the SQL adapter with BizTalk Server to receive notifications from SQL Server:

  • Adapter clients get only “incremental” notification, for example, only for those changes that were made to a database table since the last notification.

  • If many rows are inserted into a database table, the adapter clients can configure multiple receive locations to load-balance receiving notifications.

  • If the receive location on which the adapter clients are receiving notifications goes down, the adapter clients can configure the adapter to receive a notification as soon as the receive location is up again. The clients must also implement the logic in their application to process the records that may have been inserted, updated, or deleted while the receive location was down.

    Once the adapter clients receive a notification message, they can perform specific tasks based on the kind of notification received. For example, a BizTalk orchestration can be designed in such a way that it performs one set of tasks if an insert notification is received and another set of tasks if an update notification is received.

    The topics in this section provide information about how to configure the adapter for each of these scenarios. To start getting notifications from SQL Server using the SQL adapter, you must specify certain binding properties. For more information about how the adapter supports receiving messages, see Considerations Receiving Query Notifications Using the SQL adapter. For more information about the binding properties related to notifications, see Read about the BizTalk Adapter for SQL Server adapter Binding Properties. For more information about the structure of notification messages, see Message Schemas for Query Notification.

    You must also perform the following tasks on SQL Server to enable query notifications.

  • You must enable Service Broker for the SQL Server database.

  • You must ensure that the adapter client has the necessary permissions to execute commands to request notification.

    For more information about these tasks, see “Enabling Query Notifications” at https://go.microsoft.com/fwlink/?LinkID=122323.

In This Section

See Also

Develop BizTalk applications using the SQL adapter