45

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].
David Gardiner
  • 16,892
  • 20
  • 80
  • 117
Adel Khayata
  • 2,717
  • 10
  • 28
  • 46
  • 1
    Step one: show an example of one of the views, and the specific error. Also keep in mind that just because the database "works", doesn't mean you don't have real errors and warnings you didn't know about before. – John Saunders Nov 14 '12 at 09:31
  • I have updated the question with an example. – Adel Khayata Nov 14 '12 at 12:06

10 Answers10

46

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

Jojodmo
  • 23,357
  • 13
  • 65
  • 107
Gwasshoppa
  • 884
  • 12
  • 18
  • Thanks for this - I'm manually building up a database project for an Azure SQL Data Warehouse just for source control and this is exactly what I needed – How 'bout a Fresca Feb 23 '16 at 15:28
  • 2
    Make sure you add a file of type schema. If you add a file of type 'script' (a normal file with .sql extension) in wich you create the schema the unsolved reference problem will persist – DiederikTiemstra Sep 07 '17 at 10:07
30

I solved this issue.

It seems a few of my views/SPs have 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

mybrave
  • 1,662
  • 3
  • 20
  • 37
Adel Khayata
  • 2,717
  • 10
  • 28
  • 46
  • 3
    Just in case anyone sees this error and the above does not do it for you...there is also a chance you'll see this error if you have a VS DB project and the columns you're referring to in the object where the error exists do not match the table definitions you have checked into TFS (as opposed to what the actual columns are). HTH. – Sanjay Uttam Mar 19 '13 at 13:16
  • 1
    This is the workaround that I came up with as well, but I would not exactly call it a solution if you have touch the code for hundreds of database objects. – cdonner Sep 04 '13 at 15:26
  • 1
    if you use this workaround you may run into issues if the same SchemaName.TableName is used in the main DB. – H A Aug 23 '16 at 00:26
17

In my case, the function that couldn't be found was of Build Action=None and so it wasn't being included in the compile.

Changing the Build Action to Build corrected this.

fiat
  • 15,501
  • 9
  • 81
  • 103
12

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.

ClarenceG
  • 141
  • 1
  • 8
  • 1
    I've tried it. Indeed the errors went away, but the Schema compare is than broken. It doesn't recognize the structure within the project anymore.... – AXMIM Apr 27 '17 at 16:20
7

To reference another sqlproj's schema and use three-part naming, modify your .sqlproj file to add a DatabaseVariableLiteralValue element on the referenced project.

In within the element like <ProjectReference Include="..\SomeDatabaseProject\SomeDatabaseProject.sqlproj">, add the following:

<DatabaseVariableLiteralValue>SomeDatabaseProject</DatabaseVariableLiteralValue>

For example:

    <ProjectReference Include="..\SomeDatabaseProject\SomeDatabaseProject.sqlproj">
      <Name>SomeDatabaseProject</Name>
      <Project>{some-project-guid}</Project>
      <Private>True</Private>
      <DatabaseVariableLiteralValue>SomeDatabaseProject</DatabaseVariableLiteralValue>
    </ProjectReference>

Then you can use clean, three-part naming references like SomeDatabaseProject.SomeSchema.SomeTable.

Sorensen
  • 833
  • 1
  • 9
  • 13
1

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.

Nilanjan Pal
  • 11
  • 1
0

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];
superlogical
  • 14,332
  • 9
  • 66
  • 76
0

You will also get this error when you add a table (using .sql file ) and if you do not check-in the .sqlproj file

Dev
  • 1,451
  • 20
  • 30
0

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
Fetchez la vache
  • 4,940
  • 4
  • 36
  • 55
0

I have a DacPac project containing objects which use three part naming to refer to the containing database (hundreds of instances such as [thisDb].[dbo].[obj]* exist). I need compare and update this database, but the db project fails to build due to 200+ sql71561 errors.

I did not want to remove the unnecessary database name part or switch to using a database name variable. To successfully build, (properly) compare, and then update a database using three part naming or fully qualified naming to refer to itself, there is a way to pacify visual studio:

  1. Create a copy of the original db project.
  2. In the copy db project, update all local database object references to use just two part names ([dbo].[obj]) instead of three part names (I used find & replace).
  3. Make sure the copy db project targets the same SQL server version and builds successfully.
  4. Reference the copy db project from the original db project (whether via database variable, database name only, or dacpac).
  5. The original db project can now build because its references can be resolved.
  6. The original db project can now be used to update the actual database in SQL Server after a compare.
  7. A recompare after the update shows a flawless victory.
colbybhearn
  • 452
  • 9
  • 21