SSDT Database Reference to Views

SSDT Database Reference to Views by DevSeeker

Closed

as By Design Help for as By Design

Type:	Bug
ID:	766658
Opened:	10/8/2012 5:50:59 PM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

Using the latest version (10.3.20905.0 as of this writing) of SQL Server Data Tools (SSDT) I found that when adding a database reference, if the SSDT project is for database A and I add a database reference for database B, I can write views or stored procedures in SSDT for database A that can reference tables in database B.

The reason for this submission is that I cannot reference views in database B. If I create a view in database A with a reference to a view in database B, I'll get an error stating "SQL71561: View <schema>.<viewname> has an unresolved reference to object <$(database B)>.<referencedschema>.<referencedview>."

The question, is this by design that a view can't reference a view from a database reference?

Details (expand)

English

SQL Server 2012 - Developer Edition

Developer Tools (SSDT, BIDS, etc.)

Windows 7 Enterprise

US English

Create a database project (or use an existing project) that we will call database A.
Create a database reference to a database that has a view that we will call database B.
Create a view in database A that references a view in database B.

Red underline squiggly line on the referenced view in database B along with the error "SQL71561: View <schema>.<viewname> has an unresolved reference to object <$(database B)>.<referencedschema>.<referencedview>.

This error prevents a build, which prevents a publish. I can manually write the code and deploy it the old way. Technically, I can keep the code the same in SSDT also but I would no longer be able to complete a build using this object in that build.

Expect SSDT to resolve the reference to the view similarly to how it can resolve the reference to a table.

This would allow the build to complete and then I could publish the database from SSDT.

X64

0 attachments

Erick,

Thanks for responding. This particular issue is not using a localDB, as I can't publish to localdb as a result of this problem. I am not using a dacpac. I'll try to explain more detail about what I originally submitted.

I open SSDT and create a new SQL Server Database project named TestDB1, then close the solution. I then create a new SQL Server Database project named TestDB2. I then add a new table called testtable with columnA and columnB. Next I create a view named testview that selects columnB from testtable. I then save all changes and close this solution.

I then open the TestDB1 project. In Solution Explorer, I then right-click the solution, scroll down to add, then select existing project; I choose the TestDB2 project. At this point, I see both TestDB2 and TestDB1 projects in my solution explorer. I then right-click on References under the TestDB1 project and click add a database reference. The next screen already has TestDB2 as the selected option and I click ok. Now I see the reference for TestDB2 under references for the TestDB1 project.

Now I try to create a view under the TestDB1 project referencing the testview on TestDB2 project. The code for this view is 'CREATE VIEW dbo.FinalTest AS SELECT ColumnB from TestDB2.dbo.testview'. This is where the problem lies. I even tried 'CREATE VIEW dbo.FinalTest AS SELECT ColumnB from [$(TestDB2)].dbo.testview'. In both cases, I get the following error: 'SQL71561: View: [dbo].[FinalTest] has an unresolved reference to object [$(TestDB2)].[dbo].[testview]' with the only difference being if I used the database variable or the database project name.

In writing this, I realized I forgot to build the TestDB2 project. Once I built the project, this resolved the reference issue. This can be closed as user error.