Competition: What’s your favorite Redgate tool? Enter now.

SQL Compare SDK with SQL Server 2016 'OPENJSON' function

We are having trouble with our build automation using SQL Compare SDK.
We are using version 12.4 of SQL Compare SDK.
When trying to deploy a function which uses SQL Server 2016 code "OPENJSON", the SQL Compare Engine produces a script with a syntax error.
Can you help?

Function:
-- Date         Changes                                                                                             Person          Version     Meilenstein     Issue
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 23.09.2020   New Created                                                                                         N.Chautems      1.0.0.0     M107            IF-1381

ALTER FUNCTION [dbo].[fnGetMapMatching](
     @ROTrack      [Modify].ROTrack_TableType  READONLY
    ,@ServiceURL   NVARCHAR(MAX)
)
RETURNS
    @MapMatch TABLE (
        Status              NVARCHAR(MAX)   NULL
       ,Distance            FLOAT           NULL
       ,FullyMatched        BIT             NULL
       ,HasDiscontinuities  BIT             NULL
)
AS
BEGIN   
    DECLARE
        @Response              XML
       ,@Response_StatusCode   NVARCHAR(MAX)
       ,@Response_StatusNumber INT
       ,@Response_Body         NVARCHAR(MAX)
       ,@Parameters            NVARCHAR(MAX)
       ,@Headers               NVARCHAR(MAX) = '<Headers><Header Name=''Content-Type''>application/json</Header></Headers>';
 
    INSERT @MapMatch
        (Status,    Distance,   FullyMatched,   HasDiscontinuities)
    SELECT
        'NOK',      NULL,       NULL,           NULL;
 
    -- Convert table to JSON, if there are at least 3 gps positions
    IF(SELECT COUNT(*) FROM @ROTrack) > 2
    BEGIN
        SELECT @Parameters = (
        SELECT DISTINCT GeoX AS lat, GeoY AS lon
        FROM @ROTrack
        FOR JSON AUTO)
    END;
 
    IF @Parameters IS NOT NULL
    BEGIN
        -- Query the FIS API and get a response
        SET @ServiceURL = @ServiceURL + '/inner/map/map-matching';
        SET @Response = [dbo].[fnHttpRequest]('POST', @ServiceURL, @Parameters, @Headers, 300000, 1, 0);
 
        -- Extract status code and number of the response
        SET @Response_StatusCode = @response.value('Response[1]/StatusCode[1]', 'NVARCHAR(MAX)');
        SET @Response_StatusNumber = @response.value('Response[1]/StatusNumber[1]', 'INT');
 
        IF @Response_StatusNumber = 200
        BEGIN
            -- Extract just the body of the response (expecting JSON)
            SET @Response_Body = @response.value('Response[1]/Body[1]', 'NVARCHAR(MAX)');
 
            DELETE FROM @MapMatch;
            -- Parse the JSON into a tabular format
            INSERT @MapMatch
            --    (Status,                  Distance,  FullyMatched,       HasDiscontinuities)
            SELECT
                 @Response_StatusCode,    B.length,  B.fully_matched,    B.has_discontinuities
            FROM
                OPENJSON(@Response_Body) WITH
                    (
                        [shape]             NVARCHAR(MAX)
                       ,[raw_score]         NVARCHAR(MAX) 
                       ,[confidence_score]  NVARCHAR(MAX) 
                       ,[summary]           NVARCHAR(MAX) AS JSON
                    ) A
                OUTER APPLY OPENJSON(A.[summary]) WITH 
                    (
                        [length]                FLOAT
                       ,[fully_matched]         BIT
                       ,[has_discontinuities]   BIT
                    ) B;
        END
    END
    RETURN
END

Code produced by compare engine:
[...]
@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[fnGetMapMatching]'
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

 

-- Date         Changes                                                                                             Person          Version     Meilenstein     Issue
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 23.09.2020   New Created                                                                                         N.Chautems      1.0.0.0     M107            IF-1381

 

