BizTalk 2013 R2 issues while Terminating and Resuming Instance using C# code
In BizTalk, we manage active Instances, resume and terminate suspended instances using BizTalk Admin console. However, we can also do the same using C# code as well. In order to do that, we need to import Microsoft.BizTalk.Operations.dll. The location of this dll is - <BTS install directory path>\Microsoft BizTalk Server 2013 R2.
Here is the code to terminate the instance in C# :
// Get Instance IDs so we can retrieve the message.
var instanceID = new System.Guid("<MessageInstanceID>");
var biztalkOperations = new Microsoft.BizTalk.Operations.BizTalkOperations();
// Get the message
var biztalkMessage = biztalkOperations.GetMessage(messageID, instanceID);
//Terminate the message
biztalkOperations.TerminateInstance(instanceID);
Similarly in order to resume the instance, you can use the following code –
// Get Instance IDs so we can retrieve the message.
var instanceID = new System.Guid("<MessageInstanceID>");
var biztalkOperations = new Microsoft.BizTalk.Operations.BizTalkOperations();
// Get the message
var biztalkMessage = biztalkOperations.GetMessage(messageID, instanceID);
//Resume the message
biztalkOperations.ResumeInstance(instanceID);
These piece of code will work completely fine when you use it for BizTalk 2009 and lower versions. However, if you use them with 2010 and higher version, you will get the following error-
System.Data.SqlClient.SqlException was unhandled
HResult=-2146232060
Message=Conversion failed when converting from a character string to uniqueidentifier.
Source=.Net SqlClient Data Provider
ErrorCode=-2146232060
class="16"
LineNumber=16
Number=8169
Procedure=int_GetInstanceLock_BizTalkServerApplication
Server=BIZTALK2013R2
State=2
StackTrace:
at
Microsoft.BizTalk.Database.DatabaseAccessor.ExecuteReader(Int32 procIndex, Object[] procParams)
at Microsoft.BizTalk.Operations.OperationsMessageBoxAccessor.ops_OperateOnInstances(Int32 snOperation, Int32 fMultiMessagebox, Guid uidInstanceID, String nvcApplication, Int32 snApplicationOperator, String nvcHost, Int32 snHostOperator, Int32 nServiceClass, Int32 snServiceClassOperator, Guid uidServiceType, Int32 snServiceTypeOperator, Int32 nStatus, Int32 snStatusOperator, Int32 nPendingOperation, Int32 snPendingOperationOperator, DateTime dtPendingOperationTimeFrom, DateTime dtPendingOperationTimeUntil, DateTime dtStartFrom, DateTime dtStartUntil, String nvcErrorCode, Int32 snErrorCodeOperator, String nvcErrorDescription, Int32 snErrorDescriptionOperator, String nvcURI, Int32 snURIOperator, DateTime dtStartSuspend, DateTime dtEndSuspend, String nvcAdapter, Int32 snAdapterOperator, Int32 nGroupingCriteria, Int32 nGroupingMinCount, Int32 nMaxMatches, Guid uidAccessorID, Int32 nIsMasterMsgBox)
at Microsoft.BizTalk.Operations.OperationsGroup.OperateOnInstances(InstanceFilter ifc, MessageBoxDatabase mb, InstanceOperation op)
at Microsoft.BizTalk.Operations.OperationsGroup.TerminateInstances(InstanceFilter ifc, MessageBoxDatabase mb)
at Microsoft.BizTalk.Operations.OperationsGroup.TerminateInstances(InstanceFilter ifc)
at Microsoft.BizTalk.Operations.OperationsGroup.TerminateInstances(Guid instanceID)
at Microsoft.BizTalk.Operations.BizTalkOperations.TerminateInstance(Guid instanceID)
at TestSus.Program.Main(String[] args) in c:\Users\Administrator\Desktop\TestSuspend\TestSus\TestSus\Program.cs:line 18
at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
InnerException:
On taking SQL profiler and BizTalk traces, we saw that when the same piece of code was used in BizTalk 2009, the following SQL query gets executed –
Working scenario:
SP:StmtStarting IF (@snStatusOperator <> 0 AND (4 = @nStatus OR 32 = @nStatus OR 36 = @nStatus))
<ApplicationID>
054297 ABC\12345 83734 67 2014-10-30 13:21:20.537 10 BizTalkMsgBoxDb 1372235 ADCD32BTWKX01 0X010500000000000515000000F881FDE10FE14042CD68904D83DA0100 FMI 0 ADCD32BTWKX01 ABC\12345 5206 1856725667 0 1 1 8272 - P ops_OperateOnInstances 67 10 5044
In BizTalk 2010 and later –
SP:StmtStarting IF (@snStatusOperator <> 0 AND (4 = @nStatus OR 32 = @nStatus OR 36 = @nStatus))
.Net SqlClient Data Provider
054297 FMI\054297 8376 67 2014-10-30 13:21:20.537 10 BizTalkMsgBoxDb 1372235 ADCD32BTWKX01 0X010500000000000515000000F881FDE10FE14042CD68904D83DA0100 FMI 0 ADCD32BTWKX01 FMI\054297 5206 1856725667 0 1 1 8272 - P ops_OperateOnInstances 67 10 5044
In BizTalk 2009, when we run the TeminateInstance method after the GetMessage method, the application name is passed as a GUID. In BizTalk 2013 R2, the application name gets changed to .Net SQLClient Data provider if only GetMessage method gets executed first. We tried the same with other methods in Biztalk Operations but those do not alternate the application name.
The same issue is mentioned in the following msdn forum – https://social.msdn.microsoft.com/Forums/en-US/7b6a7cc1-00d3-4768-89fe-a954836f45e1/biztalkoperationsresumeinstance-method-in-biztalk-2010?forum=biztalkgeneral
The reason why this got introduced is that we introduced query optimization. Functions like GetMessage execute a select query on the databases and function like Terminate Instance or Resume Instance execute an update query on the databases.
Till BizTalk 2009, we used to pass Application ID as GUID for both Select and Update statement. When we pass GUID, a lock is acquired on the table. Therefore causing a performance overhead even while executing a Select Statement.
From BizTalk 2010 onwards, it was decided to acquire a lock only on Update statement and not on Select statements. And that is when this limitation was introduced.
Therefore the workaround is to create 2 BizTalkOperations objects. One for the Select operation methods and other for Update Operations methods.
Written By
Rasika Chaudhary
Reviewed By
Xuehong Gan
Microsoft GTSC