1

When I try to set up a database project for SQL Server 2008 in VS2010 I keep running into error "SQL03006 - Column xyz contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects ..."

One example with the original code would be as follows:

CREATE VIEW [dbo].[vwSomeView]
AS
SELECT 
    CT.ID, 
    CT.UserName, 
    ST.Title
FROM
    SomeOtherDatabase.dbo.ComputerTable CT
JOIN
    SomeOtherDatabase.dbo.SoftwareTable ST ON
    CT.ID = ST.ComputerID

Now the error only refers to e.g. column CT.UserName, not CT.ID. I also added a corresponding database reference to "SomeOtherDatabase" and updated the code as recommended by several posts I found during my search for a solution:

CREATE VIEW [dbo].[vwSomeView]
AS
SELECT 
    CT.ID, 
    CT.UserName, 
    ST.Title
FROM
    [$(SomeOtherDatabaseName)].dbo.ComputerTable CT
JOIN
    [$(SomeOtherDatabaseName)].dbo.SoftwareTable ST ON
    CT.ID = ST.ComputerID

What I don't understand is why there is a problem with one column from the CT table, but not the other. When I look at the table definition in the database project, all seems to be fine as the column is there (as expected).

I'm also not quite sure whether I can refer to the table names by their alias once that is defined, i.e. do I have a problem writing "CT.ComputerID" instead of "[$(SomeOtherDatabaseName)].dbo.ComputerTable" (I don't think so, but I keep running into so many errors that I feel a little bit lost at the moment).

Any pointers in the right direction are appreciated, thank you.

Update

The actual error message is like:

SQL03006: View: [dbo].[vwSomeView] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [$(SomeOtherDatabaseName)].[dbo].[ComputerTable]::[ID] or [$(SomeOtherDatabaseName)].[dbo].[SoftwareTable]::[ID]"

G.

Gorgsenegger
  • 7,356
  • 4
  • 51
  • 89
  • Can you post the actual message that matches the code you'e posted please? Why sanitise the error when you have given code: or have you give us sanitised code too...? – gbn May 30 '11 at 07:25
  • It's an amended version that is less verbose than the original code, although hopefully still showing the general problem that I run into. If need be I think I can set up a complete example with suitable code to create the databases and tables, but that would probably be a lot more code. – Gorgsenegger May 30 '11 at 07:43

1 Answers1

0

The JOIN is SoftwareTable .ComputerID to on ComputerTable.ID which implies to me that there is no ComputerID column in ComputerTable (the CT alias)

Do you really have a ComputerTable.ComputerID column in addition to ComputerTable.ID? Which is the unique key for the foriegn key on SoftwareTable?

The [$(SomeOtherDatabaseName)] bit is a red herring I think

Edit:

Your error is caused by SQL Server looking for a database name [$(SomeOtherDatabaseName)]

It isn't being corrcted by VS for some reason.

gbn
  • 422,506
  • 82
  • 585
  • 676
  • Sorry, that was an error. The table relationship is like "usual", CT.ID = ST.ComputerID. The [$(SomeOtherDatabaseName)] syntax I've met a couple of times though, I think I need it as soon as I reference another database project. – Gorgsenegger May 30 '11 at 07:38
  • ..and the actual error please now? You still mention CT.ComputerID in your question too... – gbn May 30 '11 at 07:41
  • Updated, hope this helps. If not I will try to create some sample code, please let me know. – Gorgsenegger May 30 '11 at 07:52
  • Thank you, but according to http://blogs.msdn.com/b/bahill/archive/2009/08/26/using-self-referencing-or-local-3-part-names.aspx this would mean that I cannot use VS2010 for Database Projects as soon as there are links between different databases. If I don't use the [$(DatabaseName)] syntax, VS2010 cannot handle it, but if I do it also doesn't work. – Gorgsenegger May 30 '11 at 08:44
  • The article says "Our guidance is to not use local 3-part names as it introduces an unnecessary layer of abstraction and dependency on the database name." Too right. The fact is that VS is not substituting the name on deployment. You can trap this error earlier by using WITH SCHEMABINDING: SQL Server won't allow view creation if references can not be resolved – gbn May 30 '11 at 09:03
  • I still don't get it. Fair enough, I don't want to state the whole multiple part identifier for local objects when the script already is in that particular database. But as soon as it is in a different one, I need multiple parts for the identifier, and the [$(...)] syntax as far as I understood is only to guarantee that the deployment of the scripts will work as expected? Unfortunately I have a server full of legacy databases (not my own of course, as such things usually are), and they are all dependent on each other. – Gorgsenegger May 30 '11 at 09:41
  • VS isn't updating the reference on deployment. Simple. Now, you have to resolve this (did you follow the article *exactly*?) or don't use this "feature". – gbn May 30 '11 at 09:53