Lesson Learned #287: Contains an unresolved reference to an object due to database collation

[Guest Jose_Manuel_Jurado]

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 - Microsoft Community Hub 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!

 

Continue reading...