Another type of SQL03006 ambiguous references

Question

I found another situation which can cause the SQL03006.  After poking around, I resolved it, and thought that the finding might be helpful to others.

I created a SQL 2010 database project by importing an existing database.  The tables came in fine, but I experienced issues on several views which joined to subqueries, where the subquery alias was the same as one of the columns in the subquery itself.  For example, the view below uses a subquery with the alias LastFloat which also happens to return a column named LastFloat.  This bothers VS2010 greatly.  But if you change the alias to, say, QLastFloat (or, if you're like me, Q), then it seems to figure it out just fine, and magically dozens of errors disappear. 

A snippet of the view and the one of the related error messages follow. 

CREATE

 

 

VIEW vw_CGCpos_RegisterStatus AS
SELECT R.RegisterID
, R.LocationID
, L.Name
, LastFloat.LastFloat
   . . .
FROM Register R
INNER JOIN Location L ON L.LocationID = R.LocationID
INNER JOIN LocationStore LS ON LS.LocationID = L.LocationID
INNER JOIN (SELECT PT.RegisterID, MAX(PT.CreationDate) AS LastFloat
    FROM POSTxn PT
    WHERE POSTxnTypeID = 7
    GROUP BY PT.RegisterID ) AS LastFloat ON LastFloat.RegisterID = R.RegisterID
LEFT OUTER JOIN
. . .

Error 182 SQL03006: Column: [dbo].[vw_CGCpos_RegisterStatus].[LastFloat] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to one or more unnamed objects or any of the following objects: ::[LastFloat], [dbo].[Location].[LastFloat]::[LastFloat], [dbo].[LocationStore].[LastFloat]::[LastFloat] or [dbo].[Register].[LastFloat]::[LastFloat].

Answer 1

Hi Rob,

thanks for the information and the heads up. This is a knonw issue with Subqueries and aliases. (As you already noticed) If you disambiguate the columns the error will be gone.

-Jens

---

Jens K. Suessmeyer http://blogs.msdn.com/Jenss

Answer 2

Hi Jens,

will this error been solved sometime soon? What worries me is that this error was know already in VS 2008 GDR R2 release for SQL 2008, but is still exists in VS 2010.

The error is really fatal. We use VS to keep databases on live and production server in sync by running schema compares. We cannot change the sql code just because VS is too stupid to accept valid SQL code (i.e. accepted by SQL Server). There are other developers creating SQL code with other tools.
If the error would be reported as warning (as it does in user defined functions and as it is in old VS 2008) everything would be fine.

At the moment we cannot migragte to VS 2010 because of that reason. Luckily we are still on SQL Server 2005, so this is possible.

Please let me know when I can expect a bug fix. We want to migrate to SQL Server 2008 next year.

Thanks, Gangolf

Answer 3

I'm running into this same issue, but while self-joining on a table.

Does anyone know how to get a view like the following to compile?

CREATE VIEW dbo.vwParentChild AS

SELECT parent.ID, parent.Column2,
 child.Column3, child.ID As ChildID
FROM Table1 parent
INNER JOIN Table1 child
 ON child.ParentID = parent.ID

Since the aliases are already different names, I'm not sure what else to do.

Answer 4

Same issue. VS2010 even fails to map a view referencing the tables in the same database, e.g.

CREATE VIEW [dbo].[ActivityTypes]
AS
SELECT   ID, [Key], Name, Description, ExportKey
FROM     MyDatabaseName.UserActivity.ActivityTypes

When referencing a project database neither variable nor literal works. I wonder how people make it work in VS2010?

Answer 5

Any word on this?  Happening to me big time.  Again all in views.  Some in DB some across DBs, some use synonyms... some across servers.

-Dan

---

If this answers your question, please Mark as Answer

Answer 6

Have you tried making the referenced views schema bound?

Or making a table valued function?