CAML Query to Return Items Less Than or Greater Than Now

Quick example how to query a list or library for items created more than 5 minutes ago. You can use the Get-Date cmdlet to increment all sorts of values.

$nowMinus5Minutes = [Microsoft.SharePoint.Utilities.SPUtility]::CreateISO8601DateTimeFromSystemDateTime((Get-Date).AddMinutes(-5))
$nowPlus5Minutes = [Microsoft.SharePoint.Utilities.SPUtility]::CreateISO8601DateTimeFromSystemDateTime((Get-Date).AddMinutes(5))

#get items created less than 5 minutes ago
		$caml = '<Where><Lt><FieldRef Name="Modified" /><Value Type="DateTime">' + $nowMinus5Minutes + '</Value></Lt></Where>'
		$sQuery.Query = $caml

The workflow could not update the item, possibly because one or more columns for the item require a different type of information.

SharePoint Designer workflow error:
The workflow could not update the item, possibly because one or more columns for the item require a different type of information.

If you look at the workflow history nothing of value is there to clue you into what field is broken.

To track down what field is broken, I added a Pause action to my Step. Then I added a Log message before each action. Doing this helped track down the broken field.

The workflow could not update the item

Once you get the workflow working again, remember to clean up the added Log actions.

In my case, the workflow was trying to update a Person / Group field with an employee that no longer worked at the company.

System.IO.FileNotFoundException: The web application at could not be found

Online, I found a lot of different solutions to the error but none of them would solve my issue. I had written a console app that reads data from a SharePoint library, then processes the data. The process is started using a scheduled task and runs under the context of a service account. The account had contribute permissions on the library and read access to the site.

The solution:
On the SharePoint Config and Content database(s), grant the account SharePoint_Shell_Access.

After doing this, I noticed this message in the app’s call stack:

 executed on connection "Data Source=SPDevDB;Initial Catalog=SP2010_Dev_Config;Integrated Security=True;Enlist=False;Connect Timeout=15", building a SqlDataReader.
[7132]

After seeing the message, it was clear that the app was first trying to connect to the config db, then it would connect to the site/library objects.

Sort SPFileCollection by Created Date

Yes, this could be done with a CAML query, but I wanted to keep it simple.

I need to get items created in ascending order (oldest first).

My solution:


SPFileCollection items = web.GetFolder(sLawsonReports).Files;
//sort the files so we get the oldest first
List sortFiles = items.Cast().OrderBy(file => file.TimeCreated).ToList();

InfoPath DisplayName is Empty When Using UserName()

You created an InfoPath form, then added a text box to show the current users display name. Safe to assume you populated the text box using a rule and a set field value action. In the set field action, your Xpath might look like this:

xdXDocument:GetDOM(“My Data Connection”)/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:Employee_x0020_Name_x0020_2/pc:Person/pc:DisplayName[../../../d:Title = concat(“DOMAIN\”, xdUser:get-UserName())]

In my case, when the user accessed userName(), it was returning a value other than what is in Active Directory. UserName() would return Jdoe but Active Directory had the user listed as jdoe. This caused the text box to be empty in my form.

Look at the local user account on their machine.

http://www.thewindowsclub.com/user-account-name-change

Copy Files Between Web Applications and Keep Metadata

What if you need to move all the files in one document library to a library in a different web app. Along with this, you also need to maintain your metadata. This includes the content types, file created by, created on, modified, and modified by metadata.

In my Dev environment, I created a simple Windows Form app.
Add a reference to the SharePoint Dll.
I named my app MoveDataSP.

In the scenario below, the code will copy the contents of folder1 from the sourceSPsite to the destinationSPsite. Both sites have libaries named the same (libraryName).

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Microsoft.SharePoint;

