Problem with SQL Query file

I'm hoping you can help with something.
I've installed the Project Server post SP1 Hotfix in my test lab and all
looks ok, apart from some errors when I execute the websps.sql query file
that forms part of the hotfix. The errors look like this.
There is no such user or group 'MSProjectServerRole'.
Here's an example of one of the entries in the query file that generates the
error.
/* 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 CREATE PROCEDURE part
doesn't appear to work as the stored procedure
MSP_WEB_SP_QRY_GetNotificationAdminInfo does not appear in Enterprise Manager
after the query file has finished executing.
Not being a SQL guy, can anyone give me a pointer as to how I can
troubleshoot this problem? I'm executing the query file as sa, so I don't
think this is a permissions thing.
Thanks
Tony
It appears that you created the proc in the wrong database. Likely, you put
it in master.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Tony Murray" <TonyMurray@.discussions.microsoft.com> wrote in message
news:1530354A-94BA-4F8C-B1E7-F31980B603A9@.microsoft.com...
I'm hoping you can help with something.
I've installed the Project Server post SP1 Hotfix in my test lab and all
looks ok, apart from some errors when I execute the websps.sql query file
that forms part of the hotfix. The errors look like this.
There is no such user or group 'MSProjectServerRole'.
Here's an example of one of the entries in the query file that generates the
error.
/* 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 CREATE PROCEDURE part
doesn't appear to work as the stored procedure
MSP_WEB_SP_QRY_GetNotificationAdminInfo does not appear in Enterprise
Manager
after the query file has finished executing.
Not being a SQL guy, can anyone give me a pointer as to how I can
troubleshoot this problem? I'm executing the query file as sa, so I don't
think this is a permissions thing.
Thanks
Tony
|||Thanks Tom, you were spot-on :-) I had highlighted the ProjectServer
database in the left hand pane of Query Analyzer, but had not specified it in
the drop down list in the right pane.
Tony
"Tom Moreau" wrote:

> It appears that you created the proc in the wrong database. Likely, you put
> it in master.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Tony Murray" <TonyMurray@.discussions.microsoft.com> wrote in message
> news:1530354A-94BA-4F8C-B1E7-F31980B603A9@.microsoft.com...
> I'm hoping you can help with something.
> I've installed the Project Server post SP1 Hotfix in my test lab and all
> looks ok, apart from some errors when I execute the websps.sql query file
> that forms part of the hotfix. The errors look like this.
> There is no such user or group 'MSProjectServerRole'.
> Here's an example of one of the entries in the query file that generates the
> error.
> /* 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 CREATE PROCEDURE part
> doesn't appear to work as the stored procedure
> MSP_WEB_SP_QRY_GetNotificationAdminInfo does not appear in Enterprise
> Manager
> after the query file has finished executing.
> Not being a SQL guy, can anyone give me a pointer as to how I can
> troubleshoot this problem? I'm executing the query file as sa, so I don't
> think this is a permissions thing.
> Thanks
> Tony
>
|||It's a common problem with DBA's. We usually belong to the sysadmin role
and have a default DB of master. If you include a USE statement, that can
fend off the wrong DB problem. Any object you create should also have the
appropriate GRANT statements, too. When you're in sysadmin, it always
works.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Tony Murray" <TonyMurray@.discussions.microsoft.com> wrote in message
news:209BE0C0-C948-4AE6-886E-F87A76AC3823@.microsoft.com...
Thanks Tom, you were spot-on :-) I had highlighted the ProjectServer
database in the left hand pane of Query Analyzer, but had not specified it
in
the drop down list in the right pane.
Tony
"Tom Moreau" wrote:

> It appears that you created the proc in the wrong database. Likely, you
> put
> it in master.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Tony Murray" <TonyMurray@.discussions.microsoft.com> wrote in message
> news:1530354A-94BA-4F8C-B1E7-F31980B603A9@.microsoft.com...
> I'm hoping you can help with something.
> I've installed the Project Server post SP1 Hotfix in my test lab and all
> looks ok, apart from some errors when I execute the websps.sql query file
> that forms part of the hotfix. The errors look like this.
> There is no such user or group 'MSProjectServerRole'.
> Here's an example of one of the entries in the query file that generates
> the
> error.
> /* 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 CREATE PROCEDURE
> part
> doesn't appear to work as the stored procedure
> MSP_WEB_SP_QRY_GetNotificationAdminInfo does not appear in Enterprise
> Manager
> after the query file has finished executing.
> Not being a SQL guy, can anyone give me a pointer as to how I can
> troubleshoot this problem? I'm executing the query file as sa, so I don't
> think this is a permissions thing.
> Thanks
> Tony
>

Problem with SQL Query file

I'm hoping you can help with something.
I've installed the Project Server post SP1 Hotfix in my test lab and all
looks ok, apart from some errors when I execute the websps.sql query file
that forms part of the hotfix. The errors look like this.
There is no such user or group 'MSProjectServerRole'.
Here's an example of one of the entries in the query file that generates the
error.
/* 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 CREATE PROCEDURE part
doesn't appear to work as the stored procedure
MSP_WEB_SP_QRY_GetNotificationAdminInfo does not appear in Enterprise Manage
r
after the query file has finished executing.
Not being a SQL guy, can anyone give me a pointer as to how I can
troubleshoot this problem? I'm executing the query file as sa, so I don't
think this is a permissions thing.
Thanks
TonyIt appears that you created the proc in the wrong database. Likely, you put
it in master.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tony Murray" <TonyMurray@.discussions.microsoft.com> wrote in message
news:1530354A-94BA-4F8C-B1E7-F31980B603A9@.microsoft.com...
I'm hoping you can help with something.
I've installed the Project Server post SP1 Hotfix in my test lab and all
looks ok, apart from some errors when I execute the websps.sql query file
that forms part of the hotfix. The errors look like this.
There is no such user or group 'MSProjectServerRole'.
Here's an example of one of the entries in the query file that generates the
error.
/* 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 CREATE PROCEDURE part
doesn't appear to work as the stored procedure
MSP_WEB_SP_QRY_GetNotificationAdminInfo does not appear in Enterprise
Manager
after the query file has finished executing.
Not being a SQL guy, can anyone give me a pointer as to how I can
troubleshoot this problem? I'm executing the query file as sa, so I don't
think this is a permissions thing.
Thanks
Tony|||Thanks Tom, you were spot-on :-) I had highlighted the ProjectServer
database in the left hand pane of Query Analyzer, but had not specified it i
n
the drop down list in the right pane.
Tony
"Tom Moreau" wrote:

> It appears that you created the proc in the wrong database. Likely, you p
ut
> it in master.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Tony Murray" <TonyMurray@.discussions.microsoft.com> wrote in message
> news:1530354A-94BA-4F8C-B1E7-F31980B603A9@.microsoft.com...
> I'm hoping you can help with something.
> I've installed the Project Server post SP1 Hotfix in my test lab and all
> looks ok, apart from some errors when I execute the websps.sql query file
> that forms part of the hotfix. The errors look like this.
> There is no such user or group 'MSProjectServerRole'.
> Here's an example of one of the entries in the query file that generates t
he
> error.
> /* 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 executio
n
> of the stored procedure comes up with the error. The CREATE PROCEDURE pa
rt
> doesn't appear to work as the stored procedure
> MSP_WEB_SP_QRY_GetNotificationAdminInfo does not appear in Enterprise
> Manager
> after the query file has finished executing.
> Not being a SQL guy, can anyone give me a pointer as to how I can
> troubleshoot this problem? I'm executing the query file as sa, so I don't
> think this is a permissions thing.
> Thanks
> Tony
>|||It's a common problem with DBA's. We usually belong to the sysadmin role
and have a default DB of master. If you include a USE statement, that can
fend off the wrong DB problem. Any object you create should also have the
appropriate GRANT statements, too. When you're in sysadmin, it always
works.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tony Murray" <TonyMurray@.discussions.microsoft.com> wrote in message
news:209BE0C0-C948-4AE6-886E-F87A76AC3823@.microsoft.com...
Thanks Tom, you were spot-on :-) I had highlighted the ProjectServer
database in the left hand pane of Query Analyzer, but had not specified it
in
the drop down list in the right pane.
Tony
"Tom Moreau" wrote:

> It appears that you created the proc in the wrong database. Likely, you
> put
> it in master.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Tony Murray" <TonyMurray@.discussions.microsoft.com> wrote in message
> news:1530354A-94BA-4F8C-B1E7-F31980B603A9@.microsoft.com...
> I'm hoping you can help with something.
> I've installed the Project Server post SP1 Hotfix in my test lab and all
> looks ok, apart from some errors when I execute the websps.sql query file
> that forms part of the hotfix. The errors look like this.
> There is no such user or group 'MSProjectServerRole'.
> Here's an example of one of the entries in the query file that generates
> the
> error.
> /* 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 CREATE PROCEDURE
> part
> doesn't appear to work as the stored procedure
> MSP_WEB_SP_QRY_GetNotificationAdminInfo does not appear in Enterprise
> Manager
> after the query file has finished executing.
> Not being a SQL guy, can anyone give me a pointer as to how I can
> troubleshoot this problem? I'm executing the query file as sa, so I don't
> think this is a permissions thing.
> Thanks
> Tony
>

Problem with SQL Query file

I'm hoping you can help with something.
I've installed the Project Server post SP1 Hotfix in my test lab and all
looks ok, apart from some errors when I execute the websps.sql query file
that forms part of the hotfix. The errors look like this.
There is no such user or group 'MSProjectServerRole'.
Here's an example of one of the entries in the query file that generates the
error.
/* 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 CREATE PROCEDURE part
doesn't appear to work as the stored procedure
MSP_WEB_SP_QRY_GetNotificationAdminInfo does not appear in Enterprise Manager
after the query file has finished executing.
Not being a SQL guy, can anyone give me a pointer as to how I can
troubleshoot this problem? I'm executing the query file as sa, so I don't
think this is a permissions thing.
Thanks
TonyIt appears that you created the proc in the wrong database. Likely, you put
it in master.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tony Murray" <TonyMurray@.discussions.microsoft.com> wrote in message
news:1530354A-94BA-4F8C-B1E7-F31980B603A9@.microsoft.com...
I'm hoping you can help with something.
I've installed the Project Server post SP1 Hotfix in my test lab and all
looks ok, apart from some errors when I execute the websps.sql query file
that forms part of the hotfix. The errors look like this.
There is no such user or group 'MSProjectServerRole'.
Here's an example of one of the entries in the query file that generates the
error.
/* 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 CREATE PROCEDURE part
doesn't appear to work as the stored procedure
MSP_WEB_SP_QRY_GetNotificationAdminInfo does not appear in Enterprise
Manager
after the query file has finished executing.
Not being a SQL guy, can anyone give me a pointer as to how I can
troubleshoot this problem? I'm executing the query file as sa, so I don't
think this is a permissions thing.
Thanks
Tony|||Thanks Tom, you were spot-on :-) I had highlighted the ProjectServer
database in the left hand pane of Query Analyzer, but had not specified it in
the drop down list in the right pane.
Tony
"Tom Moreau" wrote:
> It appears that you created the proc in the wrong database. Likely, you put
> it in master.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Tony Murray" <TonyMurray@.discussions.microsoft.com> wrote in message
> news:1530354A-94BA-4F8C-B1E7-F31980B603A9@.microsoft.com...
> I'm hoping you can help with something.
> I've installed the Project Server post SP1 Hotfix in my test lab and all
> looks ok, apart from some errors when I execute the websps.sql query file
> that forms part of the hotfix. The errors look like this.
> There is no such user or group 'MSProjectServerRole'.
> Here's an example of one of the entries in the query file that generates the
> error.
> /* 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 CREATE PROCEDURE part
> doesn't appear to work as the stored procedure
> MSP_WEB_SP_QRY_GetNotificationAdminInfo does not appear in Enterprise
> Manager
> after the query file has finished executing.
> Not being a SQL guy, can anyone give me a pointer as to how I can
> troubleshoot this problem? I'm executing the query file as sa, so I don't
> think this is a permissions thing.
> Thanks
> Tony
>|||It's a common problem with DBA's. We usually belong to the sysadmin role
and have a default DB of master. If you include a USE statement, that can
fend off the wrong DB problem. Any object you create should also have the
appropriate GRANT statements, too. When you're in sysadmin, it always
works.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tony Murray" <TonyMurray@.discussions.microsoft.com> wrote in message
news:209BE0C0-C948-4AE6-886E-F87A76AC3823@.microsoft.com...
Thanks Tom, you were spot-on :-) I had highlighted the ProjectServer
database in the left hand pane of Query Analyzer, but had not specified it
in
the drop down list in the right pane.
Tony
"Tom Moreau" wrote:
> It appears that you created the proc in the wrong database. Likely, you
> put
> it in master.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Tony Murray" <TonyMurray@.discussions.microsoft.com> wrote in message
> news:1530354A-94BA-4F8C-B1E7-F31980B603A9@.microsoft.com...
> I'm hoping you can help with something.
> I've installed the Project Server post SP1 Hotfix in my test lab and all
> looks ok, apart from some errors when I execute the websps.sql query file
> that forms part of the hotfix. The errors look like this.
> There is no such user or group 'MSProjectServerRole'.
> Here's an example of one of the entries in the query file that generates
> the
> error.
> /* 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 CREATE PROCEDURE
> part
> doesn't appear to work as the stored procedure
> MSP_WEB_SP_QRY_GetNotificationAdminInfo does not appear in Enterprise
> Manager
> after the query file has finished executing.
> Not being a SQL guy, can anyone give me a pointer as to how I can
> troubleshoot this problem? I'm executing the query file as sa, so I don't
> think this is a permissions thing.
> Thanks
> Tony
>

Problem with Saving a Stored Procedure

I have had the same problem. In the past I had the sql commands in the code. Last project was with SQL2000 and I decided to add store procedures part as a learning tool and part to better control the code. With the current project I am using SQL2005 and have a ummm fun time with the learning curve.

Below is my stored procedure.

USE [AdventureWorks]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER Procedure [dbo].[WDR_CountryAdd]

(

@.CountryName nvarchar(50),

@.CountryID int OUTPUT

)

AS

INSERT INTO Country

(

CountryName

)

VALUES

(

@.CountryName

)

SELECT

@.CountryID = @.@.Identity

When I hit the execute button, I get the following error.

Msg 208, Level 16, State 6, Procedure WDR_CountryAdd, Line 24

Invalid object name 'WDR_CountryAdd'.

I I know that it does not exist as that is what I am trying to do, create it. I have checked to make sure that I am on the currect database. So how do I associate this procedure with the data base? Do I need to write a SQL query to create the procedure? Maybe that is the answer and then just modify it.

If it doesn't exists, you need to use a CREATE PROCEDURE statement instead of an ALTER PROCEDURE statement.|||

Arnie

I appreciate the fast response.

I used a create and it ran successfully. However, it still did not show up under the stored procedures. So if it does not show up in the list, how do I access it from my web application? With SQL 2000 I just did a right click create new, did the code and it saved it to the list and then I accessed it. I was planning on a code along the lines of WDR_CountryAdd("USA") (of course in the proper codes)

Now here is a beginners question, When I first execute this procedure, do I need to supply the parameters for it to successfully attach to the database?

Jerry

|||

Never Mind. I ran the CREATE again and it worked very well. Must of had a brain dead moment there.

Thanks for the help. I have learned a lot from this forum.

Jerry

Problem with referencing System.Data.SqlServerCE.dll

Hello

I have referrenced System.Data.SqlServerCE.dll to my web project in order to create a .sdf file. I haven't wrote any code,just referenced it. When I build the project I get this error:

Error 2 'System.Data.SqlServerCe, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' cannot be loaded.

It sounds like you dont have the client dlls installed.

If you already installed the SDK you need to install it from C:\Program Files\Microsoft SQL Server Compact Edition\v3.1\SDK\bin\Desktop

if you dont have the SDK, I suggest you to install it, another option would be to download directly another redist.

|||

I have had problems too, related to incorrect referencing. There is a version in the public assemblies folder which causes this error.
C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PublicAssemblies\System.Data.SqlServerCe.dll

This specific file/version is needed by the data designer in visual studio so dont delete it, just change any references in your projects, for System.Data.SqlServerCe.dll, to point to the correct file.

Usually

C:\Program Files\Microsoft SQL Server Compact Edition\v3.1\System.Data.SqlServerCe.dll

OR

C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\System.Data.SqlServerCe.dll

Cheers

Anton