On a recent engagement, I was developing a BizTalk 2013 solution that received XML-based data from several sources and ultimately inserted rows into a SQL Server database using a stored procedure. An additional requirement was that the database connection had to be dynamic.
As with most development, everything worked fine in the development environment and even in initial UAT testing. A timeout issue started to appear intermittently as the size of the data being processed increased and the content of the database grew. Rerunning the same test data through would often complete successfully. The details of the message logged to the Windows Logs was:
A message sent to adapter “WCF-Custom” on send port “WCF.Timeout_184.108.40.206_WCF.Timeout.ProcessDataToSQL_Dyn_WCFTest_c495b1996f35b303” with URI “mssql://xxxx//Test?” is suspended.
Error details: System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. —> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out
I captured a SQL Profile for the offending stored procedure and reran it in SSMS. For one of the test cases, I noticed the processing time took between 50 seconds and 1 minute and 10 seconds and I immediately remembered that the default timeout on WCF adapter calls is 1 minute.
Since the send port is dynamic, there are no binding configuration properties to set so I added the following line to the message assignment shape:
I then redeployed the solution, sent a test message and received the exact same timeout message! Next I figured maybe it was one of the “other” timeout settings that was getting triggered so I added the following additional lines to the message assignment shape:
I then redeployed the solution, sent a test message and received the same message again. That wasn’t it. What to do now?
The first step is to create a stored procedure to mimic the processing. I added a waitfor statement to ensure that the stored procedure runs over 1 minute.
CREATE procedure [dbo].[WCFTest]
@Data as varchar(max)
set nocount on
–do some long running process
waitfor delay ’00:01:30′
The next step is to generate the schema for the WCFTest stored procedure using the ConsumeAdapterService selection of the Add Generated Items tool.
The next step is to create a BizTalk solution for testing. I am not going to provide detailed steps here but the final orchestration should look like the following:
The declaration of the orchestration messages, ports and variables are:
- Rcv_Data – standard one-way receive port
- Data_In – message declared as System.Xml.XmlDocument
- SQL_Request – message declared as TypedProcedure_dbo.WCFTest
- SQL_Response – message declared as TypedProcedure_dbo.WCFTestResponse
- xmlString – variable declared as System.String
- xmlDoc – variable declared as System.Xml.XmlDocument
- Dyn_WCFTest – dynamic Request-Response send port
Initially the MessageAssignment shape contains the following code:
xmlString = “<ns0:WCFTest xmlns:ns0=’http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo’>” +
“<ns0:Data>Data_0</ns0:Data> ” +
xmlDoc = new System.Xml.XmlDocument();
SQL_Request = xmlDoc;
SQL_Request(WCF.Action) = “TypedProcedure/dbo/WCFTest”;
SQL_Request(WCF.BindingType) = “sqlBinding”;
Dyn_WCFTest(Microsoft.XLANGs.BaseTypes.Address) = @”mssql://xxxx//Test?”;
Dyn_WCFTest(Microsoft.XLANGs.BaseTypes.TransportType) = “WCF-Custom”;
Finally, deploy the solution and configure it using a simple receive location for the input data file. Sending in any XML test message produces the following timeout message:
Next, add the following line to the MessageAssignment shape.
Redeploying the solution and sending in a test message again produces the same timeout message! Even though we set the timeout to 10 minutes, apparently there must be more.
I remembered that there is another way to configure WCF adapter settings by using the WCF.BindingConfiguration property. This is normally used to configure unexposed properties, such as the UseAmbientTransaction setting.
I removed the explicit configuration setting and added the following line to the MessageAssignment shape:
SQL_Request(WCF.BindingConfiguration)=@”<binding name=””SqlAdapterBinding”” sendTimeout=””00:10:00″” />”;
I then redeployed the solution, sent in a test message and the processing succeeded!