namespace MoveDataSP
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        public static void CopyFolderToAnotherSiteCollection(string sourceUrl, string destinationUrl,
          string listName, string folderUrl, string folderName)
        {
            string listRelatvieFolderUrl = folderUrl.Substring(folderUrl.IndexOf("/") + 1);
            using (SPSite sourceSite = new SPSite(sourceUrl))
            {
                using (SPWeb sourceWeb = sourceSite.OpenWeb())
                {
                    SPFolder sourceFolder = sourceWeb.GetFolder(folderUrl);
                    using (SPSite destSite = new SPSite(destinationUrl))
                    {
                        using (SPWeb destWeb = destSite.OpenWeb())
                        {
                            if (!destWeb.GetFolder(folderUrl).Exists)
                            {
                                SPListItem destItem = destWeb.Lists[listName].Items.Add(string.Empty, SPFileSystemObjectType.Folder, listRelatvieFolderUrl);

                                destItem.Update();
                            }

                            SPFolder destFolder = destWeb.GetFolder(folderUrl);

                            foreach (SPFile file in sourceFolder.Files)
                            {   
                                SPUser sCreatedby = destWeb.AllUsers[file.Author.ToString()];
                                SPUser sModifiedBy = destWeb.AllUsers[file.ModifiedBy.ToString()];
                                DateTime dCreated = file.TimeCreated;
                                DateTime dModified = file.TimeLastModified;

                                SPFile sFile = destFolder.Files.Add(file.Url, file.OpenBinary(), file.Properties, sCreatedby, sModifiedBy, dCreated, dModified, true);

                                SPListItem item = sFile.Item;

                                item["Created By"] = sCreatedby;
                                item["Modified By"] = sModifiedBy;
                                item["Created"] = dCreated;
                                item["Modified"] = dModified;

                                sFile.Item.SystemUpdate();

                            }

                            foreach (SPFolder folder in sourceFolder.SubFolders)
                            {
                                CopyFolderToAnotherSiteCollection(sourceUrl, destinationUrl, listName, folder.Url, folder.Name);
                            }
                        }
                    }
                }
            }
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            CopyFolderToAnotherSiteCollection("http://sourceSPsite/", "http://destinationSPsite", "libraryName", "libraryName/folder1", "folder1");
        }

    }
}

Most of the code above was borrowed from this post:
http://sharepointfieldnotes.blogspot.com/2009/11/how-to-copy-files-across-site.html

SharePoint Workflow Not Starting

For one reason or another, SharePoint Designer Workflows are not always starting when an item is added to a library.  This happens with document libraries and InfoPath form libraries.  I’ve seen this happen with SharePoint 2007 and SharePoint 2010.

What I’ve created is a way to identify, monitor, and start workflows.

Create two lists:

Workflow Monitor

Fields:

Site URL, List Name, Workflow Name

Workflow Monitor Logging

Fields:

Site URL, List Name, Workflow Name, List Item ID

All of the field types are the default single line of text.

Workflow Monitor will be populated with the site url, list name, and workflow name of the workflow you want to monitor.

Place the script in a folder on one of your SharePoint server. Then setup a scheduled task to run the script as needed.


&amp;lt;#
 loop through workflow monitor list
 get workflow item
 query associated list by created date between yesterday and today AND workflow field is empty
 if item is returned, start workflow on item.
 log item that was not started
 if there are any errors send email to DL SharePoint
#&amp;gt;

if ((Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue) -eq $null)
{
    Add-PSSnapin "Microsoft.SharePoint.PowerShell"
}

$cSite = Get-SPWeb "http://sharepointed.com"
$cList = $cSite.Lists["Workflow Monitor"]
$cListLog = $cSite.Lists["Workflow Monitor Logging"]
$errorCount = 0
$errorString = ""

foreach($config in $cList.Items)
{
	try
	{
		$fSite = Get-SPWeb $config["Site URL"].ToString()
		$wfManager = $fSite.Site.WorkFlowManager
		$fList = $fSite.Lists[$config["List Name"].ToString()]
		$fWFfield = $fList.Fields[$config["Workflow Name"].ToString()].InternalName

		#Get the list workflow
		$wfAssoc = $fList.WorkflowAssociations.GetAssociationByName($config["Workflow Name"].ToString(),"en-US")
		$wfData = $wfAssoc.AssociationData

		$sQuery = New-Object Microsoft.SharePoint.SPQuery 

		#Get all item that were created in the past day and a workflow has not ran.
		$caml = '&amp;lt;Where&amp;gt;&amp;lt;And&amp;gt;&amp;lt;IsNull&amp;gt;&amp;lt;FieldRef Name="' + $fWFfield + '" /&amp;gt;&amp;lt;/IsNull&amp;gt;&amp;lt;Geq&amp;gt;&amp;lt;FieldRef Name="Created" /&amp;gt;&amp;lt;Value Type="DateTime"&amp;gt;&amp;lt;Today OffsetDays="-1" /&amp;gt;&amp;lt;/Value&amp;gt;&amp;lt;/Geq&amp;gt;&amp;lt;/And&amp;gt;&amp;lt;/Where&amp;gt;'
		$sQuery.Query = $caml
		$fItems = $fList.GetItems($sQuery)

		foreach($lItem in $fItems)
		{
			#Start workflow
			$wf = $wfManager.StartWorkFlow($lItem,$wfAssoc,$wfData,$true)

			#Create Log entry
			$newLogItem = $cListLog.Items.Add()
			$newLogItem["Site URL"] = $config["Site URL"]
			$newLogItem["List Name"] = $config["List Name"]
			$newLogItem["List Item ID"] = $lItem["ID"]
			$newLogItem["Workflow Name"] = $config["Workflow Name"]
			$newLogItem.Update()
		}
	}
	Catch
	{
		#string.format
		$errorMessage = $_.Exception.Message.ToString()
		$errorString += $config["ID"].ToString() + " " + $errorMessage + " --- "
		$errorCount++
	}
}

