Problems with three-part (3-part) names from Azure SQL DB, self-referencing its own DB

[mbourgon]

• SqlPackage or DacFx Version: 162.5.57.1
• .NET Framework (Windows-only) or .NET Core:
• Environment (local platform and source/target platforms):
  .NET SDK:
  Version: 9.0.203
  Commit: dc7acfa194
  Workload version: 9.0.200-manifests.9df47798
  MSBuild version: 17.13.20+a4ef1e90f

Runtime Environment:
OS Name: Windows
OS Version: 10.0.17763
OS Platform: Windows
RID: win-x64
Base Path: C:\Program Files\dotnet\sdk\9.0.203\

Steps to Reproduce:

1. create a trigger in an Azure SQL database, using a three-part name on the table that self-references the name of the database. So it would be (within the AdventureWorks database) "create trigger dbo.my_trigger_here on "AdventureWorks"."dbo"."mytablehere"..." (we can also duplicate it with "create trigger dbo.my_trigger_here on [Adventureworks].[dbo].[mytablehere]..." The trigger is valid and works. It's not using an external reference so it's legitimate.
2. use SQLPACKAGE to EXTRACT it with VerifyExtraction=true (it didn't extract because of errors, I had to use VerifyExtraction=false, but this may simplify repro)
3. sqlpackage /Action:Extract /SourceServerName:myazsqldb.database.windows.net /SourceDatabaseName:mydbname /SourceUser:myusername /SourcePassword:"mypassword" /TargetFile:myservername-mydbname /p:ExtractTarget=ObjectType /p:ignoreuserloginmappings=false /DiagnosticsFile:20250416_1319.log /p:ExtractAllTableData=false /p:VerifyExtraction=false

Did this occur in prior versions? If not - which version(s) did it work in?
No idea - I'm just now trying to set up CI/CD.

(DacFx/SqlPackage/SSMS/Azure Data Studio)
Unsure which, I think DacFx because I'm having the same problem trying to BUILD it in ADS, BUILD it in VS Code (SDK-style database projects), Visual Studio Preview (BUILD from an "SDK-style database project (preview)" with the code) and EXTRACTing with SQLPackage.

[mbourgon]

I am able to BUILD it if I replace the database name with [$(DatabaseName)]. Not able to test a deploy right now, hopefully tomorrow. But say I BUILD and PUBLISH it to dev, make my changes. When I EXTRACT again back into my git folder, I'd have to change all the database names back to [$(DatabaseName)] before doing a build & checkin. It doesn't seem like there's a good way to handle it overall.

[dzsquared]

@mbourgon - is there a particular reason why you want to have 3-part naming in the database? While the syntax works in an isolated database for Azure SQL Database, if you were to do a point-in-time restore of the database to a database with a new name you would have to go through an edit the database objects before they would work. (in the meantime they would result in errors)
in general, I'd advise against using 3-part naming unecessarily with SQL projects, but I want to understand why you are using it before I stand by the generality of the recommendation.

[mbourgon]

Oh, hey, @dzsquared! (I've watched a LOT of your videos the past couple of weeks, while I work on this). LOL, you're internet-famous!

Honestly, it's a migration thing. We had to move into Azure in a hurry, and not everything was best practices before (although we have taken the opportunity to migrate some databases from SQL Server to Azure SQL DB). So we're busy cleaning up (hence the CI/CD work), but in that database alone I was getting over 1000 warnings/errors. And while I would love to fix it all right now, practically I can't. Our goal was "get it checked in so that we're doing pipelines properly, and then we'll have some time to work on more tech-debt". You're right, it's not great. I got it working for my POC by changing using VerifyExtraction=False and then using regex to clean every file from "mydbname". to "[$(DatabaseName)]." , but that means I'm going to need to come up with some sort of post-script to do that each time we run "sqlpackage extract". Hope this helps.