Read about the BizTalk Adapter for SQL Server adapter binding properties
The Microsoft BizTalk Adapter for SQL Server surfaces several binding properties. By setting these properties, you can control some of the adapter's behavior. This section describes the binding properties exposed by the SQL adapter. It also shows how you can access them by using .NET programming or by setting properties on a BizTalk Server physical port binding.
The Adapter Binding Properties
The following table shows SQL adapter binding properties grouped by category. The category refers to the node under which each binding property appears in the dialog boxes that are presented by different applications to configure the adapter (or binding).
XmlStoredProcedureRootNodeName
Category: FOR XML
Description: Specifies the name of the root node for the response schema for stored procedures that have a FOR XML clause in the SELECT statement. This root node encapsulates the XML response received from SQL Server after executing such stored procedures. You must add this root node to the response schema as described in the topic Execute Stored Procedures Having a FOR XML Clause in SQL Server using BizTalk Server.
Important
You must set this binding property while executing stored procedures with FOR XML clause.
.NET Type: string
XmlStoredProcedureRootNodeNamespace
Category: FOR XML
Description: Specifies the target namespace of the root node for the response schema for stored procedures that have a FOR XML clause in the SELECT statement.
.NET Type: string
CloseTimeout
Category: General
Description: The WCF connection close timeout. The default is 1 minute.
.NET Type: System.TimeSpan
Name
Category: General
Description: A read-only value that returns the name of the file generated by the Add Adapter Service Reference Visual Studio Plug-in to hold the WCF client class. The Add Adapter Service Reference Plug-in forms the file name by appending "Client" to the value of the Name property. The default value for this property is "SqlAdapterBinding"; for this value, the generated file will be named "SqlAdapterBindingClient".
.NET Type: string
OpenTimeout
Category: General
Description: Specifies the WCF connection open timeout. The default is 1 minute.
Important
The SQL adapter always uses OpenTimeout to set the connection open timeout when it opens a connection to SQL Server. The adapter ignores any timeout (System.TimeSpan) parameters passed when you open a communication object. For example, the adapter ignores any timeout parameters passed when opening a channel.
.NET Type: System.TimeSpan
ReceiveTimeout
Category: General
Description: Specifies the WCF message receive timeout. Essentially, this means the maximum amount of time the adapter waits for an inbound message. The default is 10 minutes.
Important
For inbound operations such as polling, we recommend setting the timeout to the maximum possible value, which is 24.20:31:23.6470000 (24 days). When using the adapter with BizTalk Server, setting the timeout to a large value does not impact the functionality of the adapter.
.NET Type: System.TimeSpan
SendTimeout
Category: General
Description: Specifies the WCF message send timeout. The default is 1 minute.
.NET Type: System.TimeSpan
EnableBizTalkCompatibilityMode
Category: BizTalk
Description: Indicates whether the adapter is being used with BizTalk Server or a .NET application.
- When using the adapters from BizTalk Server (or generating metadata for operations on SQL Server using the adapter in a BizTalk project), you must always set the property to True. This ensures that the schema generated for System.Data.DataSet is in a format compatible with BizTalk Server. Otherwise your BizTalk project will fail to compile.
- When using the adapters from Visual Studio in a .NET application, you must set the property to False if you want to use the response as a DataSet. This ensures that the schema generated for System.Data.DataSet is in a format compatible with the WCF DataContractSerializer.
.NET Type: bool (System.Boolean)
BatchSize
Category: Buffering
Description: Specifies the batch size for multiple record Insert, Update, and Delete operations on a table or view in a SQL Server database. The default is 20. For values of BatchSize greater than one, the SQL adapter batches the specified number of records into a single call. A higher value may improve performance, but affects memory consumption.
.NET Type: int (System.Int32)
ChunkSize
Category: Buffering
Description: Specifies the buffer size used for Set<column_name> operations. The default is 4194304 bytes. A higher value may improve performance, but affects memory consumption.
Note
For more information about Set<column_name> operations, see Operations on Tables and Views That Contain Large Data Types using the SQL adapter.
.NET Type: int (System.Int32)
Encrypt
Category: Connection
Description: Specifies whether SQL Server (with a valid certificate installed) uses SSL encryption for all data transfers between SQL Server and the client. Default is false.
.NET Type: bool (System.Boolean)
MaxConnectionPoolSize
Category: Connection Description: Specifies the maximum number of connections allowed in a connection pool for a specific connection string. The default is 100. This property is used for performance tuning.
Important
You must set MaxConnectionPoolSize judiciously. It is possible to exhaust the number of connections available, if this value is set too large.
.NET Type: int (System.Int32)
WorkstationId
Category: Connection
Description: Specifies a unique ID for the workstation (client computer) that connects to the SQL Server database using the SQL adapter. The value for this binding property, if specified, is used for the Workstation ID keyword of SqlConnection.ConnectionString property. For more information, see SqlConnection.ConnectionString Property.
.NET Type: string
EnablePerformanceCounters
Category: Diagnostics
Description: Specifies whether to enable the WCF LOB Adapter SDK performance counters and the SQL adapter LOB latency performance counter. The default is False; performance counters are disabled. The LOB latency performance counter measures the total time spent by the SQL adapter in making calls to the SQL Server database.
For more information about performance counters for the SQL adapter, see Use Performance Counters with the SQL adapter.
.NET Type: int (System.Int32)
InboundOperationType
Category: Inbound
Description: Specifies whether you want to perform Polling, TypedPolling, XmlPolling, or Notification inbound operation. Default is Polling.
For more information about Polling, TypedPolling, and XmlPolling see Support for Polling. For more information about Notification, see Considerations Receiving Query Notifications Using the SQL adapter.
.NET Type: enum
UseDatabaseNameInXsdNamespace
Category: Metadata
Description: Specifies whether the XSD generated for a particular artifact contains the database name. Set this to True to include the database name. Otherwise, set this to False. Default is False.
This is useful in scenarios where a single application wants to execute operations on identically named artifacts with different metadata in different databases. If there is no database name in the namespace, the generated metadata will conflict. By setting this binding property you can include the database name in the namespace, thereby making them unique. Here is an example highlighting the change in namespace.
UseDatabaseNameInXsdNamespace = False
http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/Employee
UseDatabaseNameInXsdNamespace = True
http://schemas.microsoft.com/Sql/2008/05/TableOp/MyDatabase/dbo/Employee
Notice that the database name is included in the namespace when the binding property is set to True.
.NET Type: enum
AllowIdentityInsert
Category: Miscellaneous
Description: Specifies whether the adapter can insert values for identity columns during Insert and Update operations. Set this property to True to insert or update values for identity columns. Otherwise set this to False (default).
Note
Setting this property to True translates to the adapter using SET IDENTITY_INSERT <table_name> ON. For more information, see SET IDENTITY_INSERT (Transact-SQL).
While using this binding property, you must consider the following points:
- The adapter does not validate the value you are passing for the identity column. For example, if a table has an identity column that has “Identity Seed” set to 100 and “Identity Increment” set to 1, and the adapter client passes a value, say 95, for the identity column, the adapter simply passes on this value to SQL Server.
- Even if you set AllowIdentityInsert to True, it is not mandatory for an adapter client to specify a value for the identity column in the request message. If a value is present for the identity column, the adapter passes it on to SQL Server. If a value is not present, SQL Server will insert a value based on the specification for the identity column.
.NET Type: bool (System.Boolean)
NotificationStatement
Category: Notification (Inbound)
Description: Specifies the SQL statement (SELECT or EXEC <stored procedure>) used to register for SQL Server notifications. You must specifically enter the column names in the statement as shown in the following SELECT statement:
SELECT Employee_ID,Designation FROM dbo.Employee WHERE Status=0
Note
You must specify the database object name along with the schema name. For example, dbo.Employee.
The adapter gets a notification message from SQL Server only when the result set for the specified SQL statement changes.
.NET Type: string
NotifyOnListenerStart
Category: Notification (Inbound)
Description: Specifies whether the adapter sends a notification message to the adapter clients, informing that the receive location is running, when the listener starts. Default is True.
The notification message you receive looks like the following:
<?xml version="1.0" encoding="utf-8" ?>
<Notification xmlns="http://schemas.microsoft.com/Sql/2008/05/Notification/">
<Info>ListenerStarted</Info>
<Source>SqlBinding</Source>
<Type>Startup</Type>
</Notification>
.NET Type: bool (System.Boolean)
PolledDataAvailableStatement
Category: Polling (Inbound)
Description: Specifies the SQL statement executed to determine whether any data is available for polling for a specific table in a SQL Server database. The specified statement must return a result set consisting of rows and columns. The value in the first cell of the result set indicates whether the adapter executes the SQL statement specified for the PollingStatement binding property. If the first cell of the result contains a positive value, the adapter executed the polling statement.
Following are examples of some valid statements that you can specify for this binding property:
If you are specifying a SELECT statement:
SELECT COUNT(*) from <table_name>If you are specifying a stored procedure, your stored procedure might be defined as:
CREATE PROCEDURE <procedure_name> AS BEGIN SELECT COUNT(*) FROM <table_name> END GOor
CREATE PROCEDURE <procedure_name> AS BEGIN DECLARE @count int SELECT @count = SELECT(*) FROM <table_name> SELECT @count END GO
If you are using a stored procedure, you would specify the PolledDataAvailableStatement as EXEC <procedure_name>.
Important
The statement you specify for this binding property is not executed within an adapter initiated transaction, and might be called multiple times before the actual polling statement is executed (even if executing the statement indicated that there are rows available for polling).
.NET Type: string
PollingIntervalInSeconds
Category: Polling (Inbound)
Description: Specifies the interval, in seconds, at which the SQL adapter executes the statement specified for the PolledDataAvailableStatement binding property. The default is 30 seconds. The polling interval determines the time interval between successive polls. If the statement is executed within the specified interval, the adapter is inactive for the remaining time in the interval.
.NET Type: int (System.Int32)
PollingStatement
Category: Polling (Inbound)
Description: Specifies the SQL statement to poll a SQL Server database table. You can specify a simple SELECT statement or stored procedures for the polling statement. The default is null. You must specify a value for PollingStatement to enable polling. The polling statement is executed only if there is data available for polling, which is determined by the PolledDataAvailableStatement binding property.
You can specify any number of SQL statements separated by a semicolon. You can use the polling statement to read or update data in a SQL Server database table. The SQL adapter executes the polling statements inside one transaction. When the adapter is used with BizTalk Server, the same transaction is used to submit messages from SQL Server to the BizTalk message box.
.NET Type: string
PollWhileDataFound
Category: Polling (Inbound)
Description: Specifies whether the SQL adapter ignores the polling interval and continuously executes the SQL statement specified for the PolledDataAvailableStatement binding property, if data is available in the table being polled. If no data is available in the table, the adapter reverts to execute the SQL statement at the specified polling interval. Default is false.
Consider a scenario where the polling interval is set to 60 seconds, and the statement specified for PolledDataAvailableStatement returns that data is available for polling. The adapter then executes the statement specified for the PollingStatement binding property. Assuming that the adapter takes just 10 seconds to execute the polling statement, it will now have to wait for 50 seconds before executing the PolledDataAvailableStatement again, and then subsequently execute the polling statement. Instead, to optimize the performance you can set the PollWhileDataFound binding property to true so that the adapter can start executing the next polling cycle as soon as the previous polling cycle ends.
.NET Type: bool (System.Boolean)
UseAmbientTransaction
Category: Transaction
Description: Specifies whether the SQL adapter performs the operations using the transactional context provided by the caller. Default is true, which means that the adapter always performs the operations in a transactional context. If there are other resources participating in the transaction, and SQL Server also joins the transaction, the transaction gets elevated to an MSDTC transaction.
However, there can be scenarios where you do not want the adapter to perform operations in a transactional context. For example:
- While performing a simple Select operation on a SQL Server database
- While specifying a polling statement that performs a Select operation, and does not involve any changes to the table either through a Delete statement or by invoking a stored procedure.
Both these operations do not make any updates to the database table and, hence, elevating these operations to use an MSDTC transaction can be a performance overhead. In such scenarios, you can set the binding property to false so that the SQL adapter does not perform the operations in a transactional context.
Note
- When UseAmbientTransaction is set to False, the PolledDataAvailableStatement isn't called. Instead, the adapter directly calls the PollingStatement.
- Not performing operations in a transactional context is advisable only for operations that do not make changes to the database. For operations that update data in the database, we recommend setting the binding property to true. Otherwise you might either experience message loss or duplicate messages, depending on whether you are performing inbound or outbound operations.
.NET Type: bool (System.Boolean)
How Do I Set SQL Server Binding Properties?
You can set the SQL Server binding properties when you specify a connection to SQL Server. For information about how to set binding properties when you:
Use the Consume Adapter Service BizTalk Project Add-in or the Add Adapter Service Reference Visual Studio Plug-in, see Connect to SQL Server Using Windows Authentication with the SQL adapter.
Important
While using the Consume Adapter Service Add-in or the Add Adapter Service Reference Plug-in, if you do not specify a value for a binding property of type string and whose default value is null then that binding property will not be available in the binding file (an XML file) or the app.config file respectively. You must manually add the binding property and its value in the binding file or the app.config file, if required.
Configure a send port or receive port (location) in a BizTalk Server solution, see Manually configure a physical port binding to the SQL adapter.
Use the WCF channel model in a programming solution, see Create a Channel using the SQL adapter.
Use the WCF service model in a programming solution, see Configure a Client Binding for the SQL Adapter.
See Also
Feedback
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback.
Submit and view feedback for