VS 2010 Database Project - SQL03006 Error

views:

131

answers:

VS 2010 Database Project - SQL03006 Error

I created a new SQL Server Database Project within VS 2010, imported the database objects and settings from a local database named "managers", and received the following error while attempting to build the project:

SQL03006: View: [dbo].[vw_mlFunds] has an unresolved reference to object [managers].[dbo].[mlfunds].

I don't know why this view is fully qualifying a table reference to include the actual database name and I would prefer not to have to change the sql, as it someone else's code and it technically is not incorrect. But I am thinking that fully qualifying the table name to include the name of the database is confusing the VS compiler, since it is expecting [dbo].[mlfunds], not [managers].[dbo].[mlfunds]. How best to resolve this issue? Can I set up a new database name variable/alias somewhere? Or will I have to refactor/modify the sql to get it to compile? Thanks in advance.

You need to create another database project for the [managers] database and have your project 'reference' the other project. You can do this as a simple reverse-engineer step on the [managers] database that will import all the objects in that database into a new VSDB project. See Using References in Database Projects.

Remus Rusanu 2010-08-23 20:45:50

Could you clarify this for me? I'm confused by the need for a reference when both objects (the base table and the view) reside in the same [managers] database.

Joe Stefanelli 2010-08-23 21:29:31

I thought [managed] is a different database. IF the view qualifies explicitly the table name with the database name then the view definition is technically *incorrect*, because the project can be deployed under a different DB name, and even post-deployment it can be restored/copied/attached/snapshot under a variety of names and the view would be invalid in all those scenarios.

Remus Rusanu 2010-08-23 22:13:25

I think fully qualifying the table with the database name was unintentional on the part of the original developer, and we may end up modifying the view script to remove the database name reference, but the create script for the view will run as-is without errors in ssms and, when run against the managers database, it will recognize the explicit reference to managers as being to itself. I agree this is sloppy code but was trying not to mess with it for the moment, as it will require testing. That is why I was trying to figure out if there is some way around this.

Mary Hamlin 2010-08-25 14:05:41

I'm battling something similar - I think. I have two views that reference another database. I added the other database as another db project within the same solution. I added a reference to the other project.

I have tried variable names and no variable names, but no matter what I try, I cannot get rid of those SQL03006 error messages.

jonb 2010-08-25 22:16:28

My particular issue is specific to fully qualifying an object that resides in the same database. I initially had problems getting external database references working though until I figured out that when you create the database reference you also have to declare a literal database variable where the value is set to the name of the database that you are trying to reference. Otherwise, the compiler doesn't recognize the name of the database that you are referencing and still keeps giving you those error messages. I would guess that is most likely your problem.

Mary Hamlin 2010-08-26 12:51:53

Actually, it looks like the code will have to be modified, as this is not supported. Answer found in this post:

Using local 3-part names in programmability objects

Mary Hamlin 2010-08-26 13:42:45

ansaurus

tags:

views:

answers:

VS 2010 Database Project - SQL03006 Error

related questions