SQL Database Project: build different scripts depending on build configuration

Question

The problem I want to solve is to build different scripts depending on build configuration.

Say we have two instances of SQL Server:

• Enterprise version with connected Linked servers
• LocalDb version for offline developing and unit tests

Enterprise version has views for Linked servers when LocalDB substitues those views with local tables.

Those Linked Server views and local tables have the same names and set of fields. So they are not included in build by default (Build Action = None). Instead they are included in build in BeforeBuild Target of the project file.

<Target Name="BeforeBuild">

    <ItemGroup Condition=" '$(Configuration)' == 'LocalDb'">
        <Build Include="Local_Tables\*.sql" />
    </ItemGroup> 

    <ItemGroup Condition=" '$(Configuration)' != 'LocalDb' ">
        <Build Include="Linked_Server_Views\*.sql" />
    </ItemGroup>

</Target>

But the problem is that Visual Studio caches the DB Model and if we first build project for LocalDb and then try build project for Enterprise configuration - Visual Studio outputs errors:

Error: SQL71508: The model already has an element that has the same name

If to close and open solution or Unload Project and Reload Project, Visual Studio recreates dbmdl files and Enterprise configuration is being build without errors.

So my assumption is if I refresh dbmdl cache I will get smooth build without error.

---

When you open or reload SQL Server database project in Visual Studio 2012, it creates a file with extension dbmdl, which is a deserialized and cached db model as described here.

In the moment of the dbmdl file recreation Visual Studio outputs the following:

Deserializing the project state for project 'MyProject.sqlproj'...
Detecting file changes for project 'MyProject.sqlproj'...
Deserialization has been completed for project 'MyProject.sqlproj'.

How to force Visual Studio to refresh the dbmdl cache without project reload and without changing of the project xml file?

Is there a way to refresh the the dbmdl cache placing a command into BeforeBuild or AfterBuild targets of the project xml file?

Or the whole approach to the problem is wrong and there is another way to build different scripts depending on build configuration?

Answer 1

You may have another possible option in using composite projects. Jamie Thompson blogged about them here: http://sqlblog.com/blogs/jamie_thomson/archive/2013/03/10/deployment-of-client-specific-database-code-using-ssdt.aspx

This would let you build a main project and tie in additional projects with the environment-specific code. You could deploy the appropriate one by doing some checks in the release scripts.