SPList.GetItems(SPQuery) appears to miss items if paging info not precise

We have some code that uses SPList.GetItems(SPQuery) with paging info.

The list items we were running against had the following values:

ID   Created
--   -------
51   8/30/2016 8:02:40 AM
49   8/12/2016 2:42:26 PM
48   6/27/2016 3:39:05 AM
46   6/21/2016 4:04:28 PM
45   6/21/2016 3:10:18 PM
44   6/21/2016 2:32:13 PM
43   6/21/2016 3:56:01 AM
42   6/20/2016 6:32:34 AM
41   6/20/2016 4:55:47 AM
39   6/14/2016 1:30:05 PM <-- The last item on page 1
38   6/14/2016 11:34:51 AM <-- Should be the first item on page 2
37   6/14/2016 10:21:22 AM <-- With our bad code, this one is first on page 2
36   6/14/2016 8:48:43 AM
35   6/9/2016 12:56:16 AM
32   5/25/2016 11:38:44 PM
29   5/9/2016 9:43:31 AM
28   5/9/2016 9:35:38 AM
26   4/28/2016 8:19:59 AM

Due to a bug in our code (timezone conversion!), we were providing an SPListItemCollectionPosition containing an incorrect value for the Created property. As a result, SPList.GetItems gets the first row that matches both criteria. This leads to the appearance that the paginated data is missing rows.

For example:

$webUrl = 'some-web-url';

$rowLimit = 10;

$goodPage = 'Paged=TRUE&p_ID=39&p_Created=20160614%2013%3A30%3A05';

$badPage  = 'Paged=TRUE&p_ID=39&p_Created=20160614%2011%3A30%3A05';

$web = Get-SPWeb $webUrl;

$list = $web.Lists['some-list'];

$spquery = New-Object Microsoft.SharePoint.SPQuery;

$spquery.Query = "<OrderBy><FieldRef Name='Created' Ascending='False' /></OrderBy>";

$spquery.RowLimit = $rowLimit;

$spquery.ListItemCollectionPosition = New-Object Microsoft.SharePoint.SPListItemCollectionPosition($goodPage);

$list.GetItems($spquery) | Format-Table ID, Name, @{l='Created'; e={$_['Created']}} -AutoSize;

$spquery.ListItemCollectionPosition = New-Object Microsoft.SharePoint.SPListItemCollectionPosition($badPage);

$list.GetItems($spquery) | Format-Table ID, Name, @{l='Created'; e={$_['Created']}} -AutoSize;

$list.GetItems("Id", "Created", "Name") | Format-Table ID, Name, @{l='Created'; e={$_['Created']}} -AutoSize;

Using the above powershell, the results from $goodPage works as expected. However, with $badPage, the time is off by 2 hours (we provided 11:30:05 instead of 13:30:05). Since we are sorting descending by Created, the wrong page info value causes the entry at 11:34:51 to be skipped as well. Oops!

References:

• SPListItemCollectionPosition class
• Learning SharePoint Part VII - List Pagination

The field with Id {field-GUID} defined in feature {feature-GUID} was found in the current site collection or in a subsite.

One of my site collection features is failing to activate and throws this exception:

