OPENJSON WITH issue
madnik7
Posts: 4 New member
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
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
"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.
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.
Redgate Software
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
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.
Redgate Software
Here's a download link - download
Redgate Software
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