Project Server and SQL Server 2005

[Paul Butler]

Hi

Does anyone know if Project Server 2003 will be suupported with SQL Server
2005?

I did a test install of Project 2005 against the CTP release of SQL Server
2005 but it failed during the installation during the creation of the
SP_WEB_SP_QRY_GetTasksIDs prodedure. It appears that this part of the SQL

select WASSN_ID from MSP_WEB_ASSIGNMENTS WAT2
(index=I_ASSIGN_WRESID_TEAM_LEAD) where WAT2.WRES_ID_TEAM_LEAD=@p0
) as WAT

is no longer valid in 2005. if you remove the
(index=I_ASSIGN_WRESID_TEAM_LEAD) then the procedure does compile correctly.

Is there any workaround to stop the rollback of the Project installation
when the error is thrown?
cheers
Paul.

 

[Gary L. Chefetz [MVP]]

Paul:

I don't think that Project Server 2003 support for SQL2005 is a big priority
for Microsoft, but I suspect it is for the version 12 of Project Server due
out late next year.

With that said, another poster experimenting "With" this several months ago
posted the following solution:
--------------------------------------------------------------------------
I solved it by modifying the SQL in the file WEBSPS.SQL.
It seems that SQL 2005 can't deal with a hint in the select clause without
adding the keyword 'WITH'
You have to insert this keyword into a couple of procedures in the
WEBSPS.SQL file:
e.g. the procedure: dbo.MSP_WEB_SP_QRY_GetTasksIDs

e.g.
Modify the line:
select WASSN_ID from MSP_WEB_ASSIGNMENTS WAT2
(index=I_ASSIGN_WRESID_TEAM_LEAD) ...

into:
select WASSN_ID from MSP_WEB_ASSIGNMENTS WAT2 WITH
(index=I_ASSIGN_WRESID_TEAM_LEAD) ...


You have to do this also in a couple of other procedures in the same file:
- dbo.MSP_WEB_SP_BUS_GetTasksAssignmentsForResIdwDelegation
- dbo.MSP_WEB_SP_QRY_GetSelectedTasksIDs

After these modifications the database install part of Project Server runs
smoothly.