Incoming SQL transport type

Incoming SQL Transport Type

This transport type is designed to poll a Microsoft SQL Server database or a Microsoft Azure SQL database to retrieve data by executing a stored procedure. The connection to the server is established using a connection string, which must be properly configured for the polling location. The connection string may include various settings typically supported in SQL server environments, such as connection and command timeouts, authentication credentials, and other relevant configurations.

If authentication is necessary, the connection string must also contain the appropriate credentials to access the database. Furthermore, it is essential to ensure that any required network configurations, such as firewall exceptions, are correctly established to enable seamless communication with the SQL server.

Key Information Required for SQL Polling

To successfully configure the incoming SQL polling location, five key pieces of information are necessary:

1. Connection String: This defines how the system connects to the SQL Server or Azure SQL database. It includes server details, credentials (if authentication is required), timeouts, and additional configuration settings.

2. Stored Procedure Name: The name of the stored procedure that will be executed to retrieve the data. This procedure is responsible for fetching the data that will be processed by the polling location.

3. Column or Parameter Name: The name of a column in the result set or a parameter within the stored procedure that contains the message data. This allows the system to process the data efficiently. If a column name is used, the system can poll multiple messages in a single operation by retrieving multiple rows from the result set.

4. Stored Procedure Parameters: Any parameters required by the stored procedure must be defined and passed to ensure successful execution. The direction, data type, and size of these parameters must align with the stored procedure’s requirements.

5. Use a Transaction: A checkbox determines whether a transaction is needed. If checked, the system ensures that data is properly stored before committing the transaction. This option guarantees data integrity by ensuring that any message data retrieved is successfully stored for processing before the retrieval is committed.

Stored Procedure Parameters

The parameters for the stored procedure must be clearly defined and mapped correctly. The column or parameter name specifies where the message content will be extracted from—whether as a column in the result set or a parameter within the stored procedure. If a column is specified, multiple messages can be polled and processed in one operation, with each row representing an individual message. If the column or parameter name starts with a @ it is assumed to be a parameter name, otherwise it is treated as a column name.

For additional parameters required by the stored procedure, their types, sizes, and directions (input/output) must be validated to ensure smooth operation. Any parameters configured for the procedure can also resolve macros dynamically during execution.

All parameter names must start with a @ and for parameters of types varchar(max), nvarchar(max), and varbinary(max), which can store large amounts of data, the size must be explicitly set to -1. This tells the system to handle the parameter as a large data type.

Polling Multiple Messages

By using the column or parameter name to identify a column in the result set, the polling operation can retrieve multiple messages in one batch. Each row in the result set represents an individual message, allowing for efficient processing in high-volume scenarios. When combined with a transaction, this ensures that all messages in the batch are either retrieved and stored successfully or rolled back in the event of an error, preserving data integrity across the entire batch.