Migrating PPS from one server to another

Hello,
I'm now working with Project Portfolio Server so, during this days I will write some posts about it.
The first scenario thah I have found is a migration from one Project Portfolio Server to another server. How to migrate that?.
I have follow this steps:

1- Make a database backup using Sql server Management Studio of PPSAccountData and PPSAccountIndex databases.

2- Restore them in the new Sql Server. In my case I have to install the databases in one server, MOSS 2007 and Project Server 2007 in three servers (1 WFE & 2 APP. Servers), and Project Portfolio Server in another server.

3- Install PPS in the new server. I found that if I install PPS with SP1 it gives the message "Cannot find database PPSAccountIndex". So you have to install the version without SP1 and then install SP1.

4- Once It has been installed see if you can log on as superuser. In my installation I couldn't log on, so I had to modify the web.config file:
add key="AccountID" value="-1"
with
add key="AccountID" value="1"

5- Then I tryed to connect It with Sharepoint. When I modifyed user credentials in the preferences section I found the next error:

"Cryptographic Exception"

To solve it you have to delete the cryptographic key using this SQL sentence over the PPSAccountIndex database:

DELETE FROM sfSETTINGS WHERE (Name LIKE '%key%')

Once you do that you can connect to Sharepoint Server.

Search Server in Stopping state

I have found some errors when trying to stop the search server when there is a bad configuration and the crawling never ends. This is how we solved it when the service hangs in a “Stopping” state.
Errors and solutions:
1.Try to stop and restart the service:
Net stop osearch
Net start osearch
2.Try to kill the current processes
Stsadm –o osearch –action stop
Stsadm –o osearch –action start
3.Try to kill the processes from task manager. The processes will be processing by the search service user.
4.Stop the service and kill the processes (1&2) and then go to your sqlServer and open the Activity job. Kill all processes which are using your crawling user which will be the processes which are hanging your server. Then restart the service.
Hope it helps!

Create automatic approval rules for all users

Hi,
I've got a test environment where we must use only "Outlook Add In" to update the projects. As you know, there are 2 big problems when you try to do it.
The first one is that approval rules are created for each supervisor user. So, if we have got 350 users we have to generate a tutorial and every user has to configure his own rules.
The second one is when you update a project from "Outlook Add In" or "My Tasks" section, the project is not published, so the update is not visible from the Project details.

We are going to solve the first issue creating an automatic rule in the Publish Database. Of course, YOU MUST NOT touch production databases unless you will like to lost Microsoft Support. (The next code modifys direcly Project server Publishing database).

To create automaticaly a rule you can make a console program like this:

static void Main(string[] args)
{
connectionString = ""; //Connection string
SqlConnection connCosts = new SqlConnection(connectionString);
SqlCommand cmdCosts = new SqlCommand("Select res.RES_UID from MSP_RESOURCES as res ", connCosts);
SqlDataAdapter daCosts = new SqlDataAdapter(cmdCosts);
DataTable dtCosts = new DataTable();
daCosts.Fill(dtCosts);
foreach (DataRow dr in dtCosts.Rows)
{

SqlConnection connUsr = new SqlConnection(connectionString); SqlCommand cmdUsr = new SqlCommand("Select RULE_UID from MSP_RULES where RES_UID_MGR ='" + new Guid(dr[0].ToString())+"'", connUsr);
SqlDataAdapter daUsr = new SqlDataAdapter(cmdUsr);
DataTable dtUsr = new DataTable();
daUsr.Fill(dtUsr);
if (dtUsr.Rows.Count == 0)
{
//If the rule was not created for the user create it
SqlConnection myConnection = new SqlConnection(); myConnection.ConnectionString = connectionString; myConnection.Open();
string insertstring = "INSERT into MSP_RULES(RULE_UID,RES_UID_MGR,RULE_NAME,RULE_IS_AUTOMATIC,RULE_TYPE,RULE_CONDITION_TYPE,RULE_IS_EXCL_PROJECT,RULE_IS_EXCL_RESOURCE,RULE_IS_EXCL_DELEGATEE,CREATED_DATE,MOD_DATE) values('" + Guid.NewGuid() + "','" + new Guid(dr[0].ToString()) + "','" + "Approve All" + "','" + "True" + "','"+ "5" +"','"+"0',"+"'True','True','True','"+DateTime.Now+"','"+DateTime.Now+"' ) "; SqlCommand comando = new SqlCommand(insertstring, myConnection);
comando.ExecuteNonQuery();
myConnection.Close();
}
}

}


If you activate it after ervery sinchronization It would create rules for all the new users and let other rules as the same.

Conflict updates between Project Web Access, Outlook add-in and PSI.

Hi,
Recently I have been working on a project wich integrates PSI, My Tasks (project Web Access) and Outlook Add-in to modify tasks. We have developed a project Professional emulator with a grid, sending the information to the server using Ajax. As Outlook Add-in and My Tasks don't publish the project and we have automatic rule to approve all the changes, every time the users updates their tasks through Outlook they can't see our changes in Project Web Acces but, what was wrothly was when a user opens the project with our "Web Project Professional" this user can change the tasks making conflic updates between PSI and Outlook add-in.
How did we solve it?
When a user updates his tasks using Project Web Access or outlook Add-in it only modifyes the working store. When we publish the project, the same data in working store is passed to published store, so the only thing that we have to do is compare the two datasets to know if there are changes pending.

Cost Assignments and PSI

Hi again,
After some days trying to work arround to create cost assignments through PSI without luck I found this post: http://projectserverblogs.com/?p=1540
If you look this post you will find the next lines:
Further compounding this concern is item three under User scenarios to avoid, which states that tasks with Cost Resources assigned to them should not have any assignments (meaning assignments to Cost, Material, or Work Resources) updated on that task through PWA or the PSI. This means that if you choose to disregard item one in Best practice use cases, you may only perform task updates from within Project Professional. However, it is possible to configure the system so that it will only accept Task Updates through PWA. This essentially eliminates Cost Resources as a tool for organizations who’ve chosen this configuration.
As soon as I have read this lines I started to find a work arround to create and modify cost assignments through PSI.
We couldn't find any solution yet, but in out project we use Reporting Services to make our reports and only PSI to modify tasks from Projects so we can create a new table assignments in Reporting database wich has the same structuree than MSP_ASSIGNMENTS.
Now our client can modify It's cost assignments of his tasks and get his reports through Reporting Services. Of course, if you open your project with Project Professional or use costs views you will not be able to see anything.
So we still wait for a solution for this problem wich seems that only Microsoft can solve.

Creating a cost assignment

Hi,
I've been working this days on creating cost assignments from PSI. I've found something strange in Project Server behaviour. When you create a new AssignmentRow and you select RES_TYPE = 25 (cost resource) and try to update the project, Project Server automaticaly cancell the job in the queue. I don't know why Project Server does that and the only workarround I found (and I know that is not the better workarround) was create costs assignments as work assignments and indicate in assignment Group that resource is from cost Group. If you do this there is another problem...You can't assign a cost to the resource (ASSN_COST) becouse is not a cost resource, so you have to work with ASSN_UNITS field. The problem here is that if you modify resource's units and TASK_TYPE is in mode Fixed Units (as default) will modify task duration too, so you need to set the TASK_TYPE as Fixed Duration before you assign the resource.

Project actuals error when modifying TASK_PCT_WORK_COMP through PSI

I've been working a few hours trying to modify the percent work complete on a task. I've been working with TASK_PCT_WORK, TASK_WORK (read only), TASK_ACT_WORK and TASK_REM_WORK.
As you will know the percent complete of a task can be modifyed directly (using PSI) from TASK_PCT_WORK_COMP column but today, in a new installation of Project Server, I receive an error from the queue saying ProjectActualsAreBlocked. After a few hours trying to find what does that error means I have found modifying the project from Project Professional that I couldn't modify the percent of task completed saying the same error. Project Professional gave me the clue saying that task work could only be modifyed through Project Web Access. So if you acces server setting in PWA will find that under task settings and display there is an option (which is maked as default) called "Restrict updates to Project Web Access". When I uncheked this option I could modify TASK_PCT_WORK_COMP from PSI.