Errors with Post SP1 Hotfix SQL Stored Procedure

[Tony Murray]

Hi all

I've installed the post SP1 Hotfix in my test lab and all looks ok, apart
from some errors when I execute the websps.sql stored procedure. The errors
look like this.

There is no such user or group 'MSProjectServerRole'.

Here's an example of one of the stored procedures.

/* Query #65005 */
CREATE PROCEDURE dbo.MSP_WEB_SP_QRY_GetNotificationAdminInfo
AS
select WADMIN_SMTP_SERVER_NAME,
WADMIN_SMTP_SERVER_PORT,
WADMIN_DEFAULT_LANGUAGE,
WADMIN_NTFY_FROM_EMAIL,
WADMIN_NTFY_EMAIL_TRAILER,
WADMIN_ORG_EMAIL_ADDRESS,
WADMIN_NPE_LAST_RUN,
WADMIN_NPE_NEXT_RUN,
WADMIN_NPE_SCHEDULED_TIME,
WADMIN_INTRANET_SERVER_URL,
WADMIN_EXTRANET_SERVER_URL,
WADMIN_EMAIL_CHARSET
from MSP_WEB_ADMIN

RETURN
GO

GRANT EXECUTE ON dbo.MSP_WEB_SP_QRY_GetNotificationAdminInfo TO
MSProjectServerRole
GO
********************************

I've checked that the MSProjectServerRole exists and is populated with
MSProjectServerUser, so all looks ok. I'm confused as to why the execution
of the stored procedure comes up with the error.

The effect of this is that I cannot configure email notifications using PWA.
The error I see is:

********
Microsoft JScript runtime error '800a138f'

'rs' is null or not an object

/projectserver/Admin/notification.asp, line 43
*********

There is a corresponding error in the application event log on the project
server relating to MSP_WEB_SP_QRY_GetNotificationAdminInfo.

Has anyone else experienced this error when applying the Post SP1 hotfix?
If so, do you have a resolution to the problem?

Thanks
Tony

 

[Tony Murray]

A quick update on this.

There were two things wrong.

1. I hadn't correctly specified the Project Server database in Query
Analyzer. It was focused on the Master db when I ran the query.
2. When I ran it again with the proper focus, I still got errors. These
were due to the fact that the Project Server SP1 SQL udpates had not been
successfully installed. Basically, I was trying to apply the post SP1 SQL
hotfix to a pre-SP1 database schema, which obviously wouldn't work. All was
fine after I had re-installed the SP1 SQL updates.

Tony