August 28, 2019 at 1:10 pm
Strange behaviour in my opinion, or did I miss something in documentation?
Can anyone explain this behaviour?
-- This works fine and gives the expected result:
SELECT JSON_VALUE( '{"id":"1","name":"me"}' , '$.id' )
-- This results in error message: The argument 2 of the "JSON_VALUE or JSON_QUERY" must be a string literal.
SELECT 1 WHERE JSON_VALUE( '{"id":"1","name":"me"}' , '$.id' ) = '1'
"Walking on water and developing software from a specification are easy if both are frozen." -- Edward V. Berard, "Life-Cycle Approaches"
August 28, 2019 at 4:24 pm
Interesting. I just did a direct copy/paste to my SQL Server 2017 instance and both worked with no error.
August 28, 2019 at 4:42 pm
Worked on my SQL 2016 too.
Just to be safe, add the N in front of the second literal:
SELECT 1 WHERE JSON_VALUE( '{"id":"1","name":"me"}' , N'$.id' ) = '1'
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 28, 2019 at 5:34 pm
Thanks Lynn & Scott for your answers!
I got a SQL Server 2017 box installed: and indeed on that one it works like I expected.
On the SQL Server 2016 box I tried Scott's suggestion, no such luck ... but it all depends in which database context I try it!
It does work on master, msdb & tempdb; but it gives me an error on model.
They all have the same collation '=Latin1_General_CI_AS) and compatibility-level (=130).
So, why does it work in three of them and in one not ???
Got an itchy feeling it might by a bug.
"Walking on water and developing software from a specification are easy if both are frozen." -- Edward V. Berard, "Life-Cycle Approaches"
August 28, 2019 at 9:15 pm
Check the compatibility level of the databases where you are having an issue. It may not be set to SQL Server 2016 or newer.
August 29, 2019 at 6:23 am
As I mentioned in my previous post: they all have the same compatibility level : 130 (SQL Server 2016).
BUT ... I kept digging and looking for differences between database settings. And I found "the" setting that caused the error. And it does seems a bug to me, though it may seem a bit presumptuous to say so.
It's all about the "PARAMETERIZATION" setting:
-- SET PARAMETERIZATION TO SIMPLE : NO ERROR
USE [master]
GO
ALTER DATABASE [JSON_VALUE_TEST] SET PARAMETERIZATION SIMPLE WITH NO_WAIT
GO
USE [JSON_VALUE_TEST]
GO
SELECT 1 WHERE JSON_VALUE( '{"id":"1","name":"me"}' , N'$.id' ) = '1'
-- SET PARAMETERIZATION TO FORCED : ERROR
USE [master]
GO
ALTER DATABASE [JSON_VALUE_TEST] SET PARAMETERIZATION FORCED WITH NO_WAIT
GO
USE [JSON_VALUE_TEST]
go
SELECT 1 WHERE JSON_VALUE( '{"id":"1","name":"me"}' , N'$.id' ) = '1'
Also tested it on SQL'2017: no problem there.
But migrating/upgrading is not yet an option.
"Walking on water and developing software from a specification are easy if both are frozen." -- Edward V. Berard, "Life-Cycle Approaches"
August 29, 2019 at 7:22 am
The different behaviours are documented here
https://docs.microsoft.com/en-us/sql/t-sql/functions/json-value-transact-sql?view=sql-server-2017
August 29, 2019 at 7:25 am
Thanks for your input.
Did read it, but it does not address the parameterization setting causing different behaviour.
And why it does work on a SQL Server 2017 and not on a SQL Server 2016
"Walking on water and developing software from a specification are easy if both are frozen." -- Edward V. Berard, "Life-Cycle Approaches"
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply