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.

StoredProcMod

Founding partner for the BTUG.be

There is a new User Group born in Belgium. It’s a brand new BizTalk User Group, to be more precise, and goes by the name of BTUG.be. One of the initiators is someone of our very own company… Mitch Vanhelden, one of our Business Integration consultants working with Microsoft BizTalk.
Cnext is proud to be one of the founding partners of this Belgian BizTalk User Group. The initiative is a joined effort, together with some of the other leading partners on Business Integration using Microsoft technologies.

BtugLogo-Small

As a kick off event we’re targeting a full day event somewhere in November. For more news on the progression to organize this event and much more on the User Group, you should keep an eye out at the website http://www.btug.be or follow the twitter account @BTUGbe.

You’ll be seeing us on the upcoming events of the BTUG.be…. see you there!