unable to use SqlCmdVariable in views MSBuild.Sdk.SqlProj

[sunnylnct007]

Hi, I am migrating database projects to use MSBuild.Sdk.SqlProj. I am unable to use SqlCmdVariable in views. They work fine in stored procedures.
Below works
CREATE PROCEDURE Test AS
BEGIN
Select count(*) as CountTest from [$(QrmDatabaseName)].[Master].[Qrm] qrm
END

Below doesn''t work

CREATE VIEW Test AS
Select count(*) as CountTest from [$(QrmDatabaseName)].[Master].[Qrm] qrm

Can you please let me know if this is expected behaviour?

[jmezach]

I would kind of expect that to work. Can you share the error message and a small repro for this so that we can investigate?

[sunnylnct007]

Thanks for checking. Below is the error. I will also upload a repo and let you know. Basically it doesn't fid the variable

1>C:\SourceCode\lqd-dwh-database\TestDb\Stored Procedures\InsertTest.sql(3,39): error SQL71561: View: [dbo].[Test] has an unresolved reference to object [$(QrmDatabaseName)].[Master].[Qrm].
1>C:\Users.nuget\packages\msbuild.sdk.sqlproj\2.7.2\Sdk\Sdk.targets(244,5): error MSB3073: The command "dotnet "C:\Users.nuget\packages\msbuild.sdk.sqlproj\2.7.2\Sdk../tools/net8.0/DacpacTool.dll" build -o "obj\Debug\netstandard2.0\Test.Sql.dacpac" -n "Test.Sql" -v "1.0.0" -sv Sql150 -i "obj\Debug\netstandard2.0\Test.Sql.InputFiles.txt" -sc QrmDatabaseName="" -sc DwhDatabaseName="" -sc -bp RecoveryMode=Simple -dp IncludeCompositeObjects=true -tdn "lqd_qrm" -an " exited with code 1.

But when it has stored procedure it finds it correctly
Setting property IncludeCompositeObjects to value True
1> Setting property IncludeCompositeObjects to value True
1> Setting property IncludeCompositeObjects to value True
1> Using encrypt: False
1> Adding SQLCMD variable 'QrmDatabaseName' with value 'test_qrm'

[jmezach]

Not sure what's going on just yet without a repro, but based on the above logs in your first scenario it seems that no value is passed for the QrmDatabaseName variable, hence the error. In the second scenario it is being passed.

[sunnylnct007]

Appreciate your help on this Jonathan. Please see below the repo I have created in Github for replicating this issue

https://github.com/sunnylnct007/sdkstyledbproject
Please let me know if you need more input on this for investigation

[ErikEJ]

@sunnylnct007 You need to use project (or .dacpac) references I think, have a look at the readme

[sunnylnct007]

Thanks Eric but that is not the issue here. The issue is the variable replacement not happening for views. If you want I can add the dacpac but that's not going to make it any better.

[ErikEJ]

I think the parsing of stored procedures is less strict than view definitions. So I think the sproc is also not parsing on a live deployment

[jmezach]

I'm starting to think this is actually a limitation of DacFx, not necessarily of MSBuild.Sdk.SqlProj. See https://stackoverflow.com/questions/12161392/using-ssdt-how-do-i-resolve-sql71561-errors-when-i-have-a-view-that-references for example. Might want to file an issue over at https://github.com/microsoft/dacfx/issues.