Bulk starting/stopping/overriding EDI batches in BizTalk Server 2010

Torben Chrona Christiansen wrote a very handy blog post last year on how to bulk stop and start EDI batches in BizTalk Server 2009 using SQL queries. These queries can be used to aid in creating proof of concepts, to demonstrate batching in presentations, as a setup step in integration tests, or during deployments.

Seeing as the Trading Partner Management system has been overhauled to a large degree in BizTalk Server 2010 onwards with the introduction of partner agreements, these SQL queries unfortunately no longer work.

My most recent challenge has been to implement an EDI batching platform and I thought it would be very beneficial to rewrite these queries for BizTalk Server 2010. I have found them extremely useful during the POC stage to quickly stop, start and override (force a batch to release queued up messages even though the batch’s release criteria has not been met yet) batches, and I have also found that these queries are an absolute fundamental in building EDI batching integration tests as you need to ensure that all your batches are running and starting from a clean state (overriding helps tons here) in order to ensure a deterministic outcome. I have written BizUnit test steps built around these queries and have used them to a great degree.

All the below queries should be run against the BizTalkMgmtDb in order to take action against your batches. Please note that this is not supported by Microsoft, has only been tested by myself to a certain extent, and you will need to judge for yourself how you will use this.

The below query can be used to start all batches that aren’t currently running.

The below query can be used to stop all running batches (note that this is a clean stop, any queued up messages will be batched up and sent out first).

And lastly, the below query can be used to override all running batches, thus forcing them to send out any queued up messages rather than waiting for their regular release criteria.

If anyone can come up with a cleaner way of performing the above then please do let me know. If you would like to download the SQL scripts they are located on my google drive.