microsoft / microsoft/DacFx

Fabric Warehouse: unchanged procedures repeatedly planned as ALTER PROCEDURE

Open
#829 1 comment 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

need-more-info sqldw
Dominant language
C#
Stars
460
Forks
29
Avg merge
4d 9h
Merged PRs (30d)
7

Description

## Summary

Against a Microsoft Fabric Warehouse target, `SqlPackage /Action:Script` repeatedly emits `ALTER PROCEDURE` for procedures whose deployed definitions match the SQL project source. The alters remain after a successful publish and are reproduced with current tooling.

## Environment

- Target: Microsoft Fabric Data Warehouse
- SQL project SDK: `Microsoft.Build.Sql` 2.2.0
- DSP: `Microsoft.Data.Tools.Schema.Sql.SqlDwUnifiedDatabaseSchemaProvider`
- System DACPAC: `Microsoft.SqlServer.Dacpacs.FabricDw` 170.0.2; also reproduced with 170.0.4
- SqlPackage: reproduced with 170.2.70.1 and 170.4.83.3

## Reproduction

1. Build a Fabric DW SQL project containing a stored procedure (for example, `CREATE PROCEDURE [dbo].[p] AS SELECT 1; GO`).
2. Publish the DACPAC to a Fabric Warehouse.
3. Generate a native script against the unchanged target:

```text
sqlpackage /Action:Script /SourceFile:.dacpac /TargetServerName: /TargetDatabaseName: ...
```

4. Observe an `ALTER PROCEDURE` for the unchanged procedure.
5. Repeat the same action without source or target changes; the `ALTER PROCEDURE` is emitted again.

## Evidence

For 16 affected procedures, the source SQL and `sys.sql_modules.definition` are identical after normalising line endings and removing the source-only final `GO`. The captured `ANSI_NULLS` and `QUOTED_IDENTIFIER` settings also match.

Comparing DACPAC `model.xml` contents shows the apparent difference:

- target DACPAC extracted from Fabric: procedure `BodyScript/Value` includes `QuotedIdentifiers="True" AnsiNulls="True"`
- source DACPAC built by Microsoft.Build.Sql: the equivalent `BodyScript/Value` has no such attributes

`/p:IgnoreAnsiNulls=True` and `/p:IgnoreQuotedIdentifiers=True` do not suppress these procedure changes.

## Expected behaviour

No `ALTER PROCEDURE` should be generated when the stored-procedure definition and captured settings are unchanged.

## Actual behaviour

Fabric targets repeatedly receive planned `ALTER PROCEDURE` statements, creating deployment noise and unnecessary procedure recompilation/locking risk.

Is this a known Fabric extractor/model-comparison issue, and is there a supported source-project configuration or publish property that makes the source and extracted procedure metadata comparable?

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start with the SqlPackage /Action:Script reproduction against an unchanged Microsoft Fabric Warehouse, then compare the source and extracted DACPAC model.xml entries for the affected procedures. Focus on the BodyScript/Value attributes and the IgnoreAnsiNulls and IgnoreQuotedIdentifiers properties. Done means unchanged procedures no longer produce repeated ALTER PROCEDURE statements, or the supported configuration preventing them is documented.

Written by the indexing model from the issue text.

Assessment

Tech stack
sql
Domain
databases, tooling
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
48/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.