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

Chamara Janaka asked on November 21, 2016 00:49

I upgraded Kentico from version 8.2 to 9.0. Now getting the following 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.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Hint 'noexpand' on object 'View_CMS_Tree_Joined' is invalid.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) +392 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +815 System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +4515 System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() +61 System.Data.SqlClient.SqlDataReader.get_MetaData() +138 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +6738869 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) +6741487 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite) +586 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +107 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +288 System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +180 System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +21 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +325 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +420 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +278 CMS.DataEngine.AbstractDataConnection.ExecuteQuery(String queryText, QueryDataParameters queryParams, QueryTypeEnum queryType, Boolean requiresTransaction) +261

SELECT [NodeAliasPath], [DocumentURLPath], [NodeID], [DocumentCulture] FROM View_CMS_Tree_Joined AS V WITH (NOLOCK, NOEXPAND) LEFT OUTER JOIN COM_SKU AS S WITH (NOLOCK) ON V.NodeSKUID = S.SKUID WHERE (NodeSiteID = 1) AND ([DocumentURLPath] LIKE @DocumentURLPath OR [DocumentURLPath] LIKE @DocumentURLPath1)

Caused exception: Hint 'noexpand' on object 'View_CMS_Tree_Joined' is invalid. ] CMS.DataEngine.AbstractDataConnection.HandleError(String queryText, Exception ex) +181 CMS.DataEngine.AbstractDataConnection.ExecuteQuery(String queryText, QueryDataParameters queryParams, QueryTypeEnum queryType, Boolean requiresTransaction) +776 CMS.DataEngine.GeneralConnection.RunQuery(QueryParameters query) +383 CMS.DataEngine.GeneralConnection.ExecuteQuery(QueryParameters query) +401 CMS.DataEngine.GeneralConnection.ExecuteQuery(QueryParameters query, Int32& totalRecords) +75 CMS.DataEngine.DataQueryBase1.GetDataFromDBInternal() +143 CMS.DataEngine.DataQueryBase1.GetDataFromDB() +96 CMS.DataEngine.DataQueryBase1.GetData() +149 CMS.DataEngine.DataQueryBase1.get_Result() +114 CMS.DataEngine.ObjectQueryBase2.GetResults(IDataQuery query, Int32& totalRecords) +41 CMS.DataEngine.DataQueryBase1.GetDataFromDB() +128 CMS.DocumentEngine.DocumentQueryBase2.

kentico 9 upgrade

Recent Answers

Anton Grekhovodov answered on November 21, 2016 05:38

Hi Chamara,

In Kentico 9 View_CMS_Tree_Joined is indexed, but in Kentico 8, it isn't. So you need to check that this view has indexes. If not, try no create them:

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] ******/

CREATE NONCLUSTERED INDEX [IX_View_CMS_Tree_Joined_ClassName_NodeSiteID_DocumentForeignKeyValue_DocumentCulture] ON [dbo].[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

CREATE UNIQUE CLUSTERED INDEX [IX_View_CMS_Tree_Joined_NodeSiteID_DocumentCulture_NodeID] ON [dbo].[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

1 votesVote for this answer Mark as a Correct answer

Chamara Janaka answered on November 21, 2016 05:56 (last edited on November 21, 2016 05:56)

Hi Anton,

I executed those commands manually but still couldn't get it to work.

Now when I check the script log all the insert statements to "INSERT INTO [Temp_FormDefinition]" have failed. When I check the database there is no table called "Temp_FormDefinition" either. Why the db script is failing like this? I was hoping for a smooth upgrade unfortunately, not the case.

0 votesVote for this answer Mark as a Correct answer

Anton Grekhovodov answered on November 21, 2016 07:33

Try to execute this code in SQL management studio:

SELECT [NodeAliasPath], [DocumentURLPath], [NodeID], [DocumentCulture] FROM View_CMS_Tree_Joined AS V WITH (NOLOCK, NOEXPAND)

It must work after executing the scripts.

I don't know why insert statements are failed. Did you upgrade Kentico step by step as it's described in documentation? Did you have any errors during this process?

0 votesVote for this answer Mark as a Correct answer