PowerShell: Repairing SharePoint Content Databases


Introduction

When you get a warning from SharePoint saying that a database has orphaned content, the message is not the most helpful.

This is an example of health analyser message in SharePoint Central Admin

orphaneditemshealthcheckerror

There is no information on which content database is affected, although you can click on the “Repair Automatically” option but you don’t have any context as to which database is affected. This is going to be a problem if we need to repair the database via a documented change process.

The following process will show you how to identify the item and database that is affected.

Start by running the SharePoint Management Shell.

Get-SPContentDatabase | %{Write-Host $_; $_.Repair($false);}

This will return something like below:-

Database Name 1
<OrphanedObjects Count="0" />
Database Name 2
<OrphanedObjects Count="0" />
Database Name 3
<OrphanedObjects Count="0" />
Database Name 4
<OrphanedObjects Count="0" />
Database Name 4
<OrphanedObjects Count="0" />
Database Name 5
<OrphanedObjects Count="0" />
Database Name 6
<OrphanedObjects Count="0" />
Database Name 7
<OrphanedObjects Count="0" />
Database Name 8
<OrphanedObjects Count="1">
 <Orphan Type="SecurityScope" SiteId="{166BF298-DE66-4919-A506-4F3412E8A86E}"
Name="sites/test/SiteCollectionImages/Forms/Video/docsethomepage.aspx" InRecycleBin="Yes" />
</OrphanedObjects>

Now we can see that there is some orphaned content in content database “Database Name 8”.

To fix the database, run the following:-

$repairdb = Get-SPContentDatabase -Identity "[Database Name]";

Check that the database is the right one by outputting the $repairdb object.

To repair the database run the following command.

$repairdb.Repair($true)
<OrphanedObjects Count="1">
 <Orphan Type="SecurityScope" SiteId="{166BF298-DE66-4919-A506-4F3412E8A86E}"
Name="sites/test/SiteCollectionImages/Forms/Video/docsethomepage.aspx" InRecycleBin="Yes" />
</OrphanedObjects>

Now let’s check that the corruption is gone by re-running the command

Get-SPContentDatabase | %{Write-Host $_; $_.Repair($false);}

We should see that there are no more corruptions

Now go into Central Admin site and re-run the failed health rule, to resolve the issue.

PowerShell: Getting Sandbox Solutions in a SharePoint Web Application


Introduction

Like all good SharePoint developers we have been using Sandbox Solutions where we can. Of course one of the problems with Sandbox solutions is that its difficult to find out what you have installed and where.
This is particularly important if you have a Sandbox solution that is used for branding and is installed into a number of site collections.
Therefore I built a script which will retrieve sandbox solutions in a web application.
You can filter which sandbox solutions by the name.
Please fill free to use and modify the script, though make sure you test it before using in Production.
If you do make some enhancement that you think is useful, please let me know as would love to see what additional functionality is useful.

How does the script works

The script is a PowerShell script which works in the following way:
  • The script is run by providing the Url for the Web Application
  • The script will enumerate through all the site collections in the web application and creates an array object which has two properties, SiteCollection and Solution
  • Once all site collections have been enumerated then the array will be outputted.
This way you can use the output for further processing steps. For example, to filter the results for one solution and upgrade the solutions in all the site collections returned.
The script is shown below:

