Tuesday, March 13, 2012

BizTalk SQL Agent job "Monitor BizTalk Server" failing with a syntax error?

Setting up my BizTalk Server SQL Agent jobs today, I noted that the job
Monitor BizTalk Server (BizTalkMgmtDb)
failed with a syntax error!?
Investigating the issue a bit futher, I realized this job was calling the SPROC btsmon_Inconsistent
in the BizTalkMgmtDb which in turn called the SPROC:
btsmon_MessagesWithoutReferences
This SPROC has the declaration:
declare @tsql nvarchar(4000)
However, for BizTalk installations with many applications and many hosts a mere 4000 characters is not enough.

Changing to:
declare @tsql nvarchar(max)
solved the problem.

No comments: