SQL71561 error - ambiguous reference

Question

Error   431 SQL71561: 
Computed Column: [dbo].[ViewAlertFrequency].[BeginDate] 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: 
[Lookup].[dbo].[AlertFrequency].[AlertFrequency]::[BeginDate] 
or [Lookup].[dbo].[AlertFrequency].[BeginDate]  

The current database is Lookup, so there shouldn't be any issues finding this object. This is imported from an existing view. It appears that the whole database is like this. I'm not sure what the :: syntax is.

Here is the view:

CREATE VIEW [dbo].[ViewAlertFrequency]

AS

--###
--ViewAlertFrequency
--###

--###
--used by: eobResolve
--###

SELECT DISTINCT TOP 100
    AlertFrequency.[ID]     AS [ID],
    AlertFrequency.Code     AS Code,
    AlertFrequency.[Name]       AS [Name],
    AlertFrequency.[Description]    AS [Description],
    AlertFrequency.[Rank]       AS [Rank],
    AlertFrequency.BeginDate    AS BeginDate,
    AlertFrequency.EndDate      AS EndDate

FROM         
    Lookup.dbo.AlertFrequency   AS AlertFrequency

ORDER BY
    AlertFrequency.[Rank]       ASC


GO
GRANT SELECT
    ON OBJECT::[dbo].[ViewAlertFrequency] TO [eobResolve]
    AS [dbo];

Weapon

Have you seen this? – Tameka

@IswantoSan I tried using $(DatabaseName), but it still didn't work. I just changed the 3-part name to 2. – Weapon

Possible duplicate of VS 2012 Database Project "unresolved reference to object" Error – Methyl

Answer 1

You're using 3-part naming conventions in the "FROM" clause. Change "Lookup.dbo.AlertFrequency" to "dbo.AlertFrequency" and your problem should go away. I've had mixed results with this. Changing one of the DB options seemed to be enough to resolve those 3-part names, but I then ran into other issues with cross-database references (4-part names). I figured just changing the 3-part names to 2-part [schema].[object] naming would be the easier way to handle that.

The GRANT statement is just specifying that the permission is being granted to an object as opposed to a schema. I don't think that belongs in the object definition, but we handle our permissions in post-deploy scripts because they differ slightly from one environment to the next.

Lardon

Could you explain how you change this in the DB options? – Havenot

Not sure what you're trying to change w/ the DB options. If you want to ignore this warning, you can modify the project properties to ignore the numeric portion of the warning (71561) and if trying to ignore multiple, you can enter them as a CSV list. – Lardon