SharePoint add Count to List View using powershell

As a best practice, we create lists that have substantial number of columns using powershell.

What I like about using powershell is – repetitive and controlled deployment in dev, test and prod are predictable.

Not just that, you can control the internal field names much better.

The Aggregations (or Count of rows) is done to the View – not to the List. So you need to get reference to the View (and not to the List).

$web = "http://dev/sites/yoursitename/"
$spsite = [Microsoft.SharePoint.SPSite]($web)
$web = $spsite.OpenWeb()
$view = $web.GetViewFromURL("Lists/YourList/AllItems.aspx)
$aggregations = "<FieldRef Name='LinkTitle' Type='COUNT'/>"
$view.Aggregations. = $aggregations
$view.AggregationsStatus = "On"

That’s it.

Other Aggregations Types are ‘MAX’ for Maximum, ‘SUM’ for Sum’s, above example has ‘COUNT’ for Total’s.

Now that looks very simple, but in your real life scenario you are probably building up a more complex query and aggregations.

So how do I actually build the CAML query. Using

Create a list first (using powershell or IE) – and understand the CAML by actually creating a view using UI (internet explorer) if you are uncomfortable with powershell.

When the view is available, use the SPCAML Query Helper to figure out what the CAML was for your view. I found this to be very handy.



SharePoint 2013 – Delete User Profile PictureURL values from all profiles

SharePoint offers 3 options for User Profile and Property synchronization.

1. SharePoint Profile Synchronization
2. SharePoint Active Directory Import
3. External Identity Manager

I won’t go into the depth of each, but let’s say you choose SharePoint Profile Synchronization to import Active Directory information including the Picture.

Now after syncronization, you need to run this SharePoint Powershell to convert the imported pictures into the small, medium, large sized photos that can be used properly.

Update-SPProfilePhotoStore -CreateThumbnailsForImportedPhotos 1 -MySiteHostLocation http://mysitehost:port/sites/people/

Now, it seems maybe not all PictureURL properties got imported and converted, and you have to start again.

Instead of deleting the user profiles (and potential data entered by the user), here is a quick and easy way to clear out the PictureURL property from all user profiles.

Add-PSSnapin Microsoft.Sharepoint.Powershell
$site = Get-SPSite http://mysitehost:port/
$context = Get-SPServiceContext $site
$upm = New-Object Microsoft.Office.Server.UserProfiles.UserProfileManager($context)
$upPictureURLAttribute = "PictureURL"
$profiles = $upm.GetEnumerator()
foreach ($userprofile in $profiles)
write-host "Deleting ", $userprofile[$upPictureURLAttribute].GetValue()
write-host "Deleted"

Project Server 2010 – Not receiving Email Notifications for New Task Assignments?

Your resources are not getting email notifications when a new Task is assigned to them.

I have checked off “I receive a new task assignment in my projects” for “Manage My Alerts and Reminders” as below:

And that might not be enough. You need to check off “My project tasks are modified” as well.

And the reason for that is you might be creating a Project with “n” number of tasks, and then assigning them at a later point in time. At this “later point in time” Project Server thinks this is not a New Task (or Assignment), it thinks the Task (or Assignment) is modified – in which case the second checkbox comes handy.

This is pretty annoying since some of the resources dont want to know what’s going on every day with their tasks, but rather want to know only when NEW tasks are assigned to them.

Not sure if this is a BUG or BY DESIGN. I will investigate and update this post.

SharePoint / Project Server – Service Pack 1 and June CU Refresh Package

This is for installation of “Service Pack 1” and the “June CU Refresh” upgrade to Project Server 2010. I have listed out the process for updating a farm (including the sequence of app server, project server, web front end).
Updating SharePoint 2010 is relatively OK, but introducing Project Server is a challange in itself. After reading a fair share of blogs, technet, here is how we proceeded.
Read Me First
  • Both packages are cumulative in terms of updates, refer to the KB articles below for additional details and download links. Project Server 2010 SP1 – Project Server 2010 June CU Refresh –
  • Both packages are cumulative in terms of product version. You do not need to install SharePoint Foundation 2010 or SharePoint Server 2010 “Service Pack 1” or “CU” when you are installing the Project Server 2010 Service Pack 1 or Project Server 2010 June CU Refresh package. 
High Level Procedure Overview
  1. Run all command prompts and execute all packages using “Run As Administrator” (UAC)
  2. Download the installer locally on all SharePoint servers, ensure you have enough temp storage space
  3. Run Service Pack 1 installer on all SharePoint servers
  4. Run the June CU Refresh installer on all SharePoint servers
  5. Run Configuration Wizard on all SharePoint servers in the order starting with server hosting Central Admin, other Application servers and then any Web Front End servers.
Detailed Overview

Group Commands are listed below as they are referenced in the table.

  • Col A – net stop w3svc
  • Col B – net stop ProjectQueueService14
  • Col C – Install Project Server 2010 SP1
  • Col D – reboot
  • Col E – net stop w3svc
  • Col F – net stop ProjectQueueService14
  • Col G – Install Project Server 2010 June CU Refresh
  • Col H – reboot
  • Col I – net stop w3svc
  • Col J – net stop ProjectQueueService14
  • Col K – run SharePoint Configuration Wizard
The table below outlines the sequence in which you need to execute commands for that particular Group. There is a total of 43 tasks you need to complete, starting with #1.
Server Names
Col A
Col B
Col C
Col D
Col E
Col F
Col G
Col H
Col I
Col J
Col K
Central Admin
Project App
something else
WFE 01
WFE 02
  • Complete all steps in a Column in the order indicated before moving to the next Column . As an example for “Column A”, perform steps 1, 2, 3, 4, 5 on the servers indicated in “Server Names” column before moving to Column B : Task 6. Column A command is “net stop w3svc”.
  • Steps in Column C can be run in parallel to save time.
  • Steps in Column G can be run in parallel to save time.
  • There will be a delay of 2~5 minutes (or more) before you see installer screen after executing “Run as Administrator” for Column C and Column G and that is normal. Do not log off or cancel any process from Task Manager.
  • If any installer asks you to reboot now, choose to reboot later (or choose ‘do not close applications’ and ‘reboot later’). Complete the installation and follow the reboot order listed above.
  • Steps in Column K needs to be executed and finished in whole before moving to the next step. None of the steps in Group K can be run in parallel.
Error at Step 39

There were two issues & resolutions described below.

Issue #1

This was a known issue. The SharePoint Configuration Wizard will fail at #39 in Column K. After it fails, close the wizard and start the “User Profile Synchronization Service” from Central Administration. The account to use (as logged in, and the service account) while starting is the farm account.

The User Profile Synchronization Service takes couple of minutes (10 maybe 15) to start. Once the User Profile Synchronization Service is started, open a command prompt and run following to complete #39.

psconfig.exe -cmd upgrade -inplace b2b -wait
After the above command is 100% complete, you can move to Step 40.
Issue #2
This was a new issue in Production only. In addition to the above Issue #1, another issue was resulting in the psconfig not completing. The error after running psconfig was
Failed to upgrade SharePoint Products.
An exception of type Microsoft.SharePoint.Administration.SPUpdatedConcurrencyException was thrown. Additional exception information: An update conflict has occurred, and you must re-try this action. The object SPUpgradeSession Name=Upgrade-20111020-172808-203 was updated by CORP\ACCOUNT, in the PSCONFIG (6100) process, on machine APPSERVER1. View the tracing log for more information about the conflict
This is caused due to the SharePoint 2010 Timer service not starting at all. Resolution is to manually start this “SharePoint 2010 Timer” service immediately after you run the psconfig command. The “SharePoint 2010 Timer” service is available under Control Panel, Administrative Tools and Services.
Clearing the cache was an option discussed in newsgroups, but it did not solve the issue of SPUpdatedConcurrencyException, the resolution was manually starting the Timer service.
Hope this helps you.

Project Server – Approval Centre Ribbon Disabled or Greyed Out – Cannot accept any task updates

Resources have submitted task updates and you (as a PM) need to approve / accept the task updates.

When you navigate to the Approval Center and select the task update that needs approval – you simply cannot accept (or reject) it as the entire Approval Center ribbon is greyed out.

This happens due to a known bug (pre SP1) and there is a javascript error on the Internet Explorer tool bar.

UPDATE Jan 26, 2012 – This is not fixed in SP1 and June CU Refresh Package.

The bug is one or more of the tasks in the approval center has a “START DATE” that is “EXACTLY” 63 days in the past from TODAY. I have seen this issue for a difference ranging anywhere from 61 days to 63 days.

In other words, if the difference between START DATE and TODAY is 63 days (TODAY’s date included) you will see this issue.

The following screen shot shows an updated START DATE = 11/23/2011 (Nov 23, 2011). TODAY when I took this screen shot, the date is 1/24/2012 (Jan 24, 2012). A difference of exactly 63 days (start date to the end date, end date included)

Resolution is to identify which task has a start date of 63 days in the past – then open project professional or pwa, and change the start date (to not make it 61 days difference from today).

Once done, navigate back to the approval center and you should be able to approve task updates.

Some people have reported the difference to be 60 days or 61 days. Defining the difference – I am calculating START and TODAY, and difference to be 63 days including START and TODAY.

Project Server Post SP1 – Approval Center Rules Error

If you create rules in the Approval Center PRIOR to Project Server SP1, this is how it looks. Notice there are only three columns “Name”, “Description” and “Apply Automatically”

Figure 1 – Pre SP1 Rules Page


If your users have created Rules in the Approval Center PRIOR to Project Server SP1, they cannot edit them POST SP1.


Once I apply Project Server Sp1, how do I know if a rule was created PRE SP1?


If you view the rules, the PRE SP1 rules will have “Automatically Publish” shown as blank (its actually a null) – see image.

Figure 2 – Post SP1 Rules Page with Rules from Pre SP1

They can create new rules POST SP1, but cannot edit prior rules due to a known issue. This error occurs because the Project Server 2010 Service Pack 1 update adds a new column, RULE_AUTOMATIC_PUBLISH, to the MSP_Rules table, and all existing rows are left with a null value.

See below, I created a new rule and appended the “SP1” text to the name. That rule has the “Automatically Publish” field.

Figure 3 – Post SP1 Rules Page with Rules from Post SP1

The ULS error is
System.Data.StrongTypingException: The value for column 'RULE_AUTOMATIC_PUBLISH' in table 'Rules' is DBNull. ---> System.InvalidCastException: Specified cast is not valid.
at Microsoft.Office.Project.Server.Schema.RulesDataSet.RulesRow.get_RULE_AUTOMATIC_PUBLISH()


As a workaround for this issue, there are couple of options:

  1. You can delete and then re-create the approval rule (if you know what the rule is – not an option IMHO – are you going to notify your users that “oops – there is a bug – redo your rules again”)
  2. Making an update directly to the MSP_RULES table in the Project Server 2010 Published database that holds the rules definitions (valid option)
    Update MSP_Rules
  3. This error is fixed in the June 2011 Cumulative Update for Project Server 2010 @

This is also documented @

Project Professional & Server – Failed to Publish, View failed to Load, Job cancels at 93% or 94% Failed And Blocking Correlation

Sometimes your Project Professional file will simply get corrupted. I was having these symptoms while attempting to Save, Publish or even View the schedule:

Error while publishing from Project Professional: “Could not publish the project. Error ID 23000”

Error while trying to load the Project Schedule from PWA: “The view failed to load”

Error while job in queue is cancelled: “Usually the job gets cancelled at 93% or 94%”

Failed And Blocking Correlation Microsoft.Office.Project.Server.BusinessLayer.Queue.Message: messagetype=’Microsoft.Office.Project.Server.BusinessLayer.QueueMsg.ConflictResolutionMessage’


  1. Open Pro 2010
  2. Open the Project Schedule from the server that is giving errors
  3. Now save it locally “without making changes in File Name” using File > Save & Send > Save Project as File > Save For Sharing
  4. Close Project Pro 2010 – you can keep the project checked out to yourself
  5. Now open Pro 2010 > File > Open > open the project plan from your DESKTOP
  6. File > Save As (dont change the Project Name, it might be even grayed out)
  7. Save and then publish the project plan

Update – It is better to do this process logged in as the Project Manager or Status Manager – If I did this using my “admin” account, the admin account became the Status Manager for all tasks. FYI.