Problems with Project Server Migration tool

I am still progressing with an upgrade from Project Server 2003 to Project Server 2007 and had already successfully performed a dry run of migrating projects and workspaces to the 2007 environment on a set of virtual servers. I am now in the process of setting up a testing environment on a new set of virtual servers (epmodbdev) copied from the original base virtual servers (epmodbdr) at a point in time prior to begining the installation of project server but after installing SQL Server 2005. I have successfully run the project migration tool (P12MigrationTool.exe) with the -verify option but now that I am trying to run it to migrate the project global data I am encountering the following error:
 
09/06/2007 13:11:57.688   preparing Query exec dbo.MSP_P12UPG_CreateAllViewsToProjectServer2003 ?, ?
09/06/2007 13:11:57.749   Executing query failed with -1
09/06/2007 13:11:57.749   Cmd failed retcode= -1
09/06/2007 13:11:57.749   Err1 = 37000 Err2 = [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find server ‘epmodbdev’ in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
09/06/2007 13:11:57.759   preparing Query exec dbo.MSP_P12UPG_INSERT_OR_UPDATE_RUN_PHASE_STATUS ?, ?, ?
09/06/2007 13:11:57.759   PWA Migration: Setup Views to Project Server 2003 — Failed.
09/06/2007 13:11:57.759   Time to execute CUpgrader::UpgradePWASetupViews(): 130 ms
09/06/2007 13:11:57.759   PWA Migration: Setup Views to Project Server 2003 — Failed.
09/06/2007 13:11:57.759   Updating run table
09/06/2007 13:11:57.759   preparing Query update dbo.MSP_P12UPGRADE_UPGRADE_RUNS SET UPG_RUN_STATUS = ?, UPG_RUN_FINISH_DATE = GETDATE() where UPG_RUN_UID = ?
09/06/2007 13:11:57.759   Executing Query update dbo.MSP_P12UPGRADE_UPGRADE_RUNS SET UPG_RUN_STATUS = ?, UPG_RUN_FINISH_DATE = GETDATE() where UPG_RUN_UID = ?
09/06/2007 13:11:57.779   Migration finished with return code 80004005
09/06/2007 13:11:57.779   Project migration failed.
 
I verified that the the server epmodbdev was available and the tool was able to access it because it ran fine with the -verify option. A troll through the google/msn suggested that the return code was related to lack of access so I verified that I had access as well as that the migrated project server 2003 database was online and visible.
After spending a few more hours on it I discovered that it was related to the way the server had been set up (copied from a prior VM and renamed) and that SQL Server still had the original server name stored away in the sysservers system table. The following sql statement command run using SQL Query analyser fixed this problem:
 

sp_dropserver epmodbdr

go

 

sp_addserver epmodbdev , local

go

 

After this the migration tool no longer complained about finding the server but I was now confronted with the following new error:

 

09/06/2007 15:38:10.125   preparing Query exec dbo.MSP_P12UPG_CreateAllViewsToProjectServer2003 ?, ?
09/06/2007 15:38:10.135   Executing query failed with -1
09/06/2007 15:38:10.135   Cmd failed retcode= -1
09/06/2007 15:38:10.135   Err1 = 37000 Err2 = [Microsoft][ODBC SQL Server Driver][SQL Server]Server ‘epmodbdev’ is not configured for DATA ACCESS.
09/06/2007 15:38:10.135   preparing Query exec dbo.MSP_P12UPG_INSERT_OR_UPDATE_RUN_PHASE_STATUS ?, ?, ?
09/06/2007 15:38:10.135   PWA Migration: Setup Views to Project Server 2003 — Failed.
09/06/2007 15:38:10.135   Time to execute CUpgrader::UpgradePWASetupViews(): 10 ms
09/06/2007 15:38:10.135   PWA Migration: Setup Views to Project Server 2003 — Failed.
09/06/2007 15:38:10.145   Updating run table
09/06/2007 15:38:10.145   preparing Query update dbo.MSP_P12UPGRADE_UPGRADE_RUNS SET UPG_RUN_STATUS = ?, UPG_RUN_FINISH_DATE = GETDATE() where UPG_RUN_UID = ?
09/06/2007 15:38:10.145   Executing Query update dbo.MSP_P12UPGRADE_UPGRADE_RUNS SET UPG_RUN_STATUS = ?, UPG_RUN_FINISH_DATE = GETDATE() where UPG_RUN_UID = ?
09/06/2007 15:38:10.165   Migration finished with return code 80004005
09/06/2007 15:38:10.165   Project migration failed.

Again this new error was strange as I had set up the surface area configuration to allow remote mixed mode access and the migration tool was actually being run on the database server so it was local anyway. A bit more of an investigation seemed to indicated it was related to the same issue that cause the first problem and the following  SQL statement fixed the issue and I was able to migrate the global data successfully:

EXEC sp_serveroption ‘EPMODBDEV’, ‘data access’, true

 

Advertisements

1 Comment

Filed under Project Server 2007

One response to “Problems with Project Server Migration tool

  1. Unknown

    Great tip! "sp_addserver" just made my day! 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s