Scandic Characters not supported in SQL Server

[Chetan Sharma Kandel]

Hi,

I am having problem with scandic characters(mainly,ä,ö,å) in SQL SERVER.

The issue with my database is that if i enter the special characters (by saying special characters, i am focused on ä,å,ö) manually( just editing the value in table column),its working but when i use select statement to read from json file and insert into table , it looks something like this: Mäle, Femäle. From this, what i have concluded is , database is supporting those characters but issue is in select statement.

Following is the test sql script I have used:

select gender
 from OPENROWSET(bulk 'abc/testJsonFile.json',DATA_SOURCE='MyAzureBlobStorage',single_clob) as j
cross apply OPENJSON(BulkColumn)
WITH (
gender nvarchar(200) N'$.gender'
);

Actually, I have used special characters in gender field in json file in order to test it.

Male->Mäle  and  Female->Femäle

and the output looks like this:

gender
Femäle
Mäle
Femäle

I found that database collation also has some role in supporting special characters. Is it somewhat related to collation issue or something else?

Looking forward to suggestions.

Thanks.

[#2 · Accepted solution]

Hi,

The issue is resolved now. I thought it would be beneficial for someone like me having the same issue.Therefore, i am posting the solution.

It was actually related to the encoding of the file. The json file i was using was in UTF-8. It was causing the problem as SQL Server 2017 doesn't support UTF-8 characters. Therefore, i used Azure Data Factory CopyActivity to transfer the file in UTF-8 to file in UTF-16 using source and sink "ENCODING NAME" in copy activity. The desired file was in UTF-16 now.

After that i made very small change in the script as follows:

select gender
 from OPENROWSET(bulk 'abc/testJsonFile.json',DATA_SOURCE='MyAzureBlobStorage',single_nclob) as j
cross apply OPENJSON(BulkColumn)
WITH (
gender nvarchar(200) N'$.gender'
);

I changed single_clob to single_nclob as the specified file was unicode.

And, it worked fine.

Thanks.