SPException thrown: Message: The field with Id {field-GUID} defined in feature {feature-GUID} was found in the current site collection or in a subsite.. Stack:   
 at Microsoft.SharePoint.Utilities.SPUtility.ThrowSPExceptionWithTraceTag(UInt32 tagId, ULSCat traceCategory, String resourceId, Object[] resourceArgs)    
 at Microsoft.SharePoint.Administration.SPElementDefinitionCollection.ProvisionFieldsAndContentTypes(SPFeaturePropertyCollection props, SPSite site, SPWeb web, SPFeatureActivateFlags activateFlags, Boolean fForce)    
 at Microsoft.SharePoint.Administration.SPElementDefinitionCollection.ProvisionElements(SPFeaturePropertyCollection props, SPWebApplication webapp, SPSite site, SPWeb web, SPFeatureActivateFlags activateFlags, Boolean fForce)    
 at Microsoft.SharePoint.SPFeature.Activate(SPSite siteParent, SPWeb webParent, SPFeaturePropertyCollection props, SPFeatureActivateFlags activateFlags, Boolean fForce)    
 at Microsoft.SharePoint.SPFeatureCollection.AddInternal(SPFeatureDefinition featdef, Version version, SPFeaturePropertyCollection properties, SPFeatureActivateFlags activateFlags, Boolean force, Boolean fMarkOnly)    
 at Microsoft.SharePoint.SPFeatureCollection.AddInternalWithName(Guid featureId, Int32 compatibilityLevel, String featureName, Version version, SPFeaturePropertyCollection properties, SPFeatureActivateFlags activateFlags, Boolean force, Boolean fMarkOnly, SPFeatureDefinitionScope featdefScope)    
 at Microsoft.SharePoint.WebControls.FeatureActivator.ActivateFeature(Guid featid, Int32 compatibilityLevel, SPFeatureDefinitionScope featdefScope)    
 at Microsoft.SharePoint.WebControls.FeatureActivatorItem.ToggleFeatureActivation()    
 at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)    
 at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)    
 at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)    
 at System.Web.UI.Page.ProcessRequest()    
 at System.Web.UI.Page.ProcessRequest(HttpContext context)    
 at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()    
 at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)    
 at System.Web.HttpApplication.PipelineStepManager.ResumeSteps(Exception error)    
 at System.Web.HttpApplication.BeginProcessRequestNotification(HttpContext context, AsyncCallback cb)    
 at System.Web.HttpRuntime.ProcessRequestNotificationPrivate(IIS7WorkerRequest wr, HttpContext context)    
 at System.Web.Hosting.PipelineRuntime.ProcessRequestNotificationHelper(IntPtr rootedObjectsPointer, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)    
 at System.Web.Hosting.PipelineRuntime.ProcessRequestNotification(IntPtr rootedObjectsPointer, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)    
 at System.Web.Hosting.UnsafeIISMethods.MgdIndicateCompletion(IntPtr pHandler, RequestNotificationStatus& notificationStatus)    
 at System.Web.Hosting.UnsafeIISMethods.MgdIndicateCompletion(IntPtr pHandler, RequestNotificationStatus& notificationStatus)    
 at System.Web.Hosting.PipelineRuntime.ProcessRequestNotificationHelper(IntPtr rootedObjectsPointer, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)    
 at System.Web.Hosting.PipelineRuntime.ProcessRequestNotification(IntPtr rootedObjectsPointer, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)  

I found a lot of references out there saying to redeploy the solution or use Overwrite="TRUE" in the field definition. However, not satisfied with either, I dug deeper.

I put this powershell together to figure out where the fields are coming from:

$siteUrl = 'site-collection-url'

$fieldId = 'field-GUID'

$fieldInternalName = 'field-internal-name'

$site = Get-SPSite $siteUrl

$webFields = @()

foreach ($web in $site.AllWebs) {

  foreach ($field in $web.Fields) {

    $webField = New-Object PSObject -Property ([ordered]@{

      WebUrl = $web.Url

      FieldId = $field.Id

      FieldInternalName = $field.InternalName

      FieldTitle = $field.Title

    })

    $webFields += $webField

  }

}

$webFields | Where-Object {$_.FieldId -eq $fieldId -or $_.FieldInternalName -eq $fieldInternalName} | Format-Table -AutoSize

From the results, I found 2 subwebs that contained this same definition. It looks like these subwebs were migrated into that site collection. Is is possible that this was done before the site collection feature was activated. The migration process must have created the fields at the subweb level.

Going to each of the subwebs listed by the powershell, I was able to go to Site Settings->Site Columns, locate my field, and delete it, Once these were deleted, I was able to activate my site collection feature.

Throttled:Query exceeds lookup column threshold

One of my apps gets its data via lists.asmx. In a particular environment, no data was being returned and I found these entries in ULS:

Throttled:Query exceeds lookup column threshold. List item query elapsed time: 0 milliseconds, Additional data (if available): Query HRESULT: 80070093 List internal name, flags, and URL: {list-guid}, flags=0x0008000000c4108c, URL="web-url/_vti_bin/lists.asmx" Current User: {some-id} Query XML: "<Query><OrderBy><FieldRef Name="ContentType"/></OrderBy></Query>" SQL Query: "N/A"

The query cannot be completed because the number of lookup columns it contains exceeds the lookup column threshold enforced by the administrator.

Query exceeds lookup column threshold. List: {list-guid}, View: , ViewXml: <View Scope="RecursiveAll" IncludeRootFolder="True"><Query><OrderBy><FieldRef Name="ContentType" /></OrderBy></Query><ViewFields>... and a whole ton of <FieldRef> entries.

