Lesson Learned #287: Contains an unresolved reference to an object due to database collation
Published Jan 17 2023 10:43 AM 1,349 Views

Today, we worked on a new service request that our customer got the following error message exporting the database to a BacPac: 

One or more unsupported elements were found in the schema used as part of a data package.
Error SQL71501: Error validating element [dbo].[Example]: View: [dbo].[Example] 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: [Schema1].[Table1]

Following I would like to share the troubleshooting steps done to find out this issue. 

 

We've published several articles about this error Lesson Learned #198: Getting the error "has an unresolved reference to object " exporting to bacpac ... But, in this situation, the issue is regarding about the impact that a database collection might have. 

 

We have this scenario:

 

  • We have two tables and a view.

 

 

CREATE TABLE Table1(
	[IDS] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE Table2(
	[ID] [int] NULL
) ON [PRIMARY]

CREATE or alter  VIEW [dbo].[Example]
as
   SELECT  
      fci.IDs
   from (
         SELECT AMR$2.IDs FROM Table1  AS AMR$2  
                           ,  Table2  AS ASIRV 
         ) fci
GO



 

 

 

If we run the view, for example, SELECT * from Example, everything is fine. But, for business reasons the table "Table1" needs to be rebuild again using this TSQL: 

 

 

CREATE TABLE table1(
	[IDS] [int] NULL
) ON [PRIMARY]
GO

 

 

As you could see the table "Table1" was created with "table1" and all views that we have is/are using the first letter of the name "Table1" and will report an issue at the moment of its execution. As side effect, when you need to export the database you are going to have the error message: 

 

Error SQL71501: Error validating element [dbo].[Example]: View: [dbo].[Example] 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: [Schema1].[Table1]

Following I would like to share the troubleshooting steps done to find out this issue. 

 

Enjoy!

 

Version history
Last update:
‎Jan 18 2023 03:06 AM
Updated by: