May 14, 2015

BizTalk SQL error “The join order has been enforced because a local join hint” during testing.

While doing performance benchmarking of our customers BizTalk environment, one of the developers ran the bts_CleanupMsgBox script directly via SSMS to clean up history before starting a new test run. We started seeing the following error in the event log soon after.

The following stored procedure call failed: ” { call [dbo].[bts_UpdateMsgbox_TxHost]( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}”. SQL Server returned error string: “Warning: The join order has been enforced because a local join hint is used.;Warning: The join order has been enforced because a local join hint is used.;Duplicate key was ignored.”.

With BizTalk 2013 R2, we should not run bts_CleaupMsgBox stored procedure directly from SQL Server Management Studio. Instead, run this cleanup using the Health Monitor (which internally calls the bts_CleaupMsgBox stored proc) to purge all entries in MsgBox database and also use Health Monitor to delete and purge for all the other entries (e.g. tracking). This caused the error to go away and restored my environment.

Read More

Leave a Reply

Your email address will not be published. Required fields are marked *