SSDT Database Project. Problems with References to Another Database or Server

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:

  1. linked server;
  2. four-part name;
  3. 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.

SSDT project. Reference to linked server

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.

2 Replies to “SSDT Database Project. Problems with References to Another Database or Server”

  1. Dear Mr Zavadskiy, I have found your blog looking for someone experienced in areas of SSDT and TDD. This article and answers included within are close to my issue. Perhaps you have got some idea? Stored procedures using a linked server invocation are working properly when executed in the database project. The same procedures executed in context of the unit test project returns the message: “Invalid object name …”. I suppose there is some trouble in impersonations or – generally – changing security context between the database project and the unit test project, but I found nothing helpful.

Leave a Reply to Gerard Jaryczewski Cancel reply

Your email address will not be published. Required fields are marked *