Service Manager data warehouse jobs stuck or fail with "Incorrect syntax near ')'"

Applies to: System Center 2016 Service ManagerSystem Center 2012 R2 Service Manager

 

Symptoms


You experience one or more of the following issues:

  • The MPSyncJob job is stuck on module "MP Sync SMGroupName : Associate Imported MP Vertex".
  • The Transform.common job fails, and you receive the following error message for the TransformIncidentDim module:
     
  • In the Operations Manager event log, several events are logged that contain the following error message:
     

Cause


The issues occur because Service Manager classes such as the Incident class have been extended with a new field that has a default value of a space or multiple spaces instead of NULL.

Resolution


Before you start, run the following SQL query against the DWStagingAndConfig or ServiceManager database:

use DWStagingAndConfig -- or Use ServiceManager
select managedtypepropertyname, DefaultValue from ManagedTypeProperty where managedtypepropertyname like '%' and defaultvalue = ''
order by ManagedTypePropertyName

If the query returns no results, the following resolution doesn’t apply to your system. Otherwise, follow these steps:

Step 1: Fix the issue in the management pack

  1. Use the following query to find the field and the management pack name:

    Select MP.MPName, MTP.ManagedTypePropertyName, MTP.DefaultValue from managementPack as MP
    Join ManagedTypeProperty as MTP on MTP.ManagementPackId = MP.ManagementPackId
    Where MTP.defaultvalue = ''
  2. In the Service Manager Console, select Administration.
  3. In the Administration pane, expand Administration, and then select Management Packs.
  4. In the Management Packs pane, select the management pack that's identified by the SQL query.
  5. In the Tasks pane, select Export under the name of the management pack.
  6. In the Browse For Folder dialog box, select a location for the file, and then click OK.
  7. Open the XML-formatted management pack file by using Notepad, find the "Property ID=" that matches the value that was returned by the SQL query, and then remove the DefaultValue=" " portion from the property.
  8. Find the following text in the XML file and remove the whole line:

    <ManagementPackPublicKeyToken>
  9. Open the management pack in the Service Manager Authoring Tool, and then reseal the management pack by using the same .snk key file that was previously used to seal the pack.
  10. In the Service Manager console, import the management pack.
  11. In the Service Manager console, select Data Warehouse, expand Data Warehouse, and then select Data Warehouse Jobs.
  12. In the Data Warehouse Jobs pane, right-click MPSyncJob,  and then select Resume from the Tasks list.
  13. Periodically refresh the Data Warehouse Job pane until the MPSyncJob is completed.

Step 2: Fix the issue in data warehouse by using one of the following methods:

Method 1: Restore the data warehouse databases

Restore the following databases to a date before the change:

  • DWStagingAndConfig
  • DWRepository
  • DWDataMart
  • OMDWDataMart
  • CMDWDataMart

Note Make Sure that the backups are within the period that's specified in Data Retention settings. To check Data Retention settings: In the Service Manager console, click Administration, and then go to Administration > Settings.

After you restore the data warehouse databases, data warehouse uses the updated management pack and the data that's stored on the Service Manager.

Method 2: Modify the DWStagingAndConfig database

Important If the management pack wasn't created by you, you may need to rebuild the data warehouse with new databases by using this method. Therefore, we recommend that you contact Microsoft support before you continue.

Run the following query:

Use DWStagingAndConfig  
Select * from DeploySequenceStaging   
Select DeployItemName, DeploySequenceId, Outcome, StatusID from DeployItemStaging where StatusId =2 and outcome like '%Incorrect syntax near '')''.%'
-- Click the Sequence field and view the first line.  Confirm that the management pack is one that you created before proceeding
 
DECLARE @DeploySequenceID [uniqueidentifier]
SET @DeploySequenceID = (Select DeploySequenceID from DWStagingAndConfig.dbo.DeployItemStaging WHERE outcome like '%Incorrect syntax near '')''.%')
Select @DeploySequenceID
DELETE FROM DWStagingAndConfig.dbo.DeploySequenceStaging WHERE DeploySequenceId=@DeploySequenceID
DELETE FROM DWStagingAndConfig.dbo.DeployItemStaging WHERE DeploySequenceId=@DeploySequenceID
 
DECLARE @BatchID Int
SET @BatchID = (Select Batchid from infra.workitem where STatusID=2  and ( Charindex('Incorrect syntax near '')''.',CAST([ErrorSummary] AS VARCHAR(MAX)))>0 ) )
Update infra.workitem SET statusid = 6 WHERE BatchId = @BatchID

More Information


We don’t recommend that you set default values for fields that are defined in a management pack.  If you must set a default value, make sure that it's not a blank space or multiple blank spaces.