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)

Improve this question
10
  • What is your SQL Server version (SELECT @@VERSION;)?
    – Yitzhak Khabinsky
    Commented Feb 18, 2021 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
    Commented Feb 18, 2021 at 15:37
  • 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
    Commented Feb 18, 2021 at 15:39
  • 1
    If you are on the latest CU, file a bug on Azure
    – Charlieface
    Commented Feb 18, 2021 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
    Commented Feb 18, 2021 at 16:16

1 Answer 1

Reset to default
1

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)

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.