Hint 'noexpand' on object 'View_CMS_Tree_Joined' is invalid (Kentico 11)

Sharon Parry asked on July 19, 2019 10:32

This is a NEW site - not an upgrade. Version 11.0.14. View_CMS_Tree_Joined has definitely not been modified in any way.

Database has deployed to the LIVE server and the site just throws the subject error.

Have read the few other question posts that I could find talking about this error, but they are all talking about sites that have either been upgraded, or where fields have been modified - which is not the case here.

I don't have SQL Server Management Studio access to the database on the live server.

Previously I've deployed 2 other databases (for 2 other websites - both version 11) to this same server without any problems.

kentico 11

Recent Answers

Dmitry Bastron answered on July 19, 2019 13:12

Hi Sharon,

Is it MS Azure database? Could have been issue with collation. Check if your local and deployed database have the same SQL_Latin1_General_CP1_CI_AS collation. Read more here.

0 votesVote for this answer Mark as a Correct answer

Peter Mogilnitski answered on July 19, 2019 15:54 (last edited on July 19, 2019 19:59)

It seems like you are missing indexes on View_CMS_Tree_Joined. Does admin work on your site? You may try to go to reporting and run report with SQL (if account in your CMSConnectionString has enough permissions):

SELECT o.name as view_name, i.name as index_name
    FROM sysobjects o INNER JOIN sysindexes i ON o.id = i.id 
    WHERE o.xtype = 'V' and o.name = 'View_CMS_Tree_Joined'

you should have at least 2 default Kentico indexes on your view:

IX_View_CMS_Tree_Joined_NodeSiteID_DocumentCulture_NodeID IX_View_CMS_Tree_Joined_ClassName_NodeSiteID_DocumentForeignKeyValue_DocumentCulture

0 votesVote for this answer Mark as a Correct answer

Sharon Parry answered on July 19, 2019 20:59

Thanks for your replies Dmitry and Peter, however there was nothing required to be done.

Problem resolved! The DBA deleted the database and recreated it from scratch from the backup I had supplied. This IS a MS Azure SQL Server, Dmitry, so possibly something to do with the way it had been setup.

0 votesVote for this answer Mark as a Correct answer

Dmitry Bastron answered on July 22, 2019 10:17

I would rather check collation setting asap anyway. If it's not SQL_Latin1_General_CP1_CI_AS you will encounter some random bugs, i.e. cannot move the pages from one location to another and so on.

0 votesVote for this answer Mark as a Correct answer