Failed to Insert JSON data in SQLSERVER If column name contains APOSTROPHE('s)

Question

I'm trying to load the data Json format data into SQL Server via calling store procedure using PowerShell

Below is the JSON Data

{
    "id":"2aa7ce44-2ac1-4efb-a5df-6c34086744b7",
    "isReadOnly":false,
    "isOnDedicatedCapacity":false,
    "capacityMigrationStatus":"",
    "type":"Group",
    "state":"Active",
    "name":"Wes' Test Team",
    "dashboards":[],
    "reports":[],
    "datasets":[],
    "users":[]
},
{
    "id":"cb876bcc-285a-40d7-a120-f23e73baafe8",
    "isReadOnly":false,
    "isOnDedicatedCapacity":false,
    "capacityMigrationStatus":"",
    "type":"Group",
    "state":"Active",
    "name":"Kehat's Corner",
    "dashboards":[],
    "reports":[],
    "datasets":[],
    "users":[]
}

Fail to insert the data because the column name contains '(APOSTROPHE) and its failing the script to load the data.

Could someone help me how to overcome if column name has the APOSTROPHE in the data.

PowerShell Script

Connect-PowerBIServiceAccount 
$PBIGroupsFile = "C:\TEMP\PBIGroupsExpanded.json"
$ActiveGroupsURLExPersonal = '/admin/groups?$top=5000&' + '$filter=type ne' + " 'PersonalGroup'" + '&$expand=dashboards,reports,datasets,users'
Invoke-PowerBIRestMethod -Url $ActiveGroupsURLExPersonal -Method Get | Out-File $PBIGroupsFile

Stored procedure

ALTER PROCEDURE [dbo].[InsertInventoryDataFromAPI_Temp]
    @JSON NVARCHAR(MAX) 
AS
BEGIN

DECLARE @PARSSEDJSON NVARCHAR(MAX)= SUBSTRING(@JSON,CHARINDEX('"value":',@JSON)+8,LEN(@JSON)-CHARINDEX('"value":[',@JSON)-10)

select  ROW_NUMBER () OVER (ORDER BY id) AS RowNum,* INTO #FLJSON
FROM OPENJSON(REPLACE(REPLACE(@PARSSEDJSON, CHAR(13)+CHAR(10), ''),'''',''))
WITH(
    id nvarchar(max) '$.id',
    isReadOnly nvarchar(max) '$.isReadOnly',
    isOnDedicatedCapacity nvarchar(max) '$.isOnDedicatedCapacity',
    capacityId nvarchar(max) '$.capacityId',
    capacityMigrationStatus nvarchar(max) '$.capacityMigrationStatus',
    type nvarchar(max) '$.type',
    state nvarchar(max) '$.state',
    name nvarchar(max) '$.name',
    dashboards nvarchar(max) '$.dashboards' AS JSON,
    reports nvarchar(max) '$.reports' AS JSON,
    datasets nvarchar(max) '$.datasets' AS JSON,
    users nvarchar(max) '$.users' AS JSON
    ) 

--Load Workspace Info
    INSERT INTO dbo.Workspace_temp
    Select id, isReadOnly,isOnDedicatedCapacity,capacityId,capacityMigrationStatus,type,state,name from #FLJSON

Calling the stored procedure using PowerShell

$JSON = Get-Content "C:\TEMP\PBIGroupsExpanded.json"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$SQLServer;Database=$SQLDBName;Integrated Security=True;"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "EXEC [dbo].[InsertInventoryDataFromAPI_Temp] '$JSON'"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$SqlConnection.Open()
$SQLReturn=$SQLCmd.ExecuteNonQuery()
$SqlConnection.Close()

Does this answer your question? [Way to insert text having ' (apostrophe) into a SQL table](https://stackoverflow.com/questions/8336812/way-to-insert-text-having-apostrophe-into-a-sql-table) — Misery, Jan 31 '20 at 11:52
Use a parameterized query in the PowerShell script. That will avoid the quote problem and [other issues](https://www.dbdelta.com/why-parameters-are-a-best-practice/), If you need help with that, add the relevant PowerShell code snippet to your question. — Dan Guzman, Jan 31 '20 at 11:55
@danGuzman added the PowerShell Script can you help me with that ?? — Beerendra saragadam, Jan 31 '20 at 12:33
@misery I have added the my powershell script if possible can you help me with the sample query — Beerendra saragadam, Jan 31 '20 at 12:40
@Beerendrasaragadam Please provide the powershell code calling your stored procedure — Misery, Jan 31 '20 at 12:49
@Beerendrasaragadam, the snippet you added retrieves the json data, which I assume is working. Add the snippet that calls the proc like Misery asked. — Dan Guzman, Jan 31 '20 at 12:54
@Beerendrasaragadam no repro if I simply try to parse the input without attempts to clean up. `select * from openjson('[' + @json + ']')` works just fine. — Panagiotis Kanavos, Jan 31 '20 at 15:13
@Beerendrasaragadam on the other hand the expression that produces `@PARSSEDJSON` createsan invalid JSON string — Panagiotis Kanavos, Jan 31 '20 at 15:13

score 0 · Answer 1 · answered Jan 31 '20 at 15:14

0

As @DanGuzman pointed out in comments, you need to use SQL parameters to avoid this issue

$SqlCmd.CommandText = "EXEC [dbo].[InsertInventoryDataFromAPI_Temp] @JSON"
$SqlCmd.Parameters.AddWithValue("@JSON", $JSON)

answered Jan 31 '20 at 15:14

Misery

495
4
17

score 0 · Answer 2 · answered Jan 31 '20 at 16:28

0

Below is a parameterized query example. I didn't include the SqlDataAdapter here as it's not needed for the proc call.

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand("[dbo].[InsertInventoryDataFromAPI_Temp]", $SqlConnection)
$SqlCmd.CommandType = [System.Data.CommandType]::StoredProcedure
$jsonParameter = $SqlCmd.Parameters.Add("@JSON", [System.Data.SqlDbType]::NVarChar, -1)
$jsonParameter.Value = $JSON
$SqlConnection.Open()
$SQLReturn=$SQLCmd.ExecuteNonQuery()
$SqlConnection.Close()

answered Jan 31 '20 at 16:28

Dan Guzman

43,250
3
46
71

I tried the above and got the error saying string cannot be converted to object. – Beerendra saragadam Feb 04 '20 at 09:00
Exception calling "ExecuteNonQuery" with "0" argument(s): "Failed to convert parameter value from a Object[] to a String." At line:15 char:1 + $SQLReturn=$SQLCmd.ExecuteNonQuery() + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : InvalidCastException – Beerendra saragadam Feb 04 '20 at 09:07
@Beerendrasaragadam, `Get-Content` returns an object array by default. Add the -Raw switch to return the entire file contents as a string: `Get-Content -Raw`. Also, I'm not sure why you are using `SUBSTRING` in the proc but that code corrupts the JSON. I think you can remove it and use the `@JSON` parameter directly in `OPENJSON`. – Dan Guzman Feb 04 '20 at 11:03

Failed to Insert JSON data in SQLSERVER If column name contains APOSTROPHE('s)

2 Answers2