Contributed by Celeste Dufresne 

Symptom

You begin an update from Pega Platform™ version 8.7.4 to version 8.8.4 and see the Error in the update logs. The error is not an update specific error; it detects an incompatibility issue between the Pega-provided stored procedure sppr_rebuild_indexes and the Microsoft SQL Server and Azure SQL Server naming convention. You can also see the error in other work contexts when you are not updating your Pega deployment.

Errors

Calculate Table Stats Inner:

[echo] Calculating Database Table Statistics

[echo] Executing: EXECUTE PegaRULES884.sppr_rebuild_indexes 'stats'

[pega:sql] Unable to execute sql query:

[pega:sql] EXECUTE PegaRULES884.sppr_rebuild_indexes 'stats'

[pega:sql] com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '-'.

During a Helm update in a client-managed cloud environment with latest container images on Azure Kubernetes Service (AKS), you see the following error in the installer pod:

BUILD FAILED

/opt/pega/kit/scripts/setupDatabase.xml:354: The following error occurred while executing this line:

/opt/pega/kit/scripts/setupDatabase.xml:3028: Unable to execute sql query:

EXECUTE rules.sppr_rebuild_indexes 'stats'

Explanation

Pegasystems defect

The stored procedure sppr_rebuild_indexes does not escape database and schema names that contain special characters like a dash (--) or a period (.) Therefore, the call within the stored procedure fails with an incorrect syntax error.

The Pega-provided stored procedure sppr_rebuild_indexes is not compatible with SQL Server and Azure SQL Server because SQL Server and Azure SQL Server use a three-part database naming convention, for example, db-test-pega. Because of this database naming convention, the dynamic SQL in the stored procedure sppr_rebuild_indexes needs to be escaped using square brackets, []. See Define your naming convention - Cloud Adoption Framework | Microsoft Learn.

In prior versions, if this generate indexes task failed, the update would just continue. But a fix was delivered in Pega Platform versions 8.8.4 and Pega Platform '23 to fail the update if this task fails. This is why you see the issue with the stored procedure now during an update.

Environment

The problem was reported for an update to Pega Platform version 8.8.4.

Solution

Ensure that the stored procedure sppr_rebuild_indexes is not called from an update and manually correct and run the stored procedure before and after the update:

1. Manually fix the stored procedure before running the update and then run the stored procedure. See Correcting the stored procedure.
2. Ensure that the stored procedure is not called from the update. See Preventing the stored procedure from being called during the update process.
3. Manually fix the stored procedure after running the update and then run the stored procedure. See Correcting the stored procedure.
   
   You correct the stored procedure for your updated environment so that, when it is called from the Pega Platform, it runs successfully.

Correcting the stored procedure

You can alter the stored procedure at the database level to make it work correctly as shown in the following statements.

IF @Index_type = 'rules' or @Index_type = 'index' or @Index_type = 'stats'

BEGIN

SET @cmd =  'DECLARE TableCursor CURSOR FOR SELECT "[" + table_catalog +  "."  + table_schema + ''].[" + table_name + "]" as tableName

FROM INFORMATION_SCHEMA.TABLES WHERE table_type = "BASE TABLE" AND table_name IN

Here is the example of correct SQL generated in cursor to be executed:

ALTER INDEX ALL ON [db-test-pega].[PegaRULES884].[pr4_rule_ruleset] REBUILD WITH (FILLFACTOR = 90)

UPDATE STATISTICS [db-test-pega].[PegaRULES884].[pr4_rule_ruleset]

ALTER INDEX ALL ON [db-test-pega].[PegaRULES884].[pr_sys_appcache_entry] REBUILD WITH (FILLFACTOR = 90)

UPDATE STATISTICS [db-test-pega].[PegaRULES884].[pr_sys_appcache_entry]

ALTER INDEX ALL ON [db-test-pega].[PegaRULES884].[pr_sys_app_hierarchy_flat] REBUILD WITH (FILLFACTOR = 90

UPDATE STATISTICS [db-test-pega].[PegaRULES884]. [pr_sys_app_h±erarchy_flat]

ALTER INDEX ALL ON [db-test-pega].[PegaRULES884].[pr_sys_appcache_dep] REBUILD WITH (FILLFACTOR = 90)

UPDATE STATISTICS [db-test-pega].[PegaRULES884]. [pr_sys_appcache_dep]

ALTER INDEX ALL ON [db-test-pega].[PegaRULES884].[pr_sys_appcache_shortcut] REBUILD WITH (FILLFACTOR = 90)

UPDATE STATISTICS [db-test-pega].[PegaRULES884].[pr sys appcache shortcut]

ALTER INDEX ALL ON [db-test-pega].[PegaRULES884].[pr4_rule_shortcut] REBUILD WITH (FILLFACTOR » 90)

UPDATE STATISTICS [db-test-pega].[PegaRULES884].[pr4 rule shortcut]

Be sure to call sppr_rebuild_indexes before and after the update to get the database statistics up to date. The update drops any updates you have made to the stored procedure and puts it back into the Pega-provided version; therefore, you need to update it again after the update to get it working again.

Preventing the stored procedure from being called during the update process

To prevent the calling of the stored procedure sppr_rebuild_indexes during an update to Pega Platform 8.8.4 or Pega Platform ‘23, you can add the database property calculate.table.stats=false and completed.steps.CalculateTableStats=true.

For a Helm update, add the following lines to your YAML code:

For a VM update, edit the setupDatabase.properties file to add the setting calculate.table.stats=false and completed.steps.CalculateTableStats=true.

Finding the Resolved Issue in future product releases

This issue will be addressed in future patch releases of the Pega Platform. 

Watch the Pega Support Center Pega Platform Resolved Issues and search for ISSUE-841065 sppr_rebuild_indexes call is getting incorrect syntax error. Searching for the ISSUE title, sppr_rebuild_indexes call is getting incorrect syntax error, will find the Resolved Issue in other Pega Platform releases.

Related content

Pega Documentation

Adding and deleting database indexes

Microsoft Documentation

Define your naming convention - Cloud Adoption Framework | Microsoft Learn