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.

Error in outlook add-in when user assignment is Proposed

If you have an assignamet which has got its booking type as Proposed you will find that outlook add in gives you a message like this "You have not any assignments to upload" when you try to import new assignments. This hapens becouse resource hours are not reserved, as the user is just proposed for the project.
To solve this change user's booking type from Proposed to Commited. This can be done editing the resource from the resources pages.

Error synchronizing Project Server and Active Directory resources

I have find some resources which cannot be sync with Active Directory and makes that Published database gets in an inconsistent state. For these resources you will get an "Unknown Error" when you try to modify them and if you try to remove them you will find that resources are cheked-out. As you can see in "force check-in enterprise objects" these resources are not listened as cheked-out.
Another sympthom of this is that users which are in an inconsistent state have it's status as innactive, so they can't get in Project Web Access.
How to monitor the error?
If you take a look to the Published database you will find in the MSP_RESOURCES table that users which are in an inconsistent state have his RES_CHEKEDUOT_BY filed to a empty GUID.
Of course it is not correct that value since this is what is causing the error.
How do we solve it?
1. Open an issue with Microsoft or install its hotfixes.
2. Run a sql squery wich will get the user in a consistent state:
update MSP_RESOURCES
set RES_CHECKOUTBY=Null
Where RES_NAME='Name_Of_The_User'
Of course the second option is not supported by Microsoft so you will loose Microsoft's support if you modify the databases and you don't reurn them to its original state.
After doing that you will be able to modify user's state from the Project Web Access site.

Timer job definitions for SSP

I have been working in an enviroment wich was working well unill last week. We found that the queue was not working at all. All the projects were getting enqueued but all of them were in state Waiting... After trying restarting all the services and see that was not a good solution we try to find the error.
Wen we create a new Shared Services Provider this will create two timer job definitions wich are needed for Projecct Server:
Shared Services timer job.
Project Server Synchronizing job for "SharedServicesprovidername".
If there is one of these Timer Jobs Definitions wich doesn't exists then project server sites will not work properly.
What's the solution? there are more than solution for this problem but the most easy is to create another Shared Services Provider, change the associations for the web applications to the new Shared Services Provider and delete the old one.
If you have properties defined in old Shared Services Provider (profiles, excel services, BDC, search....) you will need to configure it again in the new SSP.

Bug in eventHandler on itemAdded --> ListItem is null

I will continue posting things about sharepoint.
When you implement an ItemAdded event handler you will notify that SPItemEventProperties.ListItem is null. This is becouse a bug in sharepoint.
To work around this you should use the method spList.getItemByUniqueId() passing the memeber SPItemEventProperties.Listitemid wich would be correct.

Queue blocked in project server caused by tasks getting enqueued

El otro día trabajando en una solución me encontré con una cola con más de 700 tareas en espera de proceso. Una vez depuradas las tareas y buscando cúales eran las que producían el bloqueo en la cola me encontré con diversas tareas con el estado "Getting enqueued".
Este estado se produce cuando hay algún cliente externo que está compartiendo datos con project server, por ejemplo una publicación de un proyecto desde Project Professional. Si finaliza la conexión con el servidor durante el proceso de sincronización y transmisión de los datos a Project Server la tarea se queda en éste estado, produciendo el bloqueo de la cola para dicho proyecto.
Soluciones:

a) Encontrar el cliente que estaba enviando los datos y que acabe de enviarlos (por ejemplo que el ordenador se haya suspendido mientras realizaba la actualización).

b) Eliminar las tareas que están en este estado marcando antes la opción "Cancelar trabajos en la cola de espera" dentro de "Opciones avanzadas". Éste proceso puede provocar la pérdida de los datos que no se hayan transmitido a Project Server.

Issue in Outlook integration when assignning tasks to a resource using PSI

We we assign a task to a resource using PSI Outlook add-in for Project Server is unable to import new assignments.
The root cause of the problem was due to the fact that assignments created entirely through the PSI do not show the link in the progress column that allows clicking through to the timephased entry pop-up.
Microsoft has released a Project Server Rollup KB950816 to solve this problem.
The hotfix will be public soon.

Types of assignments

This is the table of assignments values taken from msdn.
Most used are work resource (value =2) cost resource (value = 25) and material resource (value = 21 ).


BudgetCostResource
Value=51. Budget cost resource. Can be assigned to Project summary tasks only; cannot log on to Project Web Access.
BudgetMaterialResource
Value=52. Budget material resource. Can be assigned to Project summary tasks only; cannot log on to Project Web Access.
BudgetWorkResource
Value=50. Budget work resource. Can be assigned to Project summary tasks only; cannot log on to Project Web Access.
CAN_LOG_IN_MAXIMUM
Value=20. Any resource with a resource-type value less than this value can log on to Project Web Access.
CostResources
Value=25. Cost resource. Can be assigned to Project tasks; cannot log on to Project Web Access.
GenericBudgetCostResource
Value=54. Generic budget cost resource. Can be assigned to Project summary tasks only; cannot log on to Project Web Access.
GenericBudgetMaterialResource
Value=55. Generic budget material resource. Can be assigned to Project summary tasks only; cannot log on to Project Web Access.
GenericBudgetWorkResource
Value=53. Generic budget work resource. Can be assigned to Project summary tasks only; cannot log on to Project Web Access.
GenericCostResources
Value=26. Generic cost resource. Can be assigned to Project tasks; cannot log on to Project Web Access.
GenericMaterialResource
Value=22. Generic material resource. Can be assigned to Project tasks; cannot log on to Project Web Access.
GenericWorkResource
Value=20. Generic work resource. Can be assigned to Project tasks; cannot log on to Project Web Access.
INACTIVATED_OFFSET
Value=100. An inactive resource has the value of this enumeration added to its resource-type value.
IS_NONBUDGET_TYPE_MAXIMUM
Value=50. Any resource value less than this value is not a budget resource.
MaterialResource
Value=21. Material resource. Can be assigned to Project tasks; cannot log on to Project Web Access.
PureUser
Value=1. Pure user, such as Admins. Cannot be assigned to Project tasks; can use Project Web Access.
WinProjScratchpadResource
Value=-1. Internal use only; do not use.
WinProjSummaryResource
Value=-4. Internal use only; do not use.
WinProjUnassignedResource
Value=-3. Internal use only; do not use.
WinProjUnknownResource
Value=-2. Internal use only; do not use.
WorkResource
Value=2. Work resource. This is the default value. Can be assigned to Project tasks; can log on to Project Web Access.

Create a task assignment

Hi again,
Today I'm going to explain how to create a resource asignment and a cost assignment to a task using PSI.

Variables
ProjWS --> project web service initialized
proyectoid --> GUID of the project


1. First of all we need to get the ProjectDataSet of our project.

ProjectDataSet dsNew = projWS.ReadProject(proyectoid, Project.DataStoreEnum.WorkingStore);

2. Create a new Assignment row in the project

Project.ProjectDataSet.AssignmentRow ResRow = dsNew.Assignment.NewAssignmentRow();

3. Make the assignment between the user and the task

ResRow.PROJ_UID = projectUid; //GUID of the project
ResRow.ASSN_UID = Guid.NewGuid();
ResRow.RES_UID = resUid; // GUID of the resource
ResRow.RES_TYPE = AssignmentType; //Type of assignment
ResRow.TASK_UID = taskUid; //GUID of the task

4. Add the assignment cost if is a cost resource
if ( AssignmentType= 25) {
ResRow.ASSN_COST = ResourceCost;
}

5. Add the new Assignment row to the ProjectDataSet and add it to the project

dsNew.Assignment.AddAssignmentRow(ResRow);
Guid jobGuid = Guid.NewGuid();
projWS.QueueAddToProject(jobGuid, sessionGuid, dsNew, false);

Change Project Start Date once it's created through PSI

I've seen several posts on internet of people having problems changing the project start date once the project has been created.
Here is an example of how to do it:

1. Read the project

ProjectDataSet templateProject = projWS.ReadProject(projectUID, Project.DataStoreEnum.WorkingStore);

2. create a copy to store the changes

ProjectDataSet temporal = ((Project.ProjectDataSet)(templateProject.Copy()));

3. Store the new date (fechaInicio.SelectedDate is a Daytime parameter)

temporal.Tables[temporal.Project.TableName].Rows[0][temporal.Project.PROJ_INFO_START_DATEColumn] = fechaInicio.SelectedDate;

4. Get the changes and store them in another ProjectDatatSet

ProjectDataSet updateProjectDataSet = new ProjectDataSet(); updateProjectDataSet.Project.Merge(temporal.Tables[temporal.Project.TableName], true);
updateProjectDataSet = ((Project.ProjectDataSet)updateProjectDataSet.GetChanges(DataRowState.Modified));

5. Check-in and publish the project

projWS.QueueUpdateProject(jobGuid, sessionGuid, updateProjectDataSet, false);
jobGuid = Guid.NewGuid();
projWS.QueueCheckInProject(jobGuid, newProject, true, sessionGuid,"");
WaitForQueueJobCompletion(jobGuid, 1);
jobGuid = Guid.NewGuid();
projWS.QueuePublish(jobGuid, newProject, true, "");
WaitForQueueJobCompletion(jobGuid, 1);

Modifying project tasks through PSI

Aunque parezca una tarea simple el hecho de modificar tareas de Project Server utilizando PSI deberemos tener en cuenta diferentes consideraciones que pueden provocar que los trabajos de actualización de las tareas provoquen un error en la cola de Project Server. Algunas de las consideraciones que deberemos tener son las siguientes:

1. El motor de planificación de Project Server funciona de la misma forma que el de Project Professional, de manera que si realizamos una modificación de fechas en una tarea que tiene predecesoras también deberemos modificar las fechas de las predecesoras. Dicho recálculo de fechas lo deberemos hacer nosotros teniendo en cuenta, por ejemplo, que los fines de semana no son días lectivos de trabajo por defecto en Project.

2. Las asignaciones de recursos no se podrán realizar sobre tareas resumen, sino que se realizarán sobre las tareas hijas. En el caso en el que realicemos una asignación sobre una tarea resumen ésta provocará un error general en la cola.

(más restricciones en próximos días)

Installing Project Server Service Pack 1

Durante la instalación del Service Pack 1 de Sharepoint o el de Projcet Server nos podemos encontrar con que el instalador se queda colgado en el paso 8 de 9. Ésto sucede cuando no se puede arrancar de nuevo el servicio de búsqueda por utilizar un usuario diferente al que se utilizó para la instalación de Project Server.
Para forzar que finalice de forma correcta la instalación se puede ejecutar el siguiente comando:
psconfig -cmd upgrade -inplace b2b -wait -force
Una vez realizado ésto se deberá arrancar el servicio de búsqueda a mano indicando el usuario adecuado.

Queue functions

Looking in projectTool you can find the functions to control the end state of the jobs that you are sending to the queue.
Let's take a look to these functions.


1. Function wich checks if there is an error in the job

private static bool checkStatusRowHasError(string errorInfo)
{
System.Xml.XmlTextReader xReader = new System.Xml.XmlTextReader(new System.IO.StringReader(errorInfo));
while (xReader.Read()) {
if (xReader.Name == "errinfo" && xReader.NodeType == System.Xml.XmlNodeType.Element) {
xReader.Read(); if (xReader.Value == "")
return false;
else return true; }
}
return false; }

2. Function wich waits until the job has finished or timeout has reached.

private bool WaitForQueueJobCompletion(Guid linkId, int messageType, TaskGrid.QueueSystemDerived queueSystem) {

QueueSystem.QueueStatusDataSet queueStatusDataSet = new QueueSystem.QueueStatusDataSet();
QueueSystem.QueueStatusRequestDataSet queueStatusRequestDataSet = new QueueSystem.QueueStatusRequestDataSet();
QueueSystem.QueueStatusRequestDataSet.StatusRequestRow statusRequestRow = queueStatusRequestDataSet.StatusRequest.NewStatusRequestRow();
statusRequestRow.JobGUID = linkId;

3. Coments BUGBUG are from Microsoft Engineers :) Sometimes they leave us some feedback of the development process...

statusRequestRow.JobGroupGUID = Guid.NewGuid(); statusRequestRow.MessageType = -1; //BUGBUG - Rightnow lets just use -1 queueStatusRequestDataSet.StatusRequest.AddStatusRequestRow(statusRequestRow);
bool inProcess = true;
DateTime startTime = DateTime.Now; i
nt successState = (int)QueueConstants.JobState.Success; i
nt failedState = (int)QueueConstants.JobState.Failed;
int blockedState = (int)QueueConstants.JobState.CorrelationBlocked;
while (inProcess) { queueStatusDataSet = queueSystem.ReadJobStatus(queueStatusRequestDataSet, false, QueueSystem.SortColumn.Undefined, QueueSystem.SortOrder.Undefined);
foreach (QueueSystem.QueueStatusDataSet.StatusRow statusRow in queueStatusDataSet.Status)
{
if ((statusRow["ErrorInfo"] != System.DBNull.Value && checkStatusRowHasError(statusRow["ErrorInfo"].ToString()) == true) statusRow.JobCompletionState == blockedState statusRow.JobCompletionState == failedState)
{ ///Error en la cola
inProcess = false;
return false; }
if (statusRow.JobCompletionState == successState)
{ inProcess = false; return true; }
else { inProcess = true; System.Threading.Thread.Sleep(500); } }
DateTime endTime = DateTime.Now;
TimeSpan span = endTime.Subtract(startTime);
if (span.Seconds > 30) //Wait for only 20 secs - and then bail out.
{ ///Error en la cola
return false; } }
return true; }

