In SSDT, a project can reference another database. There are two options here:
- The database is in the solution.
- The database is not in the solution.
In this blogpost I'll discuss option 2: the database is not in the solution. In this case you have to create a .dacpac file from the external database and save it on a central place.
If you create a stored procedure in a database and this references a table in another database then an unresolved reference to object warning/error is displayed.
SSDT can't determine whether this code is right (or wrong). Therefore SSDT wants to know whether this tables exists. This can be done by creating a project in the solution for this specific database or by referencig a dacpac file. Why not linking directly to a database? Well, I think that this interferes with the DTAP environment approach (distinction between Development and Production) or offline development.
Add Database Reference
Now, you can extract a .dacpac from an existing database and save it as a file in the filesystem. Below I've described how you create a database reference to a database that is not icluded in the current solution.
1. Right click on a database, press Tasks and click on extract Data-tier Application
2. Press a couple of times on Next and the .dacpac file is created.
3. The next step is to add a database reference in the project in SSDT.
4. Fill in the location of the .dacpac file and the other fields and press OK.
The database reference is created and ready for use.
5. The next step is to replace the static databasename and replace this with the Database Reference.
The Database Reference is now also mentioned in the Project properties (SQLCMD Variables)
6. Press Publish and the following window occurs.
7. The stored procedure is now adjusted with the new name.
ConclusionPretty cool option of SSDT. You can develop off line your application and publish this solution to a server when you're ready.