CODEWITHSUNDEEP
azureazure-sql-databaseazure-synapse
Nandini
Nandini

Reputation: 65

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

I have created a database project using the target as Azure SQL Datawarehouse in Visual Studio 2019 by importing the database. When I click on build it throws me an error for Materialized views

Error: SQL71640: COUNT_BIG(a) is required when using this tool to create a materialized view that has SUM(a) in the SELECT list.

Since this is already present in the Datawarehouse it should not create an issue while creating a dacpac file and I have COUNT_BIG(*) in my script. Can you let me know if anyone has faced similar issue?

Upvotes: 1

Views: 546

Answers (1)

wBob
wBob

Reputation: 14389

I have faced a similar issue and this is currently a feature of VS2019 and have received the following errors:

Severity Code Description Project File Line Suppression State Error SQL71640: Cannot create a materialized view in this tool with COUNT(a). Replace it with COUNT_BIG(a). yourProjectName yourViewName.sql

Severity Code Description Project File Line Suppression State Error SQL71640: COUNT_BIG(a) is required when using this tool to create a materialized view that has SUM(a) in the SELECT list. yourProjectName yourViewName.sql

I've just updated to version 16.10.0 today and it's still an issue. The simple workaround is to as the error suggests and convert any COUNT to COUNT_BIG as the error suggests. The tool may get updated in the future so keep an eye out for updates.

As an alternative you could start to manage your materialized views in the post-deployment scripts (untested) but then you would lose the nice dependency features of SSDT.

If you feel strongly about it you could raise a feedback item here and get some upvotes for it:

https://feedback.azure.com/forums/307516-azure-synapse-analytics

Upvotes: 0

Related Questions