Monitor BizTalk Server SQL Job fails

The monitor BizTalk Server SQL Job at one of our customers kept failing. The BizTalk job Monitor BizTalk Server (BizTalkMgmtDb) calls the stored procedure [dbo].[btsmon_Inconsistent]. While this stored procedure calls several other stored procedures to cleanup the messagebox and other meta-tables within BizTalk. Within these stored procedures SQL-commands are generated dynamically to run against the BizTalk databases/tables.

Each time the SQL jobs ran, it gave the error message:

Incorrect syntax near ‘Send_Host_x64_MessageRefCountLog’. [SQLSTATE 42000] (Error 102).¬† The step failed.

Because of the multiple host instances in the clients configuration, the generated SQL-command can become quite large. And in our case the problem lied within the called stored procedure [dbo].[btsmon_MessagesWithoutReferences].
To generate the SQL-command dynamically, a variable @tsql is used of the type nvarchar(4000)… Now here is where the problem occured. The generated t-SQL exceeded the 4000 characters that are allowed and as a result only part of the command was stored in the variable. And of course because of the partial command, the t-SQL command couldn’t be executed and raised the error shown above.

To resolve the issue, I just modified the stored procedure by changing the type of the variable @tsql from nvarchar(4000) to nvarchar(MAX). This way the dynamically generated command can be larger than 4000 characters.