Second Life of a Hungarian SharePoint Geek

September 2, 2018

How to delete events from a SharePoint calendar without messing up the Recycle Bin

Filed under: Bugs, Calendar, SP 2013 — Tags: , , — Peter Holpar @ 09:01

Recently we detected that a lot of deleted calendar entries appeared in the Recycle Bin of a SharePoint site. They were all originated from a specific calendar instance, and deleted by a custom application (written in C#) that is scheduled to run regularly to purge old entries.

Although we could have deleted the items using the bulk method described here, in this case we deleted the entries one by one. To get IDs of the “master” entries (entries that are not recurring event exception) we should delete, we run a CAML query like the below one, filtering on the EventType field of the items, just as described in my former post:

        <FieldRef Name=’EndDate’ />
        <Value Type=’DateTime’>2018-01-01 00:00:00</Value>
        <FieldRef Name=’EventType’/>
        <Value Type=’Integer’>2</Value>

You can read more about the meaning of the various EventType values in this blog post.

As I wrote in my post, we should (at least, theoretically, see explanation later) delete only the main entries, as all of the related entries (the recurring event exceptions, deleted and changed instances of the series) are deleted automatically by the system when you delete the main entry.

Having the IDs from the CAML query, we deleted the entries by iterating through the collection of IDs and invoked the the DeleteItemById method of the SPListItemCollection method, like:

foreach (int itemIDToDelete in itemIDsToDelete)

As you probably know, you can delete an item from code by invoking the Delete method of the SPListItem instance of the item (in this case the item is deleted immediately, without being recycled), or recycle it by calling the Recycle method of the SPListItem instance (in this case the item is simply moved to the Recycle Bin and you can restore it later if you wish). I should point out, that both of these methods call internally the private DeleteCore method of the SPListItem class, using the parameter value DeleteOp.Delete in the first case and DeleteOp.Recycle in the second case.

The DeleteItemById method invokes the Delete method however, so it should not miss up the Recycle Bin, as it obviously did in our case.

public void DeleteItemById(int id)

So what was the problem? After a short investigation and running a few tests, we found, that only recurring event exceptions got moved to the Recycle Bin when the system delete them automatically, the main entries were deleted permanently. It means, that the Delete method of the SPListItem class is buggy and the same is true for the DeleteItemById method, at least I don’t consider this behavior to be some kind of hidden feature.

How to solve the problem? If you have a lot of recurring event exceptions in your calendar, and don`t want to pollute your Recycle Bin, the best you can do to create some kind of extension method that deletes the related entries (recurring event exceptions) explicitly, not letting the system to delete them automatically.

I’ve created two extension methods, as shown below:

  1. public static void DeleteItemByIdIncludingRecurringEventExceptions(this SPListItemCollection items, int id)
  2. {
  3.     items.GetItemById(id).DeleteIncludingRecurringEventExceptions();
  4. }
  6. public static void DeleteIncludingRecurringEventExceptions(this SPListItem item)
  7. {
  8.     if (item == null)
  9.     {
  10.         throw new ArgumentNullException("item");
  11.     }
  13.     if (!item.ContentTypeId.IsChildOf(SPBuiltInContentTypeId.Event))
  14.     {
  15.         throw new ArgumentException(string.Format("Item must have a content type of Event ({0}) or a content type derived from that", SPBuiltInContentTypeId.Event), "item");
  16.     }
  18.     // we need to perform the check only if the item is a main entry of a recurring event series
  19.     // in this case, EventType should be 1, see
  20.     //
  21.     var eventType = item["EventType"];
  22.     if ((eventType is int) && ((int)eventType == 1))
  23.     {
  24.         SPList list = item.ParentList;
  25.         int itemId = item.ID;
  27.         // querying recurring event exceptions that belong to the current item
  28.         SPQuery query = new SPQuery();
  29.         SPListItemCollection itemsToDelete = null;
  30.         query.Query = String.Format(@"<Where><And><Gt><FieldRef Name='EventType'/><Value Type='Integer'>2</Value></Gt><Eq><FieldRef Name='MasterSeriesItemID'/><Value Type='Integer'>{0}</Value></Eq></And></Where>", itemId);
  31.         itemsToDelete = list.GetItems(query);
  33.         //
  34.         List<int> itemIDsToDelete = itemsToDelete.Cast<SPListItem>().Select(i => i.ID).ToList();
  36.         itemIDsToDelete.ForEach(i =>
  37.             {
  38.                 SPListItem subItem = list.GetItemById(i);
  39.                 try
  40.                 {
  41.                     subItem.Delete();
  42.                 }
  43.                 catch (Exception ex)
  44.                 {
  45.                     // error when deleting a recurring event exception
  46.                     // as a possible workaround, convert it to a standard event and try to delete it again
  47.                     subItem["EventType"] = 0;
  48.                     subItem.Update();
  49.                     subItem.Delete();
  50.                 }
  51.             });
  52.     }
  53.     // finally, delete the main entry as well
  54.     item.Delete();
  55. }

You can use the DeleteItemByIdIncludingRecurringEventExceptions method in place of the DeleteItemById method and DeleteIncludingRecurringEventExceptions method in place of the Delete method. We search for the related items by using the MasterSeriesItemID field value in the CAML query. You can use these methods only for items having the Event content type or a custom content type derived from it.

You can use the methods like this:

var list = web.Lists["Calendar"];          

Note, that we also had some corrupted recurring event exceptions in our calendar, probably created automatically by a faulty application. Although we could get a reference for the items itself (for example, by calling the GetItemById method), and change its properties if we wished, we got the exception below if we tried to delete them from code, or even if we only wanted to display the items from the All Events view in the browser.

Item does not exist. The page you selected contains an item that does not exist. It may have been deleted by another user.<nativehr>0x81020016</nativehr><nativestack></nativestack>
SPRequest.DeleteItem: UserPrincipalName=i:0).w|s-1-5-21-3634847118-2359816030-2114994487-3414, AppPrincipalName= ,bstrUrl=http://YourServer/Web/SubWeb ,bstrListName={A38F8D71-F481-4A93-85B8-AC42BB2BE6EC} ,lID=4596 ,dwDeleteOp=3 ,bUnRestrictedUpdateInProgress=False
System.Runtime.InteropServices.COMException: Item does not exist. The page you selected contains an item that does not exist. It may have been deleted by another user.<nativehr>0x81020016</nativehr><nativestack></nativestack>, StackTrace:    at Microsoft.SharePoint.SPListItem.DeleteCore(DeleteOp deleteOp)     at Microsoft.SharePoint.SPListItem.Delete()     at CallSite.Target(Closure , CallSite , Object , Int32 )     at System.Dynamic.UpdateDelegates.UpdateAndExecute2[T0,T1,TRet](CallSite site, T0 arg0, T1 arg1)     at System.Management.Automation.Interpreter.DynamicInstruction`3.Run(InterpretedFrame frame)     at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)     at System.Management.Automation.Interpreter.Ente…
…rTryCatchFinallyInstruction.Run(InterpretedFrame frame)     at System.Management.Automation.Interpreter.Interpreter.Run(InterpretedFrame frame)     at System.Management.Automation.Interpreter.LightLambda.RunVoid1[T0](T0 arg0)     at System.Management.Automation.DlrScriptCommandProcessor.RunClause(Action`1 clause, Object dollarUnderbar, Object inputToProcess)     at System.Management.Automation.DlrScriptCommandProcessor.Complete()     at System.Management.Automation.CommandProcessorBase.DoComplete()     at System.Management.Automation.Internal.PipelineProcessor.DoCompleteCore(CommandProcessorBase commandRequestingUpstreamCommandsToStop)     at System.Management.Automation.Internal.PipelineProcessor.SynchronousExecuteEnumerate(Object input, Hashtable errorResults, Boolean enumerate)     at S…
…ystem.Management.Automation.Runspaces.LocalPipeline.InvokeHelper()     at System.Management.Automation.Runspaces.LocalPipeline.InvokeThreadProc()     at System.Management.Automation.Runspaces.PipelineThread.WorkerProc()     at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)     at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)     at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)     at System.Threading.ThreadHelper.ThreadStart()

The simplest workaround I’ve found to delete those entries was to set their EventType field value to 0, as they would be master entries, not recurring event exceptions. After this change, it was possible to delete the items. This kind of hack is also included in the DeleteIncludingRecurringEventExceptions extension method above.

Although the code I provided here seems to do the job and not pollute the Recycle Bin any more, if you have a really large number of items to delete, for performance reasons I still would prefer the bulk deletion of the events, or you should write your own solution to select all of the recurring event exceptions in the first step, and deleting them before you delete the main entries in the second step. I don’t think it would be a great idea to run a separate CAML query for each recurring events in your calendar.

July 27, 2018

A specific case of "The file name you specified is not valid or too long" error in SharePoint Explorer view

Filed under: SP 2013 — Tags: — Peter Holpar @ 21:07

Recently I found a document library in one of our SharePoint 2013 environments, that produced an odd behavior. I describe the symptoms and the solution below.


Problem with uploading file into a document library

I opened the Site Assets document library in a web site of a SharePoint 2013 installation (I don’t think the error is specific to this special kind of document libraries or SharePoint version, but I document it for the sake of completeness), and created a folder called ‘js’. The folder was created without any problem. I wanted to copy a .js file into the folder, but received the error message “The file name you specified is not valid or too long”. There was of course no problem with the length of the file nor with the name itself. I was simply not able to upload any file into that specific library, independent of the folder or the file name and extension. See below an error message for a text file. Clicking “Try Again” was unsuccessful.


I was able to upload files into other document libraries of the same site, as well as into libraries in other sites, so I assumed the problem is related to the library itself, but to be sure, I restarted first the WebClient service in Windows, then the whole Explorer process (including the desktop), finally the operating system itself, but all of them without any result (except losing lot of time).

I observed, however, that the file was uploaded despite of the error message. It appeared both in Windows Explorer as well as in the browser (I was able to open it!), at least till I clicked “Cancel” in the dialog box above. Then the file got deleted and disappeared, as it would have not been uploaded at all. Very strange.

I created a network trace using Fiddler to investigate the steps of the process. I include the HTTP response codes and optionally further information in brackets, unsuccessful steps are written in red:

PROPFIND /YourSite/SiteAssets (207)
PROPFIND / (207)
PROPFIND /YourSite/SiteAssets/YourFile.txt (404)
PROPFIND /YourSite/SiteAssets (207)
PUT /YourSite/SiteAssets/YourFile.txt (In the request we send the X-MSDAVEXTLockTimeout header with a value of Second-3600 and do not include the file in the request body, it indicates we acquire only a lock. The response includes these headers: X-MSDAVEXTLockTimeout: Second-3600; Lock-Token: opaquelocktoken:{D126A8C0-4DA2-4BD1-919F-566A6B68B507}20180725T072618Z; X-MS-File-Checked-Out: 1. The HTTP response code is 201)
HEAD /YourSite/SiteAssets/YourFile.txt (200)
PUT /YourSite/SiteAssets/YourFile.txt We include the lock token we acquired earlier: Lock-Token: <opaquelocktoken:{D126A8C0-4DA2-4BD1-919F-566A6B68B507}20180725T072618Z>, another header X-MSDAVEXT: PROPPATCH and the file to be uploaded in the request body. The HTTP response code is 500, the response includes the header X-MSDAVEXT_Error: 589829; The%20URL%20%27SiteAssets%2fYourFile%2etxt%27%20is%20invalid%2e%20%20It%20may%20refer%20to%20a%20nonexistent%20file%20or%20folder%2c%20or%20refer%20to%20a%20valid%20file%20or%20folder%20that%20is%20not%20in%20the%20current%20Web%2e)
HEAD /YourSite/SiteAssets/YourFile.txt (200)
PUT /YourSite/SiteAssets/YourFile.txt (It’s not clear what happens in this step, we try to upload the file again, but with another headers this time, like If: (<opaquelocktoken:{D126A8C0-4DA2-4BD1-919F-566A6B68B507}20180725T072618Z>) and there is no X-MSDAVEXT. The file to be uploaded is included again in the request body. The HTTP response code is 500, the response includes the header X-MSDAVEXT_Error value as earlier.)
PROPFIND /YourSite/SiteAssets (207)
PROPFIND /YourSite/SiteAssets/desktop.ini HTTP/1.1 (404)

After clicking “Cancel”:

HEAD /YourSite/SiteAssets/YourFile.txt (200)
PUT /YourSite/SiteAssets/YourFile.txt HTTP/1.1 (This time we send only a DAV property update request, the file is not included in the request body. The HTTP response code is 500, the response includes the header X-MSDAVEXT_Error value as earlier.)
HEAD /YourSite/SiteAssets/YourFile.txt (200)
PUT /YourSite/SiteAssets/YourFile.txt HTTP/1.1 (Another put request with the header If: (<opaquelocktoken:{D126A8C0-4DA2-4BD1-919F-566A6B68B507}20180725T072618Z>) but without the file in the request body. The HTTP response code is again 500, the response includes the header X-MSDAVEXT_Error value as earlier.)
UNLOCK http://YourServer/YourSite/SiteAssets/MailAdresses.txt (we try to unlock the file, including the header Lock-Token: <opaquelocktoken:{D126A8C0-4DA2-4BD1-919F-566A6B68B507}20180725T072618Z>. The HTTP response code is 412)

PROPFIND /YourSite/SiteAssets/YourFile.txt (207)
DELETE http://YourServer/YourSite/SiteAssets/YourFile.txt (We delete the file. The HTTP response code is 204)

In the corresponding ULS log entries I found these lines (all entry with Area: SharePoint Foundation, Category: Database; Level: High):

System.Data.SqlClient.SqlException (0x80131904): Parameter ‘@tp_Version’ was supplied multiple times. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows) at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more) at System.Data.SqlClient.SqlDataRea… …der.TryNextResult(Boolean& more) at System.Data.SqlClient.SqlDataReader.NextResult() at Microsoft.SharePoint.SPSqlClient.ExecuteQueryInternal(Boolean retryfordeadlock) at Microsoft.SharePoint.SPSqlClient.ExecuteQuery(Boolean retryfordeadlock) ClientConnectionId:0ed39f0d-b333-43fd-90e3-421a19b57441 Error Number:8143,State:1,Class:16 ExecuteQuery failed with original error 0x80131904 SQL error code from last error 8143 – Parameter ‘@tp_Version’ was supplied multiple times.

Creating new files into a document library

It is the same library as earlier, but instead of copying a file into it, I have tried to create a new file this time. I’ve selected New / Text Document in Windows Explorer, have renamed it to test.txt, opened it in Notepad, added some content and saved it. The file was saved, and was available in Windows Explorer as well in the browser, but a warning appeared in the low right corner of the desktop, saying:

Delayed Write Failed
Windows was unable to save all the data for the file \YourServer\YourSite\SiteAssets\test.txt. The data has been lost. This error may be caused by a failure of your computer hardware or network connection. Please try to save this file elsewhere.


I found a page about that issue but it said really nothing, and was not right either, as the file was saved in my case, despite of the warning.

I’ve found an entry in Event Viewer (Windows Logs / Application having Source: WebClient; Level: Warning) that probably belongs to the error above:

The description for Event ID 14903 from source WebClient cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.
If the event originated on another computer, the display information had to be saved with the event.
The following information was included with the event:
\YourServer\YourSite\SiteAssets\New Text Document.txt

It wasn’t really helpful too.

The solution

What really helped was the ULS log I’ve included above, especially the part:

(0x80131904): Parameter ‘@tp_Version’ was supplied multiple times.

I was sure I hade already the same or a very similar issue (eventually with another parameter name), yet with SharePoint 2010, and at that time I verified the statement with SQL Profiler and found, that the parameter is sent really twice in the query. Unfortunately (or fortunately?), it was a long time ago, and I have not remembered the solution. But a quick search on the web helped, and I found this forum entry.

Although related to an other field (Author), the answer states, that a similar problem was caused by a field schema corruption. So I decided to check the schema of the field that belongs to the tp_Version parameter (that is the owshiddenversion field):

$web = Get-SPWeb http://YourServer/YourSite
$list = $web.Lists[‘Site Assets’]
$versionField = $list.Fields[‘owshiddenversion’]

The result was:

<Field ID="{d4e44a66-ee3a-4d02-88c9-4ec5ff3f4cd5}" Name="owshiddenversion" SourceID="" StaticName="owshiddenversion" Group="_Hidden" ColName="tp_Version" RowOrdinal="0" Hidden="TRUE" ReadOnly="TRUE" Type="Integer" SetAs="owshiddenversion" DisplayName="owshiddenversion" Sealed="FALSE" Version="1" />

I then compared the results with the field schema of another document library, where the upload works as it should:

$list = $web.Lists[‘Documents’]
$versionField = $list.Fields[‘owshiddenversion’]

The result was this time:

<Field ID="{d4e44a66-ee3a-4d02-88c9-4ec5ff3f4cd5}" ColName="tp_Version" RowOrdinal="0" Hidden="TRUE" ReadOnly="TRUE" Type="Integer" SetAs="owshiddenversion" Name="owshiddenversion" DisplayName="owshiddenversion" SourceID="" StaticName="owshiddenversion" FromBaseType="TRUE"/>

You can see, that – exactly as the forum answer suggests – the attribute FromBaseType="TRUE" is missing from the schema XML. To fix the difference, I run this script on the corrupted library:

$xml = [xml]$versionField.SchemaXmlWithResourceTokens
$versionField.SchemaXml = $xml.OuterXml

By running the script the difference in the field schema  XML was fixed, and there was no more problem with copying to and creating files in the library.

July 26, 2018

Removing the password protection from an Excel sheet using PowerShell

Filed under: Open XML, PowerShell, Tips & Tricks — Tags: , , — Peter Holpar @ 23:24

Today I received an Excel sheet that I should have to edit, although some cells whose value I had to change was protected by a password I did not know (Note: I’m using Excel 2013).

I’m quite familiar with Open XML, so I was sure, it must be possible by editing the XML files the Excel file consist of, but did not know exactly, which nodes should I edit or remove. A quick search provided me the information, how to do it manually or by using the Open XML SDK and C#.

Although I like C# and have Visual Studio installed on my PC, I decided to create a solution using PowerShell based on the C# example mentioned above, to enable the automated removal of password protection for a broader range of users. Prerequisite: the Open XML SDK must be installed on the computer.

The conversion was not really complex, a key point was the usage of the MakeGenericMethod to enable access to the generic methods of the Open XML object model.

The code snippet below illustrates the result:

  1. # change the path to point to your password protected Excel sheet, and ensure that the sheet is not opened in Excel
  2. $filePath = 'C:\Data\ProtectedSheet.xlsx'
  4. # I have the DocumentFormat.OpenXml assembly in the Global Assembly Cache (GAC). If you are like me, you can use
  5. #[System.Reflection.Assembly]::LoadWithPartialName('DocumentFormat.OpenXml')
  6. # otherwise load the assembly from its installation path
  7. [System.Reflection.Assembly]::LoadFrom('C:\Program Files (x86)\Open XML SDK\V2.5\lib\DocumentFormat.OpenXml.dll')
  9. $spreadSheetDocument = [DocumentFormat.OpenXml.Packaging.SpreadsheetDocument]::Open($filePath, $true)
  11. # Worksheet class has a non-generic overload of the RemoveAllChildren method as well
  12. $removeAllChildrenMethod = [DocumentFormat.OpenXml.Spreadsheet.Worksheet].GetMethods() | ? { $_.Name -eq 'RemoveAllChildren' -and $_.IsGenericMethod }
  13. $removeAllChildrenMethodGeneric = $removeAllChildrenMethod.MakeGenericMethod([DocumentFormat.OpenXml.Spreadsheet.SheetProtection])
  15. $sheets = $spreadSheetDocument.WorkbookPart.Workbook.ChildElements | ? { $_.LocalName -eq 'sheets'}
  16. $sheets.ChildElements.Id | % {
  17.     $relationshipId = $_.Value
  18.     $worksheetPart = $spreadSheetDocument.WorkbookPart.GetPartById($relationshipId)
  19.     $workSheet = $worksheetPart.Worksheet
  20.     $removeAllChildrenMethodGeneric.Invoke($workSheet, [System.Object[]]@())
  21.     $workSheet.Save()
  22. }
  24. $spreadSheetDocument.Close()
  25. $spreadSheetDocument.Dispose()

As mentioned in the comment in the code, you should change the path to refer to the real location of your Excel file, and mustn’t have the document opened in Excel, otherwise it will lock the file and the script can’t change it. If you want to be sure, close all instances of Excel before staring the script.

July 7, 2018

Checking user properties in Active Directory using PowerShell to identify logon issues

Filed under: Active Directory, PowerShell, Tips & Tricks — Tags: , , — Peter Holpar @ 17:45

While supporting a SharePoint environment having several thousands of users from multiple Active Directory domains, we have quite often complains, that one can not access the site. Beyond trivial network related problems, like incorrect proxy settings, it is probably the second most common reason for such complains having issues with the Active Directory account of the user.

To support such cases, I wrote a short PowerShell script that checks for the most common problems, like User must change password at next logon flag is activated, account is disabled or locked out, and password expired. Prerequisite: you should have PowerShell Active Directory module installed.

$userLogin = ‘Domain\UserName’
$userLoginSplitted = $userLogin.Split(‘\’)
$domainName = $userLoginSplitted[0]
$dcServer = Get-ADDomainController -Discover -DomainName $domainName

$user = Get-ADUser -Identity $userLoginSplitted[1] -Server $dcServer.HostName[0] -Properties Enabled, LockedOut, PwdLastSet, PasswordNeverExpires, msDS-UserPasswordExpiryTimeComputed
$pwdNeverExp = $user.PasswordNeverExpires
$pwdExpiresOn = If ($pwdNeverExp) { $null } Else { [DateTime]::FromFileTime($user."msDS-UserPasswordExpiryTimeComputed") }

Write-Host Checking user: $userLogin
Write-Host User must change password at next logon: $($user.PwdLastSet -eq 0)
Write-Host Account disabled: $(!$user.Enabled)
Write-Host Account locked out: $($user.LockedOut)
Write-Host Password expired: $((!$pwdNeverExp) -and ($pwdExpiresOn -lt [DateTime]::Now))

First thing to highlight in the script is how we get and use the domain controller. Getting the domain controller is the easy part, after splitting the user login name to a domain name and a user name, you simply invoke the Get-ADDomainController cmdlet with the Discover switch and passing the domain name you are looking for in the DomainName parameter.

Using the value returned is a bit more complicated, at least until you learn how to do it the right way. Although Active Directory cmdlets support the Server parameter, there is a lot of confusion how to use it correctly. If you simply pass the domain controller as you received it in the previous step from the Get-ADDomainController cmdlet, like:

$user = Get-ADUser -Identity $userLoginSplitted[1] -Server $dcServer

you receive this error message:

Unable to contact the server. This may be because this server does not exist, it is currently down, or it does not have the Active Directory Web Services running.

After researching the samples on the web (like Get-ADUser -Server ‘servername’) and the official documentation of the Get-ADUser cmdlet we realized that the Server parameter requires a string value, so passing the $dcServer (of type Microsoft.ActiveDirectory.Management.ADDirectoryServer) was really not a good idea. But wait, scrolling through the properties of $dcServer by PowerShell autocomplete shows it has a property called HostName. That sounds really promising! Try it out!

$user = Get-ADUser -Identity $userLoginSplitted[1] -Server $dcServer.HostName

Now you have another error message:

Cannot convert ‘Microsoft.ActiveDirectory.Management.ADPropertyValueCollection’ to the type ‘System.String’ required by parameter ‘Server’. Specified method is not supported.

Well, that means that the HostName property is of type Microsoft.ActiveDirectory.Management.ADPropertyValueCollection and not a string either, even though PowerShell displays it as string if you output it like $dcServer.HostName. To get the name of the domain controller as string you should address it in the collection using an array indexer, like $dcServer.HostName[0].

So the right usage is:

$user = Get-ADUser -Identity $userLoginSplitted[1] -Server $dcServer.HostName[0]

although you might check first if there was any domain controller found ($dcServer and its HostName property not null, and HostName has at leas a single entry) if you want to be sure.

Note furthermore, that the largest possible 64-bit value (2^63-1 = 9223372036854775807) in property msDS-UserPasswordExpiryTimeComputed means PasswordNeverExpires is true. Invoking the FromFileTime method using this value would cause an error:

Exception calling "FromFileTime" with "1" argument(s): "Not a valid Win32 FileTime. Parameter name: fileTime"

‘This site is read-only at the moment’ message in PWA

Filed under: PS 2016, Tips & Tricks — Tags: , — Peter Holpar @ 17:45

A few words about the ‘This site is read-only at the moment.‘ banner, more details about what that means can be found here and there. Recently I had the message in a new Project Server 2016 installation (so on-premise and no migration) for a PWA instance, although the site and the content database were not in read-only mode at all, as I was able to upload files into the document library of the site. I have to admit, I forgot to check, if it was possible to create Project entities, like projects or resources.


Steps I’ve performed to let the banner to disappear:

  • Setting the quota multiple times
  • Starting the service job Project Server: Database Maintenance job for Project Server Service Application multiple times
  • Calling Enable-ProjectServerLicense multiple times
  • Setting permission (db_owner) for SharePoint service users / farm account on the content database

After the last step (and few hours of trying) the banner finally disappeared, although I’m not sure, if it was really an effect of the IISRESET, or some kind of combination of the other steps played a role in the solution as well. I thing the permission change might be important as well, although after revoking them and running IISRESET once more the banner did not come back again.

Automating the Provisioning of a PWA-Instance in Project Server 2016

Filed under: ALM, PowerShell, PS 2016 — Tags: , , — Peter Holpar @ 17:43

Yet in 2015 I wrote a post about the automatic provisioning of a PWA instance. That time, it discussed the process in the context of Project Server 2013, now I updated the description to the version 2016.


First of all, be sure you have enabled the usage of Project Server in the farm by entering a valid license key, otherwise you receive the error message below, when enabling the PWASITE feature at the end of the provisioning:

Enable-SPFeature : The farm does not have a product key for Project Server.
You can add your key by using Enable-ProjectServerLicense

As suggested by the error message, you should add your key by using Enable-ProjectServerLicense like:

Enable-ProjectServerLicense -Key [Guid of you Project Server product key]

At this step you might receive a further error message, stating:

Enable-ProjectServerLicense : We encountered an error while enabling Project
Server 2016 . Project Server 2016  requires the farm to have a valid
SharePoint Server Enterprise product key.

It means Project Server requires a licensed Enterprise version of SharePoint. In our case it was a bit confusing, as the page Upgrade and Migration / Enable Enterprise Features in Cental Administration displayed that the farm has already the Enterprise features activated.


The Upgrade and Migration / Convert License Type answered the question, as it turned out the farm was not licensed at all, being in state:

SharePoint Server Trial with Enterprise Client Access License


After entering the correct license key for the enterprise version to the  Enter the Product Key text field an submitting the form:


On the Convert License Type page the Current License changed to:

SharePoint Server Trial with Enterprise Client Access

and the Enter the Product Key text field was no more editable.


Unfortunately, I have not found any solution to convert the farm license type by PowerShell, so if you need it, you should perform it in Cental Administration user interface manually. The process of converting a license type is discussed in this post in details.

After we have a licensed Enterprise version, we can enable the Project Server license as well. Note, that you should restart your PowerShell console after converting the license type from Cental Administration as discussed above, otherwise it seems to be unable to recognize the change, and you receive the same error message about the lack of a valid SharePoint Server Enterprise product key as earlier.

Enable-ProjectServerLicense -Key [Guid of you Project Server product key]

The result should be now:

Project Server 2016  is now enabled.

Second part of the preparation is to ensure you have the adequate permissions to perform the provisioning job. Personally I prefer to have db_owner membership in all databases in the farm, including the configuration database as well. It is particularly important to check the permissions, if you plan to use an already existing content database to provision your PWA site, that you created via Central Administration site and not by PowerShell. Based on my experience the databases created in CA have different permissions configured as the ones created by PowerShell, and the lack of the permission may cause your provisioning process to stuck with no trivial solution.


After being prepared, let’s see our PowerShell script that provisions a new PWA instance, including:
– A separate SharePoint content database that should contain only a single site collection: the one for the PWA. If the content DB already exists, we will use the existing one, otherwise we create a new one.
– Creating the managed path for the PWA.
– A new site collection is created for the PWA using the project web application site template, and the right locale ID (1033 in our case). If the site collection already exists (in case we re-use a former content DB), it will be dropped before creating the new one.
– The PWASITE feature will be activated on the new site collection.
– Changing to the Project Server security model.
– Disabling quotas.

  1. # change this configuration values according the values in your farm
  2. $webAppUrl = 'http://YourSharePointServer&#039;
  3. $contentDBName = 'ContentDB_PWA'
  4. $contentDBServer = 'YourSQLServer'
  6. $pwaMgdPathPostFix = "PWA"
  7. $pwaUrl = [string]::Format("{0}/{1}", $webAppUrl, $pwaMgdPathPostFix)
  8. $pwaTitle = "PWA Site"
  9. $pwaSiteTemplate = "PWA#0"
  10. $pwaLcid = 1033 # English
  11. $ownerAlias = "domain\user1"
  12. $secondaryOwnerAlias = "domain\user2"
  14. Write-Host Getting web application at $webAppUrl
  15. $webApp = Get-SPWebApplication -Identity $webAppUrl
  17. # create the content database if needed
  18. $contentDatabase = Get-SPContentDatabase -Identity $contentDBName
  19. if ($contentDatabase -eq $null) {
  20.   Write-Host Creating content database: $contentDBName
  21.   $contentDatabase = New-SPContentDatabase -Name $contentDBName -WebApplication $webApp -MaxSiteCount 1 -WarningSiteCount 0 -DatabaseServer $contentDBServer
  22. }
  23. else {
  24.   Write-Host Using existing content database: $contentDBName
  25. }
  27. # create the managed path if needed
  28. $pwaMgdPath = Get-SPManagedPath -Identity $pwaMgdPathPostFix -WebApplication $webApp -ErrorAction SilentlyContinue
  29. if ($pwaMgdPath -eq $null) {
  30.   Write-Host Creating managed path: $pwaMgdPathPostFix
  31.   $pwaMgdPath = New-SPManagedPath -RelativeURL $pwaMgdPathPostFix -WebApplication $webApp -Explicit
  32. }
  33. else {
  34.   Write-Host Using existing managed path: $pwaMgdPathPostFix
  35. }
  36. # (re)creating site collection for the PWA instance
  37. # we delete the site collection if it already exists
  38. $pwaSite = Get-SPSite -Identity $pwaUrl -ErrorAction SilentlyContinue
  39. if ($pwaSite -ne $null) {
  40.   Write-Host Deleting existing PWA site at $pwaUrl
  41.   $pwaSite.Delete()
  42. }
  44. Write-Host Creating PWA site at $pwaUrl
  45.   $pwaSite = New-SPSite -Url $pwaUrl OwnerAlias $ownerAlias SecondaryOwnerAlias$secondaryOwnerAlias -ContentDatabase $contentDatabase Template $pwaSiteTemplate -Language $pwaLcid -Name $pwaTitle
  47. # Enable PWASITE feature
  48. Enable-SPFeature pwasite -URL $pwaUrl
  50. # Enable Project Server Permissions mode
  51. Set-SPProjectPermissionMode -Url $pwaUrl -Mode ProjectServer
  53. # disabling qutoa
  54. $quota = Get-SPProjectDatabaseQuota -Url $pwaUrl
  55. $quota.IsEnabled = $false
  56. $quota.MaxDbMegaByteSize++
  57. Set-SPProjectDatabaseQuota -Url $pwaUrl $quota

A comment regarding the last step, disabling quota. If you simply read the values of the current quota using the Get-SPProjectDatabaseQuota cmdlet, disable the quota, and try to set the value by Set-SPProjectDatabaseQuota cmdlet, you get an error message:

Set-SPProjectDatabaseQuota : Cannot apply settings, the maximum database size must be greater than the read only limit.

That is because the properties MaxDbMegaByteSize and ReadOnlyMegaByteLimit have by default the same value (10240). Funny, that it later not allowed to set the same values yourself. That is why we have $quota.MaxDbMegaByteSize++ in code.

July 1, 2018

HttpRequest.Url contains always the full URL of the current request, doesn’t it?

Filed under: Bugs, SP 2013 — Tags: , — Peter Holpar @ 20:53

Recently we had to create a Wellcome menu extension in SharePoint to make a custom application page available from all of the web site context, like standard lists and pages as well as standard and custom application pages. This custom application page provides its own functionality (irrelevant to the problem described in the post), and after the user performed the task on the page, and submitted it via a button click, he should be returned to the original page, where he invoked the custom page from the menu. As the condition, if the menu item should or should not be displayed for a specific user, depends on some complex criteria (omitted from the code snippets in the post), we decided to implement the menu item on the server side by our custom MenuItemTemplate.

The CustomAction definition sets GroupId as PersonalActions and Location as Microsoft.SharePoint.StandardMenu to have the menu item in the Wellcome menu. The ControlAssembly and ControlClass attributes identify the class, in that we implemented the solution.

  1. <?xml version="1.0" encoding="utf-8"?>
  2. <Elements xmlns="">
  3.   <CustomAction
  4.       Id="YourCustomIdAction"
  5.       GroupId="PersonalActions"
  6.       Location="Microsoft.SharePoint.StandardMenu"
  7.       ControlAssembly="$SharePoint.Project.AssemblyFullName$"
  8.       ControlClass="YourNamespace.DisplayCustomMenuItem"
  9.     >
  10.   </CustomAction>
  11. </Elements>

In the CreateChildControls method of our class we create a new MenuItemTemplate instance, set its properties and finally add it to the Controls collection. See the ClientOnClickNavigateUrl property, that we set by invoking the static GetRedirectionUrl method of our helper class.

  1. public class DisplayCustomMenuItem : WebControl
  2. {
  3.     protected override void CreateChildControls()
  4.     {
  5.         MenuItemTemplate item = new MenuItemTemplate();
  6.         item.Description = "Description of your menu extension";
  7.         item.Sequence = 1000;
  8.         item.Text = "Do something";
  9.         item.ClientOnClickNavigateUrl = Helper.GetRedirectionUrl();
  11.         Controls.Add(item);
  12.     }
  13. }

The original version of the static GetRedirectionUrl method was this one:

  1. public static string GetRedirectionUrl()
  2. {
  3.     SPContext ctx = SPContext.Current;
  4.     HttpContext context = HttpContext.Current;
  5.     string absUrl = context.Request.Url.AbsoluteUri;
  6.     string result = string.Format("{0}{1}?{2}={3}", ctx.Web.Url, Constants.RedirectionPagePath, QueryString.Source, HttpUtility.UrlEncode(absUrl));
  8.     return result;
  9. }

Constants and QueryString are static classes. Constants.RedirectionPagePath stores the site relative path of our custom application page, like "/_layouts/15/OurCustomFolder/CustomApplicationPage.aspx", QueryString.Source is the name of the query string parameter (like ‘CustomSource’) variable we used to specify the URL of the page, where the user selected the menu item, and to which the application page should redirect after the user finished his task.

Our application page is inherited from the Microsoft.SharePoint.WebControls.LayoutsPageBase base class. The relevant part is the Click event handler of our button. We read the value of original URL from the query string parameter QueryString.Source, and redirect the response accordingly:

  1. protected void DoSomething_Click(object sender, EventArgs e)
  2. {
  3.     // do something here
  5.     // redirect to the original page
  6.     var sourceUrl = this.Request.QueryString[QueryString.Source];
  7.     if (!string.IsNullOrEmpty(sourceUrl))
  8.     {
  9.         LoggingService.LogMessage("Redirecting request to page: '{0}'", sourceUrl);
  10.         Response.Redirect(sourceUrl);
  11.     }
  12. }

We activated the feature in a site collection like http://YourSharePoint/SomeSiteCollection. While testing the application, we found that the redirection worked as expected for all of the list views and standard (web part or wiki) pages, but malfunctioned for any application pages, either for standard or custom ones. For example, if the start page, where the user selected the custom menu item from the Welcome menu, was the Site Settings page of the root web site or any other sub web site of our site collection, having a URL like http://YourSharePoint/SomeSiteCollection/_layouts/15/settings.aspx, our custom application was displayed, but after clicking the button, the request was redirected instead of the original Site Setting page to the Site Setting page of the root site collection of the web application (that means, to URL http://YourSharePoint/_layouts/15/settings.aspx).

After debugging the solution in the context of various site collections, it turned out, that the value of the HttpContext.Current.Request.Url for application pages refers always to the page in the context of the root site collection of the web application (that means a URL beginning with http://YourSharePoint/_layouts/15). Of course, this result is not what you expect if your site is not the root one.

After even more debugging and browsing several properties of the current SPContext and HttpContext objects in run-time, I have finally found a value in the HttpContext.Items collection (the one with the key Microsoft.SharePoint.SPGlobal.GetVTIRequestUrl), that contained exactly the same URL value that corresponded the site specific URL of the application pages, the one we needed to achieve our original goal.

So I rewrote the GetRedirectionUrl method to check if the URL returned by the HttpContext.Current.Request.Url property matches to the URL of the current web site (as it is returned by SPContext.Current.Web.Url) and if the values differ, use the value stored in the entry in the HttpContext.Items collection having the Microsoft.SharePoint.SPGlobal.GetVTIRequestUrl key (as long as it is available). The code snippet below displays the updated version of the method, that finally performs as we expected:

  1. public static string GetRedirectionUrl()
  2. {
  3.     SPContext ctx = SPContext.Current;
  4.     HttpContext context = HttpContext.Current;
  5.     string absUrl = context.Request.Url.AbsoluteUri;
  6.     string webUrl = ctx.Web.Url;
  7.     string vtiRequestUrlKey = "Microsoft.SharePoint.SPGlobal.GetVTIRequestUrl";
  8.     Uri vtiRequestUrl = context.Items.Contains(vtiRequestUrlKey) ? context.Items[vtiRequestUrlKey] as Uri : null;
  9.     string requestUrl = ((absUrl.IndexOf(webUrl) == 0) || (vtiRequestUrl == null)) ? absUrl : vtiRequestUrl.AbsoluteUri;
  10.     string result = string.Format("{0}{1}?{2}={3}", webUrl, Constants.RedirectionPagePath, QueryString.Source, HttpUtility.UrlEncode(requestUrl));
  12.     return result;
  13. }

Lesson learned: be aware that the HttpContext.Current.Request.Url may return the wrong value for application pages, and use the value of the entry with the Microsoft.SharePoint.SPGlobal.GetVTIRequestUrl key in the HttpContext.Items collection instead.

The sub-webs delivered to you are dirty

Filed under: PowerShell, SP 2013 — Tags: , — Peter Holpar @ 14:39

Recently I had to perform a simple administrative task: a SharePoint website had several sub-webs, each of them having unique permissions. Our goal was to reset the permissions to be inherited from the parent site. So I created a simple PowerShell script to achieve the goal:

$rootWeb = Get-SPWeb ‘http://YourSharePointSite/SomeSite&#8217;
$rootWeb.Webs | % { $_.ResetRoleInheritance() }

Note: Although I use the Webs property of the SPWeb object overall in this post to illustrate the problem and later the solution, the very same applies to the GetSubwebsForCurrentUser method of the SPWeb object as well.

To my greatest surprise, I received this error message for each of the sub-web sites:

Exception calling "ResetRoleInheritance" with "0" argument(s): "There are uncommitted changes on the SPWeb object, call SPWeb.Update() to commit the changes before calling this method."

To understand the source of the exception, I followed the call-chain of the SPWeb.ResetRoleInheritance method using Reflector. All of the methods and classes mentioned in the post are declared in the Microsoft.SharePoint assembly in the Microsoft.SharePoint namespace.

First, the SPWeb.ResetRoleInheritance() method invokes the virtual SPSecurableObject.ResetRoleInheritance(), that invokes the internal SPSecurableObjectImpl.ResetRoleInheritance() method, that finally calls the private SPSecurableObjectImpl.RevertRoleInheritance(bool copyRoleAssignments, bool clearSubScopes) method, where the exception get thrown.

The value of the $StackTrace variable in PowerShell confirmed the result of my research:

   at Microsoft.SharePoint.SPSecurableObjectImpl.RevertRoleInheritance(Boolean copyRoleAssignments, Boolean clearSubScopes)
   at Microsoft.SharePoint.SPWeb.ResetRoleInheritance()
   at CallSite.Target(Closure , CallSite , Object )

The SPSecurableObjectImpl.RevertRoleInheritance method contains this condition:

if ((this.m_objectType == SPObjectType.Web) && this.m_web.IsDirty)
    throw new InvalidOperationException(SPResource.GetString("SPWebHasUnCommittedChange", new object[0]));

It means, the internal IsDirty property of the SPWeb class is checked to see, if there is any uncommitted change in the SPWeb instance. It is common, that your SPWeb instance gets dirty after you change some of its properties, but in our case, we apparently have not change anything, we still get the complain about “dirtiness” our web.

Let’s see, how to check if our SPWeb instance is dirty or not, and find some kind of workaround.

Forget the PowerShell example above for a while, and switch to C#. In this case, the base version of the code looks like this:

  1. using (SPSite site = new SPSite("http://YourSharePointSite/SomeSite&quot;))
  2. {
  3.     using (SPWeb web = site.OpenWeb())
  4.     {
  5.         foreach (SPWeb subWeb in web.Webs) // or the same with webs.GetSubwebsForCurrentUser()
  6.         {
  7.             try
  8.             {
  9.                 subWeb.ResetRoleInheritance();
  10.             }
  11.             finally
  12.             {
  13.                 subWeb.Dispose();
  14.             }
  15.         }
  16.     }
  17. }

Although the IsDirty method of the SPWeb is declared as private, we can access it via Reflection, as we did it in the extension method below:

  1. static class Extensions
  2. {
  3.     public static bool IsDirty(this SPWeb web)
  4.     {
  5.         var result = false;
  7.         var pi_isDirty = typeof(SPWeb).GetProperty("IsDirty", BindingFlags.NonPublic | BindingFlags.Instance);
  8.         result = (bool)pi_isDirty.GetValue(web);
  10.         return result;
  11.     }
  12. }

Having our extension method, we can dump out easily, if  the root web site and its sub-webs are dirty or not.

  1. using (SPSite site = new SPSite("http://YourSharePointSite/SomeSite&quot;))
  2. {
  3.     using (SPWeb web = site.OpenWeb())
  4.     {
  5.         Console.WriteLine("Web '{0}' is dirty: '{1}'", web.Url, web.IsDirty());
  6.         foreach (SPWeb subWeb in web.Webs) // or the same with webs.GetSubwebsForCurrentUser()
  7.         {
  8.             try
  9.             {
  10.                 Console.WriteLine("Web '{0}' is dirty: '{1}'", subWeb.Url, subWeb.IsDirty());
  11.                 subWeb.ResetRoleInheritance();
  12.             }
  13.             finally
  14.             {
  15.                 subWeb.Dispose();
  16.             }
  17.         }
  18.     }
  19. }

The result shows, that the parent site is not dirty, but all of it sub-webs (returned either by the Webs property or the GetSubwebsForCurrentUser method) are all dirty.

There are two possible workarounds for the issue. We should either call the Update method of the SPWeb instance before invoking the ResetRoleInheritance method, thus clearing the IsDirty flag, or if we don’t want to commit any possible changes, we can create another, clear SPWeb instance from scratch based on the ID or the Url of the original SPWeb object, and invoke the ResetRoleInheritance method on the new instance. The code sample below illustrates both of these options:

  1. using (SPSite site = new SPSite("http://YourSharePointSite/SomeSite&quot;))
  2. {
  3.     using (SPWeb web = site.OpenWeb())
  4.     {
  5.         Console.WriteLine("Web '{0}' is dirty: '{1}'", web.Url, web.IsDirty());
  7.         foreach (SPWeb subWeb in web.Webs) // or the same with webs.GetSubwebsForCurrentUser()
  8.         {
  9.             try
  10.             {
  11.                 Console.WriteLine("Web '{0}' is dirty: '{1}'", subWeb.Url, subWeb.IsDirty());
  12.                 // option 1
  13.                 subWeb.Update();
  14.                 Console.WriteLine("Web '{0}' is dirty: '{1}'", subWeb.Url, subWeb.IsDirty());
  15.                 subWeb.ResetRoleInheritance();
  16.                 //// option 2
  17.                 //using (SPWeb subWebNew = site.OpenWeb(subWeb.ID)) // or site.OpenWeb(subWeb.Url)
  18.                 //{
  19.                 //    Console.WriteLine("Web '{0}' is dirty: '{1}'", subWebNew.Url, subWebNew.IsDirty());
  20.                 //    subWebNew.ResetRoleInheritance();
  21.                 //}
  22.             }
  23.             finally
  24.             {
  25.                 subWeb.Dispose();
  26.             }
  27.         }
  28.     }
  29. }

After this detour into C#, let’s go back to our original PowerShell sample. Although there are no extension methods in PowerShell, we can define a helper function to query and display the value of the IsDirty property, and we can apply both of the above workarounds to “clear” or web instance as well:

  1. function IsDirty($web) {
  2.     $pi = [Microsoft.SharePoint.SPWeb].GetProperty("IsDirty", [Reflection.BindingFlags] "NonPublic,Instance")
  3.     $isDirty = $pi.GetValue($web)
  4.     return $isDirty
  5.     Write-Host Web $($web.Url) is dirty $isDirty
  6. }
  8. $rootWeb = Get-SPWeb 'http://YourSharePointSite/SomeSite&#039;
  9. Write-Host Web $($rootWeb.Url) is dirty $($rootWeb.IsDirty)
  10. $rootWeb.Webs | % {
  11.     $web = $_
  12.     IsDirty $web
  13.     # option 1
  14.     $web.Update()
  15.     IsDirty $web
  16.     $web.ResetRoleInheritance()
  17.     ## option 2
  18.     #$webClear = Get-SPWeb $web.Url
  19.     #IsDirty $webClear
  20.     #$webClear.ResetRoleInheritance()
  21. }

But wait! Is there really nothing in PowerShell like extension methods in C#? Couldn’t we extend or object somehow to be able to write nicer code? About this theme and much more plan I write in a later post.


April 30, 2018

Faking feature activation properties

Filed under: Features, Reflection, SP 2013, Tips & Tricks — Tags: , , , — Peter Holpar @ 21:19

Assume you have a feature receiver in your SharePoint project. You would like to perform multiple actions if the feature is activated, so you organize your code according to the actions into static methods of a helpers class, like shown in the code snippet below:

  1. [Guid("8cb098ae-2017-4fff-8a53-b315abb85d79")]
  2. public class YourFeatureReceiver : SPFeatureReceiver
  3. {
  4.     public override void FeatureActivated(SPFeatureReceiverProperties properties)
  5.     {
  6.         SomeHelperClass.DoSomethingOnTheSite(properties);
  7.         SomeHelperClass.DoSomethingOtherOnTheSite(properties);
  8.         SomeHelperClass.AndFinallyDoThat(properties);
  9.     }
  10. }

Note: In this case, it is a site level feature, and we handle the FeatureActivated event, but you can apply the same technique for other kinds of feature receivers and events as well.

A helper method has the following signature, and contains code like this one:

  1. public static void DoSomethingOnTheSite(SPFeatureReceiverProperties properties)
  2. {
  3.     SPSite site = properties.Feature.Parent as SPSite;
  4.     if (site != null)
  5.     {
  6.         // do something here
  7.     }
  8. }

You would like to test the functionality of your method one-by-one from a console application, without actually having to deploy your SharePoint solution an activate your feature each time again (of course, you need at least an initial deployment of the whole solution). In my case, the helper class is included in a separate assembly, but even if it is the assembly of your SharePoint project, you should only deploy the new version of the assembly into the Global Assembly Cache (GAC).

As you can see in the method above, we use the Feature property of the SPFeatureReceiverProperties class we received in the properties parameter. Of course, the Feature property is read-only, so we need some tricks, to be able to pass the SPFeatureReceiverProperties parameter populated with the correct SPFeature in its Feature property to the method. We create first a new instance of the SPFeatureReceiverProperties class, than query the site for the feature based on its ID (se my important notice about this ID after the code block!). We can inject this SPFeature instance into our formerly crated SPFeatureReceiverProperties instance using Reflection, via the internal SetFeature method of the SPFeatureReceiverProperties class. Finally, we can invoke our helper method with the already-populated properties variable.

  1. using (SPSite site = new SPSite(yourSiteUrl))
  2. {
  3.     var properties = new SPFeatureReceiverProperties();
  5.     var yourFeatureId = new Guid("51bf2a39-b527-46cf-abd6-39aaf1dcd19b");
  7.     var feature = site.Features.FirstOrDefault(f => f.DefinitionId == yourFeatureId);
  8.     if (feature != null)
  9.     {
  10.         MethodInfo mi_setFeature = typeof(SPFeatureReceiverProperties).GetMethod("SetFeature",
  11.             BindingFlags.NonPublic | BindingFlags.Instance);
  13.         if (mi_setFeature != null)
  14.         {
  15.             mi_setFeature.Invoke(properties, new object[] { feature });
  16.             SomeHelperClass.DoSomethingOnTheSite(properties);
  17.         }
  18.     }
  19. }

Note: It is important to notice, that the Guid we used in the feature-lookup above, comparing it to the DefinitionId property, is not the same, as the Guid we saw earlier in the feature receiver code snippet. The latter one is used only by Visual Studio during the deployment process to find up the correct ReceiverClass for the event receiver. The Guid we need, the ID of the feature is available on the Manifest tab of the feature as illustrated below:


Applying this technique made our development and testing process a lot faster.

Creating statistics about web part usage from the SharePoint content database

Filed under: PowerShell, Reflection, SP 2013, SQL, Web part — Tags: , , , — Peter Holpar @ 21:08

Recently I had to create some statistics about SharePoint web site customizations, like on which pages are there Script Editor Web Parts, or Content Editor Web Parts, etc. I knew I could and probably should have done it by iterating through all web sites, all pages and then looking up the web parts on each page using SPLimitedWebPartManager class, but I was aware, the same information should be available via the content database as well, making it possible to query the info much easier and faster, although unsupported. In this post I describe, how you can do it, but use the solution at your own risk.

The web part information is stored in the AllWebParts table, the information about the pages in the AllDocs table. I joined these tables together for the first report about the Script Editor Web Parts.

SELECT AD.DirName + ‘/’ + AD.LeafName as PageUrl, AWP.tp_ZoneID as ZoneId, AWP.tp_PartOrder as WebPartOrder, AWP.tp_Class AS WebPartClass
AllWebParts AWP (nolock)
INNER JOIN AllDocs (nolock) AD ON AWP.tp_SiteId = AD.SiteId AND AWP.tp_PageUrlID = AD.Id
WHERE tp_Class LIKE ‘%ScriptEditorWebPart’

Next, I was to create a report about the Content Editor Web Parts, using a filter like:

WHERE tp_Class = ‘%ContentEditorWebPart’

However, no result found, although I was pretty sure, there are a lot of them in our web site. How is it possible?

To test it further, I’ve included a Script Editor Web Part and a Content Editor Web Part on the AllItems.aspx page of the Tasks list in one of our sub-site, and created a new query with the filter below:

WHERE DirName LIKE ‘%site/subsite/Lists/Tasks%’
AND LeafName LIKE ‘%AllItem%’

This was the result:


As you see, the Script Editor Web Part is there, and you see two further web parts (they should be the Content Editor Web Part and the XsltListViewWeb part, that was originally on the page and is responsible to display the task items in the list), however both of them with a NULL value in the WebPartClass column. What should it mean?

I have studied the structure of the AllWebParts table and the relations of its fields further, and found that there are two fields (tp_Class and tp_Assembly) that are always populated for the records, where the WebPartClass is not NULL, and there is a field called tp_WebPartTypeId – populated for each entries, even for those, where the WebPartClass , tp_Class and tp_Assembly fields are empty – that we could eventually use to find the matching web parts. But how? I made a search for ‘WebPartTypeId’ using .NET Reflector, and found the internal class Microsoft.SharePoint.WebPartPages.WebPartTypeInfo, having a private static method called GetWellKnownTypeIdDictionary that returns a Dictionary<Guid, Type> mapping Guids (WebPartTypeIds) to the actual web part type. Remark: The Guids in the WebPartTypeId are actually created from the MD5 hash of the bytes of the joined full assembly name and web part class name, see the internal static  GetTypeIdUnsafe(MD5HashProvider md5Provider, string typeFullName, string assemblyName) method of the internal sealed class Microsoft.SharePoint.ApplicationRuntime.SafeControls.


To support those so called well-known types in my former SQL-query, I wrote a short PowerShell script that invokes the private static GetWellKnownTypeIdDictionary method of the internal WebPartTypeInfo class, and emits the resulting Dictionary to a text file I can use to extend my query:

  1. $webPartTypeInfoType = [System.Type]::GetType('Microsoft.SharePoint.WebPartPages.WebPartTypeInfo, Microsoft.SharePoint, Version=, Culture=neutral, PublicKeyToken=71e9bce111e9429c')
  2. $mi_GetWellKnownTypeIdDictionary = $webPartTypeInfoType.GetMethod('GetWellKnownTypeIdDictionary', [Reflection.BindingFlags]'NonPublic, Static')
  3. $wellKnownTypeIdDictionary = $mi_GetWellKnownTypeIdDictionary.Invoke($null, $null)
  5. $wpTypes = $wellKnownTypeIdDictionary.Keys | % { "INSERT INTO @WPTypes VALUES ('$_', '$($wellKnownTypeIdDictionary[$_].Assembly.FullName)', '$($wellKnownTypeIdDictionary[$_].FullName)')" }
  6. Set-Content -Path 'C:\Data\WPTypes.txt' -Value $wpTypes

And that is already the extended version of the SQL query:

  2.    (
  3.      Id uniqueidentifier NOT NULL,
  4.      AssemblyName varchar(500),
  5.      ClassName varchar(100)
  6.    )
  8. INSERT INTO @WPTypes VALUES ('8e20cf70-0fd5-1e08-9972-38f63a6bd59a', 'Microsoft.SharePoint, Version=, Culture=neutral, PublicKeyToken=71e9bce111e9429c', 'Microsoft.SharePoint.WebPartPages.ImageWebPart')
  9. INSERT INTO @WPTypes VALUES ('ba009853-eac3-16c8-9094-a8834485ad33', 'Microsoft.SharePoint, Version=, Culture=neutral, PublicKeyToken=71e9bce111e9429c', 'Microsoft.SharePoint.WebPartPages.DataFormWebPart')
  10. INSERT INTO @WPTypes VALUES ('83216ab2-cd0e-e9fc-fc5e-6a8f3b21c37b', 'Microsoft.SharePoint, Version=, Culture=neutral, PublicKeyToken=71e9bce111e9429c', 'Microsoft.SharePoint.WebPartPages.DataViewWebPart')
  11. INSERT INTO @WPTypes VALUES ('42fddde2-e0cf-c8ab-48b7-db1fcac0a917', 'Microsoft.SharePoint, Version=, Culture=neutral, PublicKeyToken=71e9bce111e9429c', 'Microsoft.SharePoint.WebPartPages.ListFormWebPart')
  12. INSERT INTO @WPTypes VALUES ('05d0fd94-372a-5ee7-b480-ccb8f9cd2c23', 'Microsoft.SharePoint, Version=, Culture=neutral, PublicKeyToken=71e9bce111e9429c', 'Microsoft.SharePoint.WebPartPages.ListViewWebPart')
  13. INSERT INTO @WPTypes VALUES ('aef28218-44f8-0538-9805-4842c0e62811', 'Microsoft.SharePoint, Version=, Culture=neutral, PublicKeyToken=71e9bce111e9429c', 'Microsoft.SharePoint.WebPartPages.XsltListFormWebPart')
  14. INSERT INTO @WPTypes VALUES ('a6524906-3fd2-ee4e-23ee-252d3c6e0dc9', 'Microsoft.SharePoint, Version=, Culture=neutral, PublicKeyToken=71e9bce111e9429c', 'Microsoft.SharePoint.WebPartPages.XsltListViewWebPart')
  15. INSERT INTO @WPTypes VALUES ('0c6143a7-d68b-bade-e0ef-2c4d01182b0c', 'Microsoft.SharePoint, Version=, Culture=neutral, PublicKeyToken=71e9bce111e9429c', 'Microsoft.SharePoint.WebPartPages.BlogAdminWebPart')
  16. INSERT INTO @WPTypes VALUES ('afef48e1-8f94-eb71-03a6-ffceb685306a', 'Microsoft.SharePoint, Version=, Culture=neutral, PublicKeyToken=71e9bce111e9429c', 'Microsoft.SharePoint.WebPartPages.BlogMonthQuickLaunch')
  17. INSERT INTO @WPTypes VALUES ('4c06cea2-364f-47e3-e1d7-08d53f441157', 'Microsoft.SharePoint, Version=, Culture=neutral, PublicKeyToken=71e9bce111e9429c', 'Microsoft.SharePoint.WebPartPages.ContentEditorWebPart')
  18. INSERT INTO @WPTypes VALUES ('e6047383-438e-ed87-1a93-f1ff71729044', 'Microsoft.SharePoint, Version=, Culture=neutral, PublicKeyToken=71e9bce111e9429c', 'Microsoft.SharePoint.WebPartPages.TitleBarWebPart')
  19. INSERT INTO @WPTypes VALUES ('707c1e73-0b3d-898b-c755-01621802ab8c', 'Microsoft.SharePoint, Version=, Culture=neutral, PublicKeyToken=71e9bce111e9429c', 'Microsoft.SharePoint.WebPartPages.SilverlightWebPart')
  20. INSERT INTO @WPTypes VALUES ('28c23aec-2537-68b3-43b6-845b13cea19f', 'Microsoft.SharePoint, Version=, Culture=neutral, PublicKeyToken=71e9bce111e9429c', 'Microsoft.SharePoint.WebPartPages.ErrorWebPart')
  21. INSERT INTO @WPTypes VALUES ('8d6034c4-a416-e535-281a-6b714894e1aa', 'Microsoft.SharePoint, Version=, Culture=neutral, PublicKeyToken=71e9bce111e9429c', 'Microsoft.SharePoint.WebPartPages.ErrorWebPart')
  22. INSERT INTO @WPTypes VALUES ('8e814083-396a-e7d1-148b-316e3a7283f7', 'Microsoft.SharePoint, Version=, Culture=neutral, PublicKeyToken=71e9bce111e9429c', 'Microsoft.SharePoint.WebPartPages.ErrorWebPart')
  23. INSERT INTO @WPTypes VALUES ('e6377261-6920-bbfe-501f-fda7a61db10f', 'Microsoft.SharePoint, Version=, Culture=neutral, PublicKeyToken=71e9bce111e9429c', 'Microsoft.SharePoint.WebPartPages.ErrorWebPart')
  24. INSERT INTO @WPTypes VALUES ('8efd140d-eae9-5feb-06e3-f771842d2e43', 'Microsoft.SharePoint, Version=, Culture=neutral, PublicKeyToken=71e9bce111e9429c', 'Microsoft.SharePoint.WebPartPages.ErrorWebPart')
  25. INSERT INTO @WPTypes VALUES ('b3294a07-46bf-e661-d036-10670590bbd3', 'Microsoft.SharePoint, Version=, Culture=neutral, PublicKeyToken=71e9bce111e9429c', 'Microsoft.SharePoint.WebPartPages.SPUserCodeWebPart')
  27. SELECT AD.DirName + '/' + AD.LeafName as PageUrl, AWP.tp_ZoneID as ZoneId, AWP.tp_PartOrder as WebPartOrder, ISNULL(AWP.tp_Class, WPT.ClassName) AS WebPartClass
  28. FROM AllWebParts AWP (nolock)
  29. INNER JOIN AllDocs AD (nolock) ON AWP.tp_SiteId = AD.SiteId AND AWP.tp_PageUrlID = AD.Id
  30. LEFT JOIN @WPTypes WPT ON AWP.tp_WebPartTypeId = WPT.Id
  31. WHERE ISNULL(AWP.tp_Class, WPT.ClassName) LIKE '%ContentEditorWebPart'

Of course, you can change the conditions of the query as you like, for example, you can restrict it to two web part type, like:

WHERE ISNULL(AWP.tp_Class, WPT.ClassName) IN (‘Microsoft.SharePoint.WebPartPages.ScriptEditorWebPart’, ‘Microsoft.SharePoint.WebPartPages.ContentEditorWebPart’)

There are a few more columns in the AllWebParts table, that you eventually would include either in the SELECT statement or in its WHERE clause, these are:

  • tp_IsIncluded: The web part is displayed on the page, if the value is 1 (default). If you close (not delete!) a web part, the value is 0. Deleted web parts are removed from the table.
  • tp_Deleted: Assume you have a list with some pages that includes web parts, like view pages including XsltListViewWebPart instances. The web part entries in the AllWebParts table have a value of 0 at this stage. If you delete the list, these values change to 1. The web part entries will be kept in the table even after deleting the list from the first (user) level Recycle Bin, and removed only after the list is deleted from the second (site collection) level Recycle Bin.
  • tp_ListId: This is a field that is populated for list-related built-in web parts, like XsltListViewWebPart. You can look up the related list and web instances by joining the Lists and Webs views in your query respectively, as shown below (this time I omit the declaration of the @WPTypes variable and its population with value for the sake of brevity, but of course, you need it this time either):

SELECT L.tp_Title as ListTitle, W.FullUrl AS WebUrl, AD.DirName + ‘/’ + AD.LeafName as PageUrl, AWP.tp_ZoneID as ZoneId, AWP.tp_PartOrder as WebPartOrder, ISNULL(AWP.tp_Class, WPT.ClassName) AS WebPartClass, tp_ListId
FROM AllWebParts AWP (nolock)
INNER JOIN AllDocs AD (nolock) ON AWP.tp_SiteId = AD.SiteId AND AWP.tp_PageUrlID = AD.Id
LEFT JOIN @WPTypes WPT ON AWP.tp_WebPartTypeId = WPT.Id
LEFT JOIN Lists L (nolock) ON AWP.tp_SiteId = L.tp_SiteId AND AWP.tp_ListId = L.tp_ID
LEFT JOIN Webs W (nolock) ON AWP.tp_SiteId = W.SiteId AND L.tp_WebId = W.Id
WHERE ISNULL(AWP.tp_Class, WPT.ClassName) LIKE ‘%ListViewWebPart’

By including the list title or the web URL in the WHERE clause (or the ID of the list or the web if you wish) you can further limit the items returned by the query.

If there are records returned with NULL in the ListTitle and WebUrl columns it means typically that the list was deleted, but yet available in the Recycle Bin. See my comments regarding the tp_Deleted field above. Note, that despite the name of the FullUrl column in the Web view, it is actually a server relative URL.

I hope this overview has helped you to better understand what and how is stored in these tables of the SharePoint content database, as well, how the “magical” IDs of the well-known web part types do fit into the whole picture.

Older Posts »

Create a free website or blog at