#ProjectServer 2010 #MSProject Enterprise Global custom fields out of synch #PS2010 #PS2007

February 22, 2012 at 7:43 pm | Posted in Administration, Configuration, Issue | 5 Comments
Tags: , , , , ,

I recently came across an issue where the enterprise custom fields in the Enterprise Global didn’t match what was displayed in PWA > Server Settings > Enterprise Custom Fields and Lookup tables. I am not sure how or why this has happened, all I know it is seemed to be a combination of an upgrade from Project Server 2003 to Project Server 2010 with a playbooks merge of two Project Server 2007 instances(the two 2003 environments upgraded) along the way. It was decided to remove some of the Project Level custom fields from the Project Server 2010 configuration as they were no longer required. After successfully deleting the customs fields from Server Settings > Enterprise Custom Fields and Lookup tables, Project Professional 2010 was launched, connected to the server instance and found that the deleted custom fields were still present in the Project Information dialog box. I tested deleting the local cache, the local global template but still these deleted fields appeared in the Project Information dialog box. This issue was replicated from all Project Professional 2010 clients. At this point I decided to recreate the Enterprise Global using the steps below:

  • Take a full backup of the Project Server databases / farm
  • Backup the Enterprise Calendars from the Admin backup menu
  • Copy all of the enterprise global elements to a project using the organiser, these include views, tables, filters, groups, reports, maps, forms and toolbars. Save this project as a local MPP file.
  • Run the SQL script that creates the enterprise global (eglobal.sql) against the ProjectServer_Published database. The default location for the script is:

    C:\Program Files\Microsoft Office Servers\14.0\Sql\Project Server\CORE\1033

  • Once the enterprise global has been rebuilt successfully the Project Information dialog box displayed the same fields as shown in Server Settings > Enterprise Custom Fields and Lookup tables so the deleted fields had now been removed from the enterprise global. Now the enterprise global custom elements need to be copied back in. Open and check out the enterprise global, open the MPP file that was used to save all of the elements and then use the organiser to move all of the custom elements back into the new enterprise global.
  • Restore the Enterprise Calendars

These steps resolved the issues on the environment I was working on but please proceed with caution and make sure you have full backups in case you need to roll back.

Check for missing config after #ProjectServer 2007/2010 PWA instance merge #PS2010 #PS2007 #MSProject

February 20, 2012 at 10:22 pm | Posted in Administration, Configuration, Functionality, Migration, T-SQL | Leave a comment
Tags: , , , , ,

I recently worked on a Project to migrate two Project Server 2003 instances into one Project Server 2010 instance. We achieved this by migrating both Project Server 2003 instances to two separate Project Server 2007 instances then merged the smaller of the two instances into the other instance in 2007. Luckily we only needed to migrate the configuration from 2003 in 2010. For this we took a playbooks export of the global configuration from the smaller instance and imported this into the other PWA instance using the merge option. On the whole this approach worked quite well, with only a few errors on importing a couple of custom fields and views. There were some items that we needed to migrate manually (Resource Pool, Enterprise Global etc.).

Now that we had the two environments merged into one 2007 PWA instance we wanted to confirm that we had all of the entities from both systems in the one PWA instance that we were going to take forward to Project Server 2010. To do this I used a simple SQL query that runs against the Published database. Please note, it is not officially supported to query the published database directly with T-SQL so please do so at your own risk. I have used the no lock hint to avoid any tables locks and this was not run on a live production environment. The query used is below:

–SOURCEDB_Published is the PWA instance where the export was taken from

–TARGETDB_Published is the PWA instance where the exported config was imported and merged

–custom fields

use SOURCEDB_Published

select CF.MD_PROP_NAME

from dbo.MSP_CUSTOM_FIELDS CF with (nolock)

where CF.MD_Prop_Name not in (select CFT.MD_PROP_NAME from TARGETDB_Published.dbo.MSP_CUSTOM_FIELDS CFT with (nolock))

–lookup tables

use SOURCEDB_Published

select LT.LT_NAME

from dbo.MSP_LOOKUP_TABLES LT with (nolock)

where LT.LT_NAME not in (select LTT.LT_NAME from TARGETDB_Published.dbo.MSP_LOOKUP_TABLES LTT with (nolock))

–lookup table values (text values)

use SOURCEDB_Published

select LV.LT_VALUE_FULL, LT.LT_name AS [Lookup Table Name]

from dbo.MSP_LOOKUP_TABLE_VALUES LV with (nolock)

Inner Join dbo.MSP_LOOKUP_TABLES LT with (nolock)

ON LT.LT_UID = LV.LT_UID

where LV.LT_VALUE_TEXT not in

(select Cast(LVT.LT_VALUE_FULL as nvarchar(max))

from TARGETDB_Published.dbo.MSP_LOOKUP_TABLE_VALUES LVT with (nolock)

Inner Join TARGETDB_Published.dbo.MSP_LOOKUP_TABLES LTT with (nolock)

ON LTT.LT_UID = LVT.LT_UID where LTT.LT_NAME = LT.LT_NAME)

–calendars

use SOURCEDB_Published

select C.CAL_NAME

from dbo.MSP_CALENDARS C with (nolock)

where C.CAL_NAME not in (Select CT.CAL_NAME from TARGETDB_Published.dbo.MSP_CALENDARS CT with (nolock) where CT.Cal_is_base_Cal = 1)

–views (PWA)

use SOURCEDB_Published

select V.WVIEW_NAME

from dbo.MSP_WEB_VIEW_REPORTS V with (nolock)

where V.WVIEW_NAME not in (Select VT.WVIEW_NAME from TARGETDB_Published.dbo.MSP_WEB_VIEW_REPORTS VT with (nolock))

–security categories

use SOURCEDB_Published

select CAT.WSEC_CAT_NAME

from dbo.MSP_WEB_SECURITY_CATEGORIES CAT with (nolock)

where CAT.WSEC_CAT_NAME not in (Select CAT.WSEC_CAT_NAME from TARGETDB_Published.dbo.MSP_WEB_SECURITY_CATEGORIES CAT with (nolock))

–security groups

use SOURCEDB_Published

select G.WSEC_GRP_NAME

from dbo.MSP_WEB_SECURITY_GROUPS G with (nolock)

where G.WSEC_GRP_NAME not in (Select GT.WSEC_GRP_NAME from TARGETDB_Published.dbo.MSP_WEB_SECURITY_GROUPS GT with (nolock))

Hopefully that will help you validate certain entities in the PWA instance when merging two instances into one.

Project Server 2003 migration to #ProjectServer 2007 error #PS2007 #PS2010 #MSProject

February 13, 2012 at 6:23 pm | Posted in Issue | Leave a comment
Tags: , , , , , ,

I recently worked on a project to migrate from Project Server 2003 to Project Server 2010. While running the P12 migration tool the error below was thrown in the command prompt window:

PWA Migration: Setup views to Project Server 2003 – Failed

The Log file details for the error were as follows:

Err1 = 37000 Err2 = [Microsoft][ODBC SQL Server Drive][SQL Server] could not find server ‘server name’ in sys.servers. Verify that the correct server name was specified. If necessary, execute the sp_addlinkedserver to add the server.

Migration finished with error return code 80004005.

Running the –verify switch completed successfully but running the actual migration failed. 

This is due to using an FQDN or DNS Alias name for the SQL Server in the P12 configuration file rather than the actual short name for the SQL Server. After updating the configuration file “Project2007SQLServer” value the migration ran successfully.  

Create a free website or blog at WordPress.com.
Entries and comments feeds.