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.
Release : 6.7
Component : CA RELEASE AUTOMATION ACTION PACK
MSSQL: MSSQL 2014, 2016
Please find investigation summary below
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.
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"
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