Tuesday, November 1, 2016

Change compatibility level of the database and JSON in SQL Server 2016

Working with SQL Server and  JSON  requires a compatibility level of 130 (SQL Server 2016). The post here describes how you can convert a JSON String and format a table in SQL Server.

The TestPubs (copy of pubs legacy database from Microsoft) database in an instance of SQL Server 2016 brought over by a data-tier related process does not change its compatibility level. However you can change its compatiblity level from 100 to 130 and with compatibility 130, the following query takes in a JSON string and converts it to a table with defaults. Here type 5 means it is an object.

declare @json nvarchar(150)
SET @json=N'[{"color": "red","value":"#f00"},{"color":"green","value":"#0f0"},{"color":"blue","value":"#00f"},{"color":"cyan","value":"#0ff"}]';
SELECT * From OPENJSON(@json)



Json130Open_00

Now if I revert the compatibility level to 100 and try the same query as before, I spawn an error as shown.


Json100Open_00


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