Microsoft KB Archive/897065

= It takes longer than expected for outline codes to be added or to be updated in a Project Server 2003 database =

Article ID: 897065

Article Last Modified on 7/27/2006

-

APPLIES TO


 * Microsoft Office Project Server 2003

-





SYMPTOMS
If the following conditions are true when you add or update outline codes in a Microsoft Office Project Server 2003 database, it takes longer than expected for the outline codes to be added or to be updated:
 * You use a three-way database configuration instead of a single database configuration.
 * You use any one of the following Project Data Service (PDS) reference methods to update the outline codes in the database:
 * OutlineCodeAddValues
 * OutlineCodeUpdateValues
 * EnterpriseOutlineCodeUpdate



CAUSE
When you add or update outline code values, the OC_CACHED_FULL_NAME column is updated. The OC_CACHED_FULL_NAME column has more overhead in a partitioned database environment. The overhead is cause by the update statement running on a view that contains a linked table (MSP_OUTLINE_CODES) from the Project Tables database server. Therefore, if the outline codes contain many values, the stored procedure must iterate through each value to build the full cached name. This process can take significantly longer than expected.

Note The OC_CACHED_FULL_NAME field contains the full outline code value that includes all the parent levels. This outline code value is generated during the publication process when the Enterprise Global template is checked in. Additionally, this outline code value is generated when the outline codes are edited externally through PDS.



WORKAROUND
To work around this problem, add the MSP_PDS_CacheFullName stored procedure to the Project Tables database server. After you add the MSP_PDS_CacheFullName stored procedure, modify the MSP_PDS_CacheFullName stored procedure in the Main database server. When you make this modification in the Main database server, the MSP_PDS_CacheFullName stored procedure is called in the Project Tables database server. To do this, follow these steps:  Add the MSP_PDS_CacheFullName stored procedure to the Project Tables database server. To do this, follow these steps:  Start SQL Query Analyzer, and then connect to the instance of SQL Server that you have designated the Project Tables database server.  In SQL Query Analyzer, copy and paste the following Transact-SQL code: -- Change ProjectTablesDatabase to the database that contains the Project Tables USE ProjectTablesDatabase GO

/* Query PDS069 */ IF EXISTS (SELECT id FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.MSP_PDS_CacheFullName') AND OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE dbo.MSP_PDS_CacheFullName GO

/* Query PDS069 */ CREATE PROCEDURE dbo.MSP_PDS_CacheFullName @field_id INT AS BEGIN SET NOCOUNT ON -- Get the EGlobal's PROJ_ID declare @ent_global_id INT select @ent_global_id = PROJ_ID from MSP_PROJECTS where PROJ_TYPE = 2 -- Get the code mask information declare @code_mask varchar(256) select @code_mask = AS_VALUE from MSP_FIELD_ATTRIBUTES fa inner join MSP_ATTRIBUTE_STRINGS ats on fa.AS_ID = ats.AS_ID where fa.ATTRIB_ID = 200 and fa.ATTRIB_FIELD_ID = @field_id and fa.PROJ_ID = @ent_global_id -- Advance to the point of the first level separator declare @idx int set @idx = charindex(',',@code_mask) set @idx = charindex(',',@code_mask,@idx+1) -- No separator between summary elem and level-1 items declare @sep varchar(255) set @sep = '' -- Clear the values for the top-level items declare @uid_tbl table (UID int) insert into @uid_tbl select CODE_UID from MSP_OUTLINE_CODES where PROJ_ID = @ent_global_id and OC_FIELD_ID = @field_id and OC_PARENT = 0 update MSP_OUTLINE_CODES set OC_CACHED_FULL_NAME = '' where PROJ_ID = @ent_global_id and CODE_UID in (select UID from @uid_tbl) -- This is a temp table that will hold children declare @child_uid_tbl table (UID int) -- Start with the top-level items and run thru a while loop declare @uid_count int select @uid_count = count(*) from @uid_tbl -- This indicates when we have run out of levels on the mask declare @end_of_mask int set @end_of_mask = 0 while @uid_count > 0 begin -- Here we actually construct the full name update oc1 set oc1.OC_CACHED_FULL_NAME = ISNULL(cast(oc2.OC_CACHED_FULL_NAME as varchar(255)),) + @sep + ISNULL(cast(oc1.OC_NAME as varchar(255)),) from MSP_OUTLINE_CODES oc1 inner join MSP_OUTLINE_CODES oc2 on oc1.OC_PARENT = oc2.CODE_UID where oc1.PROJ_ID = @ent_global_id and oc2.PROJ_ID = @ent_global_id and oc1.CODE_UID in (select UID from @uid_tbl) -- Get all the lookup table items in the next level delete from @child_uid_tbl insert into @child_uid_tbl select CODE_UID from MSP_OUTLINE_CODES where PROJ_ID = @ent_global_id and OC_FIELD_ID = @field_id and OC_PARENT in (select UID from @uid_tbl) delete from @uid_tbl insert into @uid_tbl select UID from @child_uid_tbl select @uid_count = count(*) from @uid_tbl -- Get the list separator for the next level declare @next_idx INT set @next_idx = charindex(',', @code_mask, @idx+1) if @end_of_mask = 0 begin if @next_idx = 0 begin set @next_idx = len(@code_mask) + 1 -- this if-statement handles getting the last separator in the mask set @end_of_mask = 1 end set @sep = substring(@code_mask, @idx+1, @next_idx - @idx - 1) end else set @sep = '' -- Handle the special encoding of the comma as a separator if @sep = '&quot;,&quot;' set @sep = ',' -- Advance over the mask by 3 commas so we are in a position to get the next list separator set @idx = charindex(',', @code_mask, @idx+1) set @idx = charindex(',', @code_mask, @idx+1) set @idx = charindex(',', @code_mask, @idx+1) end END GO GRANT EXECUTE ON dbo.MSP_PDS_CacheFullName TO MSProjectServerRole

GO  Change the  to the name of the database that contains the Project Tables. On the Query menu, click Execute. On the File menu, click Exit.</li></ol> </li> Alter the MSP_PDS_CacheFullName stored procedure in the Main database server to call the MSP_PDS_CacheFullName stored procedure in the Project Tables database server. To do this, follow these steps: <ol style="list-style-type: lower-alpha;"> Start SQL Query Analyzer, and then connect to the instance of SQL Server that you have designated the Main database server.</li>  In SQL Query Analyzer, copy and paste the following Transact-SQL code: -- Change MainDatabase to the database that contains the Web tables and the Cube tables USE MainDatabase GO

/* Query PDS069 */ IF EXISTS (SELECT id FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.MSP_PDS_CacheFullName') AND OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE dbo.MSP_PDS_CacheFullName GO

/* Query PDS069 */ CREATE PROCEDURE dbo.MSP_PDS_CacheFullName @field_id INT

AS

BEGIN

EXEC [ProjectTablesDBServerName].[ProjectTablesDBName].dbo.MSP_PDS_CacheFullName @field_id

END GO

GRANT EXECUTE ON dbo.MSP_PDS_CacheFullName TO MSProjectServerRole

GO </li> Change the  to the name of the database that contains the Web tables and the Cube tables.</li> Change the  to the name of the Project Tables database server name.</li> Change the  to the name of the Project Tables database name.</li> On the Query menu, click Execute.</li> On the File menu, click Exit.</li></ol> </li></ol>

Additional query words: projectserver2003 prjsvr2003 prjsvr2k3

Keywords: kbdatabase kbwebservices kbtshoot kbprb KB897065

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.