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)
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.
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:
Code produced by compare engine:
SQL Error when executing that code: