This post is a follow up to this post about a talk I gave at the Boston Code Camp.

One of the questions at the end of my talk was:
How do you deal with multiple, dependent databases, including using symbolic links?

I decided the best way to explain working with dependancies is to do a step by step database import example.

If you would like to skip to the part about dependancies click here.

This tutorial was created with Visual Studio 2013 and SQL Server 2014 Express.

The sample project for this tutorial is on Github here.

For this example I’m going to use two dummy databases, “Database1” and “Database2”.
A view in Database2 is dependent on a table in Database1.

Open SQL Server Object Explorer
SQL Server Object Explorer
Connect to your database server
Open Database
Right click -> New Project Create New Project Check settings and click Start Import Database Dialog
For this example I am importing into Git, and will have the source code on Github here.
I chose to not import logins, permissions, or db settings because it gets complicated and for most cases you won’t need it.
Choose Git Choose Git Click Finish Finish Files that are created when adding to Git Created Files When you choose to import the solution into Git, some files are created for you:
.git directory - Holds the files that store the Git version history

.gitattributes - What kind of line ending should be used?
.gitattributes   Note that there were some issues with text=auto setting all line endings to CRLF, I’m not sure if this is still a problem, but if you see this issue then can you can change text=false.

.gitignore - Tells what files to NOT commit, such as files generated during the build process. .gitignore
Commit files to Git
Visual Studio created a Git repository for us, but no files have been added.
To add the files to Git, you can use Visual Studio’s Git integration.
Team Explorer Team Explorer Open Commit Changes
Push Changes to server (in this case Github)
Unsynced Commits Add Remote
Resolve Errors Now that you have Database2 imported, you will see that there are some errors:
Errors
Go to the Error tab and view the errors:
Errors List There are two errors listed, lets resolve one at a time.
The second error is because it is legal to reference the current database name in a view, but not legel in an SQL Database Project: Database Self Reference Once we remove the self-reference the error is resolved: Database Reference Resolved
The other error is a little more involved. The view is referencing another database.
Reference Error There are two ways to resolve this error.
  1. Import the database that is being referenced into the solution.
  2. Add a reference to a .DACPAC (for more info on DACPACs, see this post)

    For this example, I am going to do #2 above, because I don’t want to go through the hassle of having to fix any errors with Database1 after importing it.

    First we have to create the .DACPAC for Database1
Right click on Database1 in SQL Server Object Explorer Extract Dacpac Specify location Extract Dacpac dialog
I like to make a folder under the project that holds DACPACs that will be referenced.
Once the DACPAC is created, we need to add a reference to the Database2 project.
Right click on the Database2 project references and click "Add Database Reference" Add Database Reference
Add Reference Dialog
Here you have some choices to make, you can either use a database variable to refer to the referenced database (see above), or you can clear out the “Database variable” field and reference the database by name: No Db Variable For this example I’m going to use the Database variable option so that I can show how to use variables when publishing. For simple database references, I usually clear out the variable and just reference the database by name.
You will also see that I clicked “Suppress Errors”, this will suppress any reference errors if Database1 references another database.
Fix the error by referencing the DACPAC Referencing db with variable Error fixed Fixed
At this point the database is imported and the errors are resolved.
In a future blog post I will cover making changes and publishing those changes to a new or existing database. What about symbolic links? Symbolic links allow you to reference another database with a name you give the link.
To publish a database that references another database using a symbolic link, simply set the database variable equal to the name used in the Symbolic Link.