SQL Server 2019 CU3 CE 150 Invalid object name 'days'
We get following error after testing our code on SQL Server 2019 CU3 in compatibility level 150. Error doesn't happen in 140.
Msg 208, Level 16, State 1, Procedure udf_GetTicketDurationDays, Line 29 [Batch Start Line 0]
Invalid object name 'days'.
days is a CTE.
Compared to other error that I submitted this one doesn't happen always. I get it once when running and then running it again it works fine. Then same thing will happen in other area of the application with another function that's doing same thing with CTE inside a function.
Hope you can replicate it as well at least on the first try as we have this happening in more than one area but only happens once then I can't get it to happen.
Both altering function or running sys.sp_refreshsqlmodule against it fixed the issue.
I do have 2 concerns for the future CUs and how they will do this automatically.
1. We have over 350 primary databases per server. Does that mean that during patching we should expect CUs to take longer as they need to loop through all those functions and update them. In our case it's about 350 functions that are seen as inlineable. Took about 3 seconds to alter 350 udfs x 350 is about 17.5 minutes.
2. If function is used in computed column that it will cause an error trying to alter it so that could fail the patch?
Msg 3729, Level 16, State 3, Procedure sys.sp_refreshsqlmodule_internal, Line 85 [Batch Start Line 0]
Cannot ALTER 'dbo.udf_CalculateCalendarMinutes' because it is being referenced by object 'Office_Calendar_Working_Days'.
Thanks for reporting.
Could you please try the following and see if this resolves your issue?
Note: for a future CU, this refresh will be automatic. Today the process is manual after inlineability has first been determined.
After upgrading to SQL Server 2019 CU3, to re-evaluate if a UDF is eligible for inlining, refresh eligible scalar UDFs through either one of the following methods:
1) Execute sp_refreshsqlmodule for applicable scalar UDFs
2) Alter or recreate the existing scalar UDF with existing definition, permissions and set properties (see ALTER FUNCTION (Transact-SQL))
The following script generates a script to refresh metadata for existing inlined scalar UDFs:
Generates a script that can be used to refresh all active inlineable Scalar UDFs.
Note: sp_refreshsqlmodule does not affect any permissions, extended properties,
or SET options that are associated with the object.
SELECT 'EXECUTE sys.sp_refreshsqlmodule ''[' + OBJECT_SCHEMA_NAME(object_id) + +'].' + '[' + OBJECT_NAME(object_id)
+ ']'';' AS 'RefreshStatement'
WHERE is_inlineable = 1
AND inline_type = 1;
So after more testing issue seems to be with inlining. Right now we have current database and have moved compatibility level to 150 and started getting this error. What I found out is that SQL sees this function as inlineable but after altering it and not changing anything, it changes and doesn't see it as inlineable anymore. Also when creating new function with 2 on the end it doesn't see it as inlineable which is why you can't reproduce it unless you start with the backup.
Vladimir, thanks for you quick feedback. Will try that and also see if I can get it to happen more frequent and will provide an update.
Vladimir Moldovanenko commented
Elvis, I tried to reproduce it on SQL Server 2019 (RTM-CU3), using tempdb, comp 150, but it works for me. 'days' is reserved word, does it work(fail) if you change it to something else than 'days'?