Finding Alias for SharePoint Email Enabled Lists

As usual, there are several ways to do this that can be readily found via a search

Powershell: Loop through each Web Application, Site Collection (SPSite), Subweb (SPWeb), List. Then look in the EmailAlias property for what you are searching. In large environments, I suspect that this would be very slow and resource intensive. Here is a Foreach-Object pipeline that would do the trick:

Get-SPWebApplication | ForEach-Object {$_.Sites} | ForEach-Object {$_.AllWebs} | ForEach-Object {$_.Lists} | Where-Object {$_.CanReceiveEmail -and $_.EmailAlias} | Format-Table EmailAlias, Title, ParentWebUrl -AutoSize

SQL: One way is to essentially do the above but via the content databases, but this one also seems resource intensive:

USE [Some_Content_DB]
SELECT tp_Title, tp_EmailAlias, FullUrl
  FROM [dbo].[AllLists] lists
  LEFT JOIN [dbo].[AllWebs] webs ON lists.tp_WebId = webs.Id
  WHERE tp_EmailAlias IS NOT NULL

SQL: I decided to see what the Microsoft SharePoint Foundation Incoming E-Mail timer job does (job-email-delivery) since the timer job should be quite fast. Looking at the code, I can see that it calls the proc_getEmailEnabledListByAlias stored procedure. Diving into that, I see that this may be a better query:

SELECT * FROM [SharePoint_Config].[dbo].[EmailEnabledLists] 

However, I did also run across a post mentioning that it is possible the Config database and the Content database can be out of sync. So, it may still be necessary to choose the correct method depending on the problem one is trying to tackle.

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

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

Lists.asmx GetListCollections and GetListItems example

I recently had to get all items of a list as well as all lists in a SPWeb. This involved calling Lists.asmx. For the service description of Lists.asmx just go to /_vti_bin/lists.asmx

Get all lists for some SPWeb

$webUrl = 'some-web-url'

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

$uri = [uri]"$($webUrl)/_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>
<GetListCollection xmlns="http://schemas.microsoft.com/sharepoint/soap/" />
</soap12:Body>
</soap12:Envelope>
"@

$result = Invoke-WebRequest -Credential $credential -Method Post -Uri $uri -Body $bodyString -ContentType $contentType
$xml = [xml]$result.Content
$xml.Envelope.Body.GetListCollectionResponse.GetListCollectionResult.Lists.List

Get all list items for some list

$webUrl = 'some-web-url'
$listName = 'some-list-name'

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

$uri = [uri]"$($webUrl)/_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
$xml = [xml]$result.Content
$xml.Envelope.Body.GetListItemsResponse.GetListItemsResult.listitems.data.ItemCount
$xml.Envelope.Body.GetListItemsResponse.GetListItemsResult.listitems.data.row | Format-Table ows_LinkFilename, ows_Author, ows_Created, ows_Modified -AutoSize

I could probably use New-WebServiceProxy and skip all the SOAP envelope stuff. However, this better mimics the code that I was trying to troubleshoot.