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

WORKAROUND

1. Add the MSP_PDS_CacheFullName stored procedure to the Project Tables database server. To do this, follow these steps:
   1. Start SQL Query Analyzer, and then connect to the instance of SQL Server that you have designated the Project Tables database server.
   2. 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 = '","' 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 
      
      

   3. Change the ProjectTablesDatabase to the name of the database that contains the Project Tables.
   4. On the Query menu, click Execute.
   5. On the File menu, click Exit.
2. 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:
   1. Start SQL Query Analyzer, and then connect to the instance of SQL Server that you have designated the Main database server.
   2. 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 
      
      

   3. Change the MainDatabase to the name of the database that contains the Web tables and the Cube tables.
   4. Change the ProjectTablesDBServerName to the name of the Project Tables database server name.
   5. Change the ProjectTablesDBName to the name of the Project Tables database name.
   6. On the Query menu, click Execute.
   7. On the File menu, click Exit.