SharePoint (and Project Server) Shenanigans

1. Microsoft’s Vision and Roadmap for Work, Project, and Portfolio Management 

2. Microsoft Project Pro for Office 365: An Overview

3. IT Project and Portfolio Management in the Cloud

4. Developer to Developer: Meeting Custom Needs with Microsoft Project

5. Real World Reports: Business Intelligence in Microsoft Project Online and Project Server 2013

6. How to Deploy Microsoft Project: Online and Server

7. Supporting Microsoft Project Online and Project Server: Tips and Tricks

8. Microsoft Project Online Customization: Best Practices

Those who were wondering, if any, about my whereabouts. Its my pleasure to announce that i am alive and rocking 🙂

Last year had been a massive year for me, both at personal and professional fronts.
Became a father for 2nd time, and relocated to a different place. It all became a little too overwhelming during past few months, more than i have anticipated, but its all settling down well now.

So its about time to get back to business. Time to pen down more stuff. Time to reconnect.

Lets rock & roll.

By the way if anyone has already applied April 2014 CU, or planning to apply, then note that the fix for this issue is available as part of this April 2014 CU. But the fix will not solve any existing affected projects, and you need to follow Brian’s advise mentioned in his blog (URL available below) to fix it.

Also more details and immediate fix, in case you are not planning to apply CU, is available here at Brian Smith’s blog: Blog and Fix

Following are my findings related to the same issue:

Error on Project Page in PWA:

Queue Error:

You will find project stuck in queue with percentage completed less than 100%, and error details would be:

• Microsoft.Office.Project.Server.BusinessLayer.Queue.Message:
  • ProjectPublishFailure (23000). Details: id=’23000′ name=’ProjectPublishFailure’ uid=’187759d1-663f-e411-8b70-0050569b2beb’ projectuid=’1201772f-00b0-e311-baa9-782bcba8cb82′ messagetype=’Microsoft.Office.Project.Server.BusinessLayer.QueueMsg.PublishProjectMessage’ messageID=’16’ stage=” blocking=’Block’

Using JOB ID mentioned, searching ULS Log will help you find below details:

SqlError: ‘Violation of PRIMARY KEY constraint ‘PK_MSP_PROJECT_RESOURCES’. Cannot insert duplicate key in object ‘pub.MSP_PROJECT_RESOURCES’. The duplicate key value is (1201772f-00b0-e311-baa9-782bcba8cb82, f34816d9-2fb3-e311-9ecf-0050569b2beb).’    Source: ‘.Net SqlClient Data Provider’ Number: 2627 State: 1 Class: 14 Procedure: ‘MSP_PUBLISH_PROJECT_RESOURCES_INSERT’ LineNumber: 8 Server: ‘HQDXB-CPMSQLINS\CEPMSQLDB’ 4ec5b99c-37f6-c0a5-975a-cdbb44e876c8
09/18/2014 23:05:58.52 Microsoft.Office.Project.Server (0x1470) 0x1CFC Project Server                 Database                       880j High     SqlError: ‘The statement has been terminated.’    Source: ‘.Net SqlClient Data Provider’ Number: 3621 State: 0 Class: 0 Procedure: ‘MSP_PUBLISH_PROJECT_RESOURCES_INSERT’ LineNumber: 8 Server: ‘HQDXB-CPMSQLINS\CEPMSQLDB’ 4ec5b99c-37f6-c0a5-975a-cdbb44e876c8
09/18/2014 23:05:58.52 Microsoft.Office.Project.Server (0x1470) 0x1CFC Project Server                 Database                       tzku High     ConnectionString: ‘Data Source=HQDXB-CPMSQLINS\CEPMSQLDB;Initial Catalog=ProjectWebApp;Integrated Security=True;Enlist=False;Pooling=True;Min Pool Size=0;Max Pool Size=100;Connect Timeout=15;Application Name=SharePoint[Microsoft.Office.Project.Server.Queuing][2][ProjectWebApp]’    Partition: NULL ConnectionState: Open ConnectionTimeout: 15 4ec5b99c-37f6-c0a5-975a-cdbb44e876c8

Out of lot of improvements we are enjoying with Project Server 2013, one of them is having a scheduling capabilities built within  PWA, and now PWA schedule webpart do all the formula processing as well without open/publish the same project from Ms Project. Unlike its predecessor, where any formula field requires MS Project push to get populated, and hence we had dependency to Ms Project even in the presence of web client and it was nightmare sometimes to explain to end user that why they need to buy more Ms Projects licenses to cope up with web based client limitations.

But recently i found that ‘Task level number type roll up formula field, if copied to Project level number type formula field – doesnt work’ 😦

Now let me try to illustrate above through an example to make it more clearer:
And before i start, note that my server is patched with SP1 (re-released) + Jul 2014 CU.

1. Create a task level number type field, lets name it: %Weight
2. Create a task level number type formula field, with roll up Sum calculation for summary rows:

           Lets name it:  %WeightedProgress = ([% Complete] * [%Weight]) / 100

Here %complete is a default field, and %Progress will be manually updated by users.

3. Create a project level number type formula field:

          Lets Name it %ProjectProgress =  %WeightedProgress

As you can see, i am only copying data of task level formula field to project level field in order to show the data in project center.

Using PWA web scheduler:

1. I have entered %Weight for following 2 tasks, %Weighted Progress is 0 for each task because %Complete is 0, and hence at project level roll up value is also 0.

2. Update %Complete of first task with 50%, and %Weighted Progress will be calculated using formula and became 10 at task level and Project roll up level. Publish project.

 3. Open Project Center and observe % Weighted Progress, its showing 0 which was the task roll up at project level before we have made any changes.

