Skip Ribbon Commands
Skip to main content
SharePoint

MyProjectExpert

October 26
Deleting Project with Rejected Timesheet Lines Fails

​I came across a situation that when I tried deleting a project from PWA, the project deletion failed because it had outstanding rejected timesheets. Looking in the queue I found the following error messages. This occurred on Project Server 2010.  A CU was issued to fixed this bug, but in my situation where I could not apply any patches until the end of year.  Also, the project was a test project and needed to be deleted, because it was missing up the resource allocation.

When deleting a project I get the following errors.

Collections:

rejectedTimesheetLines (System.Guid) :

Item value = 'cfe6ca01-bdae-49d8-ad41-0262d01f1fe7' :

Error: id='10000' name='GeneralItemDoesNotExist' uid='7b4279e5-c8a3-45af-a9ac-c050f232c09e'

Error: id='10000' name='GeneralItemDoesNotExist' uid='d87dff17-3e3c-4ac8-a63e-854f79fe725c'

Error: id='10000' name='GeneralItemDoesNotExist' uid='3910e065-30bd-454f-8911-164910232fd3'

General Error:

ProjectDeleteFailure (23006). Details: id='23006' name='ProjectDeleteFailure' uid='e94c8143-7d28-4fa1-acb0-5e8aefb3adab' projectuid='05cfcc25-6a13-46e3-ab5c-d173942af6c5' messagetype='Microsoft.Office.Project.Server.BusinessLayer.QueueMsg.AdjustTimeSheetForDeletedProjectMessage' messageID='6' stage='' blocking='Undefined'.

Queue:

GeneralQueueJobFailed (26000) - ProjectDelete.AdjustTimeSheetForDeletedProjectMessage. Details: id='26000' name='GeneralQueueJobFailed' uid='4768f5c6-52ee-4679-83e8-81c04002d2a0' JobUID='0ef10d90-9319-4979-b49f-da8f3c9a9ece' ComputerName='SP07' GroupType='ProjectDelete' MessageType='AdjustTimeSheetForDeletedProjectMessage' MessageId='6' Stage=''. For more details, check the ULS logs on machine SP07 for entries with JobUID 0ef10d90-9319-4979-b49f-da8f3c9a9ece.

I called the MS Support team and they provided me the following query to delete all the timesheets.  I probably could just delete the rejected timesheets, but this particular project was a test project and so none of the teimesheet information was needed.

Here is what we did to resolve

1) Backup all the Project Server databases

2) Execute the SQL statements below

3) From PWA / Server Settings / Delete Objects/ Delete Published projects.

 

USE ProjectServer_Published

-- The Proj_UID should match the Proj_UID in query below

SELECT PROJ_NAME, PROJ_UID

FROM MSP_PROJECTS

WHERE PROJ_NAME like '%enter name of project%'

-- Make note of rows selected for reference

-- Replace the UID with number found above

--

SELECT * FROM MSP_TIMESHEET_ACTUALs

where ts_line_UID IN (SELECT DISTINCT A.TS_LINE_UID

FROM MSP_TIMESHEET_ACTIONS A

INNER JOIN MSP_TIMESHEET_LINES L ON A.TS_LINE_UID = L.TS_LINE_UID

WHERE A.TS_ACTION_ENUM = 1 AND L.PROJ_UID = '05CFCC25-6A13-46E3-AB5C-D173942AF6C5')

--

-- Query to remove rows

--

DELETE MSP_TIMESHEET_ACTUALs

where ts_line_UID IN (SELECT DISTINCT A.TS_LINE_UID

FROM MSP_TIMESHEET_ACTIONS A

INNER JOIN MSP_TIMESHEET_LINES L ON A.TS_LINE_UID = L.TS_LINE_UID

WHERE A.TS_ACTION_ENUM = 1 AND L.PROJ_UID = '05CFCC25-6A13-46E3-AB5C-D173942AF6C5')

--

-- Make note of number of rows selected

--

SELECT * FROM MSP_TIMESHEET_LINES

where ts_line_UID IN (SELECT DISTINCT A.TS_LINE_UID

FROM MSP_TIMESHEET_ACTIONS A

INNER JOIN MSP_TIMESHEET_LINES L ON A.TS_LINE_UID = L.TS_LINE_UID

WHERE A.TS_ACTION_ENUM = 1 AND L.PROJ_UID = '05CFCC25-6A13-46E3-AB5C-D173942AF6C5')

