Monday, May 22, 2017

Error Msg 103, Level 15, State 4

The following is formatted in JSON

["wclass",
{"student":{"name":"Linda Jones","legacySkill":"Access, VB 5.0"}etc..
},
{  "student":{"name":"Adam Davidson","legacySkill":"Cobol, MainFrame"}
},
{"student":{"name":"Charles Boyer","legacySkill":"HTML, XML"}
}]

This is a valid JSON according to RFC 4627.


json_01

However when you use this in SQL Server to look at the JSON using the OpenJSON, for example, you will get this error:


json_00

The reason for this error:

Msg 103, Level 15, State 4, Line 2
The identifier that starts with "wclass"...

lies in the fact that SQL Server string starts with a single quote and therefore you need to provide this declaration:

declare @json nvarchar(Max)
set @json=
'["wclass",
{"student":{"name":"Linda Jones","legacySkill":"Access, VB 5.0"}
},
{"student":{"name":"Adam Davidson","legacySkill":"Cobol, MainFrame"}
},
{"student":{"name":"Charles Boyer","legacySkill":"HTML, XML"}
}]'


When you do this the error goes away as shown:


json_02


The character count in @json is also important as you see in this SQL query:


json_03

The answer is that RFC 4627 validation requires a string to start with a double quote("), but the SQL Server's JSON validation requires the JSON to begin with a single quote(') as we saw in this post.

No comments:

Post a Comment

Do you know what you installed while custom installing SQL Server 2022?

 When you install any software in Windows OS, the key information gets into the Registry and some of which you can look up in the control pa...