I’ve received a note that SQL Server Data Tools has some problems when working with references to another database or server in a database project. I wanted to find out what is really happens in SSDT when I import a database with dependencies. I’m using Visual Studio 2013 with Update 4.
I’ve created a database with the stored procedure that will use the following things in queries:
- linked server;
- four-part name;
- synonym.
The import of database to SSDT database project has finished successfully. I noticed that the linked server has not been imported. I think that’s because linked server is a server object, not a database one. But you can add a linked server to your database project manually.
When I open the stored procedure which queries the linked server, I see a warning about unresolved reference.
The same warning appears when I open a procedure with a four-part name reference. As for the last case with a synonym, SSDT does not show any warning. But in all cases we’ve got some difficulties/features such as:
- Intellisense does not work on that objects;
- Build passes with a lot of warnings SQL71502 or SQL71562 about unresolved references;
- Build passes even if I’ve made an error in the name of a referenced table/field/function, etc.;
- Errors can be found only in the deployment phase.
Summary:
- we can use any form of references to another databases via linked server, four-part name notation and synonym;
- linked server is not imported to database project automatically;
- SSDT is unaware of objects through the references that will lead to a late discovering of errors.
Notes About SQL Server and .NET Development 