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?SSMS Bug: stuck SAS tokens
I'm not sure where to post SSMS bugs anymore - or even look to see if it's a preexisting bug, but I've just found one that cost me several hours: when generating a SAS token from SSMS's object explorer to restore from a backup on blob storage, SSMS does not prompt you to overwrite a pre-existing expired token. As a result, the "generated" token is not saved as a credential and the restore continually fails because it still tries to use the "stuck" expired one. The only way to get SSMS to actually use the new token is to manually drop the old one first by running the following sql: DROP CREDENTIAL [https://<credential name>] where <credential name> is typically the URL to the blob storage container, and can be found by running SELECT name from sys.credentials Once the stuck token is manually dropped, the user can then go create a new one through object explorer and the restore will actually use it and succeed. This is a bug because the object explorer makes no attempt to save the generated SAS token as a credential and doesn't notify the user that the token they just generated will not be used. I believe SSMS should either prompt the user to overwrite the pre-existing credential ("You already have a credential with that name. Would you like to overwrite it with this one?", or at least notify the user that their token cannot be used until they manually drop the expired one.