param
(
[Parameter(Mandatory=$false, HelpMessage='Solution Name')]
[string]$SolutionName="",
[Parameter(Mandatory=$false, HelpMessage='Solution Name')]
[string]$WebApplicationUrl="")$memoryAssignment = Start-SPAssignment;
$numberOfSolutionsFound = 0;
$webApplication = Get-SPWebApplication -Identity $WebApplicationUrl -ErrorAction SilentlyContinue -AssignmentCollection $memoryAssignment;
if($webApplication -ne $null)
{
#enumerate through site collections in web application
$allSites = Get-SPSite -WebApplication $webApplication -Limit ALL -Confirm:$false -AssignmentCollection $memoryAssignment;
foreach($checkSite in $allSites)
{
#Write-Output "Checking Site " $checkSite.Url " for solution " $SolutionName;
if($SolutionName -eq "")
{
$checkSolutions = Get-SPUserSolution -Site $checkSite -AssignmentCollection $memoryAssignment;
foreach($solution in $checkSolutions)
{
$output = New-Object -TypeName "System.Object";
Add-Member -InputObject $output -MemberType NoteProperty -Name "Solution" -Value "";
Add-Member -InputObject $output -MemberType NoteProperty -Name "SiteCollection" -Value "";
$output.Solution = $solution;
$output.SiteCollection = $checkSite;
Write-Output -InputObject $output;
$numberOfSolutionsFound++
}
}
else
{
$checkSolution = Get-SPUserSolution -Identity $SolutionName -Site $checkSite -ErrorAction SilentlyContinue -AssignmentCollection $memoryAssignment;
if($checkSolution -ne $null)
{
$output = New-Object -TypeName "System.Object";
Add-Member -InputObject $output -MemberType NoteProperty -Name "Solution" -Value "";
Add-Member -InputObject $output -MemberType NoteProperty -Name "SiteCollection" -Value "";
$output.Solution = $checkSolution;
$output.SiteCollection = $checkSite;
Write-Output -InputObject $output;
$numberOfSolutionsFound++
}
}
}
}&lt;/p&gt;
&lt;p style="direction: ltr; margin-top: 0; margin-left: 0; width: 7.5875in;"&gt;Write-Output "Found $numberOfSolutionsFound Instances of Solution $SolutionName";
Stop-SPAssignment $memoryAssignment;&lt;/p&gt;
&lt;p style="direction: ltr; margin-top: 0; margin-left: 0; width: 7.5875in;"&gt;

 

How to execute the script

 To execute the script do the following:
  • Logon to SharePoint 2013 server
  • Run the SharePoint Management Shell
    • .\Get-SPUserSolutionInWebApplication -WebApplicationUrl https://sharepoint
    • There is an optional parameter -SolutionName which allows you to only return the solutions with a particular name

The script will output an array of Solution Objects with an associated Site Collection object.

We can assign a value to the output and then do some further processing using that value.

Here is an example of the output from the script without assigning the result to a variable.

getusersolutionsfromwebapp

To do something more useful then assign the output to a variable. You can then enumerate through the variable array.

For example, the following screenshot shows the output being assigned to a variable $solutions and then it is being enumerated using the following:

$solutions | %{Write-Host $_.Solution.Name “in” $_.SiteCollection.Url}

enumerate-usersolutionarray

Downloads

If you would like to try out the PowerShell script, then you can download the script here:

Get-SPUserSolutionInWebApplication.zip

Please fill free to use and modify the script, though make sure you test it before using in Production.

Also let me know if you find it useful and if you have made some cool changes let me know as always looking for ways to make things better!

SharePoint 2013/Online Attachment Viewer Enhancement


Introduction

Update: 22nd January 2016 – Further updates to fix an issue with Minimal Download Strategy (MDS) and works correctly when used in multiple list view app parts on the same page.

Recently I had a client that wanted a better way to get to the attachments that were linked to a SharePoint list item from a list view web part. This request had me thinking and I didn’t think it would be too difficult to achieve using SharePoint 2013’s Client Side Rendering (CSR) user interface components. Anyway one night I decided on an approach and put the following together. attachment-viewer-example

Solution

The solution uses client side rendering to override the output that the field displays in a list view. This is achieved using a list template override to change the Attachment’s field rendering. The solution makes use of the SharePoint Callout Manager which is used to display links to the attachment. I found a set of articles which have been posted in the Resources section below really good for getting to grips with using the Callout Manager. The implementation works on both SharePoint 2013 and SharePoint Online. The one issue with the approach is that you have to reference the JavaScript file for each view, so if you create a new view then that view needs to be setup. This isn’t a problem if you are creating a custom list with a schema.xml as you can reference the JavaScript in the list’s schema. The solution makes use of the SharePoint 2013 JSOM API and the list of attachments is only created one the user has clicked on the paperclip icon to see the list of attachments. This is to try and keep the load down on the server when there are lots of attachments for a particular list item.

Resources

The following websites provided a great resource in getting the Callout Manager and SharePoint JSOM working. http://blog.alexboev.com/2012/07/custom-callouts-in-sharepoint-2013.html

http://www.learningsharepoint.com/2013/05/09/sharepoint-2013-callout-popup-tutorial/

