SqlServer 2019 using table and scalar function into cursor

0
votes

I occurred a strange error:

Msg 596, Level 21, State 1, Line 0

Cannot continue the execution because the session is in the kill state.

It seems that error is generated when I use a cursor like :

DECLARE dbCursor CURSOR
            FOR 
                SELECT (SELECT TOP 1 VAL FROM dbo.fn_Table('n:',dbo.[fn_Scalar](var)) ORDER BY VAL) 
                from table

If I taken the select to run it, it is ok.

If in cursor I execute only fn_Table OR fn_Scalar, it is ok.

If in cursor I execute BOTH functions, I have the above error.

In older versions of SQL server (than 2019) the cursor is running properly.

Could you, please, give me an idea?

Thank you.

L.E. The functions are: ALTER function [dbo].[UrlDecode](@url nvarchar(max)) returns nvarchar(max) as begin declare @foo datetime=getdate();

DECLARE @Position INT, @Base CHAR(16), @High TINYINT, @Low TINYINT, @Pattern CHAR(21)

SELECT  @Base = '0123456789abcdef',
    @Pattern = '%[%][0-9a-f][0-9a-f]%',
    @URL = REPLACE(@URL, '+', ' '),
    @Position = PATINDEX(@Pattern, @URL)

WHILE @Position > 0
    SELECT  @High = CHARINDEX(SUBSTRING(@URL, @Position + 1, 1), @Base COLLATE Latin1_General_CI_AS),
        @Low = CHARINDEX(SUBSTRING(@URL, @Position + 2, 1), @Base COLLATE Latin1_General_CI_AS),
        @URL = STUFF(@URL, @Position, 3, CHAR(16 * @High + @Low - 17)),
        @Position = PATINDEX(@Pattern, @URL)

RETURN  @URL

end

ALTER FUNCTION [dbo].[Split] (@sep VARCHAR(32), @s VARCHAR(MAX))

RETURNS @t TABLE ( val VARCHAR(MAX) )

AS BEGIN

--declare @foo datetime=getdate();
--Remove separator if the case
IF RIGHT(LTRIM(RTRIM(@s)),1) = @sep 
    SET @s = LEFT(LTRIM(RTRIM(@s)), LEN(LTRIM(RTRIM(@s))) - 1);

DECLARE @xml XML
SET @XML = N'<root><r>' + REPLACE(@s, @sep, '</r><r>') + '</r></root>'

INSERT INTO @t(val)
SELECT r.value('.','VARCHAR(250)') as Item
FROM @xml.nodes('//root/r') AS RECORDS(r)

RETURN

END

And the cursor is: DECLARE cursor_product CURSOR FOR select (SELECT TOP 1 VAL FROM dbo.split('n:',dbo.UrlDecode) ORDER BY VAL) from ABSENTEE

OPEN cursor_product;

FETCH NEXT FROM cursor_product WHILE @@FETCH_STATUS = 0 BEGIN

    FETCH NEXT FROM cursor_product
END;

CLOSE cursor_product; DEALLOCATE cursor_product;

Please, ignore how is defined the cursor, the error occours, anyway. Thank you.

2
sqlsql-serverfunctioncursorscalar
add a foo statement anywhere in fn_Scalar() : declare @foo datetime=getdate(); execute your statement. If it works, read about scalar udf inlining in sql2019 – lptr
Sorry, I don't understand the suggestion. As I said, the functions are working if are not used together into a cursor. – Marina Gratiela Stan
Do you have related messages in the SQL Server error log, such as a dump? If so, apply the latest CU if you haven't already done so. – Dan Guzman
I have the files, and I don't understand nothing from them. I installed the last updates from sql server. – Marina Gratiela Stan
Have you tried lptr suggestion ?, he told you to add a "declare @foo datetime=getdate()", to your function.He explained to you that the reason was because the scalar udf inlining added in SQL Server 2019, and to search for more information about it. This feature is an optimization of the execution of the function, if you add a variable declaration, you are disabling this new optimization, so it will be executed unoptimized, as they were executed on SQL Servers prior 2019. – Marc Guillot

2 Answers

0
votes

If the select alone runs well, I propose you to retrieve your data first into a table variable, so you can iterate it later, once the calls to your function are finished, avoiding any kind of weird locking between those calls.

DECLARE @MyData table (VAL int)

INSERT INTO @MyData (VAL)
       SELECT (SELECT TOP 1 VAL FROM dbo.fn_Table('n:',dbo.[fn_Scalar](var)) ORDER BY VAL) 
       FROM table

DECLARE dbCursor CURSOR FOR SELECT VAL FROM @MyData
0
votes

As it stands, you'll have to change the cursor's select statement (scalar udf-inlining was just a blind guess [function+sql2019 not working, but works pre-sql2019] , the problem is the cursor's execution) :

Option 1: 

The answer of Marc Guillot (populate a table and iterate over that)

Option 2: APPLY( function calls)

DECLARE cursor_product CURSOR FOR 
select f.VAL
from ABSENTEE as a
cross /*outer ?*/ apply
(
    SELECT TOP 1 VAL 
    FROM dbo.split('n:',dbo.UrlDecode(a.columnXYZ)) 
    ORDER BY VAL
) as f;

Option 3: aggregation on ABSENTEE table

DECLARE cursor_product CURSOR FOR 
select (SELECT TOP 1 VAL FROM dbo.split('n:',dbo.UrlDecode(columnXYZ)) ORDER BY VAL) 
from ABSENTEE
group by ABSENTEE.columnXYZ, ABSENTEE.othercolumn(s)

Option 4: windowed aggregation on the function's result

DECLARE cursor_product CURSOR FOR 
select 
    MIN(
          (SELECT TOP 1 VAL FROM dbo.split('n:',dbo.UrlDecode(columnXYZ)) ORDER BY VAL) 
       ) over(partition by columnXYZ)
from ABSENTEE