Search code examples
sqlsql-servertypessqldatatypessql-server-2017

Cannot find data type - error occurs only inside larger script


I am creating a UDF.
The function is successfully created ONLY if I run the CREATE FUNCTION block by itself.

If I include the CREATE FUNCTION block inside a much larger "complete" DB creation script, I get a weird error message stating an invalid data type was used.
(See code block and error screenshot below.)

USE MyDB, SET ANSI_NULLS, and SET QUOTED_IDENTIFIER are identical in both cases, and have no effect.

This is very weird, because the UDTT is created much earlier in the DB creation script, with no errors. I can see the UDTT in SSMS, so it clearly exists ...

If I re-run the complete DB creation script, I obviously get a bunch of messages about recreating objects that already exist - I ignore these messages, because that's expected.
But then I get this error, which causes this particular function creation to fail, which blocks many other object creations after it.

If I drop the database, and re-run the complete database creation script, I receive no errors before this line, and I still receive the same error message about this datatype being invalid.

What could be causing this?
If I wanted to "repair" the complete DB creation script, where should I start?

USE MyDB
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[Test_FloatToNString] ( )

RETURNS @TestResults TABLE (
                               [Category]      NVARCHAR ( MAX ) NOT NULL ,
                               [Example]       NVARCHAR ( MAX ) NOT NULL ,
                               [Expected]      NVARCHAR ( MAX ) NOT NULL ,
                               [Actual]        NVARCHAR ( MAX ) NOT NULL ,
                               [CaseSensitive] BIT              NOT NULL ,
                               [Tolerance]     NVARCHAR ( MAX ) NOT NULL ,
                               [Difference]    NVARCHAR ( MAX ) NOT NULL ,
                               [Passed]        BIT              NOT NULL ,
                               [Description]   NVARCHAR ( MAX ) NOT NULL
                           )
AS BEGIN

    DECLARE @testData [NStringTestData] ;   /* Error here, see screenshot below */

    INSERT INTO @testData   /* etc. */

enter image description here

Here's what the UDTT looks like, in the complete DB creation script:

USE MyDB
GO

/* ... */

CREATE TYPE [dbo].[NStringTestData] AS TABLE(
    [Category] [nvarchar](max) NOT NULL,
    [Example] [nvarchar](max) NOT NULL,
    [Expected] [nvarchar](max) NOT NULL,
    [Actual] [nvarchar](max) NOT NULL,
    [CaseSensitive] [bit] NOT NULL,
    [Tolerance] [int] NOT NULL,
    [Description] [nvarchar](max) NOT NULL
)
GO

Solution

  • The debugging method posted by user5151179 is very helpful.
    Here's the post-mortem, which might help others narrow down the issue in similar situations.

    This issue is caused by the DB script containing natively compiled functions/procedures being created before the problem function (which is not natively compiled, and does not reference any natively complied modules).

    I still have no idea why this happens, but moving several dozen natively complied functions to a different "location" in the script (closer to the bottom, and most importantly after the creation of the problem function) completely resolved the issue, without modifying any of the functions or procedures.

    Every object in the database is exactly the same as it was before, just created in a different order.

    This baffles me, because there were no references between any of these objects, which would require them to be created in a specific order - and the error itself was simply a data type error, for a UDTT which was at the very top of my script, created literally before any other objects in the DB.

    If anyone has any insight as to why SQL Server is so finicky about when natively complied functions and procedures are created, feel free to leave comments or post another answer.