1

I am going through the process of importing our existing SQL Server databases into SQL Server Data Tools in Visual Studio 2015 with the aim of putting them under version control.

All the database projects in my solution build correctly after splitting databases with circular references into composite projects. My problem is now trying to deploy them all to a new instance, the publishing is failing because the databases being referenced don't exist e.g.

SQL72014: .Net SqlClient Data Provider: Msg 208, Level 16, State 1, Procedure Foo, Line 13 Invalid object name 'DB1.dbo.Bar'.

If I try to deploy DB1 then it's same thing as that also references another database which hasn't been created yet

SQL72014: .Net SqlClient Data Provider: Msg 208, Level 16, State 1, Procedure Bar, Line 64 Invalid object name 'DB2.dbo.Foo'

How do I resolve this so I can publish all database projects in my solution to a new instance?

  • 1
    Perhaps not what you are trying to accomplish, but why not temporarily add a shell of the database that 's being refrerenced on the target instance? By Shell I mean, you script out the database using Tasks-Generate Scripts. – Craig Efrein Mar 30 '17 at 8:41
1

I've been able to get all databases in my solution to publish by doing the following:

  • Setting the correct connection string and publish options under Project Properties > Debug for each project in my solution.
  • Assigning multiple startup projects in Solution Properties
  • Debugging the solution will then build/deploy all databases in the correct order

Though this doesn't feel like it is the correct way to achieve this as it now means I need to modify all my projects if I want to publish to a different server.

1

I had a similar issue some years ago and I ended up resolving those dependencies by bringing around give databases into a Single one with different schemas because in the past they have been splitted without sense. So I corrected this and that allowed me to tread the sql Code for All of the objects, that by the way have been used by the same application and had a Lot or references as one database project.

Or rethink the directions where references between project are allowed and reorganize them.

Another Option maybe to exclude those scripts from the build process that reference other objects across DB boundaries.

Also, what makes me wonder: in case you resolving all circular references as you said and now have a composite project, shouldnt that man you are deploying the project to ONE database now? Afaik a composite project combines All objects to a Single database. In that case I would guess you are not Using database SQLCMD variables for All database names/usages in the project. Your Code should Look like that:

select * from [$(DatabaseDB1)].dbo.TableName

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.