1. září 2010 20:31
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.
VIEW vw_CGCpos_RegisterStatus AS
. . .
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].
2. září 2010 6:39Moderátor
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 K. Suessmeyer http://blogs.msdn.com/Jenss
22. září 2010 8:38
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.
6. října 2010 21:55
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.
19. listopadu 2010 21:07
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?
2. srpna 2011 22:19
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.
If this answers your question, please Mark as Answer
27. dubna 2012 17:47
Have you tried making the referenced views schema bound?Or making a table valued function?
- Upravený Rogge 27. dubna 2012 19:20