#If there are any errors send email
if($errorCount -gt 0)
{
	$errorString = $errorString.TrimEnd(" --- ")

	$emailSubject = "Workflow Montior Script Error"
	$emailBody = "Error running the Workflow Monitor script. &amp;lt;br&amp;gt;&amp;lt;br&amp;gt; &amp;lt;b&amp;gt;Error: &amp;lt;/b&amp;gt;"
	$emailBody += "$errorString &amp;lt;br&amp;gt;&amp;lt;br&amp;gt;"
	$emailBody += "&amp;lt;a href=$cList.URL.ToString()&amp;gt;Workflow Monitor List &amp;lt;/a&amp;gt;"
	$emailsmtpServer = "mail.doman.net"
	$emailTo = "you@sharepointed.com"
	$emailFrom = "alerts@sharepointed.com"

	Send-MailMessage -From $emailFrom -To $emailTo -Subject $emailSubject -BodyAsHtml $emailBody -SmtpServer $emailsmtpServer
}

This script will inventory your entire farm and output workflows that have failed to start int the past 59 days. *You can adjust the 59 day setting, but my farm is setup to truncate workflow history every 60 days.*


$contentWebAppServices = (Get-SPFarm).services |
? {$_.typename -eq "Microsoft SharePoint Foundation Web Application"}

$stringBuilder = New-Object System.Text.StringBuilder
$list = New-Object System.Collections.Generic.List[System.String]
$counter = 0

foreach($webApp in $contentWebAppServices.WebApplications)
{
	$webApp = Get-SPWebApplication $webApp.Url

	if($webApp -ne $null)
	{
		foreach($siteColl in $webApp.Sites)
		{
			if($siteColl -ne $null)
			{
				foreach($subWeb in $siteColl.AllWebs)
				{
					if($subWeb -ne $null)
					{
						foreach($list in $subWeb.Lists)
						{
							foreach($wf in $list.WorkflowAssociations)
							{
								if ($wf.Name -notlike "*Previous Version*")
								{
									$subWeb.Site.WorkflowManager
									$wfManager = $subWeb.Site.WorkFlowManager
									$fWFfield = $list.Fields[$wf.Name.ToString()].InternalName

									#Get the list workflow
									$wfAssoc = $list.WorkflowAssociations.GetAssociationByName($wf.Name,"en-US")
									$wfData = $wfAssoc.AssociationData

									if($wfAssoc.AutoStartCreate -eq $true)
									{
										$counter++

										$sQuery = New-Object Microsoft.SharePoint.SPQuery 

										#Get all item that were created in the past day and a workflow has not ran.
										$caml = '&amp;lt;Where&amp;gt;&amp;lt;And&amp;gt;&amp;lt;IsNull&amp;gt;&amp;lt;FieldRef Name="' + $fWFfield + '" /&amp;gt;&amp;lt;/IsNull&amp;gt;&amp;lt;Geq&amp;gt;&amp;lt;FieldRef Name="Created" /&amp;gt;&amp;lt;Value Type="DateTime"&amp;gt;&amp;lt;Today OffsetDays="-59" /&amp;gt;&amp;lt;/Value&amp;gt;&amp;lt;/Geq&amp;gt;&amp;lt;/And&amp;gt;&amp;lt;/Where&amp;gt;'
										$sQuery.Query = $caml
										$fItems = $list.GetItems($sQuery) 

										$null = $stringBuilder.Append($subWeb.URL)
										$null = $stringBuilder.Append(",")
										$null = $stringBuilder.Append($list.Title)
										$null = $stringBuilder.Append(",")
										$null = $stringBuilder.Append($wf.Name.ToString())
										$null = $stringBuilder.Append("`r`n")

									}
								}
							}

						}

						$subWeb.Dispose()
					}
				}
				$siteColl.Dispose()
			}
		}
	}
}