--

-- Query to remove rows

--

delete MSP_TIMESHEET_LINES

where ts_line_UID IN (SELECT DISTINCT A.TS_LINE_UID

FROM MSP_TIMESHEET_ACTIONS A

INNER JOIN MSP_TIMESHEET_LINES L ON A.TS_LINE_UID = L.TS_LINE_UID

WHERE A.TS_ACTION_ENUM = 1 AND L.PROJ_UID = '05CFCC25-6A13-46E3-AB5C-D173942AF6C5')

 

September 05
Using PowerShell for Automating MS Project

PowerShell is becoming part of the mainstream for IT support.  I have been using it for years and found it useful for keeping all my procedures together for installing and configuring SharePoint and Project Server.  It's a wonderful tool and as I get older and forgetful, I am finding it is helping me keep my act together.

However, I never thought about using if for MS Project until just recently.  Just recently I attended a PowerShell User Group meeting in Charlotte, NC with hopes of learning new tips and tricks for using PowerShell.   What caught my eye for this meeting was the PowerShell Scripting Games.   I have heard of this before and was curious on how this game was played.

The concept of PowerShell Scripting games is really simple.  The facilitator of the meeting, basically presents a simple problem and everyone tries to solve them using their laptops running PowerShell.  They can use any resource that they can access, including the internet.   After about fifteen minutes, the group discusses how they solved the problem.  As you can imagine, everyone has a slightly different approach on solving the problem and so there was a lot of good ideas passed around.

This particular evening, one game was to create an Excel spreadsheet and save it to three files (Peter.xls, Paul.xls and Mary.xls).  Excel is a COM object and basically PowerShell can open Excel and manipulated Excel using PowerShell.  Here is where the light turned on!  Once I saw that I said to myself, PowerShell can be used for automating Project.

I am still trying to figure out some good business cases for using PowerShell and Microsoft Project, but a few choirs such as updating the enterprise resource pool or adding new users to the enterprise pool might be possible.  More to come as I learn more about it.  What I want to show in this blog is what a PowerShell script would look like and how it is executed.

$Project = New-Object -ComObject msproject.application  
$Project.Visible = $True
$status= $Project.FileNew()
 
$status= $Project.SetTaskField("Name","aaa")
$status= $Project.SetTaskField("Duration","1")
 
$status= $Project.SelectTaskField( 1,"Name")
$status= $Project.SetTaskField("Name","bbb")
$status= $Project.SetTaskField("Duration","2")
$status= $Project.SetTaskField("Predecessors","1")
 
$status= $Project.SelectTaskField( 1,"Name")
$status= $Project.SetTaskField("Name","ccc")
$status= $Project.SetTaskField("Duration","3")
$status= $Project.SetTaskField("Predecessors","2")
 
$status= $Project.FileSaveAs('c:\Temp\MyPowerShellProject')
$project.Quit()

At here is what the script produces.

SampleProject.png 

The following procedure shows how to execute and create the MyPowerShellProject.mpp

  1. Create a file called "MyPowerShellProject.PS1" in C:\TEMP
  2. Open a DOS prompt and RUN AS ADMIN
  3. Set you default to C:\TEMP
  4. Run the following in the DOS prompt>  POWERSHELL –FILE MyPowerShellProject.PS1
  5. Once it completes, open the new project file MyPowerShellProject.MPP

Here is one tip that help me to quickly get started.  I recorded a macro and much of these translated to PowerShell.  Very cool!! 

In summary, this is very exciting and I see a lot of potential. I raises a lot of questions and is giving me new ideas. I hope that if you try this out, you will later share with me how you are using PowerShell.

August 18
Build a PWA Security Test Harness

Most people familiar with project server will agree that the "project server permission mode" security model is powerful and flexible and at the same time complex and difficult understand.  Because of this Microsoft create the SharePoint permission mode and though this security model is much simpler it lacks the security requirements that most companies required.

I have been working with project server security for years and though quite comfortable with molding PWA groups, categories and permissions to meet a PMO security requirements, I still find myself wondering the effects of my changes.  Recently I came up with a PWA SECURITY TEST HARNESS that makes this much easier to understand the impact of security changes before putting into production.  As you already know, it's best to test and develop in a test environment before rolling into production. With this in mind, the test harness can also be enable in production for a final review with little risk on production.

