Monday, May 22, 2017

Error Msg 103, Level 15, State 4

The following is formatted in JSON

{"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.


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


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=
{"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:


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


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.

Get the March 2018 Power BI update

The update is out and you can get it from here : You can also get it from Windows Store if you are running Windows 10. Well what is n...