CREATE FUNCTION [dbo].[fnGetMapMatching](
     @ROTrack      [Modify].ROTrack_TableType  READONLY
    ,@ServiceURL   NVARCHAR(MAX)
)
RETURNS
    @MapMatch TABLE (
        Status              NVARCHAR(MAX)   NULL
       ,Distance            FLOAT           NULL
       ,FullyMatched        BIT             NULL
       ,HasDiscontinuities  BIT             NULL
)
AS
BEGIN   
    DECLARE
        @Response              XML
       ,@Response_StatusCode   NVARCHAR(MAX)
       ,@Response_StatusNumber INT
       ,@Response_Body         NVARCHAR(MAX)
       ,@Parameters            NVARCHAR(MAX)
       ,@Headers               NVARCHAR(MAX) = '<Headers><Header Name=''Content-Type''>application/json</Header></Headers>';
 
    INSERT @MapMatch
        (Status,    Distance,   FullyMatched,   HasDiscontinuities)
    SELECT
        'NOK',      NULL,       NULL,           NULL;
 
    -- Convert table to JSON, if there are at least 3 gps positions
    IF(SELECT COUNT(*) FROM @ROTrack) > 2
    BEGIN
        SELECT @Parameters = (
        SELECT DISTINCT GeoX AS lat, GeoY AS lon
        FROM @ROTrack
        FOR JSON AUTO)
    END;
 
    IF @Parameters IS NOT NULL
    BEGIN
        -- Query the FIS API and get a response
        SET @ServiceURL = @ServiceURL + '/inner/map/map-matching';
        SET @Response = [dbo].[fnHttpRequest]('POST', @ServiceURL, @Parameters, @Headers, 300000, 1, 0);
 
        -- Extract status code and number of the response
        SET @Response_StatusCode = @response.value('Response[1]/StatusCode[1]', 'NVARCHAR(MAX)');
        SET @Response_StatusNumber = @response.value('Response[1]/StatusNumber[1]', 'INT');
 
        IF @Response_StatusNumber = 200
        BEGIN
            -- Extract just the body of the response (expecting JSON)
            SET @Response_Body = @response.value('Response[1]/Body[1]', 'NVARCHAR(MAX)');
 
            DELETE FROM @MapMatch;
            -- Parse the JSON into a tabular format
            INSERT @MapMatch
                (Distance,  FullyMatched,       HasDiscontinuities)
            SELECT
                 B.length,  B.fully_matched,    B.has_discontinuities
            FROM
                OPENJSON(@Response_Body) WITH
                    (
                        [shape]             NVARCHAR(MAX)
                       ,[raw_score]         NVARCHAR(MAX) 
                       ,[confidence_score]  NVARCHAR(MAX) 
                       ,[summary]           NVARCHAR(MAX) AS JSON
                    ) A
                OUTER APPLY OPENJSON(A.[summary]) WITH 
                    (
                        [length]                FLOAT
                       ,[fully_matched]         BIT
                       ,[has_discontinuities]   BIT
                    ) B;
        END
    END
    
GO
@ERROR <> 0 SET NOEXEC ON
GO
[...]

SQL Error when executing that code:
System.Data.SqlClient.SqlException: Falsche Syntax in der Nähe von "END".
[exec] bei System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
[exec] bei System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
[exec] bei System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
[exec] bei System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
[exec] bei System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
[exec] bei System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
[exec] bei RedGate.Shared.SQL.ExecutionBlock.BlockExecutor.ExecuteBlock(IExecutionBlock block, IDbConnection connection)
[exec] bei RedGate.Shared.SQL.ExecutionBlock.BlockExecutor.ExecuteBlock(IExecutionBlock block, DBConnectionInformation connection)
[exec] bei SCM.Sql.Compare.CompareTask.Compare()
[exec] bei SCM.Sql.Compare.Program.Main(String[] args)
Tagged:

Answers

  • This looks like it was caused by lack of support for OPENJSON in that version of SQL Compare, this is now supported in newer versions of SQL Compare, however we the SQL Comparison SDK has been discontinued in 2017 so it is not possible to update.
    Depending on your use case you might want to consider using SQL Change Automation instead, either using the Powershell cmdlets or using sqlcompare.exe directly
    Another alternative is to use Microsoft's DacFx library which to the best of my knowledge is free to use.
    Sergio
    Product Support Engineer
    Redgate Software Ltd
    Please see our Help Center for detailed guides on how to use our tools
  • SwissMaWiSwissMaWi Posts: 3 New member
    Thanks @Sergio R for responding to this. We know we have to migrate to SQL Change Automation at soime point, but that means to completely redesign our entire build and more important deploy chain from scratch. For small company that is not a viable project during these difficult times. That is why I am coming up with a somewhat naive question:
    Could we for a time being replace the old RedGate.SQLCompare.Engine.dll from the SDK with the newer one from SQL Compare? We have licensed both products for many years now.
    If you could check with a developer if that would be possible, that would be amazing.
  • I am afraid that is not possible.
    Sergio
    Product Support Engineer
    Redgate Software Ltd
    Please see our Help Center for detailed guides on how to use our tools
  • SwissMaWiSwissMaWi Posts: 3 New member
    What exactly is not possible?
  • My reply was an answer to your question:

    Could we for a time being replace the old RedGate.SQLCompare.Engine.dll from the SDK with the newer one from SQL Compare? We have licensed both products for many years now.
    If you could check with a developer if that would be possible, that would be amazing.

    I am afraid that is not possible.
    Sergio
    Product Support Engineer
    Redgate Software Ltd
    Please see our Help Center for detailed guides on how to use our tools
Sign In or Register to comment.