"CA Service Catalog Configuration: CA MDB" fails when upgrading to 17.1
Article ID: 91788
Updated On:
Products
SUPPORT AUTOMATION- SERVER
CA Service Desk Manager - Unified Self Service
CA Service Desk Manager
CA Service Management - Asset Portfolio Management
CA Service Management - Service Desk Manager
Issue/Introduction
Problems when upgrading CA Service Catalog to 17.1.
The following appears in install.log:
INFO [DeployThread: Configuración de CA Service Catalog: CA MDB] [ConfigureSLCMMDB] SLCM MDB Configuration Command: "C:\Program Files\CA\Service Catalog\scripts\configurator.bat" -c mdb -p "C:\Users\caadmin\AppData\Local\Temp\\slcm_config.properties"
INFO [DeployThread: Configuración de CA Service Catalog: CA MDB] [ActiveProcessHolder] Windows Process ID (PID): 2432
INFO [DeployThread: Configuración de CA Service Catalog: CA MDB] [ActiveProcessHolder] Start Monitor. Process ID (PID): 2432
DEBUG [DeployThread: Configuración de CA Service Catalog: CA MDB] [CommonProcessUtilities] Deploy module 'mdb' failed with return code - -3
DEBUG [DeployThread: Configuración de CA Service Catalog: CA MDB] [CommonProcessUtilities] Java HotSpot(TM) 64-Bit Server VM warning: ignoring option UseSplitVerifier; support was removed in 8.0
INFO [DeployThread: Configuración de CA Service Catalog: CA MDB] [ActiveProcessHolder] Exit Monitor. Process ID (PID): 2432
DEBUG [DeployThread: Configuración de CA Service Catalog: CA MDB] [CommonProcessUtilities] Command terminated with exit code: -3
DEBUG [DeployThread: Configuración de CA Service Catalog: CA MDB] [ConfigureSLCMMDB] SLCM MDB Configuration Exit Code: -3
INFO [DeployThread: Configuración de CA Service Catalog: CA MDB] [ConfigureSLCMMDB] SLCM MDB Configuration Exit Code: -3
DEBUG [DeployThread: Configuración de CA Service Catalog: CA MDB] [ConfigureSLCMMDB] Configuring mdb failed. Firing Task Error.
The following error appears in install_mdb.log:
\install_mdb.log
INFO - end
Update sql failed:ALTER procedure [dbo].[usm_sp_system_change_details_multi_param]
@idlist varchar(8000)
as
begin
select id, name, data_type, new_value as old_value, new_value, multi_value from usm_system_change_detail(NOLOCK)
where id in (SELECT splitdata FROM dbo.fnSplitString(@idlist,''))
end
DEBUG - Cannot resolve the collation conflict between "Modern_Spanish_CI_AS" and "Modern_Spanish_CS_AS_KS_WS" in the equal to operation.
INFO - MDBTools_0308I - Installation of last object failed, requeuing it to the end of the list.
INFO - MDBTools_0305E - Error processing procedure 'usm_sp_system_change_details_multi_param.xml'; details follow:
ERROR - com.microsoft.sqlserver.jdbc.SQLServerException: Cannot resolve the collation conflict between "Modern_Spanish_CI_AS" and "Modern_Spanish_CS_AS_KS_WS" in the equal to operation.
com.microsoft.sqlserver.jdbc.SQLServerException: Cannot resolve the collation conflict between "Modern_Spanish_CI_AS" and "Modern_Spanish_CS_AS_KS_WS" in the equal to operation.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1655)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:885)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:778)
The following appears in install.log:
INFO [DeployThread: Configuración de CA Service Catalog: CA MDB] [ConfigureSLCMMDB] SLCM MDB Configuration Command: "C:\Program Files\CA\Service Catalog\scripts\configurator.bat" -c mdb -p "C:\Users\caadmin\AppData\Local\Temp\\slcm_config.properties"
INFO [DeployThread: Configuración de CA Service Catalog: CA MDB] [ActiveProcessHolder] Windows Process ID (PID): 2432
INFO [DeployThread: Configuración de CA Service Catalog: CA MDB] [ActiveProcessHolder] Start Monitor. Process ID (PID): 2432
DEBUG [DeployThread: Configuración de CA Service Catalog: CA MDB] [CommonProcessUtilities] Deploy module 'mdb' failed with return code - -3
DEBUG [DeployThread: Configuración de CA Service Catalog: CA MDB] [CommonProcessUtilities] Java HotSpot(TM) 64-Bit Server VM warning: ignoring option UseSplitVerifier; support was removed in 8.0
INFO [DeployThread: Configuración de CA Service Catalog: CA MDB] [ActiveProcessHolder] Exit Monitor. Process ID (PID): 2432
DEBUG [DeployThread: Configuración de CA Service Catalog: CA MDB] [CommonProcessUtilities] Command terminated with exit code: -3
DEBUG [DeployThread: Configuración de CA Service Catalog: CA MDB] [ConfigureSLCMMDB] SLCM MDB Configuration Exit Code: -3
INFO [DeployThread: Configuración de CA Service Catalog: CA MDB] [ConfigureSLCMMDB] SLCM MDB Configuration Exit Code: -3
DEBUG [DeployThread: Configuración de CA Service Catalog: CA MDB] [ConfigureSLCMMDB] Configuring mdb failed. Firing Task Error.
The following error appears in install_mdb.log:
\install_mdb.log
INFO - end
Update sql failed:ALTER procedure [dbo].[usm_sp_system_change_details_multi_param]
@idlist varchar(8000)
as
begin
select id, name, data_type, new_value as old_value, new_value, multi_value from usm_system_change_detail(NOLOCK)
where id in (SELECT splitdata FROM dbo.fnSplitString(@idlist,''))
end
DEBUG - Cannot resolve the collation conflict between "Modern_Spanish_CI_AS" and "Modern_Spanish_CS_AS_KS_WS" in the equal to operation.
INFO - MDBTools_0308I - Installation of last object failed, requeuing it to the end of the list.
INFO - MDBTools_0305E - Error processing procedure 'usm_sp_system_change_details_multi_param.xml'; details follow:
ERROR - com.microsoft.sqlserver.jdbc.SQLServerException: Cannot resolve the collation conflict between "Modern_Spanish_CI_AS" and "Modern_Spanish_CS_AS_KS_WS" in the equal to operation.
com.microsoft.sqlserver.jdbc.SQLServerException: Cannot resolve the collation conflict between "Modern_Spanish_CI_AS" and "Modern_Spanish_CS_AS_KS_WS" in the equal to operation.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1655)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:885)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:778)
Environment
CA Service Catalog 14.1 Cum 2 Inc 2 or later
Cause
In 14.1 Cum 2 Incremental 2 the fnSplitString function isadded with the SQL_Latin1_General_CP1_CS_AS collation hardcoded
and the collation set in id, name, data_type, new_value as old_value, new_value, multi_value columns
of the usm_system_change_detail table
is different than SQL_Latin1_General_CP1_CS_AS
(ex: Modern_Spanish_CS_AS_KS_WS), causing a collation conflict when running the usm_sp_system_change_details_multi_param stored procedure.
and the collation set in id, name, data_type, new_value as old_value, new_value, multi_value columns
of the usm_system_change_detail table
is different than SQL_Latin1_General_CP1_CS_AS
(ex: Modern_Spanish_CS_AS_KS_WS), causing a collation conflict when running the usm_sp_system_change_details_multi_param stored procedure.
Resolution
We can see in the usm_sp_system_change_details_multi_param the collation is Modern_Spanish_CI_AS.
The database collation is mdb Modern_Spanish_CI_AS.
The fnSplitString collation is Modern_Spanish_CI_AS Checking the usm_sp_system_change_details_multi_param SP we can see that runs the following query:
select id, name, data_type, new_value as old_value, new_value, multi_value from usm_system_change_detail(NOLOCK)
This usm_system_change_detail table has Modern_Spanish_CI_AS collation.
However, the columns mentioned in the query above are using Modern_Spanish_CS_AS_KS_WS.
We updated the fnSplitString function to use Modern_Spanish_CS_AS_KS_WS instead database_default and we could run the store procedure.
1. Log into the mdb database
2. Expand and go to Functions > Locate dbo.fnSplitString
3. Right-click on it > Modify
Change from:
RETURNS @output TABLE(splitdata NVARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CS_AS
to
RETURNS @output TABLE(splitdata NVARCHAR(MAX) COLLATE <collation being used by id, name, data_type, new_value as old_value, new_value, multi_value columns of the usm_system_change_detail table>
(example: RETURNS @output TABLE(splitdata NVARCHAR(MAX) COLLATE Modern_Spanish_CS_AS_KS_WS)
4. Execute
5. Open again the function and you should be able to see something like:
USE [mdb]
GO
/****** Object: UserDefinedFunction [dbo].[fnSplitString] Script Date: 04/11/2018 08:05:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fnSplitString]
(
@string NVARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @output TABLE(splitdata NVARCHAR(MAX) COLLATE Modern_Spanish_CS_AS_KS_WS)
)
BEGIN
DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
WHILE @start < LEN(@string) + 1 BEGIN
IF @end = 0
SET @end = LEN(@string) + 1
INSERT INTO @output (splitdata)
VALUES(SUBSTRING(@string, @start, @end - @start))
SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)
END
RETURN
END
6. Re-run the installer.
The database collation is mdb Modern_Spanish_CI_AS.
The fnSplitString collation is Modern_Spanish_CI_AS Checking the usm_sp_system_change_details_multi_param SP we can see that runs the following query:
select id, name, data_type, new_value as old_value, new_value, multi_value from usm_system_change_detail(NOLOCK)
This usm_system_change_detail table has Modern_Spanish_CI_AS collation.
However, the columns mentioned in the query above are using Modern_Spanish_CS_AS_KS_WS.
We updated the fnSplitString function to use Modern_Spanish_CS_AS_KS_WS instead database_default and we could run the store procedure.
1. Log into the mdb database
2. Expand and go to Functions > Locate dbo.fnSplitString
3. Right-click on it > Modify
Change from:
RETURNS @output TABLE(splitdata NVARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CS_AS
to
RETURNS @output TABLE(splitdata NVARCHAR(MAX) COLLATE <collation being used by id, name, data_type, new_value as old_value, new_value, multi_value columns of the usm_system_change_detail table>
(example: RETURNS @output TABLE(splitdata NVARCHAR(MAX) COLLATE Modern_Spanish_CS_AS_KS_WS)
4. Execute
5. Open again the function and you should be able to see something like:
USE [mdb]
GO
/****** Object: UserDefinedFunction [dbo].[fnSplitString] Script Date: 04/11/2018 08:05:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fnSplitString]
(
@string NVARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @output TABLE(splitdata NVARCHAR(MAX) COLLATE Modern_Spanish_CS_AS_KS_WS)
)
BEGIN
DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
WHILE @start < LEN(@string) + 1 BEGIN
IF @end = 0
SET @end = LEN(@string) + 1
INSERT INTO @output (splitdata)
VALUES(SUBSTRING(@string, @start, @end - @start))
SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)
END
RETURN
END
6. Re-run the installer.
Feedback
Yes
No
Powered by
