Error in Fabric SQL DB with GIT Integration

[sgeheeb]

Hi,

I am having the following issue:

In my SQL DB in Fabric I need to reference various views from the "sys" schema (e.g. "sys.schemas").

I can query sys.schemas fine through the sql endpoint in Fabric and also create my own views based on sys.* elements.

I need my data base project to be in source control, so I use the git integration feature to connect to an ADO repositoy.

I can clone and open the project locally with VS Code and things look fine at first.

My troubles start when I try to build the project locally before deploying it, since I get build error SQL71501 (Unresolved reference to sys.schemas). So I added a reference to the master db (system database -> artifact reference -> master -> no) and got an error, because VS Code was looking for a master.dacpac at "

.vscode\extensions\ms-mssql.sql-database-projects-vscode-1.4.6\BuildDirectory\SystemDacpacs\DbFabric\master.dacpac".

The folder did not exist, which seemd strange to me, because it existed for other target versions (e.g. "160", "Serverless", etc.)

So I created the folder and, for a lack of ther options, put the master.dacpac from "Serverless" in there.

Now my build went through and the dependencies were resolved correctly.

I synced my VS Code project with ADO and the fabric workspace detects a change. When I try to update, i get the following error:

"Cannot save package to file. The model has build blocking errors: Error SQL71501: Error validating element [dbo].[SYSViewOnline]: View: [dbo].[SYSViewOnline] has an unresolved reference to object [sys].[schemas]."

Any ideas why this is happening? Has anyone had a similar issue?

Thanks.

[jochenj]

THANKS for the prompt reply, understand, one miracle solved 😉  I'm absolutely willing to switch from VS to VS Code as soon as VS Code has "schema compare" availble which is a essential tool in our developers workflow. Looking forward to see schema compare in VS Code soon, learned in another thread thats its on the roadmap for the april release roadmap · microsoft/vscode-mssql Wiki · GitHub

[drew-sk]

Hi folks - product team for SQL database in Fabric and CI/CD - acknowledging referencing system objects is an issue in the git integration and that there's isn't a workaround for it.

The resolution is underway and would involve both the master dacpac for SQL database in Fabric being published to nuget and automatically added as a package reference in the SQL project by the git integration.

We're working to get this added asap as I know leveraging those system views can be very valuable in your database.

[jochenj]

@drew-sk I have following scenario:
- Initially created a Fabric DB in workspace, succefully synced to GIT 
- Now i want to open the existing DB Project from GIT in VS.Code or Visual Studio to make refactoring....and then deploy changes back to workspace/sql analytics endpoint. But when i open the file in VS i get only an empty project.

Is this a supported scenario? Can i open a fabric-generated DB Project in  VS/.Code? 

Observation: the .sqlproj file, as created by fabric, in GIT has not the known content-format and therefore i cannot open it in VS.Code/VS. With content-format i mean that the .sqlproj files i know have <PropertyItem> sections where all DB ojects like tables/views... are referenced which are part of the project. But this section is completely missing. didn't found anything in docu so i asking here:
.sqlproj file created by fabric:

.sqlproj file create in VS2022:

[drew-sk]

Great question! Fabric's source control integration leverages the SQL project SDK (Microsoft.Build.Sql) - I'd recommend editing in either the SQL Database Projects extension in VS Code. A benefit of the SDK-style SQL project is that it doesn't need all those lines in the SQLproj to reference .sql files in the folders.

If you're really interested in using Visual Studio, then you'll need to add a separate install of Visual Studio with the SDK-style SSDT (preview) - https://aka.ms/ssdt-sdk-preview

[jochenj]

i have a similar issue , but with the Fabric DevOps Pipeline instead visual studio. 
- Create a DB in Fabric Workspaces
- Added a View "admin.vTableOverview" which references [INFORMATION_SCHEMA].[TABLES].

- When trying to deploy the DB with Fabric Pipeline from DEV to PROD Workspace i get error:
Backend ErrorImport failure: DacServices. Cannot save package to file. The model has build blocking errors: Error SQL71501: Error validating element [admin].[vDBObjectOverview]: View: [admin].[vDBObjectOverview] has an unresolved reference to object [INFORMATION_SCHEMA].[TABLES].[TABLE_TYPE]. Error SQL71501: Error validating element [admin].[vDBObjectOverview]: View: [admin].[vDBObjectOverview] has an unresolved reference to object [INFORMATION_SCHEMA].[ROUTINES]. ...


Is this also a (currently) not supported scenario? 

[sgeheeb]

This seems to be the same thing I am running into. I am guessing Fabric is attempting an internal build of the DB project and missing the reference to [master]. It seems like Microsoft has still not published the master db for Fabric. 

[sgeheeb]

Anyone who comes across this having similar issues: I asked at Microsoft and they told me that my basic approach is correct. However, they do not have a master db for Fabric published yet, this is why the process fails. I'll post an update when I have new information on this

[Anonymous]

Hi  @sgeheeb ,

Please make sure you have the correct references to the master database. This is crucial because system objects like sys.schemas are defined in the master database. You mentioned adding a reference to the master database, but it seems there was an issue with the master.dacpac file.

Ensure that the master.dacpac file is correctly placed in the expected directory.

Then verify that the master.dacpac file you are using is compatible with your target SQL version. Using a master.dacpac from a different version (like "Serverless") might cause issues. You might need to download the correct master.dacpac file for your target SQL version.

After placing the correct master.dacpac file, update your project references in VS Code to point to this file. This should help resolve the unresolved references.Once the references are correctly set up, try building the project again. If the build succeeds, deploy the project and check if the issue persists.

Hope it helps!

Best regards.
Community Support Team_Caitlyn

[drew-sk]

This isn't how the CI/CD integration in Fabric works - it may be a workaround in custom deployment pipelines through Azure DevOps or GitHub actions - but even then the recommendation would be to use a package reference (to a nuget feed) instead of an artifact reference (to a dacpac file).

[sgeheeb]

Thanks for you reply, can you point me to where I can find the correct dacpac for Fabric SQL DB? https://www.nuget.org/packages/Microsoft.SqlServer.Dacpacs.Master does not contain a fabric specific version but only gives me the options which the sqlprojects extensions for VS code brought along already.

The problem seems to be ocurring when the Fabric Workspaces syncs the changes from the ADO repo: I can do a build on my local version and also create the views using the SQL Endpoint in Fabric. Things go wrong when Fabric syncs with ADO (which contains a version of the database which built successfully locally).

Have you already done this successfully? Thanks!