“SQL71508: The model already has an element that has the same name” error when building SSDT solution

I recently came across this error when attempting to build an SSDT solution that had been handed over to me by another developer. The database references within the solution were quite complicated for a couple of reasons:

  1. Due to the large number of objects in some of the databases, the solution included composite projects. This meant that a long-running build of all objects was not required for every deploy. The composite project was set up such that, for an example accounts database, the “old” objects (that had not changed for some time) were contained in the project Accounts-Base & the newly created objects were added to the project Accounts. A snapshot was taken of Accounts-Base & a Database Reference was added to Accounts pointing to the snapshot dacpac as the same database.
    1 database_reference_snapshot
    This meant that most of the time we only needed to build Accounts which contained a small number of new objects & Accounts-Base only needed to be built if the snapshot changed.
  2. Some of the projects referenced other projects within the solution to allow cross-database stored procedures to build correctly. For example, Accounts may reference LIB where LIB is a library of commonly used functions.
    2 database_reference

Another point of interest is that the Target Platform for the projects was changed from SQL Server 2008 to 2012. Although I don’t know that this contributed to the error, googling for the error number returned a number of results indicating that it occurs in solutions that have been upgraded to SSDT from an earlier version so it may be related. It is also possible that the solution had been upgraded in the past although I can’t verify this as I didn’t create the solution.
3 target_platform
The error seems to have arisen because there were objects of the same name in Accounts & LIB. So, although the GetAccountId stored procedure in Accounts called the FormatId function in the same project, the fact that FormatId also existed in LIB was causing problems.
The solution I identified was to remove the database references & unload the project Accounts, delete the associated .dbmdl file (which I found in the same folder as Accounts.sqlproj) & reload the project. Then recreate the references. After this, the project built without errors.


Useful links: SSDT Database References:

Advertisements

2 comments

  1. Mark De Moss · · Reply

    This was very helpful – thank you!

  2. This was indeed helpful – thanks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Kevin Kline

Career and Technical Advice for the IT Professional

TroubleshootingSQL

Explaining the bits and bytes of SQL Server and Azure

SQL Authority with Pinal Dave

SQL Server Performance Tuning Expert

Powershellshocked

A blog about PowerShell and general Windows sysadmin stuff

Simon Learning SQL Server

I'm trying to become "better" at SQL Server and data - here's how I'm doing it!

%d bloggers like this: