Recently I’ve been working with the GIS extensions in SQL Server, and Ineeded to import a GeoJSON file into a SQL Server 2017 table.
There’s a very straightforward way to import a valid JSON file into SQL Server with the OPENROWSET command, as shown below.
Declare @geoJson nvarchar(max) SELECT @geoJson = BulkColumn FROM OPENROWSET (BULK 'C:\Users\jeremy.lindsay\Desktop\my_gis_data.json', SINGLE_CLOB) as importedGeoJson SELECT left(@geoJson, 100)
The above SQL reads the GeoJSON file from my desktop, loads it into a variable, and allows me to view the leftmost 100 characters just as a quick visual check that the code GeoJSON data loaded into the variable correctly.
So far so good – the next step was to load this GeoJSON into a table. For this, I planned to use the OPENJSON command – but I noticed that my SQL Server Management Studio instance highlighted this command as unrecognised, as shown below.
I didn’t think too much of this – it happens sometimes – but of course when I ran the command, I got an error.
'Invalid object name 'OPENJSON'.'
This made no sense to me – I was sure I had used the syntax correctly, I’ve used this on other machines and, I’d recently upgraded to SQL Server 2017 on my development machine. After a bit of googling, I found some other people had this issue on earlier versions of SQL Server, and the compatability level needs to be 130 or higher. To identify my database compatibility level, I ran the command below:
SELECT compatibility_level FROM sys.databases WHERE name = 'SampleGis';
I was greatly surprised to see it was set to 120!
I decided to double check what version of SQL Server I was using with the command:
And was again greatly surprised when the answer came back telling me I was running SQL Server 2014.
As it turned out, I had an old version of SQL Server 2014 still running on my development machine. After uninstalling this, and trying again, the version correctly came back as SQL Server 2017, and the compatibility_level of my database was 140 – this time, OPENJSON worked correctly and I was able to view and query the GeoJSON file.