The concept of the test harness is simple.  It's made up of a "TestHarnessGrp" security group and "TestHarnessCat" security category.   The TestHarnessGrp group is configured using the TestHarnessCat category.  All global and category permissions are unchecked with the exception of "Logon to Project Server'.  If "Logon to Project Server" is unchecked, then you can't access PWA, which the minimum permission that we need for testing our PWA security.

SecurityHarness1-Permissions.png 

  Create a PWA Test User profile and then assign TestHarnessGrp group to user profile.  The Test User contains only the TestHarnessGrp group and no other permissions checked. Log in PWA using TestUser and you will see something similar to screenshot below:

SecurityHarness2-NoPermissions.png 

Here is where it gets interesting.  You shouldn't see any PWA links or menu options after logging in as TestUser.  It should look very similar to the screen shot above and here is your first insight into the security model.    The few links that are shown are being controlled by SharePoint permission and not PWA.

So you next question may be; "How do I use the test harness?"  The main principal of the test harness is to understand the minimum settings required for enabling a feature in PWA.  The security model often requires one or more setting for a feature to work.  It may require either global or category permissions and the views may also need to be enabled.   For example, if you wanted to enable only the Resource Center link then what are the minimum permissions required. Then after the Resource Center is shown on the quick launch how do you control what data is shown. And finally how can you refine the project and resource list using the RBS.  The test harness, it becomes easier to understand the effect of permissions and changes to the security model.

The Resource Center is a quick launch for viewing and updating various types of resources.  Looking thru all the permission, I reasoned that enabling the global permission "Resource Center" turns on the Resource Center quick launch. To test this theory, I check "View Resource Center" in TestHarnessGrp group and then login using the TestUser account.   The "Resource Center" quick launch is now available.

SecurityHarness3-PWA-Home.png 

But when I quick on "Resource" to access the "Resource Center" the message, "View Failure" appears and then clicking OK comes up with "Access Denied".  At first this may be a frustrating but you are almost there.  Project server now wants additional information on what you can view and these permissions are set in the TestHarnessCat category.

SecurityHarness4-ViewFailure.png 

The security category provides the rules for the security reach and the views.  Looking back thru the TestHarnessCat category there is a view called "All Resources".  This sounds promising, so enable this view and test it again using TestUser.  BAM!  You can now access the quick launch and a view data.

SecurityHarness5-ResourceView.png 

Using the security harness I can further refine my views and permissions to meet the PMO requirements.   The key take away with using the test harness is to know the exact permissions and views to enable for a particular functionally.  Once this is understand, you will better equip to either add new functionality or remove functionally.

July 27
Keep the PMO moving by using Delegation

Delegation provides the ability of working in behalf of another person.  It is usually used because someone is out on vacation or just not available at the time when something needs to happen.  It's a critical tool that facilities project server processes and data is moving in a timely fashion and ensures that PMO is running smoothly.  For example, suppose timesheets are due first thing Monday morning at 10:00, so the contractors can get paid on Thursday.  Timesheets have to be approved and moved thru the cycle, but if resource forgets to submit his timesheet, then there is a good chance he may not get paid in a timely manner.  Or perhaps the timesheet needs to be approved and the timesheet manager is out on vacation.  The process of getting the timesheets approve must still continue.

Delegation ensures that processes continue in a timely manager.  Delegation is the grease that keeps are the PMO wheels turning.  Another example, is the Project Manager is out for vacation.  In the previous versions of project server, the project schedule owner had to be changed and the temporary manager could update the schedule and republish. It worked but it was not clean, because the project schedule may have update the status managers for the task and then the project manager returned and ownership had to change back. Sometimes it created a bigger mess, so delegation made this process of working in behalf of some very easy. 

It's good feature for troubleshooting issues with project server.   When a user is having a problem the technical support can work in behalf of the person and reproduce the problem.  Often, problems are very difficult to understand and by using delegation I can better understand what the issue is and how to fix it.  When the problem gets fixed, delegation can be used to verify that the problem is fixed.

Another useful function of delegation is that sending timesheets and submitting task updates for work resources who do not have access to project server.  It's usually best for individuals to enter their own time, but there are situations where a person may need to put the updates on paper and then have another person keying in those updates.  So it's easy to see that delegation can be used in many situations and it improves the process of keeping the data flow moving in project server.

