7

We have some SQL code that has been in production for some time, in a SQL Server 2016 database; but it is raising an error in a SQL Server 2019 database for the first hour or so after restarting SQL Server (anywhere from 5-10 minutes to an hour or more, probably depending on the level of activity in SQL Server). The error is "invalid object name" for a CTE (Common Table Expression).

We have a production environment with several databases in SQL Server 2016. We have now set up a new development/test environment with SQL Server 2019 (on a Windows Server 2016 machine, with 24GB of RAM and 4 CPU cores) so that we can test with SQL Server 2019. The databases on this test server are restored copies of the production databases from production backups. All the databases in the test environment have the compatibility level set to 150 (SQL Server 2019).

Early each morning, we started to see some issues with a couple of functions that use CTEs, where the function would raise errors like this:

SqlException (0x80131904): Invalid object name 'CTEuniqueName'.]

Msg 208, Level 16, State 1, Procedure ufn_FunctionName, Line 28 [Batch Start Line 0]
Invalid object name 'CTEuniqueName'.

The errors stopped happening after a short period of time and didn't happen again until the next morning.

The error was happening in a pair of stored procedures that were called one after the other, both of which called the same (user-defined SQL) function. With some testing, I learned that I could sometimes cause the same error by just calling the function, and then by just executing a block of code from the function.

I also discovered that I could consistently cause the error by restarting the SQL Server instance and calling the function or the code block. This is probably also why it was only failing early in the morning - there had been no activity on the SQL Server instance for several hours before that, so it had gone into idle mode or shut down its processes.

After repeatedly calling the function or code, at some point it would succeed without raising the error, and after that, it seemed to keep working (until I restarted the SQL Server instance again).

If I change the database that contains this function to "SQL Server 2016" compatibility mode, then the function always succeeds, even immediately after restarting the SQL Server instance. So, it seems to be an issue specific to SQL Server 2019.

The code in the function looks like the following (with anonymized names), and the function contains 2 separate "IF" blocks similar to the following; however I can cause the error with just the following block of code. The 2 views in this code are views to other databases in the same SQL Server instance.

DECLARE @MyID INT = 150589;
DECLARE @MyType VARCHAR(25) = 'Test';
DECLARE @ExpirationDate DATE;
BEGIN
 IF @MyType = 'Test'
     BEGIN
         DECLARE @Test1 INT;
         WITH CTEuniqueName(PersonID, DateComplete)
              AS (SELECT T1.PersonID, MAX(T2.DateComplete) AS DateComplete
                  FROM dbo.TABLE1 AS T1 WITH(NOLOCK)
                       LEFT JOIN dbo.VIEW2 AS T2 WITH(NOLOCK) ON T2.ID = T1.ID
                  WHERE T1.ID = @MyID
                        AND T2.SecondID IN(SELECT SecondID
                                        FROM dbo.TABLE3 WITH(NOLOCK)
                                        WHERE Name = 'TEST')
                  GROUP BY T1.PersonID)
              SELECT @ExpirationDate = CASE
                                           WHEN V3.TimeFrame > 0 THEN DATEADD(DAY, TimeFrame, CONVERT(DATE, CTE1.DateComplete))
                                           ELSE NULL
                                       END
              FROM CTEuniqueName AS CTE1
                   INNER JOIN dbo.VIEW2 AS V2 WITH(NOLOCK) ON V2.ID = CTE1.ID
                                                               AND V2.DateComplete = CTE1.DateComplete
                   INNER JOIN dbo.VIEW3 AS V3 WITH(NOLOCK) ON V3.QuizID = V2.QuizID
              WHERE TS.SecondID IN(SELECT SecondID
                                 FROM dbo.TABLE3 WITH(NOLOCK)
                                 WHERE Name = 'TEST');
     END;
END;

Also, in the time period after startup that the error is occurring, if I run the 2 stored procedures (that call the above function, and both procedures return a result set) in an SSMS window, then the procedures successfully return their respective result sets in the SSMS window and then SSMS switches to the Messages panel and displays the "invalid object name" error.

Does anyone know why this might be intermittently failing only in SQL Server 2019 (and seemingly only after SQL Server startup or a period of idel time)? Or, can anyone suggest how to troubleshoot issues like this? I have tried checking the SQL error log; and I have also tried running the Profiler with function calls included - neither of these has provided any clues.

