Azure Synapse Serverless Command Displays Special Characters Instead of Chinese Characters?

Question

I am facing an issue while running the 'serverless' command on the terminal in Azure Synapse. The command seems to display special characters (question marks) instead of correctly rendering Chinese characters.

For example, the text "南京1月装卸费" is being changed to "??1????".

I have tried adjusting the collation and encoding settings in my SQL query, using the 'Chinese_PRC_CI_AI' collation, and ensuring that the data is stored in delta/parquet. However, the problem persists.

Here is an excerpt from my SQL query:

SELECT 
    PurchaseOrderLineDescription,
    CONVERT(NVARCHAR(MAX), PurchaseOrderLineDescription) COLLATE Chinese_PRC_CI_AI AS PurchaseOrderLineDescription1
FROM  
    OPENROWSET(
        BULK 'https://dbstorageda0s.dfs.core.windows.net/BC2.0-IP/TransformLayer/Facts/Views/CoupaPurchaseOrder/',
        FORMAT = 'DELTA'
    ) AS coupapurchaseorder_ipspend_test 
WHERE 
    PurchaseOrderNumber IN ('4523955603', 'PO15044020', 'PO15164185');

Answer 1

Facing issues with the 'serverless' command in Azure Synapse displaying question marks instead of Chinese characters, adjusting collation at the column level might not be sufficient.

To resolve this, try altering the collation at the database level using the following SQL command:

USE Master;
ALTER DATABASE YourDatabaseName COLLATE Chinese_PRC_CS_AS_KS_WS;

Replace YourDatabaseName and customize the collation as needed, using AI/AS, KA/KI, and WS/WI based on your data characteristics.

After applying the database-level collation, retry the 'serverless' command. Ensure your data encoding and format (delta/parquet) are also set correctly.

Remember to take backups before making changes. If issues persist, review encoding settings and data formats.