|
|
|
| <Part 1 | Part 2 | Part 3 | |
MS Project Server 2003 - Project Rename Script - Part 2
Please read Part 1, prior to read this section.
Before you create the Rename stored procedure, please read the following and perform the
required changes.
NOTE:
1) If you do not have Windows SharePoint environment, please remove the block of code which is meant for Site Title. Remove the code between the following comments. Please see the SQL Script below to find the following code:
“ -------- Updating SharePoint Site Title Begin “------------- To
“-------- Updating SharePoint Site Title End -------------“
2) If you have SharePoint site for the project, you have to specify your SharePoint
database name in the following stored procedure.Look for the following SQL Statement:
UPDATE SharePoint.dbo.Webs
SET Title = @Newname
WHERE FULLURL = @fullurl
I referring the SharePoint database name as SharePoint in my script. Please
use the correct name as per your environment. For example, if you SharePoint database
name is STSDB, so change the above code as following:
UPDATE STSDB.dbo.Webs
SET Title = @Newname
WHERE FULLURL = @fullurl
3) Please look for @subweb and set the correct sub web name. If you SharePoint site's URL is in this format: https://epmprojectserver/sitesabc/epmprojectServer_329/default.aspx;
in this case, sites-abc/ is your subweb. Please look for SET @subweb in the script.
After you done the above changes, you have to create the following stored procedure.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.sp_ProjRename
(@projid INT, @newname VARCHAR(255), @error_code int OUTPUT)
AS
Declare
@currentName as varchar(255),
@projname as varchar(255),
@checked_out as int,
@checkedout_by as Varchar(255),
@subweb varchar(255),
@fullurl as Varchar(750)
/* Set the SubWeb Name and create/alter procedure. Based on sharepoint site's home page
URL, you have to ennter the subweb value below. Example: If the URL is in this format:
https://epmprojectserver/sites-abc/epmprojectServer_329/default.aspx;
in this case, sites-abc/ is your subweb
If you do not have SharePoint instance in your project server environment, remove the @subweb varaibale and associated code.
*/
SET @subweb = 'sites-abc/'
-- Checking whether project plan have been checked out
SELECT @checked_out = PROJ_CHECKEDOUT FROM MSP_PROJECTS
WHERE PROJ_ID = @projid
SELECT @checkedout_by = PROJ_CHECKOUTBY FROM MSP_PROJECTS
WHERE PROJ_ID = @projid
--If plan has been checked out, setting the error code as -2
IF @checked_out = 1
BEGIN
SELECT @error_code = -2
END
-- If plan is not checked out, perform the rename operations
IF @error_code = 0
BEGIN
SELECT @currentName = PROJ_NAME FROM
MSP_PROJECTS
WHERE PROJ_ID = @projid
-- Updating project title in Proj Pro
UPDATE dbo.MSP_PROJECTS
SET PROJ_NAME = @newname + '.Published',
PROJ_PROP_TITLE = @newname + '.Published',
PROJ_PROJECT='<>\' + @newname,
PROJ_EXT_EDITED=1
WHERE PROJ_ID = @projid
UPDATE dbo.MSP_WEB_PROJECTS
SET PROJ_NAME=@newname
WHERE PROJ_ID=@projid
UPDATE dbo.MSP_VIEW_PROJ_PROJECTS_STD
SET ProjectTitle = @newname
WHERE ProjectUniqueID=@projid
UPDATE dbo.MSP_VIEW_PROJ_TASKS_STD
SET TaskName=@newname
WHERE TaskUniqueID=0 And WPROJ_ID IN (SELECT WPROJ_ID FROM
ProjectServer.dbo.MSP_WEB_PROJECTS
WHERE PROJ_ID = @projid)
UPDATE dbo.MSP_WEB_ASSIGNMENTS
SET TASK_NAME=@newname + '.Published'
WHERE TASK_UID=0 And WPROJ_ID IN (SELECT WPROJ_ID FROM
ProjectServer.dbo.MSP_WEB_PROJECTS
WHERE PROJ_ID = @projid)
-------- Updating SharePoint Site Title Begin-------------
SELECT @fullurl = @subweb + WPROJ_STS_SUBWEB_NAME
FROM MSP_WEB_PROJECTS
WHERE PROJ_ID = @projid
------Specify SharePoint database name below. In my example, SharePoint is my database name
UPDATE SharePoint.dbo.Webs
SET Title = @Newname
WHERE FULLURL = @fullurl
----- -- Updating SharePoint Site Title End -------------
SELECT @error_code = @error_code + @@ERROR
END
IF @error_code = 0
BEGIN
PRINT "Project Renamed successfully."
-- Display the value returned by the procedure.
END
ELSE IF @error_code = -2
PRINT "ERROR: Project Plan has been checked out by " + @checkedout_by + ". Please check in project prior to rename."
ELSE IF @error_code > 0
RAISERROR ('An error occurred during the rename.', 11, 1)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
You have to execute the stored procedure, whenever you want to rename any enterprised
published plan. Please read Part 3 for details on how to execute the procedure.
| |
|
