Moved to Hexo at wutek.co.uk
This blog is no longer active, please go to
wutek.co.uk
Finally got round to migrating all my posts in to Hexo and hosting from my own domain at wutek.co.uk
All new posts will appear on there, but I’ll leave this blog here for archive purposes.
All posts from here are also available on the new blog too.
PowerPivot Management Dashboard: Error with “Infrastructure – Server Health” report
When loading this page in Central Administration, I was being presented with a Excel Services error and a correlation ID.
Looking at the ULS logs I noticed the following error.
Insufficient SQL database permissions for user ‘Name: <DOMAIN\USERNAME> SID: <SID-CODE> ImpersonationLevel: None’ in database ‘<DATABASE-NAME>’ on SQL Server instance ‘<SQL-INSTANCE>’. Additional error information from SQL Server is included below. The EXECUTE permission was denied on the object ‘proc_ReturnWebFeatures’, database ‘<DATABASE-NAME>’, schema ‘dbo’. 1d0d479c-5a36-c0dc-912b-5bc267b09a0a
The database name was referring to the Content Admin database of Central Administration and the user name was referring to the service application account that the PowerPivot service application had been configured to run under.
The proc_ReturnWebFeatures stored procedure required the user to be a member of the SPDataAccess role to allow EXECUTE permissions. The service application user was only in the WSS_Content_Application_Pools role.
If you run the following script, this will grant the correct rights in the database
$url = “http://centraladminsite:2013”
$webApp = Get-SPWebApplication -Identity $url
$webApp.GrantAccessToProcessIdentity(“DOMAIN\serviceapplicationuser”)
Now when you load up the PowerPivot Management Dashboard, you report should be displayed.
Updating the Version property of a field to the Version attribute in the fields SchemaXml
THIS IS VERY BAD. WE ALL KNOW THAT MAKING DIRECT UPDATES TO THE DATABASE WILL CREATE SOME SORT OF BLACK HOLE AND KITTENS DIE IN REDMOND ETC, ETC. I HAVEN’T TESTED THE LONG TERM EFFECTS AND ONLY DID THIS ON MY DEVELOPMENT ENVIRONMENT TO GET ME OUT OF A PICKLE.
Disclaimer over.
If you ever get this error when trying to update a field, either through the web UI or programmatic-ally it normally means your Version fields are out of sync. This can occur if your field has been created using the Schema XML from another environment or you have migrated content using some dodgy 3rd party app.
Run this update on your content databases and you all should be good again.
UPDATE [MyContentDatabase].[dbo].[ContentTypes]
SET [Version] = CAST([Definition] AS XML).value('(/Field/@Version)[1]', 'int')
WHERE CAST([Definition] AS XML).value('(/Field/@Version)[1]', 'int') <> [Version]
Traversing Taxonomy Terms from branch to leaf
I had a need to show the path of terms, starting from a specific term and then showing the path to the final children,
This would involve traversing the structure as the depth is unknown, so I wrote this piece of code to accomplish this using the Client Components.
Please feel free to steal and improve (Only if you give back the improved code though!)
private IEnumerable<Path> Paths(bool includeRoot, Guid termId, string siteUrl)
{
var paths = new List<Path>();
Action<ClientContext, Term, String> traverse = null;
traverse = (context, term, path) =>
{
context.Load(term, t => t.Id, t => t.Name, t => t.Terms);
context.ExecuteQuery();
if (!(term.Id == termId && !includeRoot))
path += path == string.Empty ? term.Name : ” > {0}”.ToFormat(term.Name);
if (term.Terms.Count == 0)
{
paths.Add(new Path
{
TermId = term.Id,
PathText = path
});
}
else
{
foreach (var st in term.Terms)
{
traverse(context, st, path);
}
}
};
using (var context = new ClientContext(siteUrl))
{
var rootTerm = TaxonomySession.GetTaxonomySession(context).GetTerm(termId);
traverse(context, rootTerm, string.Empty);
}
return paths;
}
public class Path
{
public Guid TermId { get; set; }
public string PathText { get; set; }
}
Field in List not available in ListItem using Managed Client Object Model
If you have a field called “Folder” it will not be available in the returned ListItem object
e.g. listItem[“Folder”].ToString()
The inner workings of the ListItem object uses an ExpandoObject to store the properties. It seems it mixes this up in the FieldValues Collection with all your custom fields. The Folder property then takes the value from the FieldValues collection to make it available to the Folder property. Thus making your own Folder field “disappear”
Here is how you can replicate it.
- Create a list based on the Custom List template
- Add a column called “Folder” and make it a text field
- Add a column called “DisplayName” and make it a text field
- Add a column called “MyField” and make it a text field
Add a couple of dummy rows of data
Create a console application in VS that references Microsoft.SharePoint.Client and Microsoft.SharePoint.Client.Runtime
Add the following code to the Main method
using (var context = new ClientContext("http://siteurl"))
{
var query = CamlQuery.CreateAllItemsQuery();
var listItems = context.Web.Lists.GetByTitle("Testing").GetItems(query);
context.Load(listItems);
context.ExecuteQuery();
foreach (var listItem in listItems)
{
Console.WriteLine("Title: {0}", listItem["Title"]);
Console.WriteLine("Folder: {0}", listItem["Folder"]);
Console.WriteLine("DisplayName: {0}", listItem["DisplayName"]);
Console.WriteLine("MyField: {0}", listItem["MyField"]);
}
}
Here is the code from .Net Reflector that shows the ListItem object populating the properties. It’s also worth noting that the other properties detailed in this method will have the same problem, but it’s unlikely that you’ll call a field FileSystemObject!
protected override bool InitOnePropertyFromJson(string peekedName, JsonReader reader)
{
bool flag = base.InitOnePropertyFromJson(peekedName, reader);
if (!flag)
{
switch (peekedName)
{
case "AttachmentFiles":
flag = true;
reader.ReadName();
base.UpdateClientObjectPropertyType("AttachmentFiles", this.AttachmentFiles, reader);
this.AttachmentFiles.FromJson(reader);
return flag;
case "ContentType":
flag = true;
reader.ReadName();
base.UpdateClientObjectPropertyType("ContentType", this.ContentType, reader);
this.ContentType.FromJson(reader);
return flag;
case "DisplayName":
flag = true;
reader.ReadName();
base.ObjectData.Properties["DisplayName"] = reader.ReadString();
return flag;
case "EffectiveBasePermissions":
flag = true;
reader.ReadName();
base.ObjectData.Properties["EffectiveBasePermissions"] = reader.Read<BasePermissions>();
return flag;
case "EffectiveBasePermissionsForUI":
flag = true;
reader.ReadName();
base.ObjectData.Properties["EffectiveBasePermissionsForUI"] = reader.Read<BasePermissions>();
return flag;
case "FieldValuesAsHtml":
flag = true;
reader.ReadName();
base.UpdateClientObjectPropertyType("FieldValuesAsHtml", this.FieldValuesAsHtml, reader);
this.FieldValuesAsHtml.FromJson(reader);
return flag;
case "FieldValuesAsText":
flag = true;
reader.ReadName();
base.UpdateClientObjectPropertyType("FieldValuesAsText", this.FieldValuesAsText, reader);
this.FieldValuesAsText.FromJson(reader);
return flag;
case "FieldValuesForEdit":
flag = true;
reader.ReadName();
base.UpdateClientObjectPropertyType("FieldValuesForEdit", this.FieldValuesForEdit, reader);
this.FieldValuesForEdit.FromJson(reader);
return flag;
case "File":
flag = true;
reader.ReadName();
base.UpdateClientObjectPropertyType("File", this.File, reader);
this.File.FromJson(reader);
return flag;
case "FileSystemObjectType":
flag = true;
reader.ReadName();
base.ObjectData.Properties["FileSystemObjectType"] = reader.ReadEnum<FileSystemObjectType>();
return flag;
case "Folder":
flag = true;
reader.ReadName();
base.UpdateClientObjectPropertyType("Folder", this.Folder, reader);
this.Folder.FromJson(reader);
return flag;
case "Id":
flag = true;
reader.ReadName();
base.ObjectData.Properties["Id"] = reader.ReadInt32();
return flag;
case "ParentList":
flag = true;
reader.ReadName();
base.UpdateClientObjectPropertyType("ParentList", this.ParentList, reader);
this.ParentList.FromJson(reader);
return flag;
}
}
return flag;
}
Here’s a link in TechNet Forums to the initial discussion and a link to the reported bug
The tale of two ampersands in a Taxonomy term
There are two types of ampersands that you need to be aware of when playing with SharePoint Taxonomy
Our favorite and most loved
& ASCII Number: 38
And the impostor
＆ ASCII Number: 65286
After reading this article by Nick Hobbs, it became apparent that when you create a term it replaces the 38 ampersand with a 65286 ampersand.
This then becomes a problem if you want to do a comparison with your original source (spreadsheet, database, etc) as they are no longer the same.
As detailed in Nick’s article, you can use the TaxonomyItem.NormalizeName method to create a “Taxonomy” version of your string for comparison.
Below is the code I used in the SharePoint 2013 Client Component which is a little different from the server code.
string myString = “This contains &”;
using (var context = new ClientContext(“http://myurl”))
{
var result = TaxonomyItem.NormalizeName(context, myString);
context.ExecuteQuery();
string normalisedString = result.Value;
}
Search the recycle bin with Powershell
Useful piece of Powershell to search for a file(s) / item(s) in the site recycle bin and then restore it/them.
Change the regular expression after match to change what files to search for.
$spsite = (Get-SPSite “http://mysite” )
$files = $spsite.RecycleBin | ?{$_.Title -match “myfile\d{2}”}
foreach ($file in $files) {
Write-Host “Found $($file.Title)”
$spsite.RecycleBin.Restore($file.ID)
}
“Hack attack” jQuery in a list view
Useful jQuery snippet to add a “View Properties” icon, so the user doesn’t have to use context menu or ribbon (apparently they prefer this)
$(".ms-listviewtable > tbody > tr:first").append("<TH class='ms-vh2 ms-vh2-icon' noWrap>View</TH>");
$(".ms-listviewtable > tbody > tr").each(function() {
if ($('td:first', $(this)).hasClass("ms-vb-title"))
{
var id = $("td.ms-vb-title > div.ms-vb", $(this)).attr("id");
var viewLink = $("<td class='ms-vb-icon'><IMG style='CURSOR:hand; BORDER-RIGHT-WIDTH: 0px; BORDER-TOP-WIDTH: 0px; BORDER-BOTTOM-WIDTH: 0px; BORDER-LEFT-WIDTH: 0px' title='View Properties' alt=Search src='/_layouts/images/gosearch15.png' /></td>");
$(this).append(viewLink);
viewLink.click(function(event){
var options = {
url: "/MyLibrary/Forms/DispForm.aspx?ID=" + id,
title: "Document Properties",
allowMaximize: true,
showClose: true,
dialogReturnValueCallback: function(dialogResult, returnValue) { }
};
SP.UI.ModalDialog.showModalDialog(options);
});
}
});
This just adds an extra column to the end with an icon that opens the ‘View Properties’ dialog.
You can either use a Content Editor web part to make it view specific or add it to a global script for all views.
It’s a little hacky, but does the job.
It relies on the title column being available to extract the id of the item.
RunAs method for running code in specific user SPServiceContext
You may have the need to run some code in a SPServiceContext of a specific user. e.g. when dealing with user profiles or activities.
This can be achieved by using a combination of the SPUser, WindowsIdentity, GenericPrincipal and the GetContext method of SPServiceContext.
From these we can set the HttpContext.Current.User property to the specified user.
Don’t forget to set the context back after the code has executed!
Below is a helper method that makes this very easy.
I haven’t done any solid testing on performance etc. so the usual caveats apply.
// Example calling code
RunAs("http://asharepointsite", "THEDOMAIN\THEUSER", c =>
{
// The code to execute. e.g. get a the UserProfileManager as the passed in user
var upm = new UserProfileManager(c, true);
// Do some stuff to the UPM as the passed in user
});
// Helper method
private static void RunAs(string siteUrl, string userName, Action<SPServiceContext> contextToUse)
{
try
{
var currentSetting = false;
var currentHttpContext = HttpContext.Current;
SPUser currentUser = null;
if (SPContext.Current != null)
{
if (SPContext.Current.Web != null)
{
currentUser = SPContext.Current.Web.CurrentUser;
}
}
SPSecurity.RunWithElevatedPrivileges(delegate
{
using (var site = new SPSite(siteUrl))
{
using (var web = site.OpenWeb())
{
try
{
var user = web.EnsureUser(userName);
currentSetting = web.AllowUnsafeUpdates;
web.AllowUnsafeUpdates = true;
var request = new HttpRequest("", web.Url, "");
HttpContext.Current = new HttpContext(request,
new HttpResponse(
new StringWriter(CultureInfo.CurrentCulture)));
HttpContext.Current.Items["HttpHandlerSPWeb"] = web;
var wi = WindowsIdentity.GetCurrent();
var newfield = typeof (WindowsIdentity).GetField("m_name",
BindingFlags.NonPublic |
BindingFlags.Instance);
if (newfield != null) newfield.SetValue(wi, user.LoginName);
if (wi != null) HttpContext.Current.User = new GenericPrincipal(wi, new string[0]);
var elevatedContext = SPServiceContext.GetContext(HttpContext.Current);
contextToUse(elevatedContext);
//Set the HTTPContext back to "normal"
HttpContext.Current = currentHttpContext;
if (currentUser != null)
{
var winId = WindowsIdentity.GetCurrent();
var oldField = typeof (WindowsIdentity).GetField("m_name",
BindingFlags.NonPublic |
BindingFlags.Instance);
if (oldField != null) oldField.SetValue(winId, currentUser.LoginName);
if (winId != null)
HttpContext.Current.User = new GenericPrincipal(winId, new string[0]);
}
}
catch (Exception ex)
{
// Log or whatever
}
finally
{
web.AllowUnsafeUpdates = currentSetting;
}
}
}
});
}
catch (Exception exO)
{
// Log or whatever
}
}
A rolling stone gathers no... [add version here] 