if($counter -gt 0)
{
	out-file -filepath C:\WorkflowOutput.csv -inputobject $stringBuilder.ToString()
}

Document ID Service and Migrating Documents

You are using the Document ID Service in SharePoint and you want to move your documents to another location.  For whatever reason, you notice when you move the documents that your document id’s are lost or reset.  This can be real bad if other systems outside of SharePoint leverage the document id to locate documents in SharePoint.

Here are the steps I used to get around this.
Move the documents from location A to B, keeping the original documents in A.
Run a crawl.
Execute the script below (input your own values for the site and libraries).
Remove / delete the documents from location A. For safe measures, also empty the recycle bin, both at the site and site collection level.
Run another crawl.
Trigger the Document Id Settings to update.

What the script is doing:
Get all the documents in the Shared Documents library of site A.
Loop through the documents.
Locate the document by name in the Shared Documents library of site B.
Update the Document ID of the item.

Things you could improve on:
Get all the documents in site A and output the Name and Document ID to a csv file.
– Then process the csv file with the script.
Add logging.
– Output your pass / fail items to csv file.
Expand the search to query a site.
– Then update the returned item.

if ((Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue) -eq $null)
{
    Add-PSSnapin "Microsoft.SharePoint.PowerShell"
}

$sSite = Get-SPWeb "http://sharepoint.net/sites/A"
$sList = $sSite.lists["Shared Documents"]

$dSite = Get-SPWeb "http://sharepoint.net/sites/B"
$dList = $dSite.lists["Shared Documents"]

$sQuery = New-Object Microsoft.SharePoint.SPQuery
$sQuery.ViewAttributes = "Scope='Recursive'"
$sQuery.RowLimit = 2000
$sQuery.Query = '<Where><Gt><FieldRef Name="ID" /><Value Type="Counter">0</Value></Gt></Where>'

do
{
	$sItems = $sList.GetItems($sQuery)
	$sQuery.ListItemCollectionPosition = $sItems.ListItemCollectionPosition
	foreach($sI in $sItems)
	{
		$docName = $sI["Name"].ToString()
		Write-Host $docName

		$dQuery = New-Object Microsoft.SharePoint.SPQuery
		$dQuery.ViewAttributes = "Scope='Recursive'"
		$dQuery.RowLimit = 2000
		$dQuery.Query = '<Where><Eq><FieldRef Name="FileLeafRef"/><Value Type="File">' + $docName + '</Value></Eq></Where>'

		do
		{
			$dItems = $dList.GetItems($dQuery)
			$dQuery.ListItemCollectionPosition = $dItems.ListItemCollectionPosition
			foreach($dI in $dItems)
			{
				try
				{
					$dI["Document ID Value"] = $sI["Document ID Value"]
					$dI.Update()
					Write-Host $dI["Name"] " has been updated"
				}
				catch
				{
					Write-Host $dI["Name"] " ---- " $_.Exception.Message
				}
			}
		}
		while ($dQuery.ListItemCollectionPosition -ne $null)
	}
}
while ($sQuery.ListItemCollectionPosition -ne $null)

This item cannot be updated because it is locked as read-only

Had to call in Microsoft Support to help unlock files that were marked as read-only. Some files appeared to be locked, but using the UI, they couldn’t be unlocked, declared as a record, or edited.

Error: This item cannot be updated because it is locked as read-only
Error: The file “your file string” is checked out for editing by SHAREPOINT\system

This script was setup to run on a SharePoint 2010 instance. If you need to run it on another version, try updating the $sharePointAssembly line.
To unlock a file, input the URL of the file in the line that starts with $fileUrl.


$recordFields =
  "_vti_ItemHoldRecordStatus",
  "_vti_ItemDeclaredRecord"

$recordProperties =
  "ecm_RecordRestrictions",
  "ecm_ItemLockHolders",
  "ecm_ItemDeleteBlockHolders"

$fileUrl = "http://siteURL/siteCollection/libraryName/folder/subFolder/fileName.pdf"