Additional information:

  • Running DBCC FREESYSTEMCACHE(N'ALL'); does cause the same error to temporarily occur again (in the same way that restarting the server does)
  • The function is not inlineable (due to the CTE), so it doesn't seem like scalar UDF inlining is the cause of the problem
  • There are no errors in the SQL Server error log when this occurs
  • I tried to reproduce the problem using newly created databases on the same instance, with just the tables required by the function, but the functions always complete successfully in the new database
3
  • 6
    If you're able to reproduce this issue each time after a reboot of the server, I'd suggest opening a ticket with Microsoft support on it. Kind of sounds like you have found a possible bug. – Taryn Jan 7 '20 at 14:01
  • Can you make a minimal reproducible example and reproduce the problem via a dbfiddle at all? – John Eisbrener Jan 7 '20 at 14:32
  • I don't have an answer, but I am abandoning/closing this question. I can only reproduce this issue in the full database running in SQL Server 2019. I have been unable to cause the same issue in any scaled-down test database with similar structures, same data in the relevant tables, and same procedures and functions. I also recently installed the CU1 update for SQL Server 2019 on the development database server, but the issue still occurs after that update. I plan to replace this specific CTE with an alternative approach, probably a table variable in the stored procedure - to avoid the problem – PhilipD Jan 29 '20 at 19:21
4

Initially, it seems like this was a bug related to the new Scalar UDF Inlining feature added in SQL Server 2019, since you mentioned that disabling inlining resolved the problem. On further inspection, the function cannot be inlined due to the presence of a CTE in the function definition.

Here's my (failed) attempt to reproduce the issue:

USE [master];
GO
DROP DATABASE IF EXISTS [256861OtherDatabase];
GO
CREATE DATABASE [256861OtherDatabase];
GO
USE [256861OtherDatabase];
GO

CREATE TABLE dbo.TableForView2
(
    ID int IDENTITY(1,1) NOT NULL,
    DateComplete datetime NOT NULL,
    SecondID int NOT NULL,
    QuizID int NOT NULL,
    CONSTRAINT PK_TableForView2 PRIMARY KEY (Id)
);
GO

CREATE TABLE dbo.TableForView3
(
    ID int IDENTITY(1,1) NOT NULL,
    Timeframe int NOT NULL,
    QuizID int NOT NULL,
    CONSTRAINT PK_TableForView3 PRIMARY KEY (Id)
);
GO

USE [master];
GO
DROP DATABASE IF EXISTS [256861];
GO
CREATE DATABASE [256861];
GO
USE [256861];
GO

CREATE TABLE dbo.TABLE1
(
    ID int IDENTITY(1,1) NOT NULL,
    PersonID int NOT NULL,
    CONSTRAINT PK_TABLE1 PRIMARY KEY (Id)
);
GO

CREATE VIEW dbo.View2
AS
SELECT
    ID,
    DateComplete,
    SecondID,
    QuizID
FROM [256861OtherDatabase].dbo.TableForView2 d
GO

CREATE TABLE dbo.TABLE3
(
    ID int IDENTITY(1,1) NOT NULL,
    SecondID int NOT NULL,
    [Name] varchar(50) NOT NULL,
    CONSTRAINT PK_TABLE3 PRIMARY KEY (Id)
);
GO

CREATE VIEW dbo.View3
AS
SELECT
    ID,
    Timeframe,
    QuizID
FROM [256861OtherDatabase].dbo.TableForView3 d
GO

