0

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": "102273335@gmail.com", 
"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

riwik
  • 41
  • 2
  • 1
    Do you have en example of the content in the json file? Probably an encoding error. – Zucchini Apr 19 '21 at 20:59
  • 1
    Encoding of the json file is "UCS-2 LE BOM". But as you can see I extract the content from it Get-Content and the japanese characters are there. – riwik Apr 19 '21 at 21:05
  • From your post I don't know the value of the $json variable. – Zucchini Apr 19 '21 at 21:07
  • Here is an example: '{ "Id": "2fe2353a-ddd7-479a-aa1a-9c2860680477", "RecordType": 20, "CreationTime": "2021-02-14T08:32:23Z", "Operation": "ViewDashboard", "UserKey": "10099", "Workload": "PowerBI", "UserId": "102273335@gmail.com", "ItemName": "テスト", "WorkSpaceName": "My Workspace", "DashboardName": "テスト", "ObjectId": "テスト" }' – riwik Apr 19 '21 at 21:07
  • 1
    Can you edit your post with the example and also provide the schema for your table to reflect the data types in it? Specifically the column with the incorrectly encoded value. – Zucchini Apr 19 '21 at 21:12

2 Answers2

0

Try setting the -Encoding flag to Utf8.

{"test":"みんな"}

Get-Content -Path ".\test.json" -Encoding Utf8
Zucchini
  • 459
  • 6
  • 16
  • I've already tried this. I think the issue is with Invoke-SqlCmd and I don't have another way to insert data into Sql table without using BULK and OpenRowset. – riwik Apr 19 '21 at 21:33
  • Ok so if i run an insert statement like this i get the same ??? entry in my table. insert into japanese_chars (text) values ('テスト'). But if i define the datatype like this it inserts it correctly. insert into japanese_chars (text) values (N'テスト') I don't know how to define it that way using the json import function though. – Zucchini Apr 19 '21 at 21:43
  • Yes. As I said, running SQL command insert into with japanese characters in values is working fine: I ran this in SSMS and it works ```insert into dbo.PowerBIActivityEvent (ItemName, Activity) VALUES (N'ンプライ', N'test123')``` the problem is running Invoke-SqlCmd with the json as a variable and japanese characters in it's content.. – riwik Apr 19 '21 at 21:55
0

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 :)

riwik
  • 41
  • 2