You may find it hard to believe, but I recently had to fix a small bug in sp_BlitzCache and sp_BlitzQueryStore.
Since both stored procedures have similar functions, they also share some temp table names (mainly the ones for parsing down XML nodes to more manageable chunks).
In the window where I was making adjustments to sp_BlitzCache, I had created one of those temp tables. When I went to run sp_BlitzQueryStore, I was flooded with invalid column name errors. Same temp table name, different column names.
Now, I hadn’t made any changes to BlitzQueryStore yet, and it had been running without error as of the last FRK release.
When I ran sp_BlitzQueryStore from a different SSMS window, it ran fine.
Even though the proc explicitly checks for and drops my temp tables, it was reading from temp tables my session created with different column names.
So what gives?
Demonstrate My Syntax
You’ll need a couple SSMS windows for this.
Run this in one of them. Note that the column name in #t1 is t, here.
USE master GO CREATE OR ALTER PROCEDURE dbo.temp_maker AS BEGIN DROP TABLE IF EXISTS #t1; CREATE TABLE #t1 ( t INT ); INSERT #t1 ( t ) SELECT TOP 10000 x.r FROM (SELECT ROW_NUMBER() OVER (ORDER BY @@ROWCOUNT) AS r FROM sys.messages AS m) AS x SELECT t.t FROM #t1 AS t; END; GO
In window #2, run this. Note that the column name in #t1 is c, here.
USE master CREATE TABLE #t1 (c INT) INSERT #t1 ( c ) SELECT TOP 10000 x.r FROM (SELECT ROW_NUMBER() OVER (ORDER BY @@ROWCOUNT) AS r FROM sys.messages AS m) AS x EXEC dbo.temp_maker
You should get this error (sometimes you have to run the create table statement a couple times in a row, then the proc — I can’t nail it down exactly).
Msg 207, Level 16, State 1, Procedure temp_maker, Line 13 [Batch Start Line 9] Invalid column name ‘t’.
Msg 207, Level 16, State 1, Procedure temp_maker, Line 17 [Batch Start Line 9] Invalid column name ‘t’.
Weird! FWIW, it also throws the same error if I check for the OBJECT_ID of #t1 instead.
Another dumb creation
DROP TABLE IF EXISTS #dumb CREATE TABLE #dumb (Id INT) INSERT #dumb ( Id ) VALUES ( 0 ) DROP TABLE IF EXISTS #dumb CREATE TABLE #dumb (Id INT) INSERT #dumb (Id) VALUES ( 0 )
Strange newfound respect for tables named #dumb.
I’m sure there’s a fun explanation for this. Perhaps it’s something to do with deferred compilation, or the magic of temp tables in stored procedures.
This isn’t the most practically helpful post. It’s mostly so I remember to avoid doing this again in the future.
And who knows, maybe it’ll save you some time, too.
Thanks for reading!