CREATE FUNCTION dbo.TestFunction 
(
    @MyID INT = 150589, 
    @MyType VARCHAR(25) = 'Test'
)
RETURNS date
AS
BEGIN;
DECLARE @ExpirationDate DATE;
 IF @MyType = 'Test'
     BEGIN
         DECLARE @Test1 INT;
         WITH CTEuniqueName(ID, DateComplete)
              AS (SELECT T1.PersonID, MAX(T2.DateComplete) AS DateComplete
                  FROM dbo.TABLE1 AS T1 WITH(NOLOCK)
                       LEFT JOIN dbo.VIEW2 AS T2 WITH(NOLOCK) ON T2.ID = T1.ID
                  WHERE T1.ID = @MyID
                        AND T2.SecondID IN(SELECT SecondID
                                        FROM dbo.TABLE3 WITH(NOLOCK)
                                        WHERE Name = 'TEST')
                  GROUP BY T1.PersonID)
              SELECT @ExpirationDate = CASE
                                           WHEN V3.TimeFrame > 0 THEN DATEADD(DAY, TimeFrame, CONVERT(DATE, CTE1.DateComplete))
                                           ELSE NULL
                                       END
              FROM CTEuniqueName AS CTE1
                   INNER JOIN dbo.VIEW2 AS V2 WITH(NOLOCK) ON V2.ID = CTE1.ID
                                                               AND V2.DateComplete = CTE1.DateComplete
                   INNER JOIN dbo.VIEW3 AS V3 WITH(NOLOCK) ON V3.QuizID = V2.QuizID
              WHERE V2.SecondID IN(SELECT SecondID
                                 FROM dbo.TABLE3 WITH(NOLOCK)
                                 WHERE Name = 'TEST');
     END;
     RETURN @ExpirationDate;
END;
GO

I noticed that the function is marked as is_inlineable = 0 in sys.sql_modules, so I ran the function creation code again with the following Extended Events session running:

CREATE EVENT SESSION [inlining] ON SERVER 
ADD EVENT sqlserver.tsql_scalar_udf_not_inlineable
ADD TARGET package0.event_file(SET filename=N'inlining')
WITH (STARTUP_STATE=OFF)
GO

It produced 1 event with a blocked_reason of "CTE," which lines up with the Inlineable scalar UDFs requirements (that were updated to include CTEs after this question was initially posted).

This still seems like buggy behavior to me. The only suggestion I have, really, is to rewrite the function to avoid the CTE. This might work around the problem, and also could allow the function to be inlined.

4
  • I replaced the development databases with backup copies from production again today, and now the problem occurs again, but only 1 or 2 times and then it starts working correctly. If I restart SQL Server, the error occurs again 1 or 2 times before starting to work. The error is still "Invalid object name 'CTEuniqueName'" (at the "FROM CTEuniqueName" line in the function). – PhilipD Jan 12 '20 at 17:55
  • I have also discovered just now that if there is no activity in SQL Server (this is Sunday afternoon and there are no other users) - if I wait 10-15 minutes, and try the same SQL statements again, I get the same error again (at least once, sometimes twice in a row if I repeat the execution of the statements). Then it starts working correctly again. – PhilipD Jan 12 '20 at 18:09
  • @Josh-Darnell I tried creating the same error starting with your code, but couldn't get the error to occur. Then, I tried the following - in the same database server I created 2 new databases (set to SQL Server 2019 mode), copied the tables (including all data) that are used by this function from the existing databases into the new databases, and also copied the function. I restarted SQL Server, and called the function with specific parameters in the new database, and it always worked. Immediately calling the same function and parameters in the original database still caused the error. – PhilipD Jan 14 '20 at 11:44
  • To follow on from the previous comment, I had a query window open with a statement to call the function with specific parameters. With the database context set to the new database, immediately after restarting SQL Server, I executed the statement a few times and it never failed. I then changed the context to the original database and executed the statement again and it failed a couple of times before starting to succeed. The original database is almost 80GB with about 500 tables - I don't know how much of that database I would need to copy before the error started happening. – PhilipD Jan 14 '20 at 11:50
2

ran into this today while a developer was testing another issue. On his SQL 2019 from time to time he encountered a "Invalid Object Name [name of UDF]".

Found this thread - and read a bit up on the new Intelligent Query Processing, and UDF Inlining.

I can now reproduce the scenario and will forward it to MS, dont know the procedure for this but guess I can figure that part out.

The problem seems to arise (at least in our case) when using an UDF created on an older SQL server and backup is restored on 2019 server.

Then the "is_inlineable" is 1 on the UDF, even if it has CTE in the body. After ALTERing the UDF on SQL 2019 the problem goes away.

Potentially this will affect all our customers upgrading to SQL 2019, so I see it as rather critical.

Below is a script to follow to reproduce. Tested on 15.0.2070.41

Best regards

/Anders

-- First steps are on non-SQL2019 server, used 14.0.2027.2 for this test
-- 1. Create Local database "InlineUDF" in SQL Server below 2019
-- 2. Execute create part of script
USE InlineUDF

