Why do I get a TSD03006 Error instead of a TSD04151 Warning for my views?

Question

So I have several databases we are trying to import into VS TS 2008 Database Edition GDR2.  However, each one has some objects that reference tables in the other dbs.  I know that I can create db references to resolve warnings (and possibly errors), but I haven't been able to get that far since none of them build and therefore none of them have produced a .dbschema yet.  The reason none of them build is because they all have at least one View that gets the TSD03006 error (because it's referencing another db!).  Any SPs that reference other db objects get the TSD04151 Warning message, which pretty much means the same thing, I think.  Except errors prevent a build and warnings do not.  I feel like I'm stuck in a Catch-22.  I can't reference another db because it won't build.  I can't build because I can't add a reference to the other db.  Yikes!

I know that I could do a work-around by excluding my views temporarily until I get a build - and then I have to remove any SPs that reference that View so that is a LOT of work!  So is there any other way to resolve this problem?  Is there some setting or trick I don't know about that would allow my project to play nicely with my Views?

As an aside, in trying to understand what this db project is trying to do, can anyone tell me why the Views get errors but SPs get Warnings for the exact same issue? 

TIA
-VG

Answer 1

It sounds like you have a loop implemented between your databases. Something along the lines of:

create database Db1;
create table t1 (c1 int);
create view v1 as select * from Db2.t1.v1;

create database Db2;
create table t1 (c1 int);
create view v1 as select * from Db1.t1.v1;

You have to break this loop in order to build your project(s);

Create four projects instead of two:

1. Db1_T1 - this only contains the table t1
2. Db1_EverythingElse - This contains everything else and has a database reference to Db1_T1 as well as a database reference to Db2_T1
3. Db2_T1 - this only contains the table t1
4. Db2_EverythingElse - This contains everything else and has a database reference to Db2_T1 as well as a database reference to Db1_T1

Now you can build the projects Db1_T1 and Db2_T1. Then you can build the Db?_EverythingElse projects. You can use Visual Studio's build dependencies tool to make sure the build order is what you need.

The answer to your errors vs. warnings question is, it's a reflection of SQL's binding behavior. You cannot execute a CREATE VIEW statement that references a table that does not exist. So, we generate an error. On the other hand you can execute a CREATE PROCEDURE statement to define a sproc that references a table that doesn't exist. So, we generate a warning. Of course, if you execute the sproc before the referenced table is created you'll get a runtime error.

---

Duke Kamstra - Program Manager - VSTS Database Edition (Data Dude, DBPro)

Answer 2

Thank you Duke.  The explanation does make sense - thank you for that.  I will see if I can incorporate your suggestion while trying to build these projects. 

As a suggestion for MS, perhaps in future you could have the db project recognize the format of an external db reference (i.e. a 3-part reference), and treat these differently - perhaps with a new warning number that specifically indicates that there is a 3-part reference that can't be verified until a db reference is added.  That way even the views would produce warnings instead of errors, the warning would be more specific to the situation, and the projects could be built more easily.

-VG

Answer 3

VG-
I'll put your suggestion on 'the list.' :)

---

Duke Kamstra - Program Manager - VSTS Database Edition (Data Dude, DBPro)