Friday, August 16, 2013

You may believe that data of your projects stored in MS Project Server are safe and stored for good. But, sometimes, it may happen that you click on your project name and you get this message:

"Unable to open the project. The workflow associated with the project has been terminated unexpectedly. Please contact your system administrator."

And you might see this happening on many projects. Why is that and how to stop it?

Well, this is default behavior of Project Server. This happens on projects that have been closed for over two months.

Let's say that you close your project (it is approved or rejected and this action closes the workflow). After 60 days, Auto Cleanup job is executed which erases all workflow instances that are closed and over 60 days old. It does that to clean (unnecessary) data from database for better server performance.

This is very bad solution, because users usually want to keep their project data for a longer time then just 60 days.

The good news is that your project data isn't lost, they are just not accessible at this time. The bad news is that your workflow history data is lost for that project and this is the only thing that got erased.

First, you need to prevent this situation happening on other projects.

So, if you want to keep your project data for more than 60 days, you need to disable this automatic Auto Cleanup job in central Administration by following these steps:

1. Go to the Central Administration.
2. On the Quick Launch, click Monitoring.
3. On the Monitoring page, in the Timer Jobs section, click Review job definitions.
4. On the Job Definitions page, in the Title column, click the Workflow Auto Cleanup link that is associated with the Web application for which you want to disable automatic workflow cleanup.
5. On the Edit Timer Job page, click Disable to disable the Workflow Auto Cleanup feature.

With these steps, you've just prevent from this situation repeating on other projects, but, projects which are already affected by this Auto Cleanup job are still blocked, you cannot open them.

How can you get to projects which are already corrupted?

There are couple of solutions, but none of them is optimal solution and probably none of them will satisfy you. Here are some of them:

SOLUTION No 1:
You can restart workflow for that corrupted project. This is the easiest way, and after this, you can open your project. BUT, it will set your project to the beginning of the workflow and this might confuse users because they might think that this project wasn't already closed and it should have been.

SOLUTION No 2:
You can change Enterprise Project Type (EPT) for your corrupted project to a EPT that has no workflow associated. BUT, problem with this approach are the same as are for SOLUTION No 1.

SOLUTION No 3:
You can insert a row to a certain table in SQL database of Project Server. This is my solution, but I would not recommend it because Microsoft strictly prohibits querying Project Server databases directly. I will not write detailed instructions for this (since inserting rows directly to database is not recommended), but if anyone is interested, just leave a comment, and I will send you a SQL queries.
With this solution, you can open your project and see its data and still have the same workflow associated and it will be in closed state.

SOLUTION No 4:
Restoring data from database backup. This is maybe the cleanest solution, but it can be executed in a very few cases because you might erase some new data with the backup. You can perform this only in specific situations and need to be extremely careful with it, because it can (and probably will) erase some of newer data or newer projects.

Again, there are no good solutions for this problem, but (for me) the least painful is the 3th solution.

Thursday, August 8, 2013

I have already written a post on locating your documents in SharePoint 2010 database in previous post. In that post, I explained where are documents stored and how can you get then directly in SQL Management Studio.I recommend reading that post since documents in SharePoint 2013 are stored in database in a very similar manner. But, there are some (small) differences.What is different (or new)?

Difference is in table name in which document bytes are stored. Instead AllDocStreams, table is now called DocStreams.

And that is it. Everything else remains the same as in post I mentioned at the begining.

Another new thing worth mentioning in SP 2013 database in table called DocsToStreams which contains data about version history of your document.

Monday, August 5, 2013

In Project Server 2010, on Project Detail Page (PDP) user has no option for setting certain Custom Field as readonly. Sometimes, field needs to be set as readonly depending on some other condition or depending on value of another field.But, there is option for inserting javascript (and jQuery) directly on the page, and, with javascript, you can do almost anything.SCENARIO:Let's say that we have one Custom Field called "Employee" that reads values from Lookup Table (values "Yes" and "No") and another Custom Field (plain text field) called "Work Place".Let's say that we want to set field "Work Place" as readonly if user in field "Employee" selects value "No".First, HTML Web Part needs to be added on Project Detail Page (Site Actions --> Edit Page --> Add a Web Part).

Just copy the following script into that Web Part that executes our scenario: