<p>FindPagesWithCriteria will result in an SQL exception if a page is set to be archived under itself.</p>

By
Øyvind Tanum
4/29/2012
1 min

I got an SQL exception when using FindPagesWithCriteria with the PageArchiveLink property included in a criteria. After some initial debugging I got the following exception message:

"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression".

Debugging the stored procedure

The CriteriaCollection was built the ordinary way:new PropertyCriteria { Name = "PageArchiveLink"....}

The problem was obviously located on the database level, so I used the the SQL Server Profiler to get the exact database call.

The PageArchiveLink property has special handling in EPiServers FindPagesWithCriteria stored procedure, and after a while I isolated the error to:SELECT tblPageLanguage.fkPageID FROM tblPageLanguageINNER JOIN tblTree ON tblTree.fkChildID=tblPageLanguage.fkPageIDINNER JOIN tblPage ON tblPage.pkID=tblPageLanguage.fkPageIDWHERE tblTree.fkParentID=@PageID AND ((SELECT pkID FROM tblPage WHERE PageGUID = tblPage.ArchivePageGUID)<>@PageLink) OR (@PageLink IS NULL AND NOT tblPage.ArchivePageGUID IS NULL)

In my case I had several pages with PageArchiveLink set to self references (i.e. the page is set to be archived under itself) and that caused the SQL exception. You get the SQL exception when you are i.e. are comparing [1,2] to 1.

The solution

To find PageArchiveLink self references, run this query:SELECT pkID FROM tblPage WHERE PageGUID = tblPage.ArchivePageGUID

To avoid this, EPiServer could implement a check to disallow self references in the PageArchiveLink property when a page is saved.

This problem was encountered in EPiServer version CMS 6 R2 (6.1.379.501).