Troubleshoot Operational Issues with the SQL adapter
This section discusses using troubleshooting techniques to resolve operational errors that you might encounter when using Microsoft BizTalk Adapter for SQL Server.
Enabling Tracing
You must enable tracing between the adapter, WCF LOB Adapter SDK, and SQL Server to gather more information about any issues you encounter while using the SQL adapter. For more information about tracing support in the SQL adapter, see Diagnostic Tracing and Message Logging in the SQL adapter.
Known Issues
The following are the most common errors you might encounter when using the SQL adapter, along with their probable cause and resolution.
Error in loading the adapter bindings
Problem
When you try to start the Add Adapter Service Reference Visual Studio Plug-in or the Consume Adapter Service BizTalk Project Add-in, you get the following error:
There was an error loading the binding, <binding name>, from your system configuration.
ConfigurationErrorsException: Exception has been thrown by the target of an invocation.
Cause
When you try to start the Add Adapter Service Reference Plug-in or the Consume Adapter Service Add-in, WCF loads the adapter bindings for all the installed adapters. In turn, the adapter bindings are dependent on the specific client software for the enterprise application. You might face this issue if you did a Typical or Complete installation of the adapter, which installs all the adapters contained in the BizTalk Adapter Pack. However, the LOB client libraries might be installed for only one enterprise application. As a result, the GUI fails to load the bindings for the other adapters.
Resolution
Make sure you do a custom installation of the adapters to install only the adapter you need.
The SQL adapter does not display in the list of adapters in BizTalk Server Administration console
Problem
Unlike the earlier version of the adapters shipped with BizTalk Server, the SQL adapter shipped with BizTalk Adapter Pack does not show up in the list of adapters in the BizTalk Server Administration console.
Cause
The latest SQL adapter is a WCF custom binding. So, although the BizTalk Server Administration console displays the WCF-Custom adapter, it does not display the WCF custom bindings and hence, does not display the WCF-based SQL adapter.
Resolution
You can explicitly add the SQL adapter to the BizTalk Server Administration console by following the steps mentioned in Adding the SQL Adapter to BizTalk Server Administration Console.
Error while performing operations on a SQL Server database
Problem
The adapter gives the following error when performing any operation on a SQL Server database using BizTalk Server.
For BizTalk Server
System.ArgumentNullException: Value cannot be null.Cause
The WCF action for the message is not specified. WCF requires a SOAP action to be specified for every operation, which informs the adapter about the operation to be performed on the LOB application.
Resolution
Specify the SOAP action in the send port or as a message context property in a BizTalk orchestration. For instructions, see Configure the SOAP action for the SQL adapter. See Messages and message schemas to see a list of actions for each operation.
InvalidOperationException with ErrorCode=5 while performing FILESTREAM operations
Problem
You get the following error while using the SQL adapter to perform FILESTREAM operations.
System.InvalidOperationException: OpenSqlFileStream returned error.
ErrorCode:5
Cause
You might have specified database credentials to connect to the SQL Server database. To perform FILESTREAM operations, you must always use Windows Authentication. The error code “5” denotes that access is denied because of incorrect credentials. For more information about the different error codes, see System Error Codes (0-499).
Resolution
Use Windows Authentication to connect to the SQL Server database. In BizTalk Server Administration console, you can do so by leaving the user name and password fields blank in the WCF-Custom or WCF-SQL port configuration dialog box.
Polling operation does not return any messages even if valid statements are specified for PollingStatement and PolledDataAvailableStatement
Problem
Even if valid values are specified for the PollingStatement and PolledDataAvailableStatement binding properties, the adapter does not receive a polling message from SQL Server.
Cause
Verify whether any other transaction has taken a lock on the table that the adapter is polling.
Resolution
If you want to poll a table that is being updated as part of another transaction, you can consider using “with (nolock)” parameter as part of the query specified for PolledDataAvailableStatement binding property to ensure that data is returned even if a lock is imposed by the other transaction. For more information, see SQL Locking in the Database Engine.
The adapter fails to insert, update, or delete large volumes of data in a single operation using BizTalk Server
Problem
The SQL adapter fails to insert, update, or delete large volumes of data in a single operation using BizTalk Server.
Cause
Inserting, updating, or deleting large volumes of data may take time and the SQL adapter or the transaction in which the operation is being performed, may time out.
Resolution
For BizTalk Server
Specify the timeout for the WCF adapter in the machine.config. Navigate to the machine.config file under <system drive>:\WINDOWS\Microsoft.NET\Framework\<version>\CONFIG and add the excerpt that resembles the following.
<configuration> <system.transactions> <machineSettings maxTimeout="02:00:00" /> </system.transactions> </configuration>With this setting, the WCF adapter timeout is set to 2 hours.
Specify the timeout settings for MSDTC transactions in the machine.config. Navigate to the machine.config file under <system drive>:\WINDOWS\Microsoft.NET\Framework\<version>\CONFIG and add the excerpt that resembles the following.
<system.transactions> <defaultSettings distributedTransactionManagerName="<computer_name>" timeout="02:00:00"/> </system.transactions>With this setting, the MSDTC timeout is set to 2 hours. The default value for MSDTC timeout is 10 minutes.
Important
You must make this change on the computers running the adapter client and SQL Server. In the excerpt, replace <computer_name> with the name of computer running the adapter client and SQL Server.
Set the SendTimeout binding property for the SQL adapter to a fairly large value. For instructions on how to set the binding properties, see Configure the binding properties for the SQL adapter.
Full schema validation in BizTalk Server fails for response messages containing DataSet
Problem
For operations that return a response message containing a DataSet, for example ExecuteReader, full schema validation fails in BizTalk Server.
Resolution
We recommend you to not do a full schema validation for response messages containing a dataset. Instead, you could do the following:
Execute the operation once that returns the response message with the schema.
Copy the schema from the response message to a .xsd file and add this file to your BizTalk project.
Use an xpath query in your orchestration to extract the data from the response message.
Error with RootNode TypeName in BizTalk Projects
Problem
In a BizTalk project in Visual Studio, if the schemas generated from the Consume Adapter Service Add-in contains invalid characters or reserved words for the RootNode TypeName property, the following error will occur while compiling the project:
Node <node reference> - Specify a valid .NET type name for this root node.
The current .NET type name of this root node is invalid (it is a reserved BizTalk Keyword or is an invalid C# identifier).
Resolution
Right-click the rood node referenced in the error and select Properties.
For the RootNode TypeName property, remove any illegal characters or reserved words, for example, dot (.).
Adapter fails to generate metadata of strongly-typed stored procedure with temporary tables
Problem
The adapter fails to generate metadata for strongly-typed stored procedures that include temporary tables in their definition. The adapter gives the following exception.
Microsoft.ServiceModel.Channels.Common.MetadataException:
Retrieval of Operation Metadata has failed while building WSDL at 'TypedProcedure/<schema>/<stored_procedure_name>' --->
System.Data.SqlClient.SqlException: Invalid object name '<temp_table_name>'.
Resolution
The SQL adapter does not support generating metadata for strongly-typed stored procedures that contain temporary tables in their definition. Instead, you should generate metadata for the same procedure from under the Procedures node while using the Add Adapter Service Reference Plug-in or Consume Adapter Service Add-in.
Invalid binding warning when using the adapter in Visual Studio
Problem
When you use the adapter to create an application in Visual Studio and you open the configuration file (app.config) generated by the adapter, you see a warning similar to the following:
The element 'bindings' has invalid child element 'sqlBinding'. List of possible elements expected: 'basicHttpBinding, customBinding, ...
Cause
This warning appears because the SQL adapter binding, sqlBinding, is not a standard binding shipped with the Windows Communication Foundation (WCF).
Resolution
You can safely ignore this warning.
BizTalk Server throws an exception if you use more than one Notification schema in the same application or use the Notification schema across multiple applications on the same host
Problem
BizTalk Server throws an XLANG exception or an exception stating that the application cannot locate the document specification because multiple schemas matched the message type.
Cause
This happens because of either of the following:
You have generated more than one Notification schema in a BizTalk Server project, deployed it to a BizTalk Server application, and then ran the application to receive notifications from the SQL Server database. Because the Notification schemas are common, there is a conflict between the schemas that are deployed in the BizTalk Server application.
In case of multiple projects, you have generated a Notification schema for each of the BizTalk Server projects, deployed each project to a separate BizTalk Server application on the same host, and then ran an application or applications to receive notifications from the SQL Server database. Because the schemas and assemblies are accessible across the applications in BizTalk Server, there is a conflict between the common schemas deployed under various BizTalk Server applications and assemblies.
Resolution
Use a single Notification schema file for a BizTalk Server application. If you need to use the Notification schema in multiple BizTalk Server applications on the same host, create an application containing a single Notification schema, and then use the notification schema from all other applications in BizTalk Server.
Adapter client throws an exception on performing an operation after the connectivity is restored between the adapter client and the SQL Server database
Problem
Adapter client throws the following exception on executing an operation on the SQL Server database:
{System.Data.Common.DbException} = {"A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)"}
Cause
During the execution of an operation, the adapter uses the connection from the SQL ADO.NET connection pool to connect to the SQL Server database, and perform the operation. If there is a brief network outage between the adapter client and the SQL Server database or if the SQL Server database is down temporarily, all the connections in the SQL ADO.NET connection pool become invalid. After the connectivity is restored and you try to perform an operation on the SQL Server database, the adapter uses the same invalid connections from the SQL ADO.NET connection pool, and therefore the adapter client throws the exception.
Resolution
The adapter client should implement retry logic in their operation execution where they should catch the exception and specify the operation retry count as “n+1”, where “n” is the value specified for the MaxConnectionPoolSize binding property. This implies that if there are “n” number of connections in the connection pool that have been rendered invalid, theoretically the adapter client should retry for a maximum of “n+1” times to get a valid connection, and hence perform the operation.
For example, to specify the retry count in BizTalk Server, open the Properties dialog box of a send port in an application, click Transport Advanced Options in the left pane of the dialog box, and in the Transport Options area, specify a value in the Retry count list.
Memory usage and thread count increases when using the adapter in a transacted inbound operation
Problem
In a transacted inbound operation, such as Polling, if there is no data available in the table being polled and the adapter continues to poll, over a period of time you experience an increase in the memory usage and the thread count.
Cause
If there is no data available in the table being polled, after every receive timeout cycle, Windows Communication Foundation (WCF) spawns a new thread to continue the polling operation. Hence, the thread count and memory usage increases over a period of time. However, if the table being polled has some data, the same thread continues to perform all subsequent polls.
Resolution
We recommend setting the ReceiveTimeout to the maximum possible value, which is 24.20:31:23.6470000 (24 days) so that a new thread is spawned only every 24 days. This will ensure that the memory usage and thread count does not grow too much too soon.
Note
If SqlAdapterInboundTransactionBehavior has been set, make sure the TransactionTimeout is also configured to maximum possible value, which is 24.20:31:23.6470000 (24 days). When using this workaround, we can add the SqlAdapterInboundTransactionBehavior only if the transaction isolation level has to be configured. Else, it is a best practice to remove that behavior.
For more information about the ReceiveTimeout binding property, see Read about the BizTalk Adapter for SQL Server adapter binding properties. For instructions on specifying binding properties, see Configure the binding properties for the SQL adapter.
Note
When using the adapter with BizTalk Server, setting the timeout to a large value does not impact the functionality of the adapter.