Unable to apply SCSM 2019 UR2 - SQL Error

Question

I am unable to update to SCSM 2019 UR2 on our Data Warehouse server. Receiving Error Code: 0x80040E14. Any suggestions? Thank you

Details from the log files:

ExecuteSql: Failed to execute SQL script. Error Code: 0x80040E14. SET NOCOUNT ON;
DECLARE @Statement nvarchar(4000) ;
DECLARE table_cursor CURSOR FOR
SELECT 'ALTER TABLE .
ALTER COLUMN '+ COLUMN_NAME +' decimal(19,0);'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE ( TABLE_NAME LIKE '%CMv5_DISK' OR TABLE_NAME LIKE '%vex_GS_DISK') AND
COLUMN_NAME = 'Size0' AND DATA_TYPE != 'decimal' ;

OPEN table_cursor     
FETCH NEXT FROM table_cursor  
INTO @Statement    
WHILE @@FETCH_STATUS = 0
BEGIN        
    EXEC sp_sqlexec @Statement
    FETCH NEXT FROM table_cursor  
    INTO @Statement
END
CLOSE table_cursor;
DEALLOCATE table_cursor;

DECLARE table_cursor CURSOR FOR
SELECT 'ALTER TABLE .
ALTER COLUMN '+ COLUMN_NAME +' decimal(19,0);'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (TABLE_NAME LIKE '%CMv5_LOGICAL_DISK' OR TABLE_NAME LIKE '%vex_GS_LOGICAL_DISK') AND
(COLUMN_NAME = 'Size0' OR COLUMN_NAME = 'FreeSpace0') AND
DATA_TYPE != 'decimal' ;

OPEN table_cursor     
FETCH NEXT FROM table_cursor  
INTO @Statement    
WHILE @@FETCH_STATUS = 0
BEGIN        
    EXEC sp_sqlexec @Statement
    FETCH NEXT FROM table_cursor  
    INTO @Statement
END
CLOSE table_cursor;
DEALLOCATE table_cursor;

DECLARE table_cursor CURSOR FOR
SELECT 'ALTER TABLE .
ALTER COLUMN '+ COLUMN_NAME +' decimal(19,0);'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (TABLE_NAME LIKE '%CMv5_OPERATING_SYSTEM' OR TABLE_NAME LIKE '%vex_GS_OPERATING_SYSTEM') AND
COLUMN_NAME = 'TotalVisibleMemorySize0' AND
DATA_TYPE != 'decimal' ;

OPEN table_cursor     
FETCH NEXT FROM table_cursor  
INTO @Statement    
WHILE @@FETCH_STATUS = 0
BEGIN        
    EXEC sp_sqlexec @Statement
    FETCH NEXT FROM table_cursor  
    INTO @Statement
END
CLOSE table_cursor;
DEALLOCATE table_cursor;

/*Bug-420319 - Dimension Table fix for OperatingSystem table /
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'OperatingSystemDim' and COLUMN_NAME = 'PhysicalMemory' and DATA_TYPE != 'decimal')
BEGIN
/ Dropping Indexes */
DROP INDEX IF EXISTS NCI0_OperatingSystemDim on dbo.OperatingSystemDim;
DROP INDEX IF EXISTS NCI1_OperatingSystemDim on dbo.OperatingSystemDim;

/* Altering Data Type */
ALTER TABLE dbo.OperatingSystemDim ALTER COLUMN PhysicalMemory decimal(19,0);

/*Recreating Indexes */
CREATE INDEX NCI0_OperatingSystemDim on dbo.OperatingSystemDim(InsertedBatchId)
INCLUDE(OperatingSystemDimKey,BaseManagedEntityId,EntityDimKey,SourceId,OSVersion,OSVersionDisplayName,ProductType,
BuildNumber,CSDVersion,ServicePackVersion,SerialNumber,InstallDate,SystemDrive,WindowsDirectory,PhysicalMemory,
LogicalProcessors,CountryCode,Locale,Description,Manufacturer,OSLanguage,MinorVersion,MajorVersion,ObjectStatus_ConfigItemObjectStatusId,
ObjectStatus,AssetStatus_ConfigItemAssetStatusId,AssetStatus,Notes,DisplayName,IsDeleted ,UpdatedBatchId);

