Remove items from a huge SharePoint list

At a certain client of mine, there was a huge list consisting over 67 million items. This is well over the suggested 30 million item limit. This list was filled with random information and was adding lots of data to the dbo.AllUserData table so we decided to remove it. After trying several approaches, we couldn’t get past the huge DELETE statement SQL builds in order to remove the list. This statement was so large, it even crashed the SQL server. One approach that worked was using the GUI to delete items. As you can guess, this would take a huge amount of mandays to click away the list ;-)

Thankfuly SharePoint accepts batch updating items so I’ve written a script to delete those items in no time (subject to your processing power).

$web = get-spweb http://
$list = $web.lists | ? { $_.title -eq "<<ListTitle>>" }
$spQuery = New-Object Microsoft.SharePoint.SPQuery
$spQuery.ViewAttributes = "Scope='Recursive'";
$spQuery.RowLimit = 100
$caml = '<OrderBy Override="TRUE"><FieldRef Name="ID"/></OrderBy>' 
$spQuery.Query = $caml 

do
{
    $listItems = $list.GetItems($spQuery)
    $count = $listItems.Count
    $spQuery.ListItemCollectionPosition = $listItems.ListItemCollectionPosition
    $batch = "<?xml version=`"1.0`" encoding=`"UTF-8`"?><Batch>"
    $j = 0
    for ($j = 0; $j -lt $count; $j++)
    {
        $item = $listItems[$j]
        write-host "`rProcessing ID: $($item.ID) ($($j+1) of $($count))" -nonewline
        $batch += "<Method><SetList Scope=`"Request`">$($list.ID)</SetList><SetVar Name=`"ID`">$($item.ID)</SetVar><SetVar Name=`"Cmd`">Delete</SetVar><SetVar Name=`"owsfileref`">$($item.File.ServerRelativeUrl)</SetVar></Method>"
        if ($i -ge $count) { break }
    }
    $batch += "</Batch>"

    write-host

    write-host "Sending batch..."
    $result = $web.ProcessBatchData($batch)

    write-host "Emptying Recycle Bin..."
    $web.RecycleBin.DeleteAll()
}
while ($spQuery.ListItemCollectionPosition -ne $null)
$web.Dispose()

Using CSOM in a Windows 8 app for SharePoint Online

Reblogged from Nicolas Peters:

Click to visit the original post

Working with the Client-Side Object Model is rather easy and that's why I looked for a solution to use it in a Windows 8 app.

There are two assemblies to be referred for working with the CSOM.

  • Microsoft.SharePoint.Client.dll
  • Microsoft.SharePoint.Client.Runtime.dll

Creating the app

Let's start by creating a new Class Library for Windows Store apps.
In here we add the references to the SharePoint assemblies.

Read more… 262 more words

Great find by Nicolas for using CSOM in a Windows 8 app!

SharePoint survey only returns 30 items

While working with surveys and modifying them, I’ve encountered the following issue: When a user uses the “Export to Spreadsheet” function offered by SharePoint, his/hers export will only contain a maximum of 30 items, even if the survey contains more than 30 responses.

The survey list is a special list. It’s impossible to add new views to the survey list, but it’s possible to modify the existing views because all they actually use is the list view web part. You can simply modify an existing view by clicking “Site Actions -> Edit Page”.

When we started working on the survey to alter some behaviour by using jQuery, we didn’t notice any difference at first. However, when a user started using the export functionality, we scratched our heads.

Checking out the source of the overview.aspx page made us much wiser. This is how a correct survey overview.aspx source looks like. We’ve opened up the page with SharePoint designer and switched to code view:

edit in advanced mode

correct survey view

Then we looked at our “corrupt” survey’s overview.aspx:

corrupt list view source

When you check the ListViewXml from the “bad” source, you’ll find a xml tag “RowLimit” set to 30. This is a standard value, but apparently the “corrupt” list view uses it to export all the results to excel. When you update this value to a higher number, the results will flow back in perfectly.

Similar problems have been reported on the following blog: http://blogs.msdn.com/b/bettertogether/archive/2010/09/16/issues-caused-by-removing-web-parts-from-standard-survey-pages.aspx

Make the ribbon scroll with your page, without editing the masterpage file

I was looking for an answer on the following question: How can I make the ribbon scroll with my page, instead of hovering on top of the workspace. Numerous blog posts mention how you can do this, by editing lots of stuff. While actually it’s quite simple. Just add the following CSS:

<style type="text/css">
body {
    overflow: auto !important;
}

#s4-workspace {
    overflow: inherit !important;
}
</style>

That’s it! No ugly modifications to masterpages or javascript injections.

As result we go from:

workspace scroll

to:

ribbon scroll

Passing the result from a dialog to the parent page

Sometimes you want to communicate with the parent page of your dialog in order to pass on the result of your work. Thankfully the SP.UI.ModalDialog framework contains the commonModalDialogClose method. The javascript CSOM has all the tools in place to construct the following application:

  • Add a button to the ribbon. This button will also open the dialog.
  • Create an application page that will be used as a dialog. This application page has a text box and a button.
  • Add javascript to capture the result of the application page, in this case just text, and put it where our cursor resides on the parent page.

First things first, we’ll add a button to our ribbon.

ribbonbutton

Since there are many tutorials available, I won’t go into detail here. Simply add an empty element to your project and replace the contents with this one:

<?xml version="1.0" encoding="utf-8"?>
<Elements xmlns="http://schemas.microsoft.com/sharepoint/">
  <CustomAction
    Id="bramnuyts.InsertButton"
    Location="CommandUI.Ribbon"
    Title="InsertRibbon Button">
    <CommandUIExtension>
      <CommandUIDefinitions>
        <CommandUIDefinition
          Location="Ribbon.EditingTools.CPInsert.Groups._children">
          <Group
            Id="Ribbon.EditingTools.CPInsert.InsertGroup"
            Sequence="1"
            Description="Insert"
            Title="Insert"
            Template="Ribbon.Templates.Flexible2"
            >
            <Controls Id="Ribbon.EditingTools.CPInsert.InsertGroup.Controls">
              <Button Id="Ribbon.EditingTools.CPInsert.InsertGroup.InsertButton"
                Command="InsertCommand"
                Image16by16="/_layouts/images/removeStylesHS.png"
                Image32by32="/_layouts/images/removeStylesHH.png"
                LabelText="Insert"
                TemplateAlias="o2" />
            </Controls>
          </Group>
        </CommandUIDefinition>
        <CommandUIDefinition
          Location="Ribbon.EditingTools.CPInsert.Scaling._children">
          <MaxSize
            Id="Ribbon.EditingTools.CPInsert.InsertGroup.MaxSize"
            Sequence="15"
            GroupId="Ribbon.EditingTools.CPInsert.InsertGroup"
            Size="LargeLarge" />
        </CommandUIDefinition>
      </CommandUIDefinitions>
      <CommandUIHandlers>
        <CommandUIHandler
          Command="InsertCommand"
          CommandAction="javascript:insert();" />
      </CommandUIHandlers>
    </CommandUIExtension>
  </CustomAction>
  <CustomAction
    Id="InsertCommand.Script"
    Location="ScriptLink"
    ScriptSrc ="/_layouts/DialogResult/insert.js" />
</Elements>

I added the javascript in a separate javascript file “insert.js”. I like the ScriptLink approach as it doesn’t dirty the elements.xml file. However there are some reported issues when using ScriptLinks so go for the approach you feel is the most safe.

insert.js

Inside my insert.js file I have 2 methods: addElement(txt) and insert(). The “insert()” method will be responsible for handling the click on the button and creating the dialog. The “addElement(txt)” method will be responsible for adding content to the rich content page. Once again SharePoint is there to help us, this time it’s the “RTE” class, which points to the Rich Text Editor. By using the “RTE.Cursor.get_range()” method, we’re able to determine where a user has put his cursor.

function addElement(txt) {
    var range = RTE.Cursor.get_range();
    range.deleteContent();
    var selection = range.parentElement();
    if (!selection) {
        return;
    }
    var span = selection.ownerDocument.createElement('span');
    span.innerText = txt;
    range.insertNode(span);
    RTE.Cursor.get_range().moveToNode(span);
    RTE.Cursor.update();
}

function insert() {
    var options = {
        url: SP.Utilities.Utility.getLayoutsPageUrl('/DialogResult/insert.aspx'),
        title: 'Insert',
        allowMaximize: false,
        showClose: true,
        width: 300,
        height: 150,
        dialogReturnValueCallback: Function.createDelegate(null, function (result, returnValue) {
            if (result == SP.UI.DialogResult.OK) {
                    addElement(returnValue);
            }
        })
    };

    SP.UI.ModalDialog.showModalDialog(options);
}

Now that we have our button and the javascript to handle the dialog, we have to create our dialog page.

insert.aspx

I added a text box and a button to the main contentplaceholder:

insert.aspx.main

Add the “Button1_Click” OnClick method to the button and add the following code to the code behind:

protected void Button1_Click(object sender, EventArgs e)
{
    int result = 1; // Result = OK
    string returnValue = TextBox1.Text;
    Page.Response.Clear();
    Page.Response.Write(String.Format(
        CultureInfo.InvariantCulture, 
        "<script type=\"text/javascript\">window.frameElement.commonModalDialogClose({0}, {1});</script>", 
        new object[] { 
            result, 
            String.IsNullOrEmpty(returnValue) ? "null" : String.Format("\"{0}\"", returnValue) 
        }));
    Page.Response.End();
}

The code will close the dialog window, pass “OK” as result and text from the text box as return value.

When you deploy the code (make sure that your package contains the button) you’ll see the following button appear when you edit your rich content page:

result ribbon

When you click the button the dialog page will appear:

result dialog

When you insert some “test text” and click insert, your text will show up where you left your cursor:

result test text

Setting several SharePoint sites read only

Setting a SharePoint site collection is quite an easy task. Setting several sites and subsites read only is not. There’s no cmdlet available to set one or more SharePoint sites read only.

For me read only means that all the permissions that exist are being put to “Read”. So here’s the script that does all the magic:

There are 3 parameters: The LogFilePath (for instance c:\temp\log.txt) where the old permissions are being written to for later consultation. The SiteUrl of the SharePoint site collection where the sites are located. And the exclusionSitesTitles is an array of titles of sites that should not be put read only. You can also change these to URLs, but then you have to edit the AllWebs line:

$webs = $site.AllWebs | ?{-not ($exclusionSitesTitles -contains $_.Title)}

Param (
  [Parameter(Mandatory=$True, Position=0)]
  [string]$LogFilePath,

  [Parameter(Mandatory=$True, Position=1)]
  [string]$SiteUrl,

  [Parameter(Mandatory=$True, Position=2)]
  [string[]]$exclusionSitesTitles
)

[System.Reflection.Assembly]::LoadWithPartialName(&quot;Microsoft.SharePoint&quot;)
$site = New-Object Microsoft.SharePoint.SPSite($siteUrl)
$webs = $site.AllWebs | ?{-not ($exclusionSitesTitles -contains $_.Title)}

function checkPermissions([Microsoft.SharePoint.SPRoleAssignmentCollection]$roles)
{
	$roles | Out-File -Append -FilePath $logfilepath
	$roles | %{
		if($_.RoleDefinitionBindings.Count -eq 1 -and $_.RoleDefinitionBindings.Contains($guestPermission))
		{
			$_.RoleDefinitionBindings.RemoveAll();
		}
		else
		{
			$_.RoleDefinitionBindings.RemoveAll();
			$_.RoleDefinitionBindings.Add($readPermission);
		}
		$_.Update();
	}
}

function checkLists($web)
{
	$web.Lists | %{
		if($_.HasUniqueRoleAssignments)
		{
			checkPermissions($_.RoleAssignments)
		}
	}
}

$webs | %{
	$readPermission = $_.RoleDefinitions.GetByType([Microsoft.SharePoint.SPRoleType]::Reader);
	$guestPermission = $_.RoleDefinitions.GetByType([Microsoft.SharePoint.SPRoleType]::Guest);
	$_.Url | Out-File -Append -FilePath $logfilepath
	if($_.HasUniqueRoleAssignments)
	{
		checkPermissions($_.RoleAssignments)
	}
	checkLists($_)
	$_.Dispose();
}
$site.Dispose();