SharePoint 2013 – Custom CallOut with File Preview – Obilogic

http://msdn.microsoft.com/en-us/library/office/dn268594(v=office.15).aspx

Setup

Watch the video showing you how to setup the attachment viewer.

To take advantage of the list attachment viewer and set it up for a particular list view, do the following:-

  • Download the zipfile, found at the bottom of the post
  • Unzip the zip file to extract the ITSP.SP.AttachmentField.js file.
  • Upload the ITSP.SP.AttachmentField.js into a suitable location, I would suggest the Site Assets library.
    • Create a folder called Scripts within the Site Assets library
    • Upload the ITSP.SP.AttachmentField.js into this folder, this keeps your content organised.
    • Alternatively you could package up the JS file to deploy the files by using a Module into a site collection.
  • Browse to the SharePoint list that you wish to enable the viewer on.
    • Make sure that the list has the Attachment field showing as this is the column that is going to be overridden
  • Click “Edit Page”

attachment-viewer-stepone-editpage

  • Click on the List Viewer Web Part, choose Edit Web Part

attachment-viewer-steptwo-editwebpart

  • The web part properties will appear on the right hand side
  • Expand the miscellaneous section
  • In the JSLink field type in the location of the ITSP.SP.AttachmentField.js file
  • E.g ~site/SiteAssets/Scripts/ITSP.SP.AttachmentField.js
  • Use the ~site token to substitute for the local site url

attachment-viewer-stepthree-jslink

  • Click Apply
  • Click Ok
  • Save the Page

Now create a list item and add some attachments to it. When you click on the paperclip icon you will get a callout which will display all the attachments for that list item. attachment-viewer-paperclip attachment-viewer-example Please let me know if you find it useful, I have tested the script with 5/6 attachments, it should scale with 20 or so documents as the list of documents is built each time you click on a particular document.

Update 21st May 2014

A new version of the script has been released to add support for the Minimal Download Strategy feature. This helps to reduce the amount of bytes being downloaded to the client. For more information please read this MSDN Blog Post.

The behaviour that you would start seeing is that the attachment callout would appear once and then never again. More information can be found here:-

Update 17th November 2015

A new version of the script has been released to add support for being used in multiple list view app parts on the same page.
Please use this version instead.

Thanks to Dar for reporting this issue.

Update 4th January 2016

It seems that I did not do enough testing and a further issue was discovered by Ken when having multiple list view web parts on the page.

Update 22nd January 2016

There still seemed to be an issue with MDS and also the control was polluting the global JavaScript pool, this update fixed those has been tested on SharePoint Online.

Download:
ITSP.SP.AttachmentField-20160122.zip

 

SharePoint Development: Speed up development with Visual Studio CKSDEV Keyboard Shortcuts


Introduction

Firstly if you are not using the CKSDEV Visual Studio extensions for SharePoint. Then please do yourself a favour and download them NOW!

Wes Hackett and the team have been doing an amazing job to save us time when developing SharePoint solutions.

You can download the Visual studio extension via Visual Studio’s gallery.

Keyboard Shortcuts

I have to say that I was a little late to the 1.2 release which I updated to in November even though it was out in August! Anyway this release is the best yet for SharePoint 2013 and Visual Studio 2012. Finally it includes a SharePoint solution deployment profile to update/upgrade your solutions. Finally, I can throw away my version!

Anyway, I noticed that there were lots more keyboard shortcuts (I am pretty sure these have been there for a while) but I wanted to start making use of them as it really makes life much easier.

Fortunately the CKSDEV development team have a strategy for the shortcut conventions so remembering them is made a little easier.

To recycle a process then the two stage shortcut will always use Alt+R

To attach the debugger to a process then the two stage shortcut will always use Alt + A

So here are the shortcuts which I use the most:-

  • Recycle SharePoint Application Pools
    • Alt+R, A
  • Recycle SharePoint Timer Service
    • Alt R. T
  • Attach debugger to SharePoint Application Pools
    • Alt A, S
  • Attach debugger to SharePoint Timer Service
    • Alt A, T

    This makes life so much easier when you are in the code, debug, fix, code, debug, fix cycle!

PowerShell Script to Restart SharePoint 2013 Farm services


 

Introduction

 

