Sep 30 2008

SQL querying the queue

Category: Project Server — Alex Angas @ 10:21 am

I recently found myself in the situation where the Project Server queue service was taking up almost all of a server’s CPU. Unfortunately due to the high demands of the queue jobs, the administration page was unavailable with a connection timeout error.

One way to try and access the administration page again might have been to stop or restart the queue service or the entire server itself, but what if unpredictable behaviour had disrupted those jobs? Instead, by carefully looking at the Project Server databases we can see the draft project database stores a lot of information about the queue in tables and views starting with MSP_QUEUE.

The following query gives a summary of jobs in the queue:
[sourcecode language='sql']
SELECT
QPG.GRP_QUEUE_ID,
QPG.CREATED_DATE,
QPG.READY_FOR_PROCESSING_DATE,
QPM.MSG_QUEUE_BODY_TYPE,
QPG.GRP_QUEUE_MESSAGE_TYPE,
QPG.GRP_QUEUE_STATE,
QPG.PROCESSING_DATE,
QPG.PERCENT_COMPLETE,
QPG.COMPLETED_DATE,
QPG.GRP_QUEUE_ERROR_INFO,
QPG.GRP_QUEUE_PRIORITY,
QPG.LAST_MSG_ID,
QPG.LAST_ADMIN_ACTION
FROM dbo.MSP_QUEUE_PROJECT_GROUP_FULL_VIEW QPG (NOLOCK)
INNER JOIN dbo.MSP_QUEUE_PROJECT_MESSAGE QPM (NOLOCK)
ON QPM.GRP_UID = QPG.GRP_UID
ORDER BY GRP_QUEUE_STATE DESC, GRP_QUEUE_PRIORITY, QPG.GRP_QUEUE_ID
[/sourcecode]

This query gives how many jobs are remaining to run:
[sourcecode language='sql']
SELECT COUNT(*)
FROM dbo.MSP_QUEUE_PROJECT_GROUP_FULL_VIEW QPG (NOLOCK)
WHERE QPG.PROCESSING_DATE IS NULL
[/sourcecode]

This query gives how many jobs of each priority are remaining to run:
[sourcecode language='sql']
SELECT QPG.GRP_QUEUE_PRIORITY, COUNT(*) AS [COUNT]
FROM dbo.MSP_QUEUE_PROJECT_GROUP_FULL_VIEW QPG (NOLOCK)
WHERE QPG.PROCESSING_DATE IS NULL
GROUP BY QPG.GRP_QUEUE_PRIORITY
[/sourcecode]

There are also an interesting statistics table, although I haven’t yet found how to get a text value for GRP_QUEUE_MESSAGE_TYPE:
[sourcecode language='sql']
SELECT QPS.*
FROM dbo.MSP_QUEUE_PROJECT_STATS QPS (NOLOCK)
[/sourcecode]

Ideally you would never want to execute raw SQL statements against the database, but as with any computer system, unpredictable things can happen. These queries might help with some troubleshooting.

Tags: , ,

Sep 13 2008

Internal name vs display name

Category: SharePoint — Alex Angas @ 12:37 pm

Anyone that’s done a reasonable amount of work with the SharePoint object model will soon come across its flaws. The one that I regularly come up against is when working with the SPField object that represents list columns.

There are inconsistencies throughout the object model when attempting to retrieve an instance of an SPField, its value, or perform various other operations. In some cases the display name is required, or the internal name, or both! Even worse, rarely does the SDK document what is required to find your field. Unpredictable behaviour can also result in the cases where it checks for display name and internal name, because what if you have two fields with the same display name?!

Here’s just a few frustrating examples:

  • SPFieldCollection.ContainsField: Internal name or display name
  • SPFieldCollection.Item[string]: Display name
  • SPViewFieldCollection.Exists: Internal name

Tom, another SharePoint developer, has made a list with more examples here. He also details the inconsistencies around when an exception is thrown – yet another place to get caught out.

One of the most important things in an API is consistency so it behaves like you would expect. I’m sure how Microsoft can fix this without introducing breaking changes so maybe we will have to live with this for a long time yet. If in doubt, as always, Reflector is your friend.

Tags: ,

Sep 10 2008

Linking tasks to issues, risks, documents, and more…

Category: Project Server — Alex Angas @ 3:14 pm

In Project Server 2003 a feature was introduced to link issues, risks and documents to tasks. It’s been carried over to 2007 as well (although the UI for it isn’t as noticeable) and I think it’s one of the coolest yet underrated features in the web interface. Let me take you through it… (Note: you will need the Create Object Link category permission to use this feature.)

Every project workspace out of the box comes with the Project Documents, Risks and Issues lists. On the add and edit pages of these lists, you can find “Link Items”:

Clicking Link Items while creating or editing an issue, for example, allows the issue to be linked to any task in the project. Also, this linking can have several meanings, such as whether the issue simply relates to the task, affects the task, or whether the task resolves the issue. Here is the dialog:

