Working with BizTalk Adapter for SQL Server Binding Properties

XmlStoredProcedureRootNodeName

(FOR XML)

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 Executing Stored Procedures Having a FOR XML Clause.

Important

You must set this binding property while executing stored procedures with FOR XML clause.

string

XmlStoredProcedureRootNodeNamespace

(FOR XML)

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.

string

CloseTimeout

(General)

The WCF connection close timeout. The default is 1 minute.

System.TimeSpan

Name

(General)

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".

string

OpenTimeout

(General)

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.

System.TimeSpan

ReceiveTimeout

(General)

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.

System.TimeSpan

SendTimeout

(General)

Specifies the WCF message send timeout. The default is 1 minute.

System.TimeSpan

EnableBizTalkCompatibilityMode

BizTalk

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.
  
  

bool (System.Boolean)

BatchSize

Buffering

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.

int (System.Int32)

ChunkSize

Buffering

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.

int (System.Int32)

Encrypt

Connection

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.

bool (System.Boolean)

MaxConnectionPoolSize

Connection

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.

int (System.Int32)

WorkstationId

Connection

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” at http://go.microsoft.com/fwlink/?LinkId=116732.

string

EnablePerformanceCounters

Diagnostics

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 Using Performance Counters.

int (System.Int32)

InboundOperationType

Inbound

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 Receiving Query Notifications.

enum

UseDatabaseNameInXsdNamespace

Metadata

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.

enum

AllowIdentityInsert

Miscellaneous

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 is False.

Note

Setting this property to True translates to the adapter using “SET IDENTITY_INSERT <table_name> ON”. For more information, see http://go.microsoft.com/fwlink/?LinkId=124620.

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.
  
  

bool (System.Boolean)

NotificationStatement

Notification (Inbound)

Specifies the SQL statement (SELECT or EXEC <stored procedure>) used to register for SQL Server notifications. Note that you must specifically specify the column names in the statement as shown in this 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.

string

NotifyOnListenerStart

Notification (Inbound)

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>

bool (System.Boolean)

PolledDataAvailableStatement

Polling (Inbound)

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:
  
  
  Copy

  SELECT COUNT(*) from <table_name>
  

• If you are specifying a stored procedure, your stored procedure might be defined as:
  
  
  Copy

  CREATE PROCEDURE <procedure_name> 
  AS
  BEGIN
       SELECT COUNT(*) FROM <table_name>
  END
  GO
  

  or
  
  
  Copy

  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).

string

PollingIntervalInSeconds

Polling (Inbound)

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.

int (System.Int32)

PollingStatement

Polling (Inbound)

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.

string

PollWhileDataFound

Polling (Inbound)

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.

bool (System.Boolean)

UseAmbientTransaction

Transaction

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

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.

bool (System.Boolean)