Japanese Characters are not recognized when using Invoke-SqlCmd -Query

Question

I'm inserting data from a json file to an SQL server table using Invoke-SqlCmd and using a stored procedure as following:

Invoke-SqlCmd -ServerInstance $servername -Database $database -Query "EXEC dbo.InsertDataFromJson @JSON='$json'

The json is obtained by getting it's raw content:

$json = Get-Content -Path "path\to.json" -Raw

$json # Content:
'{"Id": "2fe2353a-ddd7-479a-aa1a-9c2860680477", 
"RecordType": 20, 
"CreationTime": "2021-02-14T08:32:23Z", 
"Operation": "ViewDashboard", 
"UserKey": "10099", 
"Workload": "PowerBI", 
"UserId": "[email protected]", 
"ItemName": "テスト", 
"WorkSpaceName": "My Workspace", 
"DashboardName": "テスト", 
"ObjectId": "テスト" }'

All the column with strings, emails and japanese characters are NVARCHAR(MAX).

The problem is my json contains Japanese characters and they appear as ???? in the table.

When I try to insert a sample using SSMS directly it works fine.

Do you have any idea how to fix this ?

Thank you

Answer 1

Try setting the -Encoding flag to Utf8.

{"test":"みんな"}

Get-Content -Path ".\test.json" -Encoding Utf8

Answer 2

I just found an elegant solution to this mess, if you ever encounter the same problem. First, I have a stored procedure that takes a parameter. The website that helped is: https://community.idera.com/database-tools/powershell/ask_the_experts/f/sql_server__sharepoint-9/18939/examples-running-sql-stored-procedures-from-powershell-with-output-parameters

Instead of using Invoke-SqlCmd (which is the worst), I used System.Data.SqlClient.SqlCommand as follow:

$connection.ConnectionString="Server={0};Database={1};Integrated Security=True" -f $servername, $database
$connection.Open()

Here I use Integrated Security so I don't need to enter my creds. "dbo.InsertDataFromJson" is my stored procedure.

$Command = new-Object System.Data.SqlClient.SqlCommand("dbo.InsertDataFromJson", $connection)
$json = Get-Content -Path .\sample.json -Raw
$Command.Parameters.Add("@JSON", [System.Data.SqlDbType]"NVARCHAR")
$Command.Parameters["@JSON"].Value = $json
$Command.ExecuteScalar()
$connection.Close()

And Voilà! My japanese characters are there, everything is fine and I'm very happy :)