You’ll notice the drop-down at the top that reads Project Tasks. This can be changed so that this issue is linked to any risk, document or other issue as well! (Note there is no additional ‘meaning’ unless linking to a task.)

Upon clicking OK on this dialog box, a nice little summary is displayed of the link options chosen:

Of course, the issues list is just an example here. It is also possible to link items from the other lists previously mentioned. Here are the other dialog boxes shown…

Risk:

Document:

Linking’s cool, but now what?

Obviously whenever you look at a linked object you will be able to see and click on what each object is linked to which helps to describe the relations within your project, not to mention handy as well. Some examples are linking a document to a task for its delivery, or linking a risk item to a risky task! Also very useful is that the link gets propagated to the reporting database when the workspace’s project is next published, so some neat queries can be written to relate your data together.

What’s even better is what you don’t see. This functionality is powered by the Object Link Provider, which is described in some detail within the Project 2007 SDK. With some custom development work, it is possible to link tasks or project workspace items in other lists to any object you can reach with a URL. Imagine the possibilities – linking to an item in the company’s financial system, for example! This can all be done with the ObjectLinkProvider web service (examine the SDK for more details).

A few cautionary words

As you may have already encountered, some functionality in Project Server can break when you start altering project workspaces. This is even though it does nothing to stop you and virtually every customer/user has a requirement to. The Link Items feature is no exception here. If you delete the Links column from a workspace, there is no way I’ve found to put it back (this is probably because it is not a built-in SharePoint field type). Instead, try setting it to Hidden within the content type! In fact, hiding the field in the list’s content type should be done for every workspace field you don’t want to use.

Thanks for reading

Hopefully this provides a deeper look at a feature that doesn’t seem to get mentioned much. If you have good uses for “Link Items”, please add a few comments to this post!

Tags: ,

Sep 03 2008

SharePoint and SQL schemas

Category: Project Server, SharePoint — Alex Angas @ 3:57 pm

I’m just under two weeks away from installing the infrastructure update to a production environment. But of course these things need testing first so I started the install on the development environment. Thing’s went hairy for a while…

First I must admit I’m not running the update as the service account, recommended by Microsoft. This is because I’m working in a locked down environment where trying to get the permission to do this requires a lot of questions, moaning and red tape. However both my account and the service account (there’s only one service account in use here) are local administrators.

The log file pointed to by the SharePoint Configuration Wizard contains this ever so helpful line: “An exception of type Microsoft.SharePoint.PostSetupConfiguration.PostSetupConfigurationTaskException was thrown. Additional exception information: Failed to upgrade SharePoint Products and Technologies.” However, with a little more digging there are details located at C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\LOGS. (This isn’t actually the folder where I have directed diagnostic logs to, but for the purposes of an upgrade it seems SharePoint sometimes redirects it!) The logs contain errors over the several times I tried to upgrade…

Upgrade.log:

  • SqlException: The default schema does not exist.
  • SqlException: Cannot find the object ‘MFN_Epm_GetAllCustomFieldsInformation’, because it does not exist or you do not have permission.
  • SqlException: Cannot drop the function ‘dbo.MFN_Epm_GetAllCustomFieldsInformation’, because it does not exist or you do not have permission.

Then in the ULS logs:

  • The schema version (3.1.4.0) of the database SharePoint_AdminContent on server_x is not consistent with the expected database schema version (3.1.5.0) on server_x. Connections to this database from this server have been blocked to avoid data loss. Upgrade the web front end or the content database to ensure that these versions match.
  • SqlException: Cannot find the user ‘DOMAIN\myusername’, because it does not exist or you do not have permission.
  • Queue unable to interact with SQL. Queue type (Project Queue, Timesheet Queue etc): ProjectQ Exception: Microsoft.Office.Project.Server.BusinessLayer.Queue.QueueSqlException: PeriodicTasks failed —> System.Data.SqlClient.SqlException: Invalid object name ‘MSP_QUEUE_RENDEZVOUS’.

It appears that I don’t have permission to the databases. However, my account is db_owner on every database The service account permissions haven’t been changed. What’s going on?!! It turns out the root of the cause was described this exception: The default schema does not exist.

I don’t know a great deal about the schema feature introduced with SQL 2005, however with every user added to a SQL 2005 box, an accompanying schema with the same name is also created. A colleague of mine who was doing some spring cleaning on our databases removed the schema for our service account, however the service account’s SQL user was still trying to use it as its default schema! The solution was to change the schema to dbo.

Unfortunately the upgrade still didn’t quite complete since I was part of the way through the upgrade when it fell over… I also had to create a fake MFN_Epm_GetAllCustomFieldsInformation function in the Reporting database so the upgrade could find and drop it (fortunately replaced with the new version in the upgrade).

It was a big relief that this ended up working. The downside is that my user account and a schema for it have been created on the database server. It can’t be removed right now because the new database objects that come with the update have my schema set as their owner! Now I have to change them all…

Tags: , , ,