PCM - How to Troubleshoot 37000 Errors

Error: 

Error 37000: Adjudication batch processing engine is not installed correctly.

Resolution:

The Adjudication script (“Create Adjudication Job.sql”) needs to be executed against the database the user is trying to run batch adjudicate in, and then enabled.

In SSMS, navigate to SQL Server Agent > Jobs, right-click on the pcm_adjudication job and select "Enable". 

Error:

Error 37000: Adjudication batch processing engine is halted due to failure.

Resolution:

The Adjudication job needs to be reviewed in SSMS. It may have been disabled due to deadlock, job failure or server restart and just needs to be enabled again.

In SSMS, navigate to SQL Server Agent > Jobs, right-click on the pcm_adjudication job and select "Enable". 

Error:

Error: 37000: [Microsoft][ODBC SQL Server Driver][SQL Server]Login has not been granted access to check the status of the adjudication job on the server, please contact your system administrator.

Resolution:

Within the SQL server there is another database named MSDB, this is a system database that is referenced on occasion.  There are specific areas in this database that you are lacking permission to which utilize the PCM Job.

The below script will assist in granting your user permission by creating a Role (named PCM_query_jobs) that has what you need.  In order for your User profile to utilize the permissions you will need to assign your User to the Role itself after running the script below:

/*******************************************************************************************

      The Adjudication Run form needs read only access to msdb sysjobs and msdb sysjobhistory

      -To accomplish this, we create a new role, PCM_query_jobs on msdb and grant it select access to the required tables.

      -In order to use this, a User must be created on msdb that has PCM_query_jobs as a member and is assigned to

      -the desired server login.

*******************************************************************************************/

USE [msdb]

IF  NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'PCM_query_jobs' AND type = 'R')

      CREATE ROLE [PCM_query_jobs] AUTHORIZATION [dbo]

GO

USE [msdb]

-- Give it permissions on the table.

GRANT SELECT ON sysjobs TO PCM_query_jobs

GRANT SELECT ON sysjobhistory to PCM_query_jobs

GO

Once you create the role, it will show up under the MSDB database, which is where you will need to assign your User to the Role (Not in the PCM database):