Forum Discussion
Bug: "Invalid column name" after sp_recompile for valid column in custom data type
Hello, I was hoping to find a workaround for an issue I am having. I believe this is a bug in SQL Server (we are using SQL Server 2022)
I have written a utility stored procedure for solving the gaps and islands problem, utility.GetTimeIslands, which returns the longest contiguous islands from the time islands passed into it. This procedure utilizes a custom datatype, utility.TimeIslandList, which is defined as
CREATE TYPE utility.TimeIslandList AS TABLE (
ISLAND_ID BIGINT NOT NULL,
KEY_1 BIGINT NOT NULL,
KEY_2 BIGINT NOT NULL,
KEY_3 BIGINT NOT NULL,
START_DATE DATE NOT NULL,
END_DATE DATE NOT NULL,
PRIMARY KEY (ISLAND_ID)
);
The call to utility.GetTimeIslands in PRC.LoadDeals is
DECLARE Til utility.TimeIslandList;
INSERT INTO Til SELECT STG_DEAL_SCHEDULE_ID, DEAL_ID, PRICE_ACCOUNT_SET_ID, PRICE_ITEM_ID, START_DATE, END_DATE FROM #DealSchedulePrep;
INSERT #LatestTimeIslands EXEC utility.GetTimeIslands Til;
If PRC.LoadDeals is called after a server reboot, or after
EXEC sp_recompile 'utility.GetTimeIslands';
Is called, it throws the error
"Msg 207, Level 16, State 1, Procedure utility.GetTimeIslands, Line 60 [Batch Start Line 8]
Invalid column name 'KEY_3'."
Line 60 corresponds to the first statement in utility.GetTimeIslands where the custom data type is referenced
WITH ChangePoints AS (
SELECT KEY_1, KEY_2, KEY_3, START_DATE as X_DATE FROM Til
UNION SELECT KEY_1, KEY_2, KEY_3, DATEADD(DD, CASE END_DATE WHEN '9999-12-31' THEN 0 ELSE 1 END, END_DATE) FROM Til
)
...
However, if
DECLARE Til utility.TimeIslandList;
EXEC utility.GetTimeIslands Til;
Is called prior to calling PRC.LoadDeals, then PRC.LoadDeals does not throw the error. And PRC.LoadDeals will execute successfully until utility.GetTimeIslands is recompiled.
Another interesting twist is that adding those lines to PRC.LoadDeals, prior to the actual call, does not help. It throws the same error on the empty call, if the empty call is made inside the stored procedure before the empty call is made outside the stored procedure. For that matter, once any call is made to utility.GetTimeIslands outside the stored procedure, calls made to it inside the stored procedure will work.
Has anyone ever seen anything like this? If so, is there a solution outside of making the GetTimeIslands empty call after each server restart? Does anyone have a guess as to why the error message calls out KEY_3 specifically?
1 Reply
- martin_smithCopper Contributor
Has anyone ever seen anything like this?
Yes. I've seen this sort of thing when there are #temp tables named the same in both parent and child scopes.
See this example DB Fiddle https://dbfiddle.uk/H05-MO65
Line numbers often just point to the statement that has the problem rather than the actual specific line so per the additional code you showed in the https://stackoverflow.com/q/79769659/73226 this statement is, in fact, a couple of CTEs followed by
INSERT INTO #Islands (KEY_1, KEY_2, KEY_3, START_DATE, END_DATE)
So you could definitely see this type of thing if the outer proc also has a temp table called #Islands but without the KEY_3 column