2

I'm trying to run this query, but it fails it fails with The argument 2 of the "JSON_VALUE or JSON_QUERY" must be a string literal.

select  mt.pk

from    My_Table mt

where   json_value(mt.json_data, '$.group_id') != ''

As you can see, my parameter 2 is a string literal. The error is due a bug in SQL Server 2016, when forced parameterization is on for the database.

I have confirmed that forced parameterization is turned on in my database, using:

select name, is_parameterization_forced from sys.databases

I am generally aware that you can force simple parameterization by creating a plan guide, then using it for the query. However, reviewing this documentation, I think that may be too complicated for my actual situation. This is partly because in reality, these queries are be generated on the fly and it's not realistic to also generate plan guides for all of them.

Reviewing Query Hint docs, it says

The PARAMETERIZATION query hint can only be specified inside a plan guide. It cannot be specified directly within a query.

Upgrading to SQL Server 2017 is also not currently viable. Turning off forced parameterization for these databases is also not viable.

I am thinking that if I can set simple paramerization on for the query or session, that would be a workaround. Is such a technique possible? Is there any other workaround?

(PS: I realize that looking for solutions with simple parameterization is a little bit X/Y... any help is appreciated)

  • The only thing short of a plan guide is to try "Optimize for Ad Hoc Workloads" instead of forced parameterization. That will save your plan cache, give you better plans for expensive queries, but will not reduce compilations as much as forced parameterization. – David Browne - Microsoft Nov 2 '17 at 17:29
  • Another option might be to run the query from a different database (eg TempDb), referencing the tables with 3-part names or synonyms. Eg exec tempdb.sys.sp_executesql N'select * from TargetDB.dbo.t where json_value(doc, ''$.group_id'') = '''' ' – David Browne - Microsoft Nov 2 '17 at 17:32
  • Going cross database is clever... it does work, even though it's a massive hack. What's even more bizarre is that apparently that had a connection level side effect of some kind... I'm able to run the original query normally with json_value afterwards... but only in that tab of SSMS. Copy/paste the identical sql to another tab, errors per original description. Bizarre. But, it is a bug area after all. – Factor Mystic Nov 2 '17 at 21:07
2

So far I have collected the following workarounds:

  1. Wrap the json_value function in a dummy subquery. For example,

    select  mt.pk
    
    from    My_Table mt
    
    where   (select json_value(mt.json_data, '$.group_id')) != ''
    
  2. Per David's first comment, try "Optimize for Ad Hoc Workloads" instead of forced parameterization. I haven't tested this, but it's plausible since the problem is forced parameterization.

  3. Per David's second comment, execute the SQL statement dynamically in another database, and reference the original db with 3 part naming. I have confirmed that this works. For example,

    exec tempdb.sys.sp_executesql N'select * from TargetDB.dbo.t where json_value(doc, ''$.group_id'') = '''' '
    

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.