Azure Data Warehouse function has problem when we create dacpac file in Visual studio 2019

Nandini S 21

I have created a database project where my source and target is Azure SQL Datawarehouse in Visual Studio 2019 by importing the database. When I perform build, it throws me an error for function as below:

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

Below is the function definition used:

CREATE FUNCTION [dbo].[fn_Customer] (@CustomerId [bigint]) RETURNS TABLE
AS
RETURN (
SELECT CustomerId,CustomerEmail
FROM Dimcustomer WHERE CustomerId = @CustomerId

)

Since this is already present in the Datawarehouse it should not create an issue while creating a dacpac file. Can you let me know if anyone has faced similar issue?

Samara Soucy - MSFT 5,131 Reputation points

2021-06-08T05:19:12.55+00:00

Hi Nandini,

I'm taking a look at this for you and will let you know what I find.

7 answers

nickolas 31 Reputation points

2021-11-17T14:45:28.983+00:00

I installed VS 2022 last night and this is still an issue for me.
Please sign in to rate this answer.

2 people found this answer helpful.
Jake Schlackman 11 Reputation points

2021-11-17T14:51:49.56+00:00

Well, you answered my question -- I was about to install VS 2022 to find out.

Thanks! Now we play the waiting game I guess.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Samara Soucy - MSFT 5,131 Reputation points

2021-06-09T03:04:38.35+00:00

The error does not appear if the return type is a single value. Since inline tables is a preview feature, it isn't supported yet in SSDT.
Please sign in to rate this answer.

1 person found this answer helpful.
Jake Schlackman 11 Reputation points

2021-11-10T15:25:05.383+00:00

Has there been any update on this? When is it planned to leave the preview mode?

Cameron 46 Reputation points

2021-12-07T02:59:24.597+00:00

Is there any updates on this issue? It's been outstanding for some time now (I notice schema compare isn't picking up table valued functions any more, which only causes more problems...)

nickolas 31 Reputation points

2021-12-07T03:22:45.027+00:00

Oddly enough, I was checking up on where this is today before I meet with our Fast Track team tomorrow. Hopefully I’ll get some push on this from that end.

WZIAFP 157 Reputation points

2022-04-22T13:46:18.187+00:00

Hi
Is there any workaround for this?

Cameron 46 Reputation points

2022-04-22T23:42:09.593+00:00

One work around I've somewhat successfully implemented here is creating a stored procedure that will re-create the function, just some simple exec SQL strings eg -

CREATE PROC [dbo].[createMyTableFunction] AS BEGIN SET NOCOUNT ON declare @sql nvarchar(4000) set @sql = ' IF OBJECT_ID(''dbo.myTableFunction'') IS NOT NULL DROP FUNCTION dbo.myTableFunction ' exec sp_executesql @sql set @sql = ' CREATE FUNCTION [dbo].[myTableFunction] (@inputOne [nvarchar](255),@inputChar [nchar](1)) RETURNS TABLE AS RETURN ( select top 1000 @inputOne as resultOne, @inputChar as MainAccount, row_number() over (order by c.name) from sys.columns c cross join sys.columns c2 ) ' exec sp_executesql @sql END

From there you can call this SP in your post deployment script to ensure it's always deployed to any new environments. If you want to get fancy, you could probably script out the screation of this SP, but it needs to exist as a static SP before you bring it into your SSDT project for deployment.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Graeme J Martin 6 Reputation points

2023-04-13T14:08:07.1766667+00:00

You could add the function script in Post Deployment, as a 'CREATE OR ALTER FUNCTION...' script. Kinda sucks, but this is STILL an issue in April 2023. [Dedicated Pool]
Please sign in to rate this answer.

1 person found this answer helpful.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Victor Ochieng 1 Reputation point

2021-07-07T14:35:05.82+00:00

Thanks @Nandis-3305, and just to let you know that am experiencig the same error on Visual Studio 2019.

@Samara Soucy - MSFT , what's the work around if its not supported yet?

Thanks for the pointers!
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Susanne Zaunmayr 1 Reputation point

2021-10-18T06:57:26.353+00:00

Change Build Action to Compile
Please sign in to rate this answer.
nickolas 31 Reputation points

2021-11-17T01:20:18.297+00:00

I tried this and was almost successful. It worked for all of functions except for ones that are a dependency to other objects. Those objects then have missing object errors.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Azure Data Warehouse function has problem when we create dacpac file in Visual studio 2019

7 answers

Your answer