2

My team has gone years (before I joined) of not adopting sql server database projects. Primarily because there are items that have circular references therefor can't build. I realize the recommended approach is to factor out or fix the circular references.

However to prove we can use them without having to fix all that technical debt, I'd like to hook my sql server project's build process to remove the items from the Build item group. I'm very familiar with msbuild but so far nothing seems to be changing that file being included in the build and failing it.

Setting the file(s) to None instead of Build works fine, until we try to do a schema compare. Where it either sets them back to build, or adds them again with a new file name ending in _1.sql

<Target Name="CustomSqlHook" BeforeTargets="CheckRequiredProperties;BeforeBuild;All;_SetupSqlBuildInputs;SqlBuild;SqlStaticCodeAnalysis;ResolveArtifactReferences;Build">
    <Message Text="Build Before: @(Build)" Importance="high" />
    <Message Importance="High" Text="custom sql hooked" />
    <Message Importance="High" Text="build items are @(Build-&gt;Count())" />
    <ItemGroup>
      <Build Remove="project\Views\uvw_survey_history.sql" />
    </ItemGroup>
    <Message Text="-------------------------" />
    <Message Text="Build After: @(Build)" Importance="high" />
    <Message Importance="High" Text="build items are @(Build-&gt;Count())" />
</Target>

which shows the count of included items in the build group as going from 2978 to 2977. However the build still fails on the same file. sqlerror

Maslow
  • 18,464
  • 20
  • 106
  • 193
  • You may want to look at creating dacpac files and adding those as database references in order to avoid the issues with circular references. I wrote about that here: http://schottsql.blogspot.com/2012/10/ssdt-external-database-references.html – Peter Schott Jul 05 '14 at 03:08
  • @PeterSchott - I fail to see how that would work around circular references. If Database A references B and B references A, one of them still has to be built first to make the dacpac the other references. – Peter T. LaComb Jr. Jul 07 '14 at 14:51
  • You can use sqlpackage to extract a dacpac from the currently working DBs. Use those dacpacs for references. As for building, I had to go through some tweaks to get that to work properly (one major circular reference). Involved turning off the transaction checks/rollbacks on failure and deploying one database twice for its initial build. Once that was done, everything worked and continues to work with no issues. – Peter Schott Jul 07 '14 at 17:03

1 Answers1

1

You can add a configuration to your project (you have Debug/Release by default - Add 'Schema').

Then manually edit the project file to add the following attribute to each item you only want included when you do a schema compare:

Condition="'$(Configuration)' == 'Schema' "

Peter T. LaComb Jr.
  • 2,935
  • 2
  • 29
  • 44
  • 1
    This is certainly adding value to what I was trying to do, but it seems to break across the dimension of the schema configuration not being able to build so you can't do schema comparison. – Maslow Jul 03 '14 at 18:29