How to make 'Schema Compare' of Database->SQL Project respect SQL-CMD Variables

Question

I have a Visual Studio 2013 solution with 2 SQL Projects DB1, DB2.

DB1 has a stored procedure that references DB2.

If I use a .dacpac and synonyms in procedures

SELECT * FROM [$(DB2)].[dbo].[Table1]

then Compare Schema from Database to the SQL Project wrongly detects the above as a change because it does not process variables/synonyms.

If instead I use

SELECT * FROM DB2.[dbo].[Table1]

and change the stored procedure Build type to None (so that the project Builds) then Schema compare when going from Database to Proejct** will 'not see' the stored proc in my project and add a new proc to the SQL Database Project on every compare

After a schema compare I will now see

• DB1
  • dbo
    • Stored Procedures
      • sp_myStoredProcedure.sql
      • sp_myStoredProcedure1.sql
      • sp_myStoredProceduren.sql

where n = # of schema compares!

If there was a way to ignore Build Error SQL7501 then it should work using the 2nd option but it seems it cannot be ignored.

Another solution is to save the schema compare and manually select skip on all the procs that have references to DB2 however I would like to detect changes in these procedures.

This seems like a simple and common use case. Has someone come up with a workaround for this design flaw?

Update

After testing Kevin's answer I have identified why some of my views did not process correctly with SC. His answer is technically correct howerver:

If you have a view in DB1:

SELECT * FROM DB1.dbo.Table1 T1
INNER JOIN DB2.dbo.Table2 T2 
ON T2.Field1 = T1.Field1

and in your DB1 SQL Project the original (without self reference DB1)

SELECT * FROM dbo.Table1 T1
INNER JOIN [$(DB2)].dbo.Table2 T2 
ON T2.Field1 = T1.Field1

Schema compare will fail to replace the variable correctly and identify a change: [$(DB2)] -> $(DB2)

The problem is the self reference DB1.dbo.Table which in my case had been inserted half way down a large number of joins many of which were DB2 references.

This causes SC to wrongly flag all the [$(DB2)] as changes. Possibly because the database sql does not 'build' in VS and reverts to text compare.

So this is not really a bug but it is a confusing outcome for a developer who doesn't manually compare every line of the SQL.

I think this issue could be expanded to the following:

Any time a Database SQL does not build SQL CMD Variables will not be parsed and will result in errors that may obscure the original build failure.

I must also add that in my case DB2 also references DB1!

This may be part of the reason for the failure to report errors correctly.

In the end to avoid circular dependencies (Projects cannot reference each other) I built DB1 referencing DB2 using a Project Reference but checking 'supress build errors in the referenced project'. DB2 did not build because it referenced DB1.

Then once DB1 built I used the output DACPAC in the bin folder, copied it to another location and in DB2 referenced that DB1 DACPAC. Now any time DB1 changes I have to rebuilt copy the DACPAC to this folder. Luckily for me this will not change too much.

This whole process is very convuluted and SQL projects should allow referencing of each other (with remote error supression) but regardless in the end I managed to get 2 db's that reference each other to be built and all with synonyms and schema compare compatible!

And it only took 2 days of struggle!

https://connect.microsoft.com/VisualStudio/feedback/details/1291555

Answer 1

It is possible to avoid this issue by changing the SQLCMD Variables Default or Local settings in the Database Project Properties. The behavior is: - If a Local value is defined, this is what is used in Schema Compare - If no Local value is defined, the Default value will be used instead. Therefore updating the Local value to match your referenced database name, rebuilding and doing an new schema compare should solve this for you.

If you have multiple databases you wish to target, the best option right now is to set different values for the "Local" value depending on your configuration. What this means is that:

1. You create a new Solution Configuration in the Build -> Configuration Manager dialog for each target. This allows you to change some settings and have them vary per configuration

2. You edit your projectname.sqlproj.user file that should be in the base of your solution. This contains the Local value for the database, and you can make the value change depending on the configuration. In my example I had only 1 variable $(DB2) and this mapped to the SqlCmdVar__1 setting in the user settings. I changed it from:

   Debug

To:

<SqlCmdVar__1 Condition=" '$(Configuration)' == 'Debug' ">Debug</SqlCmdVar__1>
<SqlCmdVar__1 Condition=" '$(Configuration)' == 'Release' ">Release</SqlCmdVar__1>

As you can see this means that in Debug configuration it'll have a different value to release. In the real world you'd probably create a config per server you are targeting

This is more cumbersome than would be ideal, but it does solve your issue and is the best way to do so given the present tools.

Update: to work around potential issues with circular dependencies between database projects you should use Composite Projects. The basic process is:

• Create "DB1_Core" and "DB2_Core" projects. Put the objects referenced by other databases in the Core project
• In your DB1 project add "DB1_Core" as a "Same Database" reference. This will ensure that when publishing with "Include Composite Objects = true" that your DB1 project publishes just like it did before - all the Core objects will be included.
• Do the same for DB2 project
• DB1 should only need a reference to DB2_Core, and DB2 references DB1_Core. This breaks the circular dependency and allows you to build safely.

This is a best practice and follows similar patterns to C# and other project types. There is a presentation covering composite projects - link is on the SSDT blog here.