The information in this article applies to:
- DOVICO Timesheet versions 9.0 and above
Symptoms:
After synchronizing with a Microsoft Project Resource Pool file (.mpp) or Microsoft Project Server Resource Pool, employees were duplicated in DOVICO Timesheet.
Cause:
This has occurred because the employees were entered into DOVICO Timesheet before synchronizing with the Microsoft Project/Project Server Resource Pool file. When you synchronize with the Microsoft Project /Project Server Resource pool, DOVICO Timesheet imports the employees from the Microsoft Project Resource Pool regardless of what employees currently exist in DOVICO Timesheet and therefore duplicate employees are created.
Resolution:
The resolution involves retrieving the Unique employee IDs from Microsoft Project/Project Server and using an SQL query to update the Employee table in DOVICO Timesheet.
Important:
The steps involved in retrieving the unique IDs vary depending if using Microsoft Project or Micrsoft Project Server. Please follow the steps carefully.
STEP 1
-
Backup your database as a precaution.
-
In DOVICO Timesheet, delete all duplicated employees created when using the Microsoft Project Link - employees having ‘temp’ User IDs.
If using Microsoft Project follow Step 2
If using Microsoft Project Server go directly to Step 3
STEP 2: Retrieving Unique IDs
-
Open your Resource Pool file in Microsoft Project.
-
Right click on any column and select Insert Column.
-
From the ‘Field Name’ drop-down list, select Unique ID.
-
Make note of the Name and Unique ID associated with each employee. These will be required in following steps.
-
Go directly to Step 6.
STEP 3: Creating a new temporary database
-
Log into the Timesheet Database Manager (Start, All Programs, DOVICO Timesheet, Timesheet Database Manager).
-
Select File, New Database from the menu.
-
Enter the name of the new database in the Database Name field. The new database will be created in the directory where DOVICO Timesheet is installed.
If the SQL Server is not installed locally, then you will need to select the Create database on remote SQL Server option. Once this option has been selected, you will need to specify the SQL Server Data Path. This is the directory path on the SQL Server where the database files (*.mdf and *.ldf) will be stored. This has to be a local path on the remote SQL server. IMPORTANT: This cannot be a UNC or Mapped Network Drive path.
-
Click OK when done.
STEP 4: Synchronize Project Server resources to the new database.
-
Log into the new DOVICO Timesheet database created in Step 3. Note you will be prompted to create an Administrator account for the new database.
-
Navigate to Integration, Microsoft Project and choose the Project Server Link tab.
-
Enter the connection information required to connect to Microsoft Project Server.
-
Once connected, select Get Resources.
STEP 5: Retrieving Unique IDs
-
Launch SQL 2005 Management Studio or SQL Server Management Studio Express.
-
Navigate to the database created in Step 3 and expand it until you see the tables.
-
Right click on the Employee table and select Open Table.
-
For each employee make note of the name and the unique ID located in the Integrate column. The Integrate column format displayed will be either (<INT><MSP MSPID="12" /></INT>) or (<INT><MSP MSPID="f4572c86-7452-4d29-9d28-1a8a49ad5f89" /></INT>) depending on your version of Microsoft Project Server.
Note: Once the final steps (6a – e) have been successfully concluded, the new database created in Step 3 can be deleted.
STEP 6: Updating Unique IDs
-
Launch SQL 2005 Management Studio or SQL Server Management Studio Express.
-
Navigate to your live database, locate and expand until you see Tables.
-
Right click on the Employee table and select Open Table.
-
Select the SQL button Show SQL Pane.
-
In the SQL Pane, run one of the following queries. The query must be run once for each employee linked. You can choose to update the employee table using employee Name or ID.
WARNING: If you are using the Active Directory link then you must use Query Option #3.
Query Option #1. This query requires the unique IDs retrieved from Microsoft Project/Project Server and the Employee first and last name.
UPDATE EMPLOYEE SET [INTEGRATE]='<INT><MSP MSPID="unique id" /></INT>' WHERE [LAST]='employee last name' AND [FIRST]='employee first name'
Query Option #2. This query requires the unique IDs from Microsoft Project/Project Server and the employee ID from the Employee table.
UPDATE EMPLOYEE SET [INTEGRATE]='<INT><MSP MSPID="unique id" /></INT>' WHERE [ID] = employee id
Query Option #3. This query appends the Microsoft Project unique ID to the Active Directory ID in the Employee table's Integrate column.
UPDATE EMPLOYEE SET INTEGRATE.modify('insert <MSP MSPID="unique id" /> as last into (/INT)[1]') WHERE ID = employee id
Note: The queries in this example are only to be used in the specified cases mentioned above. If your scenario differs from this article please contact Dovico Technical Support.