And finally I find delegation useful for testing security and new processes within the PMO.  When setting up a new PMO or making change to the processes on project server, delegation is useful for playing different role and making sure that processes work as they were designed.  With delegation, it's easy to switch from one person to another and test different roles.

How to Use Delegation

Using delegation is straight forward.  First a delegation session needs to be defined. It contains three parts.  One person is assigned as the "delegate" who is doing the work and another person assigned the "working on behalf of" person and then a period of time is specified as to when the delegate is allowed to work in behalf of another.  As simple as it is, the strategy for managing delegates can be complex.  Delegation needs to be easy to maintain and at the same time, you need to prevent it from being a way to hack project server.  Depending on the version of project server, there is a "Manage Delegates" and "Act as a Delegate" option found under server settings.

  • Manage Delegates:  Delegate records must first be defined as to who is the delegate, working on behalf of someone and during a period of time.   The manage delegates requires special permission and discussed later in this paper.
  • Act as a Delegate:  The delegate turns on the delegation.  Once delegation is turned on yellow bar on ribbon indicates that you are currently in delegation mode and working in behalf of someone.

Step 1:  Using "Manage Delegates" to define a delegation session

Setting and managing delegates is easy, however, there are some issues that we need to be concerned with.  For example, it would not be good for delegates to work in behalf of the PWA "Administrator".  If delegation is not properly designed, the keys to controlling PWA are given out freely to all those who have the "Delegate" security permission.  Most people are ethical and wouldn't take advantage of this, but you still must be careful.  Especially, if processes are tied into timesheets and contractor payments.

DelegarionAdd.gif 

Step 2: Act as a delegate.  Click on this option and then click on who you will be working on behalf of.  If there are not records, then go back to step one.  Once delegation is enable, there is a yellow bar on the ribbon.DelegationRibbon.gif

  • Turning off delegation requires clicking on the "here" to  get a ribbon option of "Stop Delegation"

DelegationStop2.gif 

Configuring Delegate Managers and Acting as Delegates

Configuring delegate managers and delegates is done by setting PWA security permissions in categories and groups.  Configuration follows the same principles as defining any security permission.  Often there is a category that defines the reach of how has features enables and a group that includes categories and global permissions.  The out-of-box security has delegation setup for project managers and resource managers and may be a good place to study on how they are originally defined.

In the Global Permissions section in security group contains the following switches to enable or disable delegation:

  • Can Be Delegate:  Enables this user to become a delegate for another user.  This is required for anyone who will a delegate.  It can be set in one or more groups.  It's a good practice to great a new group called "Delegates" to assign to users that do not fit in any of the typical groups.
  • Manage My Delegates: Enables the user to create his or her own delegations.  Basically, it allows you to define a delegate for yourself when you are out. It's limited in that you can only set up people who will be working in your behalf.  Setting the time period and selecting your delegate, creates delegate working in your behalf.  Take note that there is not a "Working on Behalf Of" in this example.   The assumption is that the delegate is working in your behalf or the person who has this permission enabled.


DelegationFigure1.png 

Figure 1

  • Manage My Resource Delegates: – Enables the user to setup delegations for other users.
    • Set delegation period defines when the delegate can enable delegation.
    • Set delegate lists all resources who have "Delegate" permission.  The user must have delegate permission in one of their assign groups or it can simply be enable on the user update page. Create a security group called "Delegates" and put resources in that group versus setting a permission on the user update page.
    • Working on behalf of lists all resources that have the "Manage Resource Delegates" permission set in a category.
    • Recommend that a group called "Delegate Manager" that contains the users that manage the delegation process.

 DelegationFigure2.png

Figure 2

Point of confusion. When "Manage My Delegates" is checked and "Manage My Resource Delegates" is not checked, the managed menu only allows you to define your delegate and will look like figure 1. However, if "Manage My Delegates" and "Manage My Resource Delegates" are both checked, then the menu looks like figure 2. Where the confusion is that it's hard to tell that the two permissions are enable.  When both permissions are set then you must specify yourself you username in the "Working on behalf of" field.   Also, when the "Manage My Delegates" is unchecked, this means that you cannot have someone to delegate for you.

