Using SSDT, how do I resolve SQL71561 errors when I have a view that references objects in a different database?

Question

I have a database project in SSDT and when I import a view that references objects in a different database, I get error SQL71561, with a description along these lines:

Error 4 SQL71561: View: [schema].[viewname] has an unresolved reference to object [other_db].[schema].[table].[column]

I spent some time trying to figure this out, so to help others running into this I will post the answer that worked for me.

Whoredom

Answer 1

To resolve this, I added a reference to the other database, clearing out the "Database variable" field in the dialog. If I didn't clear this field, when doing a schema compare, SSDT would generate the update script using the database variable name, which would fail.

1. Add a Database Reference to the project.
2. In my case the other database was another project in the same solution, so I was able to select it in the first drop down on the "Add Database Reference" dialog.
3. Make sure the text in the "Database name" field is correct.
4. Clear out the "Database variable" field.

Look at the "Example usage" text and verify that it looks as expected. Click "OK" to add the reference and that should take care of the 'unresolved reference' errors.

After doing this, I was able to do a schema comparison, but trying to build the project produced the following error:

Error 408 SQL00208: Invalid object name 'db.schema.table'.

Going to the project properties and unchecking "Enable extended Transact-SQL verification for common objects" allowed the project to build successfully.

Whoredom

Any idea on somehow suppressing the errors? I can't add a database reference since I have encrypted content in the database I need to reference. – Ilianailine

That option has now been removed by Microsoft (Sunsetted).. are there any alternatives? – Sealer

Using the variable name instead of the direct name fixed the error, but when I do a comparison to a database it shows up as needing to be updated. – Nihon

You can also use a dacpac file a reference it in your project. – Smilax

Answer 2

My problem was from a view. In the view I had...

...FROM [MyDatabase].[dbo].[MyTable]

I replaced it with...

...FROM [MyTable]

The reason is that you may be importing the bacpac/dacpac into a different database name so the reference to [MyDatabase] might not be valid.

Partitive

Great point, this is what fixed it for me. It makes complete sense when you consider that one of the big uses for SSDT is to keep a group of databases in sync with a single Schema. In that case, why would I ever supply a specific database name? I would just have to change it for every DB I deployed to. Damn...it seems so simple now that you've pointed it out, haha. Thanks Jason – Byrom

This is good to work around the immediate error, but consequences may be severe, such as in UDFs. This should not be used long term. – Subversion

@Subversion Could you please elaborate on why it could be severe – Mannikin

Answer 3

You can run into this if your build order is incorrect.

I ran into this when pulling a fresh copy of a project from source control and doing "build solution".

If a reference is not working, make sure you build the referenced database [other_db]. Once I built the [other_db] my references worked.

Statampere

Answer 4

These errors started appearing for me when I changed the Project Properties > Target Platform from SQL Server 2016 to SQL Server 2014.

In my scenario I have a database that is created by an external tool in one SSDT project (A) and my SQL views, etc in another project (B) with a reference from B->A.

Having developed against SQL 2016 I found our test environment was running 2014 so changed the target platform in (B) so I could deploy. (A) isn't deployed - the external tool is also installed and configured to produce the same database.

Strangely, I was later able to alter views in (B) and publish, but then wanted to remove a column. At this point the publish kept failing due to these reference errors. Changing the Target Platform on project (A) to 2014 then cleared the error and allowed me to proceed.

Trevortrevorr

Answer 5

After trying all the above four answers I was still getting same error for two of my stored procedures.(From 1000+ errors to just 2 )

So what worked now is, I simply replaced the table alias name with the table name itself and hoila the build succeeded.

--From
table1 t1 join table2 t2
t1.col1 = t2.col1
--To
table1 t1 join table2 t2
table1.col1 = t2.col1

Sanguinolent

Answer 6

None of above solutions works for me.

But this one:

and change those script that refer to other database project. for example if you have a view in AdventureWorks that refer to AdventureWorksDW, use this syntax:

select * from [$(Your Database Variable)].Schema.Object

e.g.

select * from [$(Your Database Variable)].dbo.dimDate

Delaminate