Office365 SharePoint - Rename Multiple Files in Document Library

Unfortunately, Office 365 SharePoint doesn't let you to do too much using the built-in PowerShell cmdlets. Sometimes this trick can also come in handy with On Premise SharePoint installations when you just need a quick way to manipulate files in a library. Using PowerShell commands this is much more complicated.
Last day I ran into an issue (maybe I'll post that later) when I had to rename multiple files in a Document Library that resides on an Office 365 SharePoint instance.

What I did was the following:

Step1 - Add the website to the IE's Trusted Sites

First of all, you have to use Internet Explorer. Open in Explorer mode only works with this browser. Add the website to the trusted site's list then restart the browser. Otherwise it will complain later.

Now browse to your SharePoint Library again.

Step 2 - Open the Library in Explorer

Go to Library -> Open with Explorer

Step 3 - Map the location as a Network Drive

Now that you can see the library in Explorer, you need to grab the URL from the Address bar (ctrl + c) and map a network drive:

Press the ALT + T -> Map Network Drive

Choose a drive letter, paste your path and Finish. You may not want to reconnect the drive at logon.

Now, that you can see your files as a drive you can use your favorite tool to manipulate them.

Step 4 - Use your favorite tool to manipulate the files

I just used cmd to rename all pdf files but you can do much, much more with Total Commander if you have to.

Of course, this method work with SharePoint Server (On Premise) as well.

Cannot set unknown member 'CompositeTask.PreserveIncompleteTasks'. HTTP headers received from the server

The problem

Today I encountered a strange issue when trying to publish a basic SharePoint 2013 Workflow that has a Start Task Process action in it.

The error I received was the following:

Cannot set unknown member 'CompositeTask.PreserveIncompleteTasks'. HTTP headers received from the server


Microsoft.Workflow.Client.ActivityValidationException: Workflow XAML failed validation due to the following errors:
Cannot set unknown member 'CompositeTask.PreserveIncompleteTasks'. HTTP headers received from the server - ActivityId: 51341428-f7a1-4856-8303-fcbc699335ea. NodeId: SPSRV. Scope: /SharePoint/default/8f0c9dfe-6f01-499c-a356-a417da9b39b4/53d5e7f1-0360-4909-8997-71eaf26b140d. Client ActivityId : a1627f9c-bdfc-80a3-b6fd-cf246a4e8993. ---> System.Net.WebException: The remote server ret

Solution

Checking the logs didn't provide any useful clue. Digging the net I found a post (linked at the bottom) that suggested re-registering the SharePoint 2013 Workflow Service. So I did that.

Register-SPWorkflowService -SPSite https://portal.mydomain.net -WorkflowHostUri https://spsrv:12290 -Force

Question: Will this command affect my currently running workflows?
Answer: No, all workflows will continue running smoothly, even if they are in progress, paused or waiting for an item to change. Tested.

Resources

• http://social.technet.microsoft.com/Forums/en-US/15e9a231-7737-44fb-9207-3945c44b1001/spd2013-workflow-publish-failed-for-ept-site-workflow?forum=projserv2010setup
• http://ranaictiu-technicalblog.blogspot.com/2013/02/sharepoint-2013-workflow-manager.html

SharePoint: Are there any Users currently on the site?

Problem

Ever wondered is there anyone connected to the SharePoint Site before doing an iisreset or a server reboot? Or how much the site is used on a daily basis?

Solution

The Performance Monitor tool is just about perfect for this. You can make a custom console, save it and use later. Or you can collect data over time to have a better overview on the server usage (Data Collector Sets section, not covered in this post).

Below I'll detail step by step how to create a custom MMC Console.

1. First, start a blank mmc console
   
   
   
   
   
2. Add a Performance Monitor Snap-In
   
   
   
   
   
   
   
3. You've got a blank Performance Monitor Console. Now you have to add the Counters.
   
   
   
   
4. Click on the green Plus Sign and find the following counters:
   
   Web Service Category
   - Current Anonymous Users
   - Current Non-Anonymous Users
   
   At the Instances section you can select a particular website or just _Total.
   
   
   
   I also like to add other helpful counters like CPU Usage, Memory, etc. For more info on counters please consult this blog post.
   
   
   
5. Go to View -> Customize and remove the clutter
   
   
   
   
   
6. Go to File -> Save As and save the console for later use. There you go.
   
   

Obviously, you can use this to monitor any kind of IIS Website like TFS, etc.

Updating Permissions on List Items via PowerShell

The following script crawls all document libraries from a path of site and looks for *budget*.xls* and will replace all permissions on the Item with Read.
The script is useful to cut access for a pattern of files.

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

$site = get-spsite -identity "http://myspportal"

foreach($web in $site.AllWebs)
    {

    if ($web.Url.Contains("http://myspportal/HR/"))     # Look for only under HR
        {

        $Lists = $web.Lists | Where-Object {$_.Title -Like "*Reports*"}   # Pattern to look for in LISTS names
        $roleReadOnly = $web.RoleDefinitions["Read"]

        foreach($list in $lists)
            {
            
             foreach($item in $list.Items | Where-Object {$_.Name -like "*budget*.xls*"})     # Filename pattern is *budget*.xls*
                {
                Write-Host $item.ParentList.ParentWeb.Url+'/'+$item.URL + '      ' + $item.Name
                if ($item.HasUniqueRoleAssignments -eq $false)
                    {
                    $item.BreakRoleInheritance($true);
                    }

                foreach ($ra in $item.RoleAssignments | Where-Object {$_.RoleDefinitionBindings.Name -eq "Contribute" -or $_.RoleDefinitionBindings.Name -eq "Full Control" -or $_.RoleDefinitionBindings.Name -eq "Edit"})
                    {
                        $ra.RoleDefinitionBindings.RemoveAll()
                        $ra.RoleDefinitionBindings.Add($roleReadOnly)
                        $ra.Update()
                    }

                }
            }
        }
    }

SharePoint 2010 vs 2013 Workflow Inconsistencies

These days I was tearing my hair out. I am rewriting some old workflows on my SharePoint system and can't decide whether to use v 2010 or 2013 workflows. There are definitely some useful features in the 2013 workflows but there are also some very basic features missing.

Here they are:

There is no impersonation step

This is somewhat understandable. Impersonation step in unreliable because when the employee who deployed the workflow leaves the company and the AD user is removed, the workflow will fail to start.

Cannot set permission on items

There is no way to replace permission on items. This was very useful in some cases.

Lookup Field - Additional Fields

Consider having a Lookup field linked to another list that is retrieving some additional columns. For example the Title is the main column, and you also retrieve the ID and Status fields. In 2010 you can work with these additional columns, but in 2013 only the main Lookup column is showing up.

In 2010 Workflow:

In 2013 Workflow:

This is crazy. Yes, you could retrieve the ID as the main column instead, then do a Lookup on the other list in the workflow. But in this case the New Item form shows a dropdown with some numbers instead the lookup field of your choice. Here you can use InfoPath to customize - which is retiring.

There is no Else-If

Serously??
Even the Else branch is missing from the auto-complete.

Wait for field to not equal a value

In 2010 Workflow you have all the options:

In 2013 Workflow there is only the Equal:

Start List/Site Workflow

Via a 2013 WF you can only start a v2010 workflow. Wtf??

That's it for now... I'll update this post once I find some more annoyances.

Set Permissions on Multiple Sites using PowerShell

These days I had a request to add an Active Directory group with Contributor rights on a SharePoint Site Collection. Since many sites had broken inheritance, using the UI was not an option so I created a small PowerShell Script that enumerates all Webs and if the Inheritance is broken, it adds the group with the specified Role.

Notes:

• The If command uses the $web.Url.Contains directive in order to modify the rights only on a subset of sites. If all Webs have to be crawled, use if ($web.HasUniquePerm -and $web.RequestAccessEnabled) instead.
• This script modifies permissions only on webs. Lists and Items with unique permission will not be touched.

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


$site = Get-SPSite -Identity "http://spdev/sites/SiteCollection"


foreach($web in $site.AllWebs)
    {

    if ($web.HasUniquePerm -and $web.RequestAccessEnabled -and ($web.Url.Contains("/SiteCollection/BU1") -or $web.Url.Contains("/SiteCollection/BU2")))
        {
            $account = $web.EnsureUser("Domain\QATeam")
            $role = $web.RoleDefinitions["Contribute"]

            $assignment = New-Object Microsoft.SharePoint.SPRoleAssignment($account)
            $assignment.RoleDefinitionBindings.Add($role)

            $web.RoleAssignments.Add($assignment)
        }
    $web.Dispose()
    }
$site.Dispose()

References

• http://social.technet.microsoft.com/Forums/sharepoint/en-US/4373cef1-2471-4199-aa16-784684fc82de/adding-permission-to-list-using-powershell

SharePoint Start Workflow on All Items of a List via PowerShell

To start a List Workflow in SharePoint on All Items of a list is a pain through GUI but it's a piece of cake in PowerShell. Note that the Workflow will not start instantly (as opposed to triggering through the UI) but in maximum 5 minutes because it's started through the SharePoint Timer Service.

#updated on 10/17/2013

# URL of the Site
$web = Get-SPWeb -Identity "https://sharepointsrv/site1"

$manager = $web.Site.WorkFlowManager

# Name of the list
$list = $web.Lists["Shared Documents"]

# Name of the Workflow
$assoc = $list.WorkflowAssociations.GetAssociationByName("On Item Created","en-US")

$data = $assoc.AssociationData
$items = $list.Items
foreach($item in $items)
 {
 $wf = $manager.StartWorkFlow($item,$assoc,$data,$true)
 }
 
$manager.Dispose()
$web.Dispose()
#

References

• http://social.technet.microsoft.com/Forums/sharepoint/en-US/dffdceab-f2fe-4886-8e2a-ea26cf08c78a/how-to-start-a-speific-workflow-programatically-in-sharepoint-2010-via-powershell

SharePoint 2013 Meeting Workspace Page Not Found

The Problem

I have some legacy Meeting Workspaces imported over from SharePoint 2010 that are still in use. Recently I enabled the Navigate Up button on a test site collection using this guide because users are really missing that feature. After applying the new master page to all subsites, I observed that the meeting workspace is no longer working. If I click on a meeting from a different date, a Page Not Found error is displayed on the site.

The logs:

 20635 08/22/2013 15:56:05.84   w3wp.exe (0x15D0)                         0x2EDC  SharePoint Foundation           Logging Correlation Data        xmnv  Medium    Name=Request (GET:https://spdevsrv:443/sites/dev1/WeeklyStatus/undefined?InstanceID=20130710&Paged=Next&p_StartTimeUTC=20130703T120000Z&View=%7bFEF87B6F%2d54D6%2d44CD%2d8055%2dFA404C0A5B50%7d)  57843b9c-4d5d-80a3-b6fd-c40b260782c1
 20636 08/22/2013 15:56:05.84   w3wp.exe (0x15D0)                         0x2EDC  SharePoint Foundation           Authentication Authorization    agb9s  Medium    Non-OAuth request. IsAuthenticated=True, UserIdentityName=0#.w|domain\user, ClaimsCount=43  57843b9c-4d5d-80a3-b6fd-c40b260782c1
 20637 08/22/2013 15:56:05.85   w3wp.exe (0x15D0)                         0x58E8  SharePoint Foundation           General                         af71  Medium    HTTP Request method: GET  57843b9c-4d5d-80a3-b6fd-c40b260782c1
 20638 08/22/2013 15:56:05.85   w3wp.exe (0x15D0)                         0x58E8  SharePoint Foundation           General                         af75  Medium    Overridden HTTP request method: GET  57843b9c-4d5d-80a3-b6fd-c40b260782c1
 20639 08/22/2013 15:56:05.85   w3wp.exe (0x15D0)                         0x58E8  SharePoint Foundation           General                         af74  Medium    HTTP request URL: /sites/dev1/WeeklyStatus/undefined?InstanceID=20130710&Paged=Next&p_StartTimeUTC=20130703T120000Z&View=%7bFEF87B6F%2d54D6%2d44CD%2d8055%2dFA404C0A5B50%7d  57843b9c-4d5d-80a3-b6fd-c40b260782c1
 20640 08/22/2013 15:56:05.85   w3wp.exe (0x15D0)                         0x58E8  SharePoint Foundation           Files                           aise3  Medium    Failure when fetching document. 0x80070002  57843b9c-4d5d-80a3-b6fd-c40b260782c1
 20641 08/22/2013 15:56:05.87   w3wp.exe (0x15D0)                         0x5198  SharePoint Foundation           Monitoring                      b4ly  Medium    Leaving Monitored Scope (Request (GET:https://spdevsrv:443/sites/dev1/WeeklyStatus/undefined?InstanceID=20130710&Paged=Next&p_StartTimeUTC=20130703T120000Z&View=%7bFEF87B6F%2d54D6%2d44CD%2d8055%2dFA404C0A5B50%7d)). Execution Time=37.2469  57843b9c-4d5d-80a3-b6fd-c40b260782c1

The Solution

You need to apply the mwsdefaultv15.master page to all Meeting Workspace sites. Since the SharePoint Server Publishing feature is not enabled (and cannot be enabled) on this type of site, you have to do the job in PowerShell.

$web =  Get-SPWeb -Identity "https://spdevsrv:443/sites/dev1"
$web.MasterUrl = "/sites/dev1/_catalogs/masterpage/mwsdefaultv15.master"
$web.CustomMasterUrl = "/sites/dev1/_catalogs/masterpage/mwsdefaultv15.master"
$web.Update()
$web.Dispose()

If you have a bunch of Meeting Workspaces then the script below might come in handy. For the sake of safety please back up your Site Collection first.

$site = Get-SPSite -Identity "https://spdevsrv:443/sites/dev1"
$webs = $site.AllWebs

$CorrectMasterPage = "/_catalogs/masterpage/mwsdefaultv15.master"

foreach($web in $webs)
 {
 if (($web.WebTemplate -eq "MPS") -and ($web.WebTemplateId -eq 2))
  {
  write-host "Updating" $web.Url
  $MasterPageURL = $web.ServerRelativeUrl + $CorrectMasterPage
  $web.MasterUrl = $MasterPageURL
  $web.CustomMasterUrl = $MasterPageURL
  $web.Update()
  }
 $web.Dispose()
 }

References

• http://www.manageprojectsonsharepoint.com/blog/2012/12/19/restore-navigate-up-on-sharepoint-2013/
• http://www.toddklindt.com/blog/Lists/Posts/Post.aspx?ID=226
• http://social.technet.microsoft.com/Forums/en-US/34039e09-7b66-4bf6-9087-4bac24f6cae8/meeting-workspace-woes-custom-master-page

Find SharePoint Web Template Name using PowerShell

You can find out the Template Name and Id of a SharePoint Site using the following PowerShell command:

$web =  Get-SPWeb -Identity "http://spsrv/sites/web1/"
Write-Host $web.WebTemplate "," $web.WebTemplateId

To list all Template Names and IDs:

Get-SPWebTemplate | select ID, Name, Title | Sort-Object ID

References:

• http://blogs.technet.com/b/praveenh/archive/2013/04/04/get-a-list-of-web-templates-and-ids-in-a-sharepoint-site.aspx
• http://social.technet.microsoft.com/Forums/sharepoint/en-US/07cf57c3-8965-4f85-8911-2c95aa78fed5/how-to-get-the-name-of-the-template-in-sharepoint-2010-using-powershell

SharePoint PowerShell Create View Based on Existing View

The following PowerShell script crawls the specified lists, copies the fields from the All Items views and creates a new view named "Created By Me" and sets it as default. Tested with SharePoint 2010 but should work with 2013 as well. Tested with list but not with Document Libraries.

I used this script as a base.

$ver = $host | select version
if ($ver.Version.Major -gt 1) {$host.Runspace.ThreadOptions = "ReuseThread"} 
if ((Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue) -eq $null) 
{
 Add-PSSnapin "Microsoft.SharePoint.PowerShell"
}

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint") 

$web = Get-SPWeb -Identity "http://portal/sites/Requests"
$lists=$web.Lists["User Request", "Hardware Request",  "Employee Request"]
$SourceView="All Items"
$NewViewName="Created By Me"
$NewViewDefault=$true


foreach($list in $lists) {

 $view = $list.Views[$SourceView]
 $Viewfields = $list.Views[$SourceView].ViewFields.ToStringCollection()
 $viewRowLimit="100"
 $viewPaged=$true
 $viewDefaultView=$NewViewDefault

# Setting the Query for the View
 $viewQuery = ""
 $viewName = $NewViewName

# Finally – Provisioning the View
 $myListView = $list.Views.Add($viewName, $viewFields, $viewQuery, 100, $True, $False, "HTML", $False)

# You need to Update the View for changes made to the view
# Updating the List is not enough
 $myListView.DefaultView = $True
 $myListView.Update()
 $list.Update()
}

$web.Dispose()

Microsoft.Office.Web.Environment.Sharepoint.CacheCreationJob - Object reference not set to an instance of an object.

Problem

The Event Log of the SharePoint Server is reddish due to events occurring every 5 minutes:

Event Log Error:

Log Name:      Application

Source:        Microsoft-SharePoint Products-SharePoint Foundation

Date:          2/18/2013 7:00:01 PM

Event ID:      6398

Task Category: Timer

Level:         Critical

Keywords:     

Description:

The Execute method of job definition Microsoft.Office.Web.Environment.Sharepoint.CacheCreationJob (ID 8ea0b71c-08c4-4644-a0bc-c361c391bc45) threw an exception. More information is included below.

Object reference not set to an instance of an object.

Event Xml:

 

   

    6398

    14

    1

    12

    0

    0x4000000000000000

   

    355489

   

   

    Application

 

 

    Microsoft.Office.Web.Environment.Sharepoint.CacheCreationJob

    8ea0b71c-08c4-4644-a0bc-c361c391bc45

    Object reference not set to an instance of an object.

 

ULS Log:

3880        02/18/2013 19:05:00.04   OWSTIMER.EXE (0x0AA0)                     0x2C2C  SharePoint Foundation           Monitoring                      nasq  Medium    Entering monitored scope (Timer Job OfficeWebApplicationsCacheCreationJob)  1ad52fe0-5766-4a71-8991-e4aa0cb9b356

3881        02/18/2013 19:05:00.04   OWSTIMER.EXE (0x0AA0)                     0x2C2C  Office Web Apps                 Office Viewing Architecture     cu85  Medium    Cache Creation Job Starting [JobId: 8ea0b71c-08c4-4644-a0bc-c361c391bc45]  1ad52fe0-5766-4a71-8991-e4aa0cb9b356

3882        02/18/2013 19:05:00.04   OWSTIMER.EXE (0x0AA0)                     0x2C2C  Office Web Apps                 Office Viewing Architecture     cu86  Exception  Cache Creation Job encountered an exception [JobId: 8ea0b71c-08c4-4644-a0bc-c361c391bc45] System.NullReferenceException: Object reference not set to an instance of an object.     at Microsoft.SharePoint.SPSite.PreinitializeServer(SPRequest request)     at Microsoft.SharePoint.SPSite.GetSPRequest()     at Microsoft.SharePoint.SPSite.get_Request()     at Microsoft.SharePoint.SPSite.get_Usage()     at Microsoft.Office.Web.Environment.Sharepoint.WacSharepointCacheOM.EnsureCacheSiteCollection(SPWebApplication webApp, Boolean elevateReturn, String ownerLogin, String ownerName, String ownerEmail)     at Microsoft.Office.Web.Environment.Sharepoint.WacSharepointCacheOM.EnsureCacheSiteCollection(SPWebApplication webApp)     at Microsoft.Office.Web.Environment.Sharepoint.CacheCreationJob.Execute(SPJo...  1ad52fe0-5766-4a71-8991-e4aa0cb9b356

3883        02/18/2013 19:05:00.04*  OWSTIMER.EXE (0x0AA0)                     0x2C2C  Office Web Apps                 Office Viewing Architecture     cu86  Exception  ...bState jobState)  1ad52fe0-5766-4a71-8991-e4aa0cb9b356

3884        02/18/2013 19:05:00.04   OWSTIMER.EXE (0x0AA0)                     0x2C2C  Office Web Apps                 Unified Logging Service         c91s  Monitorable  Watson bucket parameters: Office Web Apps, ULSException14, 52a97675 "office web apps", 0e00178d "14.0.6029.0", 1f65804a "microsoft.sharepoint", 0e0017ee "14.0.6126.0", 5021a68d "wed aug 08 02:36:45 2012", 000057b1 "000057b1", 01ab21e4 "01ab21e4", 4a6d3421 "nullreferenceexception", 63753836 "cu86"  1ad52fe0-5766-4a71-8991-e4aa0cb9b356

3885        02/18/2013 19:05:00.04   OWSTIMER.EXE (0x0AA0)                     0x2C2C  SharePoint Foundation           Timer                           6398  Critical  The Execute method of job definition Microsoft.Office.Web.Environment.Sharepoint.CacheCreationJob (ID 8ea0b71c-08c4-4644-a0bc-c361c391bc45) threw an exception. More information is included below.  Object reference not set to an instance of an object.  1ad52fe0-5766-4a71-8991-e4aa0cb9b356

3886        02/18/2013 19:05:00.04   OWSTIMER.EXE (0x0AA0)                     0x2C2C  SharePoint Foundation           Timer                           72ae  Unexpected  Exception stack trace:    at Microsoft.SharePoint.SPSite.PreinitializeServer(SPRequest request)     at Microsoft.SharePoint.SPSite.GetSPRequest()     at Microsoft.SharePoint.SPSite.get_Request()     at Microsoft.SharePoint.SPSite.get_Usage()     at Microsoft.Office.Web.Environment.Sharepoint.WacSharepointCacheOM.EnsureCacheSiteCollection(SPWebApplication webApp, Boolean elevateReturn, String ownerLogin, String ownerName, String ownerEmail)     at Microsoft.Office.Web.Environment.Sharepoint.WacSharepointCacheOM.EnsureCacheSiteCollection(SPWebApplication webApp)     at Microsoft.Office.Web.Environment.Sharepoint.CacheCreationJob.Execute(SPJobState jobState)     at Microsoft.SharePoint.Administration.SPTimerJobInvokeInternal.Invoke(SPJobDefinition jd, Guid targetInstanceId, Boolean isTimerSer...  1ad52fe0-5766-4a71-8991-e4aa0cb9b356

3887        02/18/2013 19:05:00.04*  OWSTIMER.EXE (0x0AA0)                     0x2C2C  SharePoint Foundation           Timer                           72ae  Unexpected  ...vice, Int32& result)  1ad52fe0-5766-4a71-8991-e4aa0cb9b356

3891        02/18/2013 19:05:00.05   OWSTIMER.EXE (0x0AA0)                     0x2C2C  SharePoint Foundation           Monitoring                      b4ly  Medium    Leaving Monitored Scope (Timer Job OfficeWebApplicationsCacheCreationJob). Execution Time=14.8229  1ad52fe0-5766-4a71-8991-e4aa0cb9b356

3892        02/18/2013 19:05:00.05   OWSTIMER.EXE (0x0AA0)                     0x2C2C  SharePoint Foundation           Performance                     nask  Monitorable  An SPRequest object was not disposed before the end of this thread.  To avoid wasting system resources, dispose of this object or its parent (such as an SPSite or SPWeb) as soon as you are done using it.  This object will now be disposed.  Allocation Id: {ADFDB253-11D8-4E24-806E-DF9C81239608}  To determine where this object was allocated, set Microsoft.SharePoint.Administration.SPWebService.ContentService.CollectSPRequestAllocationCallStacks = true.  1ad52fe0-5766-4a71-8991-e4aa0cb9b356

The problem is the "Office Web Apps Cache Creation" SP Job tries to run every 5 minutes but fails for some reason. The Cleanup job also fails that is scheduled to run daily.

Solution

I tried without luck:

• modifying the Cache DB's settings with Set-SPOfficeWebAppsCache
• moving the Cache Db onto a different Content Database
• removing the Word Viewing Service Application
• stopping Excel Calculation Services, PowerPoint Service, Word Viewing Service
• disabling and re-enabling the Office Web Apps Site Collection Feature

Then, I mounted the Office Web Apps ISO, and started a repair. Rebooted the server and good to go! No more errors.

UPDATE: Don't forget to run the SharePoint 2010 Products Configuration Wizard after the installation if your search doesn't work.

How to Restore SharePoint MySites from Backup

The Story

On Friday I had a very bad surprise. All managers from the company were waked with this emails like:

Subject: The My Site of X Y is scheduled for deletion

The My Site of X Y is scheduled for deletion in 14 days. As their manager you are now the temporary owner of their site. This temporary ownership gives you access to the site to copy any business-related information you might need. To access the site use this URL: https://mysites.portal.domain.local/personal/xy

Pretty uncomfortable. The day before this I deleted the AD Sync Connection from SharePoint's User Profile Service Application and this caused it. The sync job ran and with no connections SP thought that all the users have been deleted.

Theoretically there should be 14 days until these profiles are deleted but for some reason I can't see past activity for the use users.

Long story short, I need to restore MySites from a backup.


The Process

The restoring process is simpler than thought. Later I found out that I had to restore the User Profile Service Application as well, I will detail that later.

Restoring MySites from Backup

1. Unmount the MySites Content Database 

Unmount the MySites content database by going to Central Administration -> Application Management -> Manage Content Databases.

Select the MySites Web Application on the right

Check Remove Content Database and click OK

2. Restore the Database in SQL Server Management Studio from backup


3. Add back the content database in Central Administration

Central Administration -> Application Management -> Manage Content Databases.

Select the Mysites Web Application on the right

Click on Add Content Database and fill out the form

I had another issue: The profiles' data was wiped. I am referring to the content seen under the marked tabs:

This data is stored in the User Profile Service Application's databases (Profile Database and/or Social Database, not sure). So these had to be restored as well.

Restoring the User Profile Service Application from Backup

The process is fairly simple as long as you have the SQL backups.


1. Stop the User Profile Service and User Profile Synchronization Service in Central Administration -> Manage Services on Server

2. Restore the Profile DB and Social DB from backup on the SQL Server

3. Start the User Profile Service and User Profile Synchronization Service in Central Admin.

Unexpected: Failed to get legacy traces

SharePoint Server 2010 Enterprise.

Checking the SharePoint Logs, found the following errors repeating each minute:

OWSTIMER.EXE (0x17A8)                     0x3264  SharePoint Foundation           Monitoring                      nasq  Medium    Entering monitored scope (Timer Job job-diagnostics-io-intensive-query-provider)

OWSTIMER.EXE (0x17A8)                     0x3264  Extended Diagnostic Providers   SqlTrace Provider               2001  Unexpected  Failed to get legacy traces

OWSTIMER.EXE (0x17A8)                     0x3264  SharePoint Foundation           Timer                           6398  Critical  The Execute method of job definition Microsoft.SharePoint.Diagnostics.SPIOIntensiveQueryDiagnosticProvider (ID c2f9c9c0-5d41-43a4-98bf-c59b007c3065) threw an exception. More information is included below.  You do not have permission to run 'SP_TRACE_CREATE'.

OWSTIMER.EXE (0x17A8)                     0x3264  SharePoint Foundation           Timer                           72ae  Unexpected  Exception stack trace:    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)     at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)     at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)     at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)     at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)     at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Bo...

OWSTIMER.EXE (0x17A8)                     0x3264  SharePoint Foundation           Timer                           72ae  Unexpected  ...olean returnStream, String method, DbAsyncResult result)     at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)     at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()     at Microsoft.SharePoint.Diagnostics.SqlTraceHelper.CreateTrace()     at Microsoft.SharePoint.Diagnostics.SqlTraceStreamer.Init(SqlTraceParameters parameters)     at Microsoft.SharePoint.Diagnostics.SPSqlTraceDiagnosticProvider.InitializeBackgroundThreads()     at Microsoft.SharePoint.Diagnostics.SPSqlTraceDiagnosticProvider.Sync()     at Microsoft.SharePoint.Diagnostics.SPDiagnosticsProvider.Execute(Guid targetInstanceId)     at Microsoft.SharePoint.Administration.SPTimerJobInvokeInternal.Invoke(SPJobDefinition jd, Guid targetInstanceId, Boolean isTi...

OWSTIMER.EXE (0x17A8)                     0x3264  SharePoint Foundation           Timer                           72ae  Unexpected  ...merService, Int32& result)

OWSTIMER.EXE (0x17A8)                     0x3264  SharePoint Foundation           Monitoring                      b4ly  Medium    Leaving Monitored Scope (Timer Job job-diagnostics-io-intensive-query-provider). Execution Time=9.3616 

Solution:

Grant the user running the SharePoint 2010 Timer (SPTimerV4) service, which is usually the Farm account, serveradmin or sysadmin rights on the SQL Server. Then, restart the Timer service.

Explanation:

blogs.msdn.com/b/kaevans/archive/2012/02/09/sharepoint-error-6398-you-do-not-have-permission-to-run-sp-trace-create.aspx - great article!

Some Advanced InfoPath Rules

In this tutorial I will create a "smart" InfoPath form that will change its contents based on some scenarios.

Scenario: 

> employees subscribe each month for some company benefit in the upcoming month.
> subscriptions are open from 24th till the end of the month otherwise closed.
> once an employee has subscribed it cannot subscribe again in that month (show instead a thank you page).

Solution:

This can be achieved by creating a Site Workflow that registers the data into a SharePoint List. The Workflow form is customized with InfoPath. In this post I will insist on the form.

Prerequisites:

1. Have a SharePoint List to store subscriptions. Fields:

• Person (Person type)
• MonthTag (String type. It will contain "2012-11" like strings to identify the month when the subscription was made. This will allow easy grouping and filtering for later processing.)
• SuperscriptionID (String type. Combination of Person Username and MonthTag to determine if a person already subscribed for that month. Its value is calculated by the workflow and filled in at creation. E.g.: "2012-11.Levente.Rog")
• Any other fields that need input from the user.

2. A Site Workflow that creates the subscription (adds an entry in the above mentioned list). End users will Start the workflow itself (by giving them a link). This Workflow will have the following steps:

• Generate the Month Tag (2012-11 like strings)
• Generate SuperscriptionID (2012-11.Levente.Rog -like strings)
• Create an Item in the Subscriptions List with the previously mentioned values. Using the Workflow Context:Initiator variable we know who is the logged in user so the end user does not have to fill out his own name in the form (that would be lame isn't?)
• Send a confirmation email

Upon request I can create a tutorial explaining in greater detail covering these workflow steps.

Form Customization

The form of the Workflow is the one that I will customize now.
This is how I managed to solve this scenario:


What I did is I created separate sections for three scenarios (disregard the 201-12. section for now, I will explain that later).

1. First scenario is when registrations are open. Two conditions have to fulfill for this section to show up:

• Date of month >= 24
• User has not subscribed yet in the upcoming month

To achieve this, I created the following InfoPath Formatting rules with the action to Hide the section.

• The expression: number(substring(xdDate:Today(), 9, 2)) < 24

• Any occurence of Subscription ID is equal to concat(substring(addDays(today(), 30), 1, 7), ".", translate(userName(), "ABCDEFGHIJKLMNOPQRSTUVWYXZ", "abcdefghijklmnopqrstuvwyxz"))
  
  
  What I did with that function is:
  Generate the "Month Tag" that is a unique string each month. Since we subscribe for benefits for the next month we calculate one month (addDays 30) forward and crop the first 7 characters from the ISO Date to get a 2012-11-like key. We concatenate that with the lowercase username. This way we'll get something like:
  2012-11.levente.rog . We also record that same string in the SharePoint list as well. This way we know if that user already subscribed for that month.
  - Any occurrence of Subscription ID - we get that by creating a second connection to the same SharePoint List (Data Ribbon -> To SharePoint List). If any of those empty comes out true, the section will be hidden.

2. If the user has already subscribed show a thank you message.
One rule I have here, basically the negated version of rule #2 seen previously:

• All occurences of Subscription ID are not equal to concat(substring(addDays(today(), 30), 1, 7), ".", translate(userName(), "ABCDEFGHIJKLMNOPQRSTUVWYXZ", "abcdefghijklmnopqrstuvwyxz"))

3. Show a registrations are closed message if date is <= 24th.

This is also a variation, negating the #1 rule used in the first scenario.

• The expression: number(substring(xdDate:Today(), 9, 2)) >= 24

4. That dummy "2012-12" section is just a calculated value containing the Subscription ID generation formula. For some reason, when I haven't actually used this formula, it was not evaluated in the rule. This section is always hidden from the end user (by creating a dummy rule: myfield does not equal "asdf" => Hide).

• Calculated Value XPath expression: concat(substring(xdDate:AddDays(xdDate:Today(), 30), 1, 7), ".", translate(xdUser:get-UserName(), "ABCDEFGHIJKLMNOPQRSTUVWYXZ", "abcdefghijklmnopqrstuvwyxz"))

That's it. Save and Publish.

The workflow is redirected to itself by changing the &Source= parameter in the URL the link to the workflow itself. For example:

http://sptest/Subscriptions/_layouts/IniWrkflIP.aspx?TemplateID={fd570692-3e3e-4c6d-a613-d11932bfc5e2}&Source=http://sptest/Subscriptions/_layouts/IniWrkflIP.aspx?TemplateID={fd570692-3e3e-4c6d-a613-d11932bfc5e2}

After completing the workflow, it will be redirected to itself again, showing the same form. But since the user now is subscribed, the "Thank you" section will show up this time.

SharePoint Workflow Cannot Access External List

SharePoint Server 2010.


I set up a Site Workflow that reads some properties of an employee from an external SQL Database.
> created a dbreader user in SQL Server
> added the SQL credentials to Secure Store
> created External Content Type in Designer
> created External List based on the previously created ECT
> user is able to see data in the External List
> created Workflow that reads data from the External List.
> running the Workflow will return empty data for any query from the External List
> following error in the logs

w3wp.exe (0x029C)                         0x3D4C  Secure Store Service            Secure Store                    efr5  High      ValidateCredentialClaims - Access Denied: Claims stored in the credentials did not match with the group claim for a group app.

w3wp.exe (0x029C)                         0x3D4C  Secure Store Service            Secure Store                    7493  Critical  The Microsoft Secure Store Service application Secure Store Service failed to retrieve credentials. The error returned was 'Access is denied.'. For more information, see the Microsoft SharePoint Products and Technologies Software Development Kit (SDK).

w3wp.exe (0x029C)                         0x3D4C  Secure Store Service            Secure Store                    efp6  Monitorable  GetRestrictedCredentials failed with the following exception: System.ServiceModel.FaultException`1[Microsoft.Office.SecureStoreService.Server.SecureStoreServiceFault]: Access is denied. (Fault Detail is equal to Microsoft.Office.SecureStoreService.Server.SecureStoreServiceFault). &nbsp

w3wp.exe (0x43B8)                         0x4320  SharePoint Foundation           Workflow Infrastructure         el2x  Medium    The workflow could not find the specified item in the external data source. Make sure the user has permissions to access the external data source and read items.

Solution:

The System Account had no permission over the Secure Store Object to read the credentials to the SQL Server. The System Account is the account which is running the Application Pool .

Central Administration -> Manage Service Applications -> Secure Store Service : Manage -> Edit the application ID -> Next -> Next -> Add the AppPool user to the Members list.

A really nice, detailed guide on Business Connectivity Services can be found here.

SharePoint Site Owner Cannot Modify Group Memberships

SharePoint Server 2010.
User is member of the Site Owners group, however he is not able to modify group membership. He can grant permissions only directly.

As seen in the screenshot, the New menu is missing when opening the Group's membership.

A possible problem is that the Group owner field in Group Settings is incorrect. Maybe a legacy setting from SharePoint 2007 or a manually created SharePoint group.

Solution: Add the Owners group as the group owner.

In this case, if the site's name is Dashboard, add Dashboard Owners as the owner of all 3 site-related groups: Dashboard Visitors, Dashboard Members, Dashboard Owners.

You may need Site Collection or Farm admin rights to restore these permissions.