SQL Compare SDK with SQL Server 2016 'OPENJSON' function

SwissMaWi

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)

Sergio R

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.

SwissMaWi

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.

Sergio R

I am afraid that is not possible.

SwissMaWi

What exactly is not possible?

Sergio R

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.