OPENJSON WITH issue

madnik7madnik7 Posts: 4 New member
edited January 25, 2017 2:54PM in SQL Source Control
the SourceControl can not retrieve a function that has "OPENJSON WITH" clause. It comms the change and then always throw an error.
The function can be compiled and run successfully by SQL server

I am using version: 5.4.0.3902

here is the code:
ALTER FUNCTION [dbo].[fn_ClubPermissions](@Context NVARCHAR(MAX), @ClubId INT)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @Clubpermissions NVARCHAR(MAX) = '';
SELECT @Clubpermissions = REPLACE(REPLACE(ClubPermission, '','')
FROM OPENJSON(@Context)
WITH (Permissions nvarchar(MAX) '$.User.Permissions' AS JSON )
CROSS APPLY OPENJSON (Permissions)
WITH ( ClubId int '$.ClubId' ,
ClubPermission NVARCHAR(MAX) '$.ClubPermission' as Json
)

RETURN @Clubpermissions
END

Comments

  • kwilliamskwilliams Posts: 12 New member
    I'm using version 5.4.0.3902 with TFS and am experiencing the same issue
    "After successfully checking in the view I recieve the error "There was an error parsing the file {0} at line {1}, column {2}, near the text."

    I receive this error
      with a view that uses CROSS APPLY OPENJSON(r.ApplicationData) WITH([KEY] VARCHAR(MAX) '$.KEY' ,CROSS APPLY OPENJSON(r.[ApplicationData])
    [list=2]with a SP that uses the exact same syntax inside a CTE[/list]

    The view and the stored proc work and have been deployed to SQL server. this is strictly an issue whereby SLQ SOURCE CONTROL blocks me from managing any other changes to the database.
  • I'm sorry that you're experiencing this problem. I've replicated the problem and it seems to be down to a bug in the SQL Compare engine's parser, which has since been fixed. As soon as SQL Source Control is released with the latest version of the engine, this problem should be resolved.

    In the mean time, you can work around this by telling SQL Source Control to not throw exceptions on parser errors for this database. If you go to the Setup tab in SQL Source Control and go down to the section labelled Options just for this database, you'll see a link named Comparison options. Click on this. In the comparison options window, find the option in the Behavior section named Throw exceptions on SQL parser errors and make sure this is unchecked. Then click the blue Save button to save the options.

    You'll probably want to turn this back on when the fix is released.
    Software Engineer
    Redgate Software
  • kwilliamskwilliams Posts: 12 New member
    Robert,
    changing the "Throw exceptions on SQL parser errors" to false does seem to have gotten me past the original error, but now I get "unexpected token: ["r",<1184>,line=8,col=23] [char=246]"

    the text of the view

    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO

    CREATE VIEW [dbo].[vw_DataByRequest]
    as
    SELECT [r].[EffectiveScreeningRequestID] ,
    LTRIM(RTRIM([j].[KEY])) [KEY] ,
    LTRIM(RTRIM([j].[VALUE])) [VALUE]
    FROM [dbo].[MyTable] [r]
    CROSS APPLY OPENJSON(r.[Data])
    WITH (
    [KEY] VARCHAR(MAX) '$.Key'
    , [VALUE] VARCHAR(MAX) '$.Value'
    ) j
    WHERE ISJSON(r.[Data])=1
    GO
  • I can replicate this problem when I use your script. Unfortunately it all seems parser related so I think you may need to wait for the next update of SQL Source Control with the latest SQL Compare engine. I'll let you know when the update gets released.

    In the mean time, you may want to filter out this view so that you can keep using SQL Source Control without the error occurring.

    Apologies.
    Software Engineer
    Redgate Software
  • Can you try the latest release of SQL Source Control? I think this should fix the problem.

    Here's a download link - download
    Software Engineer
    Redgate Software
  • kwilliamskwilliams Posts: 12 New member
    Robert;
    I installed that version, as soon as it was available, and returned the Comparison Options back to their defaults (re-enabled Throw Exceptions On SQL parser errors) .

    So far it does appear to have solved these issues.

    Thanks
Sign In or Register to comment.