PCK UTF-8 for MS SQL Server fails if Language not set to English

Products

• CA Automic Workload Automation - Automation Engine
• Automic SaaS
• CA Automic One Automation

Issue/Introduction

When launching the  PCK.AUTOMIC_UTF-8 on a source MS SQL Server that was installed on a language other than English, it always fails on the Jobs PCK.AUTOMIC_UTF-8.MIG-SETP03.SUPPORT.NOILM.SRC with the error below

Wrong SQL statement in 'PCK.AUTOMIC_UTF-8.MIG.SQLI.GET.START.AH_IDNR.SRC'

This occurs on the call to the Vara PCK.AUTOMIC_UTF-8.MIG.SQLI.GET.START.AH_IDNR.SRC, it fails complaining about the conversion of the varchar into datetime as the Jobs passes the value that is 1997-02-22 (it comes from PCK.AUTOMIC_UTF-8.MIG.SQLI.GET.SRC.START.DATE)

To troubleshoot this, we simply had to preview the Vara SQLi with the bindpar set to "1997-02-22" or launch the equivalent query directly on SQL Server Studio while being connected to the source database:

select coalesce((select max(AH_IDNR) as AH_IDNR_START from AH where ah_timestamp4 <= '1997-02-22'),
                 (select min(ah_idnr) from ah)
                )

This will fail and will produce the following error depending on the MS SQL Server language:

German error in MS SQL Server:

Bei der Konvertierung eines varchar-Datentyps in einen datetime-Datentyp liegt der Wert außerhalb des gültigen Bereichs

French error in MS SQL Server:

La conversion d'un type de données varchar en type de données datetime a créé une valeur hors limites.

Spanish error in MS SQL Server:

La conversión del tipo de datos varchar en datetime produjo un valor fuera de intervalo.

Environment

Package.UTF8.DB.Migration 1.0.3 for MS SQL Server

Cause

Defect

Resolution

Workaround 1:

Ask your DBA to change the default Language to English for the user/database used in the Connection Object.

Workaround 2:

Edit the failing JOBS from the folder 

/PACKAGES/PCK.AUTOMIC_UTF8_DB/SOURCE/MOVE.NON-UTF8.DB.TO.UTF8/STEPS

Adding the following as first line:

set language english;

Solution:

Use a fix version listed below or a newer version if available.

Fix version: UTF-8 DB Migration Action Pack 1.0.4 - available

Additional Information

Defect ID: DE129159