3

I have a Scalar-valued Function that I've been using for years in my database. Its always worked correctly. I recently moved the database to SQL Server 2019 and when I changed the compatibility level to SQL Server 2019 (150), my function blew up like nitroglycerin. Reverting the compatibility level back down to SQL Server 2017 (140) allowed things to function as normal.

This is the function:

CREATE FUNCTION [dbo].[Authentication_SHA1ToBase64](@source AS varchar(8000))
RETURNS varchar(8000)
BEGIN

    DECLARE
        @bin    varbinary(8000),
        @result varchar(8000)

    SET @bin = CONVERT(varbinary(8000), @source)

    RETURN CAST(N'' as xml).value('xs:base64Binary(xs:hexBinary(sql:variable("@bin")))', 'varchar(8000)')

END

At SQL Server 2019 (150) compatibility level, the following:

SELECT [dbo].[Authentication_SHA1ToBase64]('0x640AB2BAE07BEDC4C163F679A746F7AB7FB5D1FA')

Errors as follows:

Msg 596, Level 21, State 1, Line 0
Cannot continue the execution because the session is in the kill state.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

The odd thing is that if I just execute the same as a query:

DECLARE
    @bin    varbinary(8000)

SET @bin = CONVERT(varbinary(8000), '0x640AB2BAE07BEDC4C163F679A746F7AB7FB5D1FA')

SELECT [Result]=CAST(N'' as xml).value('xs:base64Binary(xs:hexBinary(sql:variable("@bin")))', 'varchar(8000)')

I get the expected result:

Result
--------------------------------------------------------
MHg2NDBBQjJCQUUwN0JFREM0QzE2M0Y2NzlBNzQ2RjdBQjdGQjVEMUZB

Maybe there's some internal async issue that's breaking the function? Anyone have an idea if there's a newer method in SQL Server 2019 to convert to base64?

UPDATE: @@VERSION is: Microsoft SQL Server 2019 (RTM-GDR) (KB4583458) - 15.0.2080.9 (X64) Nov 6 2020 16:50:01 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Datacenter 10.0 (Build 17763: ) (Hypervisor)

jscarle
  • 1,045
  • 9
  • 17
  • What is your SQL Server version (SELECT @@VERSION;)? – Yitzhak Khabinsky Feb 18 '21 at 15:33
  • An error of level 20 or 21 smells like a bug in SQL Server, specially if it fixes itself by changing the compatibility level. – Alejandro Feb 18 '21 at 15:37
  • The OP is *clearly* using SQL Server 2019, @YitzhakKhabinsky . – Thom A Feb 18 '21 at 15:37
  • I smell an inlining issue. If you turn off inlining, I bet this works as expected. – Thom A Feb 18 '21 at 15:38
  • Check the SQL Server error log for related messages. A dump suggests a bug. Make sure you are on the latest CU. – Dan Guzman Feb 18 '21 at 15:39
  • 1
    @Larnu, to help we need SQL Server full version, including its build. There were many bugs pertaining to the udfs in different 2019 builds. – Yitzhak Khabinsky Feb 18 '21 at 15:39
  • In response to the @@VERSION: Microsoft SQL Server 2019 (RTM-GDR) (KB4583458) - 15.0.2080.9 (X64) Nov 6 2020 16:50:01 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Datacenter 10.0 (Build 17763: ) (Hypervisor) – jscarle Feb 18 '21 at 15:59
  • 1
    If you are on the latest CU, file a bug on Azure – Charlieface Feb 18 '21 at 16:09
  • 1
    15.0.2080.9 is just the GDR update; so yes, you're 9 CU's behind (the latest version is 15.0.4102.2). As the others have suggested, I would suggest updating to the latest CU, as they won't address a fault on an old CU. – Thom A Feb 18 '21 at 16:16
  • I updated to CU9, modified compatibility back to 150, and it works! Microsoft SQL Server 2019 (RTM-CU9) (KB5000642) - 15.0.4102.2 (X64) Jan 25 2021 20:16:12 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Datacenter 10.0 (Build 17763: ) (Hypervisor) – jscarle Feb 18 '21 at 17:09

1 Answers1

0

I updated to CU9, modified compatibility back to 150, and it works! Microsoft SQL Server 2019 (RTM-CU9) (KB5000642) - 15.0.4102.2 (X64) Jan 25 2021 20:16:12 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Datacenter 10.0 (Build 17763: ) (Hypervisor)

jscarle
  • 1,045
  • 9
  • 17