CREATE INDEX NCI1_OperatingSystemDim on dbo.OperatingSystemDim(UpdatedBatchId)
INCLUDE(OperatingSystemDimKey,BaseManagedEntityId,EntityDimKey,SourceId,OSVersion,OSVersionDisplayName,ProductType,
BuildNumber,CSDVersion,ServicePackVersion,SerialNumber,InstallDate,SystemDrive,WindowsDirectory,PhysicalMemory,
LogicalProcessors,CountryCode,Locale,Description,Manufacturer,OSLanguage,MinorVersion,MajorVersion,ObjectStatus_ConfigItemObjectStatusId,
ObjectStatus,AssetStatus_ConfigItemAssetStatusId,AssetStatus,Notes,DisplayName,IsDeleted ,InsertedBatchId);

/*Refreshing View After datatype change */
IF EXISTS (SELECT 1 from INFORMATION_SCHEMA.VIEWS where TABLE_NAME = 'OperatingSystemDimvw')
BEGIN
EXEC sp_refreshview 'dbo.OperatingSystemDimvw';
END
END

/*Bug-420319 - Dimension Table fix for LogicalDisk table */
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'LogicalDiskDim' and COLUMN_NAME = 'Size' and DATA_TYPE != 'decimal')
BEGIN

/Altering DataType/
ALTER TABLE dbo.LogicalDiskDim ALTER COLUMN Size decimal(19,0);

/*Refreshing View after datatype change */
IF EXISTS (SELECT 1 from INFORMATION_SCHEMA.VIEWS where TABLE_NAME = 'LogicalDiskDimvw')
BEGIN
EXEC sp_refreshview 'dbo.LogicalDiskDimvw';
END
END

if object_id (N'etl.ShredWaterMark', N'IF') is not null
drop function etl.ShredWaterMark;. Additional Error Description : Incorrect syntax near the keyword 'IF'.
ExecuteSqlScriptFileFromMsi: Failed to execute sql script in the specified file. Error Code: 0x80040E14. E:\Program Files\Microsoft System Center\Service Manager\DW\build_scdw_db.sql

Answer 1

Hi @Asp, Peter ,

I would concentrate on the following:

ExecuteSqlScriptFileFromMsi: Failed to execute sql script in the specified file. Error Code: 0x80040E14. E:\Program Files\Microsoft System Center\Service Manager\DW\build_scdw_db.sql

May I ask how exactly are you applying the Update?
Do you see any record like this in the logs:

RECONFIGURE. Additional Error Description : Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.

Is your DW synchronizing with the SM database? Can you please check on all the DW jobs and their status. From:

Update Rollup 2 for System Center Service Manager 2019
https://support.microsoft.com/en-us/help/4558753/update-rollup-2-for-system-center-service-manager-2019

Make sure that the Data Warehouse Server has successfully synchronized with the Management Server at least one time without any Management Packs in “Pending Disassociation”, “Pending Association”, or “Imported” status.

Are you running the install as admin?

----------

(If the reply was helpful please don't forget to upvote or accept as answer, thank you)

Thanks and Regards,
Stoyan

Answer 2

Thank you for your response. It appears that our data warehouse is the culprit. The Service Manager Service Catalog Cube Library management pack has failed deployment. We have made changes to this management pack to add additional field. I suspect that is where the problems lie. Any suggestions to troubleshooting this problem. Thank you again, Peter

Answer 3

Hi Peter,

so we are already one step closer to solving this :)

So let me first start by saying that you can sync your custom data to the datawarehouse. Here an example (not a Microsoft link):

Syncing ServiceMgr Data Warehouse With Custom CI Data
https://z3br1.wordpress.com/2013/10/14/syncing-servicemgr-data-warehouse-with-custom-ci-data-2/

You can learn more about outriggers here:

Overview of customizing the Service Manager data warehouse
https://learn.microsoft.com/en-us/system-center/scsm/customize-dw?view=sc-sm-2019

The thing is that this customizations should not be the cause for the failing MPSyncJob. In the worst case the customized data won't appear in the DW tables as mentioned here:

New classes don't appear in reporting or even in database
New classes don't appear in reporting or even in database

What I would suggest is, to do a bit more digging and eventually reset the jobs. You will find all this information in a nice post from Andreas Baumgarten here:

MP Sync Job - Waiting for Deployment/Execution to complete
mp-sync-job-waiting-for-deploymentexecution-to-complete

Please do some troubleshooting, like described in one of the articles, referenced in the post and let me know how it looks like.

----------

(If the reply was helpful please don't forget to upvote or accept as answer, thank you)
Thanks and Regards,
Stoyan