$sharePointAssembly = [System.Reflection.Assembly]::Load("Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c")

Add-Type -TypeDefinition @"
using Microsoft.SharePoint;

public class EventsDisabler : SPEventReceiverBase
{
  public EventsDisabler() {}
  
  public bool EventsDisabled
  {
    get { return !EventFiringEnabled; }
    set { EventFiringEnabled = !value; }
  }
}
"@ -ReferencedAssemblies $sharePointAssembly

Write-Host "Getting site collection at $fileUrl..."
[Microsoft.SharePoint.SPSite]$site = New-Object Microsoft.SharePoint.SPSite($fileUrl)
if ($site -eq $null) { exit }
$siteUrl = $site.Url
Write-Host "Found site collection $siteUrl"
Write-Host ""

Write-Host "Getting web at $fileUrl..."
[Microsoft.SharePoint.SPWeb]$web = $site.OpenWeb()
if ($web -eq $null) { exit }
$webTitle = $web.Title
Write-Host "Found web $webTitle"
Write-Host ""

Write-Host "Verifying current user is System Account"
$web.CurrentUser.ID
$site.SystemAccount.ID
if ($web.CurrentUser.ID -ne $site.SystemAccount.ID)
{
  Write-Error "Please run this script as System Account" -Category PermissionDenied
  #exit
}
Write-Host ""

Write-Host "Getting list at $fileUrl..."
Write-Host $fileUrl
[Microsoft.SharePoint.SPList]$list = $web.GetList($fileUrl)
if ($list -eq $null) { exit }
$listTitle = $list.Title
Write-Host "Found list $listTitle"
Write-Host ""

Write-Host "Getting list item at $fileUrl..."
[Microsoft.SharePoint.SPListItem]$listItem = $web.GetListItem($fileUrl)
if ($listItem -eq $null) { exit }
$listItemName = $listItem.Name
Write-Host "Found list item $listItemName"
Write-Host ""

$eventsDisabler = New-Object EventsDisabler
$eventsOriginallyDisabled = $eventsDisabler.EventsDisabled
if ($eventsOriginallyDisabled -eq $false)
{
  Write-Host "Disabling events"
  $eventsDisabler.EventsDisabled = $true
  Write-Host ""
}

$didWork = $false
$itemNeedsUpdate = $false

#Discard any check-out
if ($listItem.File -ne $null -and $listItem.File.CheckOutType -ne [Microsoft.SharePoint.SPFile+SPCheckOutType]::None)
{
  Write-Host "Undoing check-out"
  $listItem.File.UndoCheckOut()
  $didWork = $true
}
else
{
  Write-Host "No file or file is not checked out"
  Write-Host ""
}

#Iterate the Record fields and set all values to null
foreach($recordField in $recordFields)
{
  if ($listItem.Fields.ContainsField($recordField) -eq $true -and $listItem[$recordField] -ne $null)
  {
    $recordFieldValue = $listItem[$recordField]
    Write-Host "$recordField = $recordFieldValue"
    Write-Host "Setting $recordField to null"
    $listItem[$recordField] = $null
    $didWork = $true
    $itemNeedsUpdate = $true
  }
}

#Iterate the Record properties and remove any that exist
foreach($recordProperty in $recordProperties)
{
  if ($listItem.Properties.ContainsKey($recordProperty) -eq $true)
  {
    $recordPropertyValue = $listItem.Properties[$recordProperty]
    Write-Host "$recordProperty = $recordPropertyValue"
    Write-Host "Removing property $recordProperty"
    $listItem.Properties.Remove($recordProperty)
    $didWork = $true
    $itemNeedsUpdate = $true
  }
}

#Remove the icon Record lock overlay
if ($listItem.IconOverlay -eq "lockoverlay.png")
{
  Write-Host "Removing the icon Record lock overlay"
  $listItem.IconOverlay = $null
  $didWork = $true
  $itemNeedsUpdate = $true
}

if ($didWork -ne $true)
{
  Write-Host "No changes were made"
}
Write-Host ""

#Update the item
if ($itemNeedsUpdate -eq $true)
{  
  Write-Host "Updating item"
  $listItem.SystemUpdate()
  
  Write-Host ""
}

if ($eventsOriginallyDisabled -ne $true)
{
  Write-Host "Enabling events"
  $eventsDisabler.EventsDisabled = $false
  Write-Host ""
}

$web.Dispose()
$site.Dispose()