VS 2012 Database Project “unresolved reference to

Question

I created SQL Server Database Project in VS 2012 & imported our database. When I build the project, I get a lot of "unresolved reference to object" Errors. These errors are only for a few views I have in my database. The syntax for these views are correct & I am not using temp tables. What should I check to solve this issue?

UPDATE: This is one example:

    CREATE view [Factory].[NonStartedOrders]
as
 SELECT 

"Customers"."CustomerName", "Customers"."CustomerAName",
"Customers"."MarketID",
"Orders"."OrderID", 
"Orders"."ApproveDate", 
"FactoryOrders"."FactoryID", 
"FactoryOrders"."EstEndDate", 
"FactoryOrders"."StatusID", 
"FactoryOrders"."TotalWeight", 
"Karats"."KaratEName"

FROM   (("Taiba"."Sales"."FactoryOrders" "FactoryOrders" 
INNER JOIN "Taiba"."Sales"."Orders" "Orders" ON "FactoryOrders"."OrderID"="Orders"."OrderID") 
INNER JOIN "Taiba"."General"."Customers" "Customers" ON "Orders"."CustomerID"="Customers"."CustomerID") 
INNER JOIN "Taiba"."MasterPiece"."Karats" "Karats" ON "Orders"."MKaratID"="Karats"."KaratID"

"Taiba" here is my database name. I am getting 30 errors only for this view. These are a few errors of them:

Error   217 SQL71561: View: [Factory].[NonStartedOrders] has an unresolved reference to object [Taiba].[Sales].[FactoryOrders]

Error   219 SQL71561: View: [Factory].[NonStartedOrders] 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: [Taiba].[Sales].[FactoryOrders].[FactoryOrders]::[OrderID], [Taiba].[Sales].[FactoryOrders].[OrderID] or [Taiba].[Sales].[Orders].[FactoryOrders]::[OrderID].

Answer 1

One other possibility is that the schema you have used in your view/table etc does not exist in the project. You may need to add the schema to the VS Database Project.

Right Click the project and Add > New Item > Schema

Answer 2

It is possible that the objects are inside NotInBuild tag in the project file for naming or other issue. In my case I saved the file with ..sql and the extra dot was causing it to move under NotInBuild tag inside project file. I corrected the extension and moved the missing object under build tag and that resolved the issue.

Answer 3

I solved this issue.

It seems a few of my views/SPs has referenced the tables using this naming convention ( 3 parts qualified name ):

DatabaseName.SchemaName.TableName

I changed all references to be as the following:

SchemaName.TableName

Answer 4

This happened to me when building a CTE in Visual Studio 2015. I changed the Build Action to Compile and the errors went away.

1. Select the file(s) with the error
2. Press F4 to see the properties.
3. Select 'Compile' in the Build Action drop down list.

Hope this helps someone.

Answer 5

Try explicitly defining the class:

class: Specifies the class of the securable on which the permission is being granted. The scope qualifier :: is required.

Read the docs on the SQL GRANT statement: http://technet.microsoft.com/en-us/library/ms187965.aspx

In my case I had a User Defined Table Type.

GRANT EXECUTE ON TYPE::[dbo].[MessageTableType] TO [PostOffice_Role];

Answer 6

This may be an edge case but if in your object definition you are also documenting the object (we do, anyway...) using sp_addextendedproperty you can also get this error if you have stated an incorrect object type - which can happen if copy & pasting. The "unresolved reference to object" error makes sense here when you think about it.

For example the following will recreate the error as the level1type should be 'PROCEDURE' and not 'FUNCTION'.

EXEC sp_addextendedproperty
  @name = N'MS_Description',
  @value = N'Description of the stored procedure...',
  @level0type = N'SCHEMA',
  @level0name = N'dbo',
  @level1type = FUNCTION',
  @level1name = N'spMyStoredProcedureName'
GO