GeoJson, GIS, SQL Server

‘Invalid object name ‘OPENJSON’.’ – SQL Server doesn’t recognise OPENJSON when bulk importing files

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

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:

SELECT @@VERSION

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.


About me: I regularly post about Microsoft technologies and .NET – if you’re interested, please follow me on Twitter, or have a look at my previous posts here. Thanks!

One thought on “‘Invalid object name ‘OPENJSON’.’ – SQL Server doesn’t recognise OPENJSON when bulk importing files

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s