CREATE TABLE Test ([Id] INT, [ParentID] INT NULL, [Name] NVARCHAR(10))

INSERT INTO Test (Id, ParentID, Name)
SELECT 1, NULL, 'Root'
UNION SELECT 2, 1, 'Child'
UNION SELECT 3, 1, 'Child1'
UNION SELECT 4, 1, 'Child2'
UNION SELECT 5, 1, 'Child3'
UNION SELECT 6, 3, 'Child4'
UNION SELECT 7, 6, 'Child5'
UNION SELECT 8, 5, 'Child6'
UNION SELECT 9, 2, 'Child7'

GO
CREATE FUNCTION [dbo].[ChildrenFilter]
(
    @parentId int
)
RETURNS int
AS
BEGIN

    DECLARE @returnValue int
    DECLARE @result int
    
    BEGIN
     -- Recursive statement selecting InspectionJob inclusive all childs
     WITH ChildrenCountRecursive(id) AS (
        SELECT Test.[Id]
        FROM   Test            
        WHERE Test.[Id] = @parentId
      UNION ALL
        SELECT  Test.[Id]
        FROM Test
        INNER JOIN ChildrenCountRecursive AS tr ON Test.[ParentID] = tr.[Id]
     )
     -- End of recursive statement
      
     -- Check inspection job and all childs for vesseltype and metadata.
     SELECT @result = COUNT(ChildrenCountRecursive.Id)
     FROM ChildrenCountRecursive
    END
    
    IF (@result >= 2)
      SELECT @returnValue = @parentId
    ELSE
      SELECT @returnValue = null
    
    RETURN @returnValue 
END

GO

-- 3. Backup database, and restore on SQL 2019 server

-- 4. Examine Inlineable on UDF
USE InlineUDF

SELECT is_inlineable, NAME
FROM
    sys.sql_modules m
INNER JOIN 
    sys.objects o on o.object_id = m.object_id
WHERE 
    o.name = 'ChildrenFilter'
-- UDF is inlineable.

-- 5. Clear proc cache - and run query. 
DBCC FREESYSTEMCACHE(N'ALL');

select * from Test 
WHERE Id IN (SELECT [dbo].[ChildrenFilter](Id))

-- 6. Update compatibility level to 150
ALTER DATABASE [InlineUDF] SET COMPATIBILITY_LEVEL = 150

 --7. Clear cache and try again - first time it fails - second time it's good. 
DBCC FREESYSTEMCACHE(N'ALL');

select * from Test 
WHERE Id IN (SELECT [dbo].[ChildrenFilter](Id))

-- 8. Alter UDF
GO
ALTER FUNCTION [dbo].[ChildrenFilter]
(
    @parentId int
)
RETURNS int
AS
BEGIN

    DECLARE @returnValue int
    DECLARE @result int
    
    BEGIN
     -- Recursive statement selecting InspectionJob inclusive all childs
     WITH ChildrenCountRecursive(id) AS (
        SELECT Test.[Id]
        FROM   Test            
        WHERE Test.[Id] = @parentId
      UNION ALL
        SELECT  Test.[Id]
        FROM Test
        INNER JOIN ChildrenCountRecursive AS tr ON Test.[ParentID] = tr.[Id]
     )
     -- End of recursive statement
      
     -- Check inspection job and all childs for vesseltype and metadata.
     SELECT @result = COUNT(ChildrenCountRecursive.Id)
     FROM ChildrenCountRecursive
    END
    
    IF (@result >= 2)
      SELECT @returnValue = @parentId
    ELSE
      SELECT @returnValue = null
    
    RETURN @returnValue 
END
GO
-- 9. Examine Inlineable on UDF
SELECT is_inlineable, NAME
FROM
    sys.sql_modules m
INNER JOIN 
    sys.objects o on o.object_id = m.object_id
WHERE 
    o.name = 'ChildrenFilter'
-- UDF is inlineable.

-- 10. Clear proc cache - and run query. 
DBCC FREESYSTEMCACHE(N'ALL');
GO
select * from Test 
WHERE Id IN (SELECT [dbo].[ChildrenFilter](Id))

-- Now it works...
1
0

I had the same issue. The first time (or first execution of a plan) the query fails. Changing the query causes this error again.

My solution was to edit (some minor code change like adding a ";") and recompile the scalar function. After recompile no errors occurred.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.