If you are creating a sql server project with SSDT (see here) and your sql refers to another database you will need to add a database reference to allow the project to build. You can do this by right clicking your references folder and selecting ‘Add Database Reference’

AddDatabaseReference

If the database you are referring to is also a part of the solution – for instance if you are creating a data warehouse and you have a supporting staging database, it is very similar to the process of adding an external project reference to a c# project. you just need to select ‘Database projects in the current solution’ and choose your database from the dropdown list.

Database project in the current solution

You can also reference a system database here. Occasionally however you will need to reference a database that is neither of the above.  If the database you need to reference is already a data tier application you can browse for the dacpac but if it is not (for instance it may be a legacy database) you will need to create a data tier application from it first.

To extract a dacpac from an existing database, open the sql server object explorer in Visual Studio, connect to the server, right click the database and choose ‘Extract Data-tier application’.

Extract Data Tier app

Choose where to save the dacpac, then select ‘Extract Schema Only’ and decide whether you need the additional options such as user login mappings etc. Once you have created the dacpac you can go back to the add database reference dialog and select the dacpac you just created.

Database Location

In the Database Location section of the Add Database Reference dialog set the location of the referenced database e.g. ‘Different database, same server’, the name of the database (should be pre-filled) and whether you want to add a database variable to embed into your sql instead of a hardcoded path.  Finally (and often very importantly!) you may want to check the ‘Supress errors caused by unresolved references in the referenced project’ option at the bottom of the dialog.  This is especially useful in legacy databases that themselves reference other databases.

Add Database Reference

After clicking ok you should be good to go. This reference is there just to allow the application to compile, it does not get included in the deployment, but is an excellent way of making sure that your external references are correct.

As a final note, you need to ensure that your references are kept up to date if you are working on a project.  Although you can create a NuGet feed for your created dacpacs, as of Sept 2014 NuGet does not yet support importing to an SSDT project. For now you will have to keep a manual check.

SHARE IT:

Trackbacks for this post

Leave a Reply