I came upon this bug while working on my earlier posting regarding Reporting Database Refresh http://blogs.msdn.com/b/brismith/archive/2011/07/14/project-server-2010-reporting-database-refresh-failing-with-large-resource-pools.aspx – and specifically trying to bulk edit lots of resources at once in the Resource Center. It appears we throw a very complex and large query over to SQL and it has some issues parsing it. The failure is pretty silent for the user – who just gets the Resource Center page back and might assume everything worked ok. However there will be no jobs in the queue, the change will not have been made – and the resources will all be left check-out (as you will find out if you try the process again!). The answer is to check all the resources back in again (or at least the ones you checked out – someone else may be doing work with other resources) and then reduce the number you have selected and try again. The size and complexity of the query depends mainly on the number of resources, but also will be affected by the number of custom fields at the resource level – but my finding were that you could edit around 400 resources at once with bulk edit if you had around 24 resource custom fields. Your mileage may vary!
The errors that are seen are interesting – in that it appeared to depend on the severity of the issue. With just over the triggering number of resources the failure was pretty silent in the USL logs and SQL Server – exceeding by a few more (20+) then gave errors in SQL Server of Error: 208, Severity: 16, State: 0 (which tends to mean object not found, and I’m guessing it was not finding the temporary table it wanted to use in part of the query) – but still nothing much in the ULS logs – and it was only when increasing the number of resources close to 500 did I see the following in SQL Server
- Exception - Error: 191, Severity: 15, State: 1
- User Error Message - Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries.
and then the ULS logs gave me an exception too:
- 07/15/2011 15:49:02.07 w3wp.exe (0x19B8) 0x220C Project Server General 0000 Exception Exception occurred in method Microsoft.Office.Project.Server.BusinessLayer.Resource.ReadResources Microsoft.Office.Project.Server.DataAccessLayer.FilterDal+FilterException: Error during filter query execution. Query: declare @ResUid UniqueIdentifier; set @ResUid = eb736432-cd8d-4db2-8d9b-ad57bb3f0085; declare @EntUids NVarChar; set @EntUids = e162554e-45f0-496a-a86e-0010ef91ae13,bb57a927-1b4b-42e9-b29b-001c63e0b53c,... followed by hundreds more GUIDs
I’ll be logging this bug internally too so we can consider a fix for it – or at least a limit on the resource selection so you don’t run in to this one.