If you have the right database permissions it is perfectly possible to point Project Professional 2003 at a Project Server 2007 database using the save-as option and specifying an ODBC connection. Please do not do this! Reason 1 is that it will not work and will give an error message, but reason 2 is that it could come back and haunt you later. In failing it does try to make the database look like a 2003 version, and it adds some data, and even adds some columns to tables where it thinks they are missing (I said you need the right permissions so only someone with a high level of SQL Server access would get this far).
The time you find the failure is if you ever try to recover your system on another server (or even the same server after a re-build). These extra columns then cause a failure of the PWA provisioning process against the set of databases. During the creating databases stage it will fail with a "Failed - See the Event Log" error. In the event log will be Exception: Insert Error: Column name or number of supplied values does not match table definition. You will now have an MSP_TASKS table with 130 columns compared to the expected 128 - so when it tries to create a stored procedure that will later be used to copy data from MSP_TASKS to MSP_TASKS_SAVED the columns no longer match. The extra columns are:-
Other evidence of the issue is:-
Recovery is reasonably painless, the extra columns can be deleted, and then provisioning will work.
Technorati Tags: Project Server 2007
One problem I have seen a few times is the Project Web Access site provisioning running into issues. If it fails completely then the error messages are pretty good - and you can generally resolve the issue and re-try and everything will be good. However, what do you do when it just sits on "Waiting for Resources" and nothing happens? The quick answer is that this relies on the SharePoint Timer Service and a couple of Shared Service Provider services that can be viewed through Timer Job Definitions (Shared Services Timer Job and Project Server Synchronizing Job for 'SharedServices1' - or whatever yours is called). If these are not running then you will be "Waiting for resources" for a very long time! Also there could be multiple versions of the timer jobs if you have multiple SSPs so it can get confusing, particularly as the first one does not differentiate by name. (Clue - the JobId in the URL for the job is the Id of the timer job row in the Objects table in SharePoint_Config database, and the Properties column from this row will lead you to the Guid of the TargetSharedResourceProvider - which will be the Id of the Shared Services Provider also in the same table.)
So to dig a little deeper so you can understand where it might be stuck I'll explain what is going on in the background which hopefully will help you find what is stopping it from working.
So, step 1 - you have been on the Create a Project Web Access Site page (CreatePWA.aspx) and entered all the details, and it goes back to the Manage Project Web Access Sites (ManagePWA.aspx) page and just sits there. At that point a row has been added to the MIPScheduledJob table in the SharedService1_DB (your database name may vary - this is the default). This is a pre-synchronizing job for the site, and is added to the database by the account running the Shared Services Provider application pool.
Step 2. The Shared Service Timer Job picks up the row from this database table and adds a row to the SharePoint_Config database Objects table. In the properties field of this table is some XML describing the site to be built, database names etc. This timer job will run as the account of the farm administrator (i.e. the account running the OWSTIMER service)
Step 3. The Project Server Synchronizing Job for 'SharedServices1' sees this row and actions it by creating the site, creating the database and then configuring the instance of PWA. If everything is working you see the status change on the ManagePWA page as these different stages are processed, and finally it will say Provisioned! Again, this timer service runs as the farm administrator.
Waiting for resources will be seen until the early stages of step 3.
So check all your services are running and your timer jobs are present and enabled and all should be good. One other workaround that generally gets things moving again is to create a new Shared Services Provider - which will then create new timer jobs and overcome any underlying issues. The web applications can then be associated with the new SSP, the new SSP can be made the default if you are not using the old one for anything else, and the old one could be deleted.
I mention the databases here on the condition that you can look - but don't touch!
Just when I thought I'd seen nearly all of the error messages possible when building a cube along comes another one. And the longest yet! I won't bore you all with the full error message but the key piece is:-
and so it goes on for another 3 or 4 pages...
The reason this happened was that the customer has the Project Server databases on a named instance of SQL - lets call this "server1\PS2007". When provisioning the PWA site from their application server they used an alias for the instance - lets call this "SQLServername". Now when they ask for a cube build the servername that gets passed over to Analysis Services for use when connecting back to the Reporting database is SQLServername (and not the server1\PS2007). But the Analysis Serices server does not have the alias set - so does not recognize SQLServername - hence the error. Resolution is reasonably straightforward - just make the alias available to the Analysis Services machine and all is good!
*** Update *** For a slight variation on this regarding 32 and 64 bit aliases - see http://blogs.msdn.com/brismith/archive/2009/10/08/project-server-2007-another-olap-cube-building-gotcha-is-your-alias-32-or-64-bit.aspx
I have been asked to present a session at the Project Conference titled "Administration of an EPM Solution" and wanted to check that I would be addressing the right issues.
The session will cover administrative functions both in Windows SharePoint Services and Project Server. The SharePoint 3.0 Central Administration features of Diagnostic Logging, Shared Services (including provisioning new PWA sites), Timer jobs, Backup/Restore and administrative account management will be covered. In Project Server I will be looking at the administrative side of Cube Building, Custom Fields, Active Directory Synchronization, User Management, Administrative Backup/Restore, Project Workspaces and last and definitely not least Queue Management. The focus will mainly be on new and changed administrative features of Project Server 2007 so not too much time will be spent on features that have changed little since 2003. This session will also not be covering the administration of Project Portfolio Server.
Am I missing anything?
We have had a few questions on events, and the fact that sometimes, or more correctly, in some circumstances, they don't fire. So if you are using the PSI to make things happen then all should work and usually the description gives you a good idea when they will fire. However, you might also expect that doing a similar thing through Project Professional or Project Web Access will fire the same event. In many cases this is true - a publish is a publish is a publish and wherever you publish a project from will fire the "publish" events. However in saving from Project Professional after deleting some entity in the project you will not see the "EntitiesDeleted" event fire - that would get triggered by a PSI action doing the same thing. Project Professional and PWA make use of the same PSI web services in many cases, but also have a private winproj web service and pwa web service. It is when using these "private" web services that event behavior may be different.
Give us feedback if you are missing some events that as an ISV would make your integration to Project Server easier.
The SDK should be going through another revision later this month and we will be updating the event area to give a little more information..