3

I'm trying to use OPENJSON in a Python script to import some basic JSON into a SQL database. I initially tried with a more complex JSON file, but simplified it for the sake of this post. Here's what I have:

sql_statement = "declare @json nvarchar(max) = '{\"name\":\"James\"}'; SELECT * FROM OPENJSON(@json) WITH (name nvarchar(20))" 
cursor.execute(sql_statement)
cursor.commit()
connection.close()

The error I receive:

pypyodbc.ProgrammingError: (u'42000', u"[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.")

Any thoughts on why I'm seeing this error? I was successfully able to execute other SQL queries with the same pypyodbc / database configuration.

user1944673
  • 279
  • 1
  • 4
  • 13
  • 1
    Very important to tag the DBMS here being SQL Server as `OPENJSON` is a TSQL specific method used by no other SQL engine. Also, the error seems clear. You are not properly using the `WITH()` clause (usually reserved for `SELECT` statements referenced by name in main query). In fact, it may be redundant to include here. Aside -always test queries in Management Studio. – Parfait Jul 24 '16 at 21:48

1 Answers1

8

The problem could be that your database is running in an older compatibility level, where OPEN JSON is not available.

To find the compatibility level of your database, run following SQL statement:

SELECT compatibility_level  FROM sys.databases WHERE name = 'your_db_name';

If the result is 120 or lower, you'll need to update your compatibility level to 130, by running:

ALTER DATABASE your_db_name SET COMPATIBILITY_LEVEL = 130;

Note: In case your database is actually Azure SQL DB, you should check the version as well, as OPEN JSON is not available for versions prior to 12.x

maya-msft
  • 422
  • 2
  • 7