A while back I wrote a quick script to ‘Restart services on a SharePoint 2010 Farm’, this blog post can be found here.

 

Anyway, I have slightly updated it for SharePoint 2013. Its pretty much the same though I am also restarting the Search process too and also checking that the service exists on the server before restarting.

How you find it useful

 

The Script

 

Here is the script:-

 <pre>
param
(
    [Parameter(Mandatory=$false, HelpMessage='-ServiceNames Optional, provide a set of service names to restart.')]
    [Array]$ServiceNames=@("SharePoint Timer Service","SharePoint Administration", "SharePoint Server Search 15", "World Wide Web Publishing Service","IIS Admin Service")
);

Write-Host "Attempting to get SharePoint Servers in Farm" -ForegroundColor White;
$farm = Get-SPFarm;
$servers = $farm.Servers;
Write-Host "Found" $servers.Count "Servers in Farm (including database servers)" -ForegroundColor White;
foreach($server in $servers)
{
    if($server.Role -ne [Microsoft.SharePoint.Administration.SPServerRole]::Invalid)
    {
        Write-Host "Attempting to restart services on" $server.Name -ForegroundColor White;
        foreach($serviceName in $ServiceNames)
        {
            $serviceInstance = Get-Service -ComputerName $server.Name -Name $serviceName -ErrorAction SilentlyContinue;
            if($serviceInstance -ne $null)
            {
                Write-Host "Attempting to restart service" $serviceName ".." -ForegroundColor White -NoNewline;
                try
                {
                    $restartServiceOutput="";
                    Restart-Service -InputObject $serviceInstance;
                    Write-Host " Done!" -ForegroundColor Green;
                }
                catch
                {
                    Write-Host "Error Occured: " $_.Message;
                }
            }
        }
    }
}
</pre>

Download the script here: Restart-SPServices2013.zip

SharePoint 2013 Search: Keep getting Search Results even when there is no query made


Introduction

This is a quick one which caused me some problems on a project I have been working on.

So a quick introduction to SharePoint 2013 Search. SharePoint 2010 had a feature in search called “Search Scopes” this provided a way to select the content that you wanted to return results from based on certain criteria. For example if it had a certain Content Type or was found in a particular location.

In SharePoint 2013 Scopes have been replaced by Result Sources which provide the same feature plus some much more powerful selection criteria using tokens. For example you can retrieve the user performing the search and return content where the user is the author.

Problem

The problem is that I have a search page which has both the search query web part and a search results web part.

After setting up a custom Result Source query to refine the results down I would always get results displaying, even before a query had been submitted search.

The Result Source is configured by:-

  • Browsing to the Site Collection
  • Click on the Site Settings Icon
  • Choose Site Settings->Site Collection Administration->Search Result Sources
  • Create a New Result Source
  • Part of the setup is to create the Query Transform, this brings up the window below

Here is what was setup:-ThisQueryAlwaysReturnsResults

The query always returns search results. Normally results only appear when you click on the Test tab, providing the search query and click Test Query.

This is a problem as because the search will always execute as the user goes on the page and is just plain messy.

So I spent sometime trying to work out what was going on.

Solution

I have to say the solution took sometime and it was only after re-reading the following Technet article http://technet.microsoft.com/en-us/library/dn186229.aspx#BKMK_How_does_query_transform_affect_query the section at the bottom “Narrowing search results by using a Query Transform” gave me the answer.

The key to all of this is that my query is not waiting for the search term before executing the search, it just always executes the search.

The example that the article provides is this:-

{?{searchTerms} ContentClass=urn:content-class:SPSPeople}

So that gave me the solution, the important thing is that if you want the query to wait for search terms to be filled in then wrap your query with curly brackets.

So the problem query goes from this:-

{searchTerms} (IsDocument=True AND (ContentTypeId:0x0101009CA89B0104F04692A106A1F88E847F7D*))

to this:-

{?{searchTerms} (IsDocument=True AND (ContentTypeId:0x0101009CA89B0104F04692A106A1F88E847F7D*))}

When I use the query above with the Query Transform this is the result, note the query returns no results.

ThisQueryWaitsforQueryBeforeReturnsResults

The query that I have will take any query and ensure that only search results that are documents that have a ContentTypeId which starts with a particular value.

I hope that helps someone!