4. Open project, and update %Complete of 2nd task with 50% and that will change %Weighted Progress value to 25. Publish project.

5. Open Project Center, and observe %Weighted Progress, its showing 10 which was the task roll up at project level before we have made change to 2nd task.

By now you may have learnt that its following a unique pattern while updating project level custom field. That its showing you previous value at project center level once you publish your project, although at Project level it updates at the same time.

So the first time when at Project level it was 0, in Project Center it was appearing blank.
Second time when at Project level it was 10, in Project Center it was appearing 0.
Third time when at Project level it was 25, in Project Center it was appearing 10.

Lets update our project plan one more time to assure our conclusion.

6. Open project, and update %Complete of Task A with 100% and that will change %Weighted Progress value to 35. Publish project.

7. Open Project Center, and observe %Weighted Progress, its showing 25 which was the task roll up at project level before we have made change to Task A.

So Fourth time when at Project level it was 35, in Project Center it was appearing 25.

I hope an issue is clear, now a workaround to this is to open/publish using Ms Project and everything will work like a charm, just like old times (Project Server 2010).

Though its not workable in my current scenario because not all the end user have Ms Project on their PCs.

With the hope that we get fix to this issue soon :), see ya next time.

So let me kill all this unnecessary noise with the final announcement that Service Pack 1 is now available to download, and all the clarifications in a nut shell is that Just apply this SP1 to your environment even if you have applied the previously released SP, and also feel free to apply this SP1 if you have April 2014 CU in place.  don’t forget to  run the configuration wizard in the end 🙂

Also do not forget that unlike old times, this SP1 is not a roll up package, which mean you should install SharePoint 2013 SP1 + Project Server 2013 SP1 and then run the configuration wizard in the end.

Download links for updated SP1:

• SharePoint Foundation 2013 Service Pack 1
• SharePoint Foundation 2013 Language Pack Service Pack 1
• SharePoint Server 2013 Service Pack 1
• SharePoint Server 2013 Language Pack Service Pack 1
• Project Server 2013 Service Pack 1
• Office Web Apps Server 2013 Service Pack 1

For details please see Brian Smith post, you can find details of almost all the queries in your mind on his blog post.

Also see Stefan Bogner’s post for details related to Service Pack 1 release announcement and clarifications.

As always try any patches in your test environment prior to apply to production.

Happy patching 😀

Also Project Server 2013 CU is not a roll-up package, which means it doesn’t includes SharePoint server fixes.

Test the CU in your test environment first prior to apply to production.

Project Server 2013 individual Apr 2014 CU: http://bit.ly/1g8zmuj
Project 2013 Apr 2014 CU: http://bit.ly/1g8zmuk
SharePoint Server 2013 Apr 2014 CU: http://bit.ly/1hij6eG

Project Server 2010 individual Apr 2014 CU:http://bit.ly/1g8zmuo
Project 2010 Apr 2014 CU: http://bit.ly/1g8zkTe

“We have recently uncovered an issue with this Service Pack 1 package that may prevent customers who have Service Pack 1 from deploying future public or cumulative updates. As a precautionary measure, we have deactivated the download page until a new package is published.”

Download pages for Project Server and SharePoint are also updated with above reason.

For more details, check Brian Smith post: http://bit.ly/1fE43L8

For Project Server 2013, you can download from below:

• http://bit.ly/OPdBtd

For Project 2013 client, 32bit or 64bit, you can download from below:

• 32 bit – http://bit.ly/1dOZBoq
• 64 bit – http://bit.ly/OPdBJq

For SharePoint 2013 server, you can download from below:

• http://bit.ly/1dOZBor

Few important things to note here, that unlike previous versions, this time SP1 is not a roll-up version. Which means that Project Server SP1 doesn’t include SharePoint side of update, and vice verse.

http://bit.ly/OPdzBr

Also another important point, is that as part of lost resource fix if you have updated the required sproc, then you may need to update them again as SP1 will re-write those sprocs during installation/setup.

http://bit.ly/1dOZDgd

Also read the Project Client versioning details in Brians’s post (same link as above).

And in the end as always, try all the updates in test environment prior to apply to production.

Happy patching.

The issue occurred while running SharePoint 2010 setup, after installing pre-requisites, and following first few initial screens of setup wizard. Once reached to the point where you can enter SharePoint database server name, and credentials which can be used to talk to database server. You may see this error:

Cannot connect to database master at SQL Server at <server_name>. The database might not exist, or the current user does not have permission to connect to it.

Now if you have followed the recommended technet article to setup service accounts and permission, this error might be little misleading at the start. But root cause of the issue is completely valid.

There is already a very detailed and helpful blog is available for you to follow to start troubleshooting:
http://blogs.technet.com/b/tothesharepoint/archive/2011/01/12/troubleshooting-sharepoint-configuration-error-cannot-connect-to-database-master-at-server-name.aspx

The possible causes of that error are as follows, first 2 are available in this blog, and the 3rd is the one i found and is the reason of this post:

1. Permissions of the account used to configure has correct permission within SQL based on technet article.
2. SQL Server in windows firewall required port, by default its 1433, is allowed to receive traffic from SharePoint application server.
3. Verify that user through which you are running setup has got permissions to login to SQL Server. Yes, its very common in production or customer environments that you use different credentials to login to server than to the one you use to for deployment. Now either allow that user login permission to SQL Server by going to SQL Server and adding that user under logins within security of SQL Server. Or, run the setup as an administrator. In fact a good practice, is to always run the setup as an administrator to avoid such glitches.

Have a good day at work.