Tracking Database Service Instances Aggregation SQL Script

This script is an example to aggregate data of Service Instances from the tracking database (BizTalkDTADb):

SELECT

    dta_Svc.strServiceName + '_' + dta_SvcSta.strState AS 'Service Name'

    , COUNT(*) AS 'Number of Hits'

    , MAX(DATEDIFF(millisecond, [dta_SI].dtStartTime, [dta_SI].dtEndTime)) AS 'Maximum Execution Time'

    , AVG(CONVERT(BIGINT, DATEDIFF(millisecond, [dta_SI].dtStartTime, [dta_SI].dtEndTime))) AS 'Average Execution Time'

    , MIN(DATEDIFF(millisecond, [dta_SI].dtStartTime, [dta_SI].dtEndTime)) AS 'Minimum Execution Time'

FROM [BizTalkDTADb].[dbo].[dta_ServiceInstances] [dta_SI]

INNER JOIN

    [BizTalkDTADb].[dbo].[dta_Services] dta_Svc ON [dta_SI].uidServiceId = dta_Svc.uidServiceId

INNER JOIN

    [BizTalkMgmtDb].[dbo].[bts_assembly] bts_ass ON dta_Svc.strAssemblyName = bts_ass.nvcFullName

INNER JOIN

    [BizTalkDTADb].[dbo].[dta_ServiceState] dta_SvcSta ON [dta_SI].nServiceStateId = dta_SvcSta.nServiceStateId

WHERE

      [dta_SI].dtEndTime IS NOT NULL

GROUP BY dta_Svc.strServiceName, dta_SvcSta.strState

ORDER BY dta_Svc.strServiceName

The sample result of the script is:

In my case, this script is further enhanced by filtering to get only the last 5 minutes tracked service instances and wrapped as a stored procedure. The data from the stored procedure is used to feed the monitoring system.

Host cannot be deleted: there are suspended service instances for this host which need to be removed

I experienced this error when I tried to delete unused host in my BizTalk environment:

In my case, there are no suspended service instances for the related host.

After further research, there is subscription related with the host. You can check the subscription in the Subscription table of BizTalkMsgBoxDb:

SELECT *
  FROM [BizTalkMsgBoxDb].[dbo].[Subscription]
  WHERE nvcApplicationName = 'HostName'

If the query result returns row(s), then you have to remove the subscription with the related host. In my case, there is a dynamic send port related with the host. I un-enlist the send port and re-try to remove the host. That's it!

Orchestrations failed to be (re)deployed due to binding errors

If you experience errors when you try to (re)deploy orchestrations through your visual studio (due to cached Binding Information):

You can solve this problem either by:

1. Set the Redeploy property of your Orchestrations project to false (right click the orchestration project, choose properties, choose deployment on the left panel, and set the Redeploy value under general on the right panel to False).
 

2. Or clear the deployment binding information of visual studio by deleting them in C:\Documents and Settings\[User Name]\Application Data\Microsoft\BizTalk Server\Deployment\BindingFiles or C:\Users\[User Name]\AppData\Roaming\Microsoft\BizTalk Server\Deployment\BindingFiles.You can delete the whole folder if you want to. By deleting this, you enforce visual studio to create new binding information.

Afterward, you can retry the deployment.

Sometimes Lost Tab/Esc in Virtual PC

I've experienced this problem several times. At first, I just restarted and usually solved the problem. But today, it didn't solve my problem, so these are the possible solutions:

• Delete Options.xml file located in %AppData%\Roaming\Microsoft\Virtual PC folder
• Add new Path Rule to the "Local Security Policy\Software Restriction Policies\Additional Rules" with value "%appdata%\microsoft\virtual pc\vpckeyboard.dll" and Unrestricted security level

I've tried the first solution. Both solutions require you to shut down your Virtual PC including the console.

Word 2007 changes "Outline Level" to Level 1 randomly to paragraphs

I experienced this recently when I was busy finalizing some documents. How to prevent/fix this annoying Word 2007 behavior as follows:

Tips to prevent or deal with the problem

• Exclude the TOC until you’re ready to either print the document or convert to PDF. The TOC and the DM seem to interact to create instability.
• Always close the DM pane before closing your file or Word. The DM is more stable when a document is opened with the DM pane is closed.
• Always open documents with DM pane closed. If you’re uncertain, start Word first, close the pane, then open your document.
• You can tell that your DM is being scrambled if Word shows the “Word is formatting the document…” status bar while the file is opening.
• Always check your DM when you first open the file.
• The only time I’ve noticed a DM become scrambled is on open. This doesn’t mean there aren’t other times it’ll become scrambled. For example, I’d be cautious by closing the DM pane when generating a TOC, or printing or converting documents to PDF, especially if they contain a TOC.
• I don’t know what makes a file susceptible to scrambling on open in the first place.
• As soon as you notice your DM is scrambled, close your document without saving.
• Saving the file with a scrambled DM will permanently scramble both the DM and the TOC. It’s actually the heading level tags that get scrambled.
• Use Word’s auto-backup feature: Tools -> Options; Save tab; check “Always create backup copy.” Lets you roll back one save.
• Create and keep frequent backups of your own, even several in a day if you’re being particularly productive: time is precious; disk space is cheap.
• If you don’t need the really rapid navigation offered by the DM, try using Outline view instead. According to what I’ve read, it’s not prone to scrambling.
• Additional observations: Merging an older, unscrambled copy with a scrambled copy passes the scrambling on to the good copy, no matter which direction you merge (good into bad, or bad into good), or which document’s formatting you opt to retain. I haven’t found any way to repair/recover a scrambled and saved file, but I haven’t tried any commercial recovery products.

To remedy
If your DM becomes scrambled and you haven’t saved your document:

1. Close the document and Word without saving
2. Open Word
3. Close the DM pane
4. Open your file

From another comment by by Jan Zambor :
To repair a scrambled doc:
1. select entire doc (Ctrl+A)
2. go to Paragraph windows
3. set Outline level to "Body Text" (headings will NOT be set to body text, you should end up with the correct doc)

The adapter Microsoft Dynamics AX 2009 raised an error message: "The message does not contain a body part."

I experienced this error when trying to update a data synchronously with AIF. This same error also happens with delete service.

For now, Rasmus Jaeger said that the workaround is:

A simple check, in the AIF update and delete services in AX, which checks if the message part is empty. If the message part is empty, AX returns a predefined static xml document to BizTalk. 

So, we are waiting for the hot fix ............

Note to Self: Invoking Static Members of a Class in BRE

To invoke static members of a class, StaticSupport must be added in the registry:

• For 32 bit, the location is in:
  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\BusinessRules\3.0\StaticSupport
• For 64 bit, the location is in:
  HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\BusinessRules\3.0\StaticSupport

The Messaging Engine encountered an error during the processing of one or more inbound messages

I experienced this error when a receive port disassembles a flat file in 2 cases: 

• A 1 MB flat file contains more than 10000 rows
  The work around that I did is by throttling the Threads per CPU to 100.
• A 30 MB flat file contains more than 300000 rows
  I noticed that the Memory used by the receive host has grown up more than 3 GB. The receive host is a 32-bit only host. Thus, it recycles the host. So, by using 64-bit host and also throttling the Threads per CPU, the flat file was processed successfully. The 64-bit receive host consumed more than 6 GB of memory.