Блог

У меня проблема, которая работает в SQL Server 2017, но не в SQL Server 2019. Это связано с кэшированием базы данных tempdb. Это связано с созданием временных таблиц в хранимых процедурах и изменением их структуры с использованием динамического SQL. Нам необходимо сделать это для различных нужд динамической отчетности. При первом вызове структура кэшируется, а последующий вызов процедуры завершается неудачно или возвращает недопустимые результаты. Как мне предотвратить кэширование таких таблиц? Ниже приведен пример кода и показано, как он работает в 2017 году. Помощь приветствуется.

 CREATE PROCEDURE [dbo].[tempDBCachingCheck]
    @yearList varchar(max)
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @yearCount int 
    DECLARE @yearCounter INT 
    DECLARE @yearValue INT 

    DECLARE @sql nvarchar(max) 

    -- With table variable 
    DECLARE @tempYearList TABLE (id INT IDENTITY(1,1), rpt_yr int)
    INSERT INTO @tempYearList (rpt_yr)

    SELECT value FROM STRING_SPLIT(@yearList, ',');                                                                                                                                                                                                                    
                                                               
    SELECT * FROM @tempYearList

    --------------------------------------------------------------------
    --With temporary table, since we will be altering this with dynamic sql 
    CREATE TABLE #returnTable (id INT IDENTITY(1,1))
    
    -- Tried adding a named constraint to not make it cache, but does not work 
    ALTER TABLE #returnTable
    ADD CONSTRAINT UC_ID UNIQUE (id);
        
    SELECT @yearCount = COUNT(*) FROM @tempYearList 
        
    -- Add the years as columns to the return table to demostrate the problem 
    SET @sql = N'ALTER TABLE #returnTable ADD ' 
    SET @yearCounter = 1

    WHILE @yearCounter <= @yearCount 
    BEGIN   
        SELECT @yearValue = rpt_yr FROM @tempYearList WHERE id = @yearCounter

        IF @yearCounter > 1
            SET @Sql = @Sql   N', '

        SET @sql = @sql   N' ['   convert(varchar(20), @yearValue)   N'] float' 
        SET @yearCounter = @yearCounter   1
    END

    EXECUTE sp_executesql @sql 
    
    SELECT * FROM #returnTable

    -- No need to drop the temporary tables but doing just in case 
    DROP TABLE #returnTable 
END 
GO                               

-- run these statements and you will see the second call with return the cached #returnTable
EXEC tempDBCachingCheck '2019,2020' 
EXEC tempDBCachingCheck '2017,2018,2019,2020'
GO 

-- Clear temp table cache and call in reverse order, then will hit an error 
-- 'A severe error occurred on the current command. The results, if any, should be discarded.' 
USE tempDB
GO 
DBCC FREEPROCCACHE
GO 

EXEC tempDBCachingCheck '2017,2018,2019,2020'
EXEC tempDBCachingCheck '2019,2020'
GO 
  

Кажется, это было исправлено в одном из накопительных обновлений. Описание, похоже, соответствует:

KB4538853:

При повторном запуске хранимой процедуры, которая использует временную таблицу с индексами на SQL Server 2019, клиент может получить неожиданную ошибку с сообщением «Произошла серьезная ошибка в текущей команде», и на SQL Server записывается исключение нарушения доступа. Если такая же рабочая нагрузка выполняется в любой предыдущей основной версии SQL Server, эта проблема не возникает.

Рекомендация Дэна Гусмана установить новейший CU — это правильный путь.

Использование: EXEC tempDBCachingCheck '2017,2018,2019,2020' WITH RECOMPILE также может помочь.