SOAP exception: Microsoft.SharePoint.SPQueryThrottledException: The query cannot be completed because the number of lookup columns it contains exceeds the lookup column threshold enforced by the administrator. ---> System.Runtime.InteropServices.COMException (0x80070093): The query cannot be completed because the number of lookup columns it contains exceeds the lookup column threshold enforced by the administrator.    

I found a few references to this, but it is not the more common 5000 item List View Threshold throttling. Instead this is the List View Lookup Threshold setting of 8 (12 in SharePoint 2013).

It turns out that my app is using the default view and not specifying which fields to return in the query. In fact, there is a Level=Medium ULS log entry that has my exact query. Putting this into powershell as follows, I was able to confirm the behaviour:

$webUrl = 'some-web-url'

$listName = '{list-guid}' # note: keep the curly braces

$credential = Get-Credential -Message "Enter your login for $($webUrl)"

$uri = [uri]"$($communityRoot)/_vti_bin/lists.asmx"

$contentType = 'text/xml'

$bodyString = @"

<?xml version="1.0" encoding="utf-8"?>

  <soap12:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap12="http://www.w3.org/2003/05/soap-envelope">

    <soap12:Body>

      <GetListItems xmlns="http://schemas.microsoft.com/sharepoint/soap/">

        <listName>$($listName)</listName>

        <viewName></viewName>

        <rowLimit>5000</rowLimit>

        <query>

          <Query xmlns="">

            <OrderBy> <FieldRef Name="ContentType" /> </OrderBy>

          </Query>

        </query>

      <viewFields><ViewFields xmlns="" /></viewFields>

      <queryOptions>

        <QueryOptions xmlns="http://schemas.microsoft.com/sharepoint/soap/">

          <IncludeMandatoryColumns>FALSE</IncludeMandatoryColumns>

          <IncludeAttachmentUrls>TRUE</IncludeAttachmentUrls>

          <ViewAttributes Scope='RecursiveAll' IncludeRootFolder='True' />

          <DateInUtc>TRUE</DateInUtc>

        </QueryOptions>

      </queryOptions>

    </GetListItems>

  </soap12:Body>

</soap12:Envelope>

"@

$result = Invoke-WebRequest -Credential $credential -Method Post -Uri $uri -Body $bodyString -ContentType $contentType

$result.RawContent

To confirm the view and the number of lookup columns, I used this powershell:

$webUrl = 'some-web-url'

$web = Get-SPWeb $webUrl

$list = $web.Lists['Shared Documents']

$fields = $list.Fields

$defaultViewXml = [xml]$list.DefaultView.GetViewXml()

Write-Output "Default View: $($list.DefaultView.Title)"

$defaultViewXml.View.ViewFields.FieldRef | Format-Table @{l="Type"; e={$fields.GetField($_.Name).TypeDisplayName}}, Name -AutoSize -Wrap

Reference: This is a very well written article on GetListItems - https://msdn.microsoft.com/en-us/library/websvclists.lists.getlistitems(v=office.14).aspx

Powershell Out-GridView: Column title ending in slash ('/') causes values not to show

I was trying to spit out a DataTable in Out-GridView where the column headers happen to be URLs. I noticed that sometimes the values of the columns are not displaying in Out-GridView when the column title ends in a slash.

eg:

$dt = New-Object System.Data.DataTable
$dt.Columns.Add("ColumnA")
$dt.Columns.Add("ColumnB/")
$dt.Rows.Add("row1a", "row1b")
$dt | Format-Table -AutoSize
$dt | Out-GridView


The output of $dt | Format-Table -AutoSize is:

ColumnA ColumnB/
------- --------
row1a   row1b


The output of $dt | Out-GridView is:

It looks like there is a bug in GridView there. For now, I can append .Trim('/') to my column titles.

It looks like trailing whitespace is a problem too: https://windowsserver.uservoice.com/forums/301869-powershell/suggestions/11718579-out-gridview-fails-to-display-data-for-properties

Get WebTemplate / WebTemplateId from page source

Noting this down so I remember.

1. Go to page
2. View source
3. Look for g_wsaSiteTemplateId

Reference: https://nickhobbs.wordpress.com/2013/05/18/sharepoint-how-to-check-which-site-template-was-used-to-create-a-site-just-using-a-web-browser/