Version of the script

Version 8.28, dated 20251124 (November 24, 2025)

What is the current behavior?

When running sp_BlitzCache with @AI = 2 (or @AI = 1), the procedure works correctly initially after running the creation script. However, after running it several times, it fails with:

Msg 207, Level 16, State 1, Procedure sp_BlitzCache, Line 4871 [Batch Start Line 0]
Invalid column name 'ai_prompt'.

The failure occurs at line 5115 during the "Building AI prompts for query plans" phase when attempting to UPDATE the ai_prompt column in ##BlitzCacheProcs.

Re-running the creation script temporarily fixes the issue until it fails again.

If the current behavior is a bug, please provide the steps to reproduce.

1. Run the sp_BlitzCache creation script against an Azure SQL Database
2. Execute: EXEC sp_BlitzCache @SortOrder = 'query hash', @AI = 2;
3. Verify it works successfully
4. Allow the ##BlitzCacheProcs global temp table to be dropped (e.g., close connection, restart session, or manually DROP TABLE ##BlitzCacheProcs)
5. Execute the same command again: EXEC sp_BlitzCache @SortOrder = 'query hash', @AI = 2;
6. Observe the "Invalid column name 'ai_prompt'" error

What is the expected behavior?

The @AI parameter should work consistently regardless of whether the ##BlitzCacheProcs table is being reused or recreated.

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?

Confirmed on Azure SQL Database. The issue is likely to affect all versions of SQL Server that support the AI features, as it's a table definition problem.

The root cause is a mismatch between two CREATE TABLE ##BlitzCacheProcs statements:

• Line 49-242 (outside procedure): Includes ai_prompt, ai_advice, ai_payload, ai_raw_response columns (lines 238-241)
• Line 2659-2845 (inside procedure): Missing these four AI-related columns, ending with only Pattern NVARCHAR(20) at line 2844

Suggested Fix:

Add the missing columns after line 2843 in the internal table creation:

        Pattern NVARCHAR(20),
        ai_prompt NVARCHAR(MAX),
        ai_advice NVARCHAR(MAX),
        ai_payload NVARCHAR(MAX),
        ai_raw_response NVARCHAR(MAX)
    );

This will make both table definitions consistent and ensure the AI features work regardless of when the table is created.