BizTalk Terminator–DataAccess::RunExecutionAsync: Invalid column name

BizTalk Terminator is a very useful tool for troubleshooting common BizTalk issues. It also provides various tasks to view information like Spool Count and Orphaned Instances from the MsgBoxDB.

Sometimes while executing a Terminator task you might come across this exception

************************************************************

DataAccess::RunExecutionAsync: Invalid column name ‘Relationship’. Invalid column name ‘Relationship’

************************************************************

The Terminator tool works by executing WMI and SQL scripts against your BizTalk SQL Server.

In this particular case the ‘View Spool Message Count of Instances’ executes a SQL script. You can view the script in the ‘Description’ tab.

The script internally uses a table called TempMessageReferences to aggregate all the information needed to calculate the Spool count.

The table TempMessageReferences is created in the MsgBoxDB temporarily… it is supposed to be created at the beginning of the Terminator script and dropped at the end.

In this particular case the script was failing because the TempMessageReferences table did not have a column called Relationship.

The table structure expected by the ‘View Spool Message Count of Instances’ script is

but the one present in the MsgBoxDB was

 

The table name TempMessageReferences is used across various SQL scripts that the BTS Terminator uses. So in this case for some reason the TempMessageReferences table was created from a different SQL script (Terminator Task) and when the  ‘View Spool Message Count of Instances’ was being run it was failing.

To resolve this issue we just dropped the TempMessageReferences table from the MsgBoxDB and reran the Terminator task. Most likely two administrators had run a Terminator Task against the same BizTalk Cluster which overwrote the table.

The Terminator tool though very useful and easy to use has to be used with great precaution. I have personally seen a lot of administrators simply ignore the initial warnings of stopping the host instances, SQL Agent and backing up the DB.

Few tasks in Terminator under the View section, even though just query information create and drop tables in the MsgBoxDB. The tasks under the Delete and Manage section directly make changes to the tables that BizTalk uses.