SQL Server Project View references tables from different Database

1
votes

I am converting a database model into a SQLModel so we can start deploying with a dacpac. I have completed this with a few other databases but none references external databases. I have an issue where a couple views and stored procedures reference tables from a different database that is be on the same server. For stored procedures there isn't an error because procedures do not need to have the tables created unitil runtime. But for the views I get build errors for

contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous

I have been trying to figure out a way to make it work without having to have a second project that has the fields in place or to reference a different dacpac. I co-worker suggested to try synonyms but that didn't solve the problem either.

Any help would be greatly appreciated.

2
sql-serversql-server-2012dacpacsqlproj
Do the tables that the views are based on exist?TSQL is resolved when it is executed (so the tables listed in a stored procedure don't have to exist at the time of creation) but the tables/views that a view is based on must exist when created. - benjamin moskovits
They exist in an external database on the same instance the view is referencing the table as EXTERNALDB.dbo.TABLE - greektreat

2 Answers

0
votes

You will need to create additional SQL projects and import the other dbo's for each external database reference. Then, create references in your main project to the additional (external ref) projects. You will probably have to find/replace any three-level references in your main db (maindb.schema.object -> schema.object) that reference the main db as well. FINALLY, build the solution and if it is error-free your reference errors should clear up.

You can use the error codes reported (71561, 71501) to search around for how other people have resolved this, but the above steps worked for me.

0
votes

I just ran this on two databases (local and external database named x6 on SQL Server 2012)

create view dbo.view1 
as

select * from dbo.x5 inner join x6.dbo.t2
on dbo.x5.i1 = x6.dbo.t2.x1
;
go

create view dbo.view2
as

select * from dbo.x5 inner join x6.dbo.t2
on dbo.x5.i1 = x6.dbo.t2

View2 was not created because I did not refer to the column that was used to join to table x5 with a message of:

The multi-part identifier "x6.dbo.t2" could not be bound. So it worked when I was explicit about columns used to join. Also if there are duplicate names in the two tables it could get confused.

If there are a column named state1 in both tables and you do a select name1 from.... if will give you an ambiguous reference error.