Defining PMO delegation and "working on behalf of" governance.

The following statements can be used to determine the PMO delegation and "working on behalf of" policy for your organization.

  1. The PMO Staff needs the ability to assign delegates for Project Managers, Resource Managers or any role that requires attention when individual is unavailable.
  2. Project Managers can assign someone to "work in their behalf of" as a project manager when they are unavailable and the delegation period is for one week. It's important that project schedules are maintained and published weekly to ensure work resources are working on the appropriate deliverables.  Does the PMO manage the schedule or will this be assigned to a backup project manager?
  3. Resource Managers can assign someone "to work in their behalf" as a resource manager when they are unavailable and the delegation periods is for one week.
  4. The administrator has keys to everything in the project server, so no one is allow to delegate as Administrator.  When someone needs to cover for an administrator they should be assigned the role when the need arises.
  5. Work resources can be assigned to delegates to send final timesheets or submit task sheets. When timesheets are used for billing, a special delegate can be assign to complete and submit timesheets for resources.
  6. Should a special security group called DELEGATES or DELEGATE MANAGER be used for managing delegates and delegation?
  7. Other PMO groups such as Day-to-day Resource Managers and Enterprise Managers need delegates to keep the flow of project server moving.

Strategy of Implementing Delegation Governance.

  1. Define security groups and categories that allow users to become delegates and to manage their 'working on behalf of" resources. Allowing project manages and resources managers to set up "working on behalf of" when the need arises speeds up the process of keeping data moving, however it can also open up some doors that need to be closed.  For example, if not careful, a project manager could setup delegation as an executive and then they can see all projects and resources.
  2. Provide a security group called "Delegate Manager".  Security permission are setup allowing designated people to create and managed the delegation list.
  3. Provide a security group called "Delegates".   Individuals in this group are grant the security permission to be a delegate.

Ways to Monitor Delegation

Once delegates and delegation is defined and deployed it then becomes critical to monitor delegation and ensure that it is being used properly, by the right people and at the right times.  This may be more important as timesheets become the official time tracking tool and timesheets need to be in on time. Below are a list of reports that can be used to help with monitoring.

  • Report sent to PMO listing all the "Delegates" and "Working on Behalf of" and saved in audit file or database for auditing purposes.
  • Report sent to project managers that list work resource that have delegates working in their behalf and ensuring that the right delegates are assigned.
  • Report sent to resource manager that list work resource who have delegates working in their behalf which provides a simple way to validate that the right people are delegates.
  • Report sent showing who was managing time in their behalf during the coming weeks.
  • Report sent showing who has been acting as delegate

 

June 23
UNKNOWN ERROR when opening Approval Center

Do not adjust your browser. The unknown error message below is what you may see when accessing the Approval Center page.   Uuugggg!

UNKNOWN ERRORS.png 

If you have seen this before, then you know the feeling.  It's a general emptiness that you have because there are no traces for determining what caused it and yet you must solve this. When looking in the ULS logs there is nothing.  This is because the error is coming from the browser and not the project server.  The browser is expecting something and it was NULL. This is my most feared error message.

I originally used the "Developer Dashboard" to troubleshoot this message.  I actually felt I got pretty far with my analysis.  It failed on a store procedure called MSP_WEB_SP_QRY_Status_ReadTimePhasedDataForAssignments. The problem was that one or more AssnUID were invalid.  I learned later that in this case a timesheet was referring to a project that no longer existed and I found out later, indeed someone was cleaning up PWA and decided to delete from project.

The fix to the problem is relatively simple.

  1. Run the SQL query below
  2. If the query returns enough information to determine
    1. Which resource that submitted the bad timesheet
    2. Which project and task is missing
  3. The resource listed in query then needs to do the following
    1. Recall timesheet
    2. Delete timesheet
    3. Create the timesheet
    4. Re-submit the timesheet

-------------------------------------------------------------------

USE ProjectWebApp

SELECT

       TSL.TS_UID Affected_TimeSheetID,

       TS.TS_NAME TimeSheet_Name,

       TSL.TS_LINE_CACHED_PROJ_NAME Deleted_Proj_Name,

       RES.RES_NAME ResourceNamne,

       TP.WPRD_START_DATE TimesheetStart,

       TP.WPRD_FINISH_DATE TimesheetFinish

