Dealing with p_UI_ISBATCHRUN errors

When working with BPM Connect data process control, a common error that occurs for developers is the dreaded misbehavior of p_UI_ISBATCHRUN. The exact error will look like this:

The page has misbehaved. If this error persists, please contact for assistance. Reference: "P_UI_ISBATCHRUN"

What is this, and what causes it? There are several different scenarios and causes for this error. To trouble shoot, it helps to understand what P_UI_BATCHRUN is. P_UI_BATCHRUN is a stored procedure stored in the BPMConnect database. The purpose of this routine is to determine if any BPMC related SQL Server Agent jobs are running in the database. This aids in collision control and feed run status. This routine tends to be sensitive due to the underlying way SQL Server Database Engine integrates with SQL agent. The underlying code uses the standard msdb.dbo.sp_help_job routine. This routine to be used in this context requires an openrowset call against the database. Since the openrowset call actually uses and external oledb call, it makes it prone to external server security timeouts.
As mentioned previously, there are many different things that can cause this error, however, there seem to be three primary reason that are most common.

Issue 1: Wrong version of .NET 2.0
For newer versions of Data Process Control to run properly, it requires the use of .NET 2.0 Service Pack 2 for proper operation (DPC .NET Versions Table). If you are running DPC version 2.2.0 or greater, download .NET 2.0 Service Pack 2 from Microsoft and install on the server.

Issue 2: Ad Hoc Distribied Queries are not enbaled on the SQL Server.
Another reason for this error tends to be the Ad Hoc Distributed Queries option on the SQL Server Surface area on the database server is disabled. To remedy, execute the following code:

sp_configure 'show advanced options', 1
reconfigure
go
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure
go

Issue 3: SQLOLEDB either is not authenticating or is timing out.
The most effective way to test SQLOLEDB behavior in the context of p_UI_ISBATCHRUN is to execute this command:

select *
from openrowset('sqloledb'
, 'server=SERVERNAME;trusted_connection=yes'
, 'set fmtonly off exec msdb.dbo.sp_help_job')

If this command yields a list of feed names, it is working correctly. If it does not, it should return either an error or a timeout message. This helps indicate where the problem may lie. Timeouts are usually indicative of an under powered server, or poor server performance. Security blockage can be either do to SQL Server severity configuration settings, or, windows server settings. Appropriate measures should be taken depending on the exact error encountered.


If none of these remedies work, it is suggested to execute the routine directly in SQL Server. Example:
use bpmconnect
use
exec p_UI_ISBATCHRUN

This will help determine if the issue resides in the routine itself, or, if it is a link up issue with the Data Process Control. If the routine is operating correctly, it should return a 1 or 0. If you are able to successfully return a 1 or 0 executing in the SQL Server Management Studio, but the error still resides in Data Process Control, this would indicate some kind of link up issue with the Data Process Control to the database. If it is not, this approach will give you more detailed error messaging than what’s offered on the Data Process Control and hopefully help you resolve the issue.

If all else fails, a possible work around solution is to change the Collision Management mode of the Data Process Control. To do this, go to Data Process Control -> Settings - > CollisionManagement. Change this from "AGENT" to "LOG". This instructs the DPC to base its status and collision detection not off of the SQL Server Agent, but the LOG Tables of BPM Connect. This executes the sister routine, P_UI_ISBATCHRUN2, which can be less prone to OLEDB security errors. LOG mode is not ideal, as the collision detection is not as good, but as work around may be your only choice if the above remedies do not yield results.

For additional information on p_UI_ISBATCHRUN, see this entry
http://bpmconnect.blogspot.com/2010/04/dpc-error-puiisbatchrun.html