Build error when using valid SQL 2017 syntax

chris1411

Hello, I'm using ReadyRoll to deploy to a SQL 2017 database. I am using the OPENJSON in some of my scripts and have confirmed that everything works correctly on the development database. However, whenever I go to generate the ReadyRoll script, I am getting a build error related to the OPENJSON function and passing in a variable for the path option. I am targeting SQL Server 2017 and both the dev and shadow databases are set to compatibility level 140. Here is an example of some valid SQL 2017 syntax that does not build:

DECLARE @json NVARCHAR(4000) = N'{ "path": { "to":{ "sub-object":["en-GB", "en-UK","de-AT","es-AR","sr-Cyrl"] } } }';
DECLARE @path NVARCHAR(50) = '$.path.to."sub-object"'

SELECT [key], value FROM OPENJSON(@json,@path)

The error says "Incorrect syntax near @path". I have tried other functions that were introduced with SQL 2017 (such as STRING_AGG) and they build with no issues. According to the MS documentation for OPENJSON, you can provide a variable for the path option starting with SQL 2017:

https://docs.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql?view=sql-server-2017

Is this a bug with ReadyRoll?

Russell D

I suspect this is a problem with the Compare Engine rather than ReadyRoll itself; can you share your deployment script for testing?

chris1411

The deployment script just had a stored proc with code similar to what I provided above. Just throw those lines into a script and try to build and you'll see what I mean.

Russell D

Yes, I get a successful build. What version are you using?

chris1411

1.17.18155.10346