Problem when entering accented characters via insert query (with sqlcmd in MSSQL)

Products

• CA Release Automation - Release Operations Center (Nolio)

Issue/Introduction

When we try to insert values  with accented characters, for example 'Aè%T', on MSSQL table, action is completed successfully but, after check, the value inside table is 'Aè%T'.

We launch this query via SQLCMD from windows server:

insert into dbo.PROVA (SYSTEM) values ('Aè%T');

Field type is CHAR (30)

As we have also encountered troubles with accented characters in handling other files or actions, it seems that CA Release Automation doesn't handle these characters.

Could you please find the solution.                       

Environment

Release : 6.7

Component : CA RELEASE AUTOMATION ACTION PACK

MSSQL: MSSQL 2014, 2016

Cause

Please find investigation summary below

Investigation Summary

1: The RA action in use  "Run Command Line" whose only task is to invoke a command line with appropriate parameters and than validated exit code against what configured in action to decided status of fail/passed based on exit code returned by underlying script.

2: The action is configured as below 

Command line executed: cmd /c "sqlcmd -S test.broadcom.com\.,1433 *********-d TESTUSR -b -i "C:\Temp\test_sql.sql" >> "C:\Temp\test_sql.log

Expected Return value: 0

3: The accent characters in question I believe is part of the file C:\Temp\test_sql.sql which is passed as one of the parameter to the sqlcmd invoke via RA "Run Command Line"

4: The issue seems to be specific to SQL and not to Release Automation (RA) action pack.

Validation steps

1: Running the same command manually on the server result in same error what is recorded when the command called via RA action "Run Command Line"

Resolution

In the investigation it was found that starting with SP2 for SQL Server 2014, BCP and BULK INSERT support UTF-8 files via code page '65001'. The code page can be mentioned either on command line or in the script doing bulk insert

- SQL File

      BULK INSERT MyTable FROM 'D:\data.csv' WITH ( CODEPAGE = '65001' , DATAFILETYPE = 'char' , FIELDTERMINATOR = ',' );

- Command Line: 

      cmd /c "sqlcmd -S test.broadcom.com\.,1433 ........... -d TESTUSR -b -i "C:\Temp\test_sql.sql" -f 65001 >> "C:\Temp\test_sql.log