SSDT: Unresolved reference to object

Question

I have an existing SQL Server 2014 database and I want to add it to source control (SSDT in Visual Studio 2017).

I have a database project with lot's of views and stored procedures. MyDatabase is current database.

Every view and stored procedure is written in the following way:

create view MyView 
as
    select
        Id
    from MyDatabase..MyTable

".." means the default schema name here (dbo). And it works in SQL Server. But SSDT considers such a construct as an error:

View MyView has an unresolved reference to MyDatabase.dbo.MyTable.

So SSDT knows perfectly well, that database is MyDatabase and skipped schema name is dbo.

But I can't build my project with such errors. I can't also rewrite MyDatabase..MyTable to MyDatabase.dbo.MyTable.

So is there any way to solve this problem SSDT?

I would try replacing it with: `from $(DatabaseName)..MyTable` then SSDT should be able to properly match the name or skip the database and schema part at all. — Lukasz Szozda, Dec 29 '19 at 12:21
You don't have to add another reference, just use: $(DatabaseName) — Lukasz Szozda, Dec 29 '19 at 12:24
when I write from $(DatabaseName)..MyTable in view, I gen error: Incorrect syntax near '.'. — Alex Sham, Dec 29 '19 at 12:27
In SSMS or VS? The value of $(DatabaseName) is replaced during publish with target database name — Lukasz Szozda, Dec 29 '19 at 12:27

Lukasz Szozda · Accepted Answer · 2019-12-29T12:34:36.150

The 3-part name could be replaced as [$(DatabaseName)]..MyTable:

select Id from MyDatabase..MyTable
=>
select Id from [$(DatabaseName)]..MyTable

Using local 3-part names in programmability objects

While VSTS:DB does not support local 3 part names it does support the use of variables and literals to resolve references to external databases. The $(DatabaseName) variable is an ambient variable that will have its value replaced at the time of deployment. This variable gets its value from the project properties deployment tab. Since $(DatabaseName) is always replaced at deployment with the target database name and references through variables are resolved you may use a variable in your local 3-part names.

Our guidance is to not use local 3-part names as it introduces an unnecessary layer of abstraction and dependency on the database name

SSDT: Unresolved reference to object

1 Answers1