Hint 'noexpand' on object 'View_CMS_Tree_Joined' is invalid. Kentico 9

Scott L asked on December 5, 2018 14:56

Hi All,

I'm about to try an upgrade from Kentico 9 to 10. I had to change a column from varchar to nvarchar.

To do this i had to update an index so it didn't use the column and then remove a view, then re add it after I updated the column.

Now when I go onto the site I get this error:

Hint 'noexpand' on object 'View_CMS_Tree_Joined' is invalid. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Hint 'noexpand' on object 'View_CMS_Tree_Joined' is invalid.

How do I fix this so I can start the upgrade?

Thanks, Scott

Recent Answers


Juraj Ondrus answered on December 6, 2018 14:15

Hi,
It looks like you are missing some indexes. Could you please check you have these indexes in the View_CMS_Tree_Joined view:
IX_View_CMS_Tree_Joined_ClassName_NodeSiteID_DocumentForeignKeyValue_DocumentCulture and
IX_View_CMS_Tree_Joined_NodeSiteID_DocumentCulture_NodeID? If not, you can create them (sorry for the formatting):

SET ARITHABORT ON SET CONCAT_NULL_YIELDS_NULL ON SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON SET NUMERIC_ROUNDABORT OFF GO

/****** Object: Index [IX_View_CMS_Tree_Joined_NodeSiteID_DocumentCulture_NodeID] Script Date: 06.12.2018 14:13:04 ******/ CREATE UNIQUE CLUSTERED INDEX [IX_View_CMS_Tree_Joined_NodeSiteID_DocumentCulture_NodeID] ON [View_CMS_Tree_Joined] ( [NodeSiteID] ASC, [DocumentCulture] ASC, [NodeID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO

SET ARITHABORT ON SET CONCAT_NULL_YIELDS_NULL ON SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON SET NUMERIC_ROUNDABORT OFF GO

/****** Object: Index [IX_View_CMS_Tree_Joined_ClassName_NodeSiteID_DocumentForeignKeyValue_DocumentCulture] Script Date: 06.12.2018 14:13:10 ******/ CREATE NONCLUSTERED INDEX [IX_View_CMS_Tree_Joined_ClassName_NodeSiteID_DocumentForeignKeyValue_DocumentCulture] ON [View_CMS_Tree_Joined] ( [ClassName] ASC, [NodeSiteID] ASC, [DocumentForeignKeyValue] ASC, [DocumentCulture] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO

0 votesVote for this answer Mark as a Correct answer

   Please, sign in to be able to submit a new answer.