24.0 DB Load fails | Cannot convert to text/ntext or collate to 'Latin1_General_100_CI_AS_SC

Products

CA Automic Workload Automation - Automation Engine CA Automic One Automation

Issue/Introduction

24.0 DB Load of a pre 24.0 UTF-8 migrated database fails with the following error:

U00003592 UCUDB - Status: '42000' Native error: '4189' Msg: 'Cannot convert to text/ntext or collate to 'Latin1_General_100_CI_AS_SC_UTF8' because these legacy LOB types do not support UTF-8 or UTF-16 encodings. Use types varchar(max), nvarchar(max) or a collation which does not have the _SC or _UTF8 flags.'

This also shows up in a popup message as well as:

U00003590 UCUDB - DB error: '', '', '', ''

Steps to reproduce:

Run the DB migration from non-UTF-8 to UTF-8 on SQL Server, steps 1, 2, 3
Run the db load utility, loading /db/general/24.0/UC_UPD.TXT

Full trace snippet:

20240215/075748.880 - U00009909 TRACE: (BINDPAR: TABLE_Name ) 00007FF873649144 000002
00000000 4148 >AH<
20240215/075748.880 - U00009909 TRACE: (BINDPAR: TABLE_Name5 ) 00007FF87364914F 000005
00000000 41485F5F 5F >AH___<
20240215/075748.880 - U00009909 TRACE: (BINDPAR: TABLE_Prefix ) 00007FF873649155 000002
00000000 4148 >AH<
20240215/075748.880 - U00009909 TRACE: (BINDPAR: TABLE_Text ) 00007FF87364915B 000014
00000000 61726368 69766520 68656164 6572 >archive header<
20240215/075748.880 - U00009909 TRACE: (BINDPAR: TABLE_Lfd ) 00007FF87364925C 000002
00000000 C800 >..<
20240215/075748.880 - >200<
20240215/075748.880 - U00009909 TRACE: (BINDPAR: TABLE_UType ) 00007FF87364925E 000002
00000000 5520 >U <
20240215/075748.880 - U00009909 TRACE: (BINDPAR: TABLE_Check ) 00007FF873649264 000004
00000000 00000000 >....<
20240215/075748.880 - >0<
20240215/075748.880 - U00009909 TRACE: (BINDPAR: TABLE_Children ) 00007FF87364945E 000224
00000000 52482C30 322C4150 442C3031 2C415044 >RH,02,APD,01,APD<
00000010 432C3031 2C414A50 502C3031 2C414A50 >C,01,AJPP,01,AJP<
00000020 50412C30 312C414A 5050432C 30312C41 >PA,01,AJPPC,01,A<
00000030 434D542C 30322C41 48472C30 312C4148 >CMT,02,AHG,01,AH<
00000040 47482C30 312C4150 50462C30 312C4146 >GH,01,APPF,01,AF<
00000050 432C3031 2C415753 2C30312C 41562C30 >C,01,AWS,01,AV,0<
00000060 312C414A 5050562C 30312C41 4A505641 >1,AJPPV,01,AJPVA<
00000070 2C30312C 4143562C 30312C41 424C4F42 >,01,ACV,01,ABLOB<
00000080 2C30312C 414A5050 4F2C3031 2C414A50 >,01,AJPPO,01,AJP<
00000090 4F562C30 312C414A 504F502C 30312C41 >OV,01,AJPOP,01,A<
000000A0 4A505046 2C30312C 414A5046 562C3031 >JPPF,01,AJPFV,01<
000000B0 2C415242 2C30312C 414A5043 562C3031 >,ARB,01,AJPCV,01<
000000C0 2C414341 2C30312C 41505544 2C30312C >,ACA,01,APUD,01,<
000000D0 41505544 412C3031 2C415553 522C3031 >APUDA,01,AUSR,01<
20240215/075748.880 - U00009909 TRACE: (BINDPAR: TABLE_AvgLen ) 00007FF873649860 000004
00000000 CA000000 >....<
20240215/075748.880 - >202<
20240215/075748.880 - U00009909 TRACE: (BINDPAR: TABLE_IlmFlag ) 00007FF873649864 000001
00000000 53 >S<
20240215/075748.880 - U00009909 TRACE: (BINDPAR: TABLE_IlmKey ) 00007FF873649866 000007
00000000 41485F49 646E72 >AH_Idnr<
20240215/075748.880 - U00009909 TRACE: (BINDPAR: TABLE_LoadAtomic ) 00007FF87364987C 000002
00000000 0000 >..<
20240215/075748.880 - >0<
20240215/075748.880 - U00009909 TRACE: (BINDPAR: TABLE_Name ) 00007FF873649144 000002
00000000 4148 >AH<
20240215/075748.880 - UPDATE UC_TABLE SET TABLE_Name = ?,TABLE_Name5 = ?,TABLE_Prefix = ?,TABLE_Text = ?,TABLE_Lfd = ?,TABLE_UType = ?,TABLE_CheckFlg = NULL,TABLE_Check = ?,TABLE_WhereIni = NULL,TABLE_WhereDef = NULL,TABLE_Children = ?,TABLE_AvgLen = ?,TABLE_IlmFlag = ?,TABLE_IlmKey = ?,TABLE_LoadAtomic = ? WHERE TABLE_Name = ?
20240215/075748.880 - U00029108 UCUDB: SQL_ERROR Database handles DB-HENV: ec370920 DB-HDBC: ec373060
20240215/075748.880 - U00003591 UCUDB - DB error info: OPC: 'SQLExecDirect' Return code: 'ERROR'
20240215/075748.880 - U00003592 UCUDB - Status: '42000' Native error: '4189' Msg: 'Cannot convert to text/ntext or collate to 'Latin1_General_100_CI_AS_SC_UTF8' because these legacy LOB types do not support UTF-8 or UTF-16 encodings. Use types varchar(max), nvarchar(max) or a collation which does not have the _SC or _UTF8 flags.'
20240215/075748.880 - U00003594 UCUDB Ret: '3590' opcode: 'UPWC' SQL Stmnt: 'UPDATE UC_TABLE SET TABLE_Name = ?,TABLE_Name5 = ?,TABLE_Prefix = ?,TABLE_Text = ?,TABLE_Lfd = ?,TABLE_UType = ?,TABLE_CheckFlg = NULL,TABLE_Check = ?,TABLE_WhereIni = NULL,TABLE_WhereDef = NULL,TABLE_Children = ?,TABLE_AvgLen = ?,TABLE_IlmFlag = ?,TABLE_IlmKey = ?,TABLE_LoadAtomic = ? WHERE TABLE_Name = ?'
20240215/075748.880 - UCUDB32 UPWC RET 3590 HSTMT: 0000028BEC374B70 VALUE: 0000000000000000 ALL: 0.00094 DB: 0.00039 ODBC: 0.00003 UDB: 0.00051
20240215/075748.880 - UCUDB Memory Information (Opc=0212): Mem Usage = 8192 ( 227725312, 227733504 ), VM Size = 0 ( 646119424, 646119424 )
20240215/075748.880 - UCUDB32 CLAL RET 0000 HSTMT: 0000000000000000 VALUE: 0000000000000000 ALL: 0.00001 DB: 0.00000 ODBC: 0.00000 UDB: 0.00001
20240215/075748.881 - UCUDB32 CLAL RET 0000 HSTMT: 0000000000000000 VALUE: 0000000000000000 ALL: 0.00000 DB: 0.00000 ODBC: 0.00000 UDB: 0.00000
20240215/075748.994 - UCUDB32 RBCK RET 0000 HSTMT: 0000000000000000 VALUE: 0000000000000000 ALL: 0.11329 DB: 0.11328 ODBC: 0.00000 UDB: 0.00001
20240215/075748.994 - ucdbup: exit mit ret: 0001
20240215/075748.994 - U00003590 UCUDB - DB error: 'SQLExecDirect', 'ERROR ', '42000', 'Cannot convert to text/ntext or collate to 'Latin1_General_100_CI_AS_SC_UTF8' because these legacy LOB types do not support UTF-8 or UTF-16 encodings. Use types varchar(max), nvarchar(max) or a collation which does not have the _SC or _UTF8 flags.'
20240215/075751.670 - U00003590 UCUDB - DB error: '', '', '', ''

Environment

Release: 24.0

Database Type: SQL Server

Database collation: UTF-8

Cause

Incorrect options on ODBC connection

Resolution

To resolve this, make sure that the Perform translation for character data option is not activated (default as of MS SQL Server 2019 and later) in the Connection Encryption.

This step is also in the documentation here: https://docs.automic.com/documentation/webhelp/english/ALL/components/DOCU/24.0/Automic%20Automation%20Guides/Content/Installation_Common/PreparationSteps/PrepareAEDB_MSSQL.htm?tocpath=Installing%7CManual%20Installation%7CPreparing%20for%20the%20Manual%20Installation%7CPreparing%20the%20AE%20Database%7CPreparing%20the%20AE%20Database%20-%20MS%20SQL%7C_____0

Be sure that the Windows Beta feature is turned on.

Microsoft SQL Server/MS SQL Server on Linux

If you use ODBC:
- Activate the Trust server certificate option in the Microsoft SQL Server DSN Configuration window (default as of MS SQL Server 2019 and later)
- Make sure that the Perform translation for character data option is not activated (default as of MS SQL Server 2019 and later) in the Connection Encryption.
If you do not use ODBC, make the following definitions in the INI file of the Automation Engine and Utilities:
- TrustServerCertificate=Yes
- AutoTranslate=NO