Qlik Replicate: Replicating special characters from Sysbase ASE to SQL server 2017

[HOX]

During the replication from Sybase ASE 16.x to SQL server 2017, the conversion on the columns that contain special characters is incorrect; i.e.: if the Source has “Denial – no auth” the Target is getting “Denial ? no auth”

Environment

Qlik replicate 7.0.0.856
Source endpoint Sybase ASE version 16
Target Endpoint SQL Server 2017

Resolution

Use the Character Substitution option to convert the Special Characters with the values supported by the Target location, in this case, the hexadecimal value.

Steps followed to determine the issue:

1. Testing the transformation, STRING to WSTRING

   1. Search and choose WSTRING

      
      
      

   2. Change the WSTRING value from 50 to 500

      
      
      

   3. Reload the Target and check the SQL SERVER Table

      Note: The error persists using this option.

2. Testing the Character Substitution
   

   1. Generate the varbinary for the column MCTR_DESC for all the records that contain hyphen (-)

      

   2. Copy the result to notepad

   3. Generate the varbinary for the column MCTR_DESC for all the records that contain APOSTROPHE  (‘)

      

   4. Copy the result to  notepad

      
   5. Applying character substitution
      
      

      Add Source Character 96&92
      Add Substitute Character 2d&27

       

How to identify the correct Source Character and Substitute Character values?

1.  For the Source Character:
   
   
   
   
   
   Notes:
   
   
   • As we know in the binary system, a letter, number, space or sign is equivalent to two hexadecimal characters.
   • The counting starts after the letter “x”
     
2. For Substitute Character
   
   Identify the respective hexadecimal value for the special character.