If your database references other databases, you’ll need a *.dacpac file for each external database referenced in your code. For example, if you reference AdventureWorksDW2008 from Adventureworks2008, you’ll need to add that as a database reference.
To add a Database Reference to your project
Right-click the “References” folder in your project or choose the “Project” menu and select the option to “Add Database Reference”
Browse for your dacpac file
Choose the appropriate database location. (Same, Different DB / Same Server, or Different DB / Different Server)
If your database name doesn’t change, clear out the “Database Variable” column. It isn’t immediately obvious, but doing this will let you keep referencing your database in the same manner you do currently.
If this database name can vary, populate the Database Variable field. You’ll need to replace this reference in your code with the variable from this column.
If you’re referencing a database on a linked server, this would probably be better served with a static database name and a “Server Variable”. Make sure that server variable is set appropriately when you publish the database.
This should be addressed with a future release of SSDT.
You may choose to suppress errors if there are unresolved references in the project. This is useful if your dacpac file could be out of date. You can still code against an object you know to exist, but isn’t in the reference file yet.
If you use Linked Servers, you’ll probably want to use the Server Variable to ensure that you can release your code in different environments.
Some Best Practices and Lessons Learned
Make a shared folder to store your dacpac files in a common place. This should be easily accessible from all of your SQL Projects.
If you use C:\DatabaseProjects as your base for all SQL Server projects, create a folder in there to store your shared dacpac files.
Because all paths are relative to the project, you may need to copy the master/msdb dacpac files that ship with SSDT into your shared folder. You can find these files in your Visual Studio folder in a path similar to the following.
Drive:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\VersionNumber\SqlSchemas
If you support multiple SQL Server editions, you may want to rename master/msdb files with their user-friendly version number. E.g., the master.dacpac file in your “90” folder could be renamed to master2005.dacpac
This should be addressed in a future release of SSDT and could already be fixed.
If you use variables, don’t forget to replace any TSQL with the variable in the form of [$(VariableName)].
Square braces may not be necessary, but in most cases, they’ll work as expected for DB or Server names.
If you need to reference a dacpac that is already in use, you’ll need to create a copy of it. This comes up more often if you’re referencing a replicated database for which you already have a production dacpac.
Choose good variable names for the Server variable name.
Don’t forget to set the variables in the Project and/or Publication properties!
I’ve been working with SQL Server for quite some time. Along the way, I’ve learned quite a few things and realized that I won’t ever know everything about SQL Server. I intended to keep growing and learning to be able to do my job well and share my experiences with others.
I currently work for a Health-related non-profit based in Boise as a Database Architect.