FROM

       PUB.MSP_TIMESHEET_LINES TSL                                                                            -- Timesheet Lines

       Full OUTER JOIN PUB.MSP_PROJECTS P              ON P.PROJ_UID=TSL.PROJ_UID  -- Projects

       Full OUTER JOIN PUB.MSP_TIMESHEETS TS           ON TS.TS_UID=TSL.TS_UID           -- Timesheets

       Full OUTER JOIN PUB.MSP_RESOURCES RES           ON TS.RES_UID=RES.RES_UID  -- Resources

       Full OUTER JOIN PUB.MSP_WEB_TIME_PERIODS TP ON TP.WPRD_UID=TS.WPRD_UID     -- Timesheet Periods

WHERE

       TSL.TS_Line_validation_Type = 1                 -- 0 is Administrative Task, 1 is Project Task

       AND P.PROJ_NAME is null                                -- Check for Deleted Projects

       AND TS.TS_STATUS_ENUM IN (1,2)                  -- Check for Submitted Timesheets

GROUP BY

       TSL.TS_UID

       , TS.TS_NAME

       , TSL.TS_LINE_CACHED_PROJ_NAME

       , RES.RES_NAME

       , TP.WPRD_START_DATE

       , TP.WPRD_FINISH_DATE

 

March 26
My Top Four Technical Sessions at Project Conference 2014

The Project Conference 2014 was held in Anaheim, CA this past February.  It was a great event with over a hundred sessions.  The sessions were organized by tracks.  The major tracks were Technical, Product and Business.  Oh course, I could not go to them all, however, I have been watching them on Channel 9 at http://channel9.msdn.com/Events/Project/2014

I learned a lot from all the technical sessions, but my favorite technical tracks where the following.

#1 Read World Reports: Business Intelligence in Project Online and Project Server, presented by Andrew Lavinsky and Mike Mclean.  The one thing that top this on top of the charts was using LINQPad for creating ODATA queries.  Amazing tool and makes reporting building using ODATA so much easier.

#2 Troubleshooting Tips for Project and Project Server, presented by Adrian Jenkins and Brian Smith.  Just a lot of good techniques and tools for troubleshooting.  Tools like SQL Profiler, ULS Viewer and Developer Dashboard, but my favorite was enabling logging in the project client.

#3 The Great Database Migration, Project Server 2010 to 2013 Migration in 8 easy Step presented by Richard Van Langen.  Richard took us into a deep dive with migration and the exact steps for using PowerShell.  I love PowerShell, so you may only like this if you are a PowerShell nut.

#4 Top SharePoint 2013 features for IT Executives by Chris Givens.   So many great features in SharePoint 2013 and Chris just touches the tip of the iceberg of many new features that could enhance a PMO.  Some of my favorite are Metadata Navigation to create friendly names with site navigation, embedded code for videos, site mailboxes and eDiscovery to name a few.

 

March 19
Measuring Growth of Project Server 2013 at Client Site

This graph shows typical deployment of project server 2013.  Each bar shows the number of hours submitted each week for projects.  Several keys points to take away with this graph.  First is that monitoring the PMO earlier provides feedback that the system is being used by the pilot group and how much effort.  The slow increase of number of hours each week should be encouraging and indicates that project managers and team members are actually entering work effort into timesheets and project schedules.

Y13-WK38 thru Y13WK42 -- Requirements and configuration phase with pilot group using tool. 

Y13-WK43 thru Y13WK52 -- Rollout to more departments.  A few dips in submitted time due to Thanksgiving and Christmas and New Year Holiday.

Y14-WK01 thru Y14-WK10 -- Project Server in operational mode. New habits being formed like getting team members to submit timesheets each week and having them approved.

 

 

February 27
New PowerShell in Project Server 2013 SP1

After upgrading to Project Server 2013 SP1 I discovered four new PowerShell commands.  Actually it was three, the fourth must have come out with a previous CU. 

Get-SPProjectEnterpriseProjectTaskSync:  Shows status of Task Sync

Enable-SPProjectEnterpriseTask Sync:  This enable's task sync

Disable-SPProjectEnteperTaskSync:   This disable task sync.

So now I am looking to see what PtojectEnterpriseTaskSync does and how to use it effectively

The forth new command is Test-SPProjectServiceApplication.   As i mention earlier, this was release with one of the CU and I just happen to notice it when digging into new PowerShell commands after SP1.

