Build error SQL71636: You cannot use types or statements that modify the database state.

[jasonhorner]

• SqlPackage or DacFx Version: 162.1.167.1
• .NET Framework (Windows-only) or .NET Core:
• Environment (local platform and source/target platforms):

Steps to Reproduce: Create a new database project in ADS that targets synapse azure sql dedicated pool project

1. Create a Table

   CREATE TABLE [dbo].[DimTest]
   (
   [Id] INT 
   )
   (
   DISTRIBUTION = ROUND_ROBIN,
   HEAP
   )

2. Create and inline TVF that accesses the table:

   CREATE FUNCTION [dbo].[fnTest] (@Id bigint) 
   RETURNS TABLE
   AS
   RETURN (
   SELECT Id FROM [dbo].[DimTest]
   WHERE Id = @Id
   )

This will work if you run within SSMS,

However if put in as objects in a database project (SSDT or ADS) it will cause the build error: Build error SQL71636: You cannot use types or statements that modify the database state.

see here for more context: https://learn.microsoft.com/en-us/answers/questions/419193/azure-data-warehouse-function-has-problem-when-we

Did this occur in prior versions? If not - which version(s) did it work in?

(DacFx/SqlPackage/SSMS/Azure Data Studio)

[jasonhorner]

as a side note if the function doesn't reference a table or view directly the project will build successfully for example

CREATE FUNCTION [dbo].[fnTest] (@Id bigint) 
RETURNS TABLE
AS
RETURN (
SELECT @Id As Id
)

[keen85]

same problem 😒