Configuration wizard failed: ERR Login Failed for ProjectServer_Published. Failed Upgrade

Issue:

Psconfig failed at step 3 of 4 with the following error in upgrade log:

03/10/2012 2:51:06 8 ERR Login Failed for ProjectServer_Published. Failed Upgrade.
03/10/2012 2:51:08 8 ERR Login Failed for ProjectServer_Draft. Failed Upgrade.
03/10/2012 2:51:09 8 ERR Login Failed for ProjectServer_Archive. Failed Upgrade.
03/10/2012 2:51:11 8 ERR Login Failed for ProjectServer2010_Published . Failed Upgrade.
03/10/2012 2:51:14 8 ERR Login Failed for ProjectServer_Reporting . Failed Upgrade.
03/10/2012 2:51:18 8 ERR Login Failed for ProjectServer2010_Draft. Failed Upgrade.

Cause:

Checked the Database server and didn’t find the above listed DB's. After analysing the logs found that there are 2 project service application were created and the one "Project web service application" was not provisioned successfully. When I looked at the database status it was showing as not responding.

Fix:

Checked and ensure that the application is not being used and needs to be deleted. Executed the following PowerShell cmdlets and removed the Orphaned PWA and was able to complete the wizard successfully.

$psi = get-spserviceapplication | ? {$_.Typename -like "*Project*}
$psi
Outcome : This will return the various Project service applications
$sa = get-spserviceapplication | ? {$_.Id -eq "GUID of the Service App from above"}
$sa
Outcome : Assign the individual service app object to $sa and verify it has been set.
$sc = $sa.SiteCollection
$sc
Outcome : Will assign the SiteCollection details to $sc and display the contents.
Note Id value.
$sa.SiteCollection.Remove("Site Collection ID from above");
$sc

Publish a project based on project name Project Server 2010 Powershell

 The following script is a sample of what an administrator may want to publish a specific project using  PowerShell. The script reads the list of existing projects in Project Server and publishes project based on the project name. Set the $url variable to point to the desired instance of Project Server, and then run the script.

$path = "c:\program files (x86)\Microsoft\Powershell PSI Cmdlets for Project Server 2010\ProjectPSICmdlets.dll"
$assem = [System.Reflection.Assembly]::LoadFile($path)
import-module -assembly $assem


$url = "http://pwaurl/pwa";

$dataset = New-Object System.Data.DataSet
$dataset = psi-ReadProjectList $url;
foreach ($p $projName in $projectList)
{

             if ($p.PROJ_NAME -eq "MyTestProject")
       {
             $projectUid = $p.PROJ_UID;
              "Publishing project guid = " + $projectUid
              $sessionUid = [System.Guid]::NewGuid();
              psi-CheckOutProject $url $projectUid $sessionUid "PS Check out"
              $jobUid = [System.Guid]::NewGuid()
              psi-QueuePublish $url $jobUid $projectUid $true
              $jobUid = [System.Guid]::NewGuid()
              psi-QueueCheckInProject $url $jobUid $projectUid $true $sessionUid "PS Check In"
       }

}

Get the Custom List Columns details Project Server 2010 PowerShell

This scripts returns the custom list columns information like ID, Name & Internal Name of the column in SharePoint 2010/ Project Server 2010 project sites.

[system.reflection.assembly]::loadwithpartialname("microsoft.sharepoint")
$site= New-Object Microsoft.SharePoint.SPSite ("http://pwaurl/pwa/27test")
$web=$site.OpenWeb()
$list=$web.Lists["Actions"]
$list.Fields |select ID, title, internalname| more

Project Server 2010 - SQL- Get Project Tasks & Resources

This SQL query returns the Project Tasks data by all resources assigned to Task for Projects from Project server reporting database. This query might be useful for PMO/ Project Managers to have a quick look on the project/Tasks & Resources assigned to Tasks. This can be used in SSRS reports/Excel reports or in .NET webparts. Project Name can be passed dynamically to fetch the milestone count

Tasks and Resources for projects.sql :

SELECT     
            dbo.MSP_EpmAssignment_UserView.ProjectUID,
            dbo.MSP_EpmAssignment_UserView.TaskUID,
            dbo.MSP_EpmProject_UserView.ProjectName,
            dbo.MSP_EpmTask_UserView.TaskName,
            dbo.MSP_EpmAssignment_UserView.ResourceUID,
            dbo.MSP_EpmResource_UserView.ResourceName,
            dbo.MSP_EpmResource_UserView.ResourceInitials
           
INTO #TempTable

FROM         dbo.MSP_EpmAssignment_UserView INNER JOIN
dbo.MSP_EpmProject_UserView ON dbo.MSP_EpmAssignment_UserView.ProjectUID = dbo.MSP_EpmProject_UserView.ProjectUID INNER JOIN
dbo.MSP_EpmTask_UserView ON dbo.MSP_EpmAssignment_UserView.TaskUID = dbo.MSP_EpmTask_UserView.TaskUID INNER JOIN
dbo.MSP_EpmResource_UserView ON dbo.MSP_EpmAssignment_UserView.ResourceUID = dbo.MSP_EpmResource_UserView.ResourceUID

SELECT
  ProjectUID,
  TaskUID,
  ProjectName,
  TaskName,
  STUFF((
    SELECT ', ' + ResourceInitials 
    FROM #TempTable
    WHERE (TaskUID = Results.TaskUID)
    FOR XML PATH (''))
  ,1,2,'') AS ResourceInitialsCombined,
   STUFF((
    SELECT ', ' + ResourceName 
    FROM #TempTable
    WHERE (TaskUID = Results.TaskUID)
    FOR XML PATH (''))
  ,1,2,'') AS ResourceNameCombined
FROM #TempTable Results
GROUP BY TaskUID,ProjectUID,ProjectName,TaskName

DROP TABLE #TempTable