SQL transport type

SQL Transport Type

This transport type is designed for transmitting messages to a Microsoft SQL Server database or a Microsoft Azure SQL database by executing a stored procedure on the server. The connection to the server is established via a connection string, which must be properly configured for the target location. The connection string can include a wide array of settings typically supported in SQL server environments, such as connection timeouts, command timeouts, and other related parameters.

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

To successfully send a message using this transport type, four key pieces of information are mandatory:

1. Connection String: The connection string is critical as it specifies the server details, authentication credentials (if required), and any additional configuration settings such as timeouts and networking options.

2. Stored Procedure Name: This is the name of the stored procedure that will be executed to handle the incoming message.

3. Message Parameter: The name of the parameter within the stored procedure that will receive and process the message is required. The message content will overwrite any pre-configured value for this parameter.

4. Stored Procedure Parameters: The parameters of the stored procedure must be precisely defined. At least one parameter must be designated to accept the incoming message. It is crucial that the direction, data type, and size of each parameter align with the stored procedure’s requirements.

For successful transmission and processing of the message, all configurations must comply with the SQL server environment's requirements and the specific stored procedure being executed. It is essential to validate all parameter types, sizes, and directions to ensure smooth operation and prevent runtime errors during the procedure execution.

Stored Procedure Parameters

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.