1

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
greektreat
  • 2,329
  • 3
  • 30
  • 53
  • 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 Mar 11 '15 at 14:10
  • They exist in an external database on the same instance the view is referencing the table as EXTERNALDB.dbo.TABLE – greektreat Mar 11 '15 at 14:12

2 Answers2

0

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.

C B
  • 91
  • 2
  • Ya that is what I plan to do if I can't figure this out any other way. I was surprised that there isn't a way around this. I will let you know what I ended up doing. – greektreat Mar 11 '15 at 14:46
  • If you find something please do - our architecture has external referenced views in external db's, so almost every project in my solution has references! PITA! – C B Mar 11 '15 at 14:55
0

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.

benjamin moskovits
  • 5,261
  • 1
  • 12
  • 22