Create a new Project from a template

Today, in our first post, we are going to learn how to crate a new project from a template using PSI.
PSI are web services wich allow us to connect with Project Server.
Let's see how to use them.
First open a new project and import PSI web references. The first one you need is project.asmx
You can find it in your server under the Project Web Access site.
http://servername/pwa/_vti_bin/PSI/project.asmx
Once you have imported it you can create new projects from a template.

Variables
server --> name of the server.
PROJECTWEBSERVICE --> Project web service adress

1. Create and configure connection
Project.Project projWS;
if (projWS == null)
{
projWS = new Project.Project();
projWS.Url = server + PROJECTWEBSERVICE;
projWS.CookieContainer = new CookieContainer();
projWS.Credentials = CredentialCache.DefaultCredentials;
}

2. Implement functions to get UIDs from templates
private Guid GetProjectTemplateGUID(string templateName) {
Guid tempateGuid = Guid.NewGuid();
Project.ProjectDataSet myProjectList = projWS.ReadProjectList();
foreach (DataRow row in myProjectList.Project) {
if ((string)row["PROJ_NAME"] == templateName) {
tempateGuid = (Guid)row["PROJ_UID"];
return tempateGuid;
}
}
return tempateGuid;
}

3. Create the project from the template

Guid plantillaid = GetProjectTemplateGUID(plantilla.Text);
Guid newProject = Guid.Empty;
newProject = projWS.CreateProjectFromTemplate(plantillaid, nombre.Text);

4. Once you have your new project Created let's change some of its information

4.1 Declare variables to read the project

Project.ProjectDataSet templateProject;
Guid jobGuid = Guid.NewGuid();
Guid sessionGuid = Guid.NewGuid();

4.2 Check-out the project to read it's properties

projWS.CheckOutProject(newProject, sessionGuid, "");
templateProject = projWS.ReadProject(newProject, Project.DataStoreEnum.WorkingStore);

4.3 Create a temp DataSet to store the changes.
//Hemos creado el proyecto, cambiamos los parámetros según los introducidos ProjectDataSet temporal = ((Project.ProjectDataSet)(templateProject.Copy()));

4.4 Change the start date of the project

temporal.Tables[temporal.Project.TableName].Rows[0][temporal.Project.PROJ_INFO_START_DATEColumn] = fechaInicio.SelectedDate;

4.5 Change the owner of the project

Guid propietario = GetResourceGuid(realUser);
if (propietario != Guid.Empty)
temporal.Tables[temporal.Project.TableName].Rows[0][temporal.Project.ProjectOwnerIDColumn] = propietario.ToString();
ProjectDataSet updateProjectDataSet = new ProjectDataSet(); updateProjectDataSet.Project.Merge(temporal.Tables[temporal.Project.TableName], true);
updateProjectDataSet = ((Project.ProjectDataSet)updateProjectDataSet.GetChanges(DataRowState.Modified));

4.6 If there are any changes --> store them

if (updateProjectDataSet != null)
{
projWS.QueueUpdateProject(jobGuid, sessionGuid, updateProjectDataSet, false);
WaitForQueueJobCompletion(jobGuid, 1);
}

4.7 Check-in and publish the project

jobGuid = Guid.NewGuid();
projWS.QueueCheckInProject(jobGuid, newProject, true, sessionGuid,"");
WaitForQueueJobCompletion(jobGuid, 1);
jobGuid = Guid.NewGuid();
projWS.QueuePublish(jobGuid, newProject, true, "");
WaitForQueueJobCompletion(jobGuid, 1);

Welcome to my project Server blog

This blog is created to explain all my experiences with project server and its PSI.
I will create a new post every day explaining all my experiences with Project Server and Project Professional.
Enjoy it !!!