SSDT Database Reference to Views - by Antoine Hernandez

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 766658 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 10/8/2012 5:50:59 PM
Access Restriction Public


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? 
Sign in to post a comment.
Posted by Microsoft on 10/17/2012 at 3:52 PM
I'm glad to hear you figured out how to get this working for your scenario. You are using a database reference to another project. In this case, as you've noted, you need to build the referenced project in order for the validation through to the other project to work. It should be noted that the value you set for the command variable when establishing the referenced project is editable in project's properties, during publish (UI) or deployment with SQLPackage.
Posted by Antoine Hernandez on 10/17/2012 at 3:13 PM

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.
Posted by Microsoft on 10/17/2012 at 12:50 PM
Hello DevSeeker -

I could not reproduce your issue with a couple databases hosted in localDB Express. I used a reference to a local dacpac and set the SQLCMD variable to 'databaseB'. The referenced view did not produce an error and I was able to deploy (locally). What are the details on how you set upt he reference?

- Erick