February 26
PowerShell Pre-Upgrade Check for Project Server 2013 Upgrade

Now that the Project Server 2013 SP1 has been announced, we can get ready to perform the upgrade.  The following script can be a sanity check prior to upgrading to make sure your environment is suited for the upgrade.  If you find something out of the ordinary, you may want to research and fix prior to the upgrade. 

The following PowerShell script is used to check for miscellaneous items that may need to be fixed prior to service packs and cumulative updates. Basically, I load the script and setup my variables. Then I run each line examining the results looking for anything that may be out of ordinary. At the end, I have a few PowerShell fixes.  For example,  I discovered I had a poorly provision PWA, so I removed it prior to upgrade.

################################################################################## #
# PreUpgradeCheck.ps1
# Updating CU for Project Server
#
Set-ExecutionPolicy "Unrestricted"
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
##################################################################################
#
# Prior to updating CU ---- look for possible issues
#
##################################################################################
$DBServer        = "LABA-2012SQL"
$ProjectDB       = "LABA_ProjectWebApp"
$WebApp          = "http://LABA-2013SP"
$PWA             = "http://LABA-2013SP/PWA"
$WSS_ContentDB   = "LABA_ProjectServer_WSS_Content"
$Admin_ContentDB = "LABA_Admin_Content"
#
# Not required:  Used for additional provisioned PWA sites
# Setting for second provision OOB (Out-of-box)
# OOB is used for referrencing what the OOB configuration looks like
#
$ProjectDBOOB    = "LABA_ProjectWebAppOOB"
$PWAOOB          = "http://LABA-2013SP/PWAOOB"
#
# Examine all and how many PWA instances
# List project instances
# 1) Look for any instances that should be deleted
#
Get-SPProjectWebInstance
#
# Test Project Web Instance
# 1) Check that all STATUS have PASSED
#
Test-SPProjectWebInstance -Identity $PWA
Test-SPProjectWebInstance -Identity $PWAOOB
#
# Test the Project Database State
# 1) Exists is TRUE
# 2) IsProjectDatabase is TRUE
# 3) all others should be FALSE
#
Get-SPProjectDatabaseState -DatabaseServer $DBServer -Name $ProjectDB
Get-SPProjectDatabaseState -DatabaseServer $DBServer -Name $ProjectDBOOB
#
# Test Project Database
# 1) ERROR           should be FALSE
# 2) UpgradeBlocking should be FALSE
#
Test-SPProjectDatabase -WebInstance $PWA
Test-SPProjectDatabase -WebInstance $PWAOOB
#
# Test Each SharePoint Content Database and all categories should be ok
# 1) ERROR           should be FALSE
# 2) UPgradeBlocking should be FALSE
# 3) Message should be positive
#
Test-SPContentDatabase -WebApplication $WebApp -name $WSS_ContentDB
Test-SPContentDatabase -WebApplication $WebApp -name $Admin_ContentDB
#
##################################################################################
##################################################################################
##################################################################################
##
## Tools to fix issue with upgrades
##
##################################################################################
#
# The following lines is used to remove a bad or unused Project Provisioning
#
$PSI= Get-SPServiceApplication | WHERE {$_.TypeName -like "*Project*" }
#Test-SPProjectServiceApplication -Identity 2ffcc47f-19ac-4b9e-9d14-e03f8f1bfdb1
Test-SPProjectServiceApplication -Identity $PSI
#
# Steps to remove a bad instance of PWA
#
$psi = Get-SPServiceApplication | ?{$_.TYpename -like "*Project*"}
$psi.sitecollection.remove("9e21262b-c63d-4a7c-860c-6f9b909de99b")
##
## Remove-SPProjectDatabase
## Remove-SPProjectDatabase -DatabaseServer LABA-2012SQL.LAB.LOCAL -Name LABA-ProjectServerDB
#
Test-SPProjectWebInstance  

February 17
PowerShell Steps to Configure Project Server 2013

PowerShell is such a cool tool. What I really like about it is that it documents exactly how things are setup in a server. The notes become the exact script.  This is important if you need to rebuilt your server.  I have seen other PowerShell scripts for seting up Project Server in SharePoint.  Mine is a litte different in that I include several steps that may have been left out and I create a managed path called PWA. The managed path PW makes it feels like the standard setup used in Project Server 2010. 

These steps can be configured about SharePoint 2013 and Project Server 2013 have been installed and the SharePoint Configuration Wizard has been run.  Once that is done, these PowerShell commands can be run on the servers that are running project.

############################################################################
# Author: Michael Wharton (MyProjectExpert.com)
# Date: 02/14/2013  (Happy Valentine Day)
# Description: Configure Project Server 2013 and creating PWA as subsite
#
# Once project server is installed the following steps can be done.  In the real world
# some of these steps may have already been done, in which case you would skip the command
# or generate an error indicating.
############################################################################
Set-ExecutionPolicy "Unrestricted"
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
###########################################################################
#  Define key variables that need to be defined.
###########################################################################
$SqlServerName = "LAB-2012SQL"
$SharePointName = "LAB-2013SP"
$WebAppName  = "SharePoint - 80"
$WebAppURL = "http://LAB-2013SP"
$PWA = "http://LAB-2013SP/PWA"
$ProjectService = "Project Service App"
$ProjectWebAppPool = "Project Web App Pool"
$AcctFarmAdmin = "LAB\FarmAdmin"
$AcctServiceSP = "LAB\ServiceSP"
$ProjectWebAppDB = "LAB_ProjectWebApp"
$PWAContentDB  = "LAB_PWA_WSS_CONTENT"
############################################################################

#  Start Project Server Application Service
$PSServiceInstanceGUID = Get-SPServiceInstance | Where {$_.TypeName -eq "Project Server Application Service"} 
Start-SPServiceInstance $PSServiceInstanceGUID 

#  Setup managed account.  This may have already been done
$CredServiceSP = Get-Credential -UserName $AcctServiceSP -Message "Enter Password"
New-SPManagedAccount -Credential $CredServiceSP

#  Create application Pool. You could use an existing one
New-SPServiceApplicationPool  -Name $ProjectWebAppPool -Account $AcctServiceSP

#  Create the project server application
New-SPProjectServiceApplication –Name $ProjectService –ApplicationPool $ProjectWebAppPool –Proxy

#  Create web application
New-SPWebApplication -Name $WebAppName -port 80 -URL $WebAppURL -DatabaseName $PWAContentDB -DatabaseServer $SqlServerName -AuthenticationMethod NTLM -ApplicationPool $ProjectWebAppPool -ApplicationPoolAccount (Get-SPManagedAccount $AcctServiceSP)

#  Create root site and site collection.  I like to use the root as the PMO main page
New-SPSite $WebAppUrl -OwnerAlias $AcctFarmAdmin  -Name "Project Management Office" -Template "STS#0"

#  Test home site for PMO and check if it is working
START $WebAppUrl

#  Create project database. Notice that tag parameter.  It's required to connect to the PWA site 
New-SPProjectDatabase -Name $ProjectWebAppDB -ServiceApplication $ProjectService -DatabaseServer $SqlServerName -Tag "ProjectWebAppDB"

#  Create managed path.  I always have liked the /PWA convention
New-SPManagedPath -WebApplication $WebAppURL -RelativeURL "/PWA" -Explicit

#  Create the project home site.  Its a bit tricky, but the key is to link site with project database using the tag
New-SPSite $PWA -OwnerAlias $AcctFarmAdmin -ContentDatabase $PWAContentdb -Template PWA#0
$web=Get-SPWeb $PWA
$web.Properties["PWA_TAG"]="ProjectWebAppDB"
$web.Properties.Update()
Enable-SPFeature pwasite -URL $PWA

#  Not required, but I typically set the permission mode to project
Set-SPPRojectPermissionMode –Url $PWA -AdministratorAccount $AdminAcct -Mode ProjectServer

#  Start the new PWA site
START $PWA

1 - 10Next

 ‭(Hidden)‬ MyProjectExpert Blog

 About this blog

MVP-Landscape.gif 

MichaelWharton.jpg 

Welcome to My Project Expert site.   This site is dedicated to the implementation and deployment of Microsoft Project Server and the governance required for a successful PMO.

It's mostly my field notes as I find new tips and tricks or perhaps clear up some information that I find confusing.

Michael Wharton, MVP, MBA, PMP, MCT, MCST, MCDBA, MCSD, MCSE+I, MCC2011 and 2012