IF EXISTS (SELECT id FROM dbo.sysobjects WHERE id = OBJECT_ID(N'MSP_RESTORE_PROJECT') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE MSP_RESTORE_PROJECT
GO

/*
 *  this procedure returns the following error codes
 *    - 5001 Source database does not exist
 *    - 5002 Destination database does not exist
 *    - 5003 There is no project with that proj_id in the source database
 *    - 5004 Source project is with a restricted type (global)
 *    - 5005 Source project is with a restricted type (resglobal)
 *    - 5006 There is no project with that proj_id in the destination database
 *    - 5007 Source and destination project names do not match
 *    - 5008 The project has been currently checked out in the destination database
 *    - 5009 Source and destination web proj do not match
 *    - 5010 Source and destination web proj_ids do not match
 *    - 5011 Restoring project functionality is not supported in this version of ProjectServer
 * 
 */

create procedure MSP_RESTORE_PROJECT
	@SRC_DB           varchar(255),
	@DEST_DB          varchar(255),
	@SRC_PROJ_ID      integer,
	@SRC_PROJ_NAME    varchar(255),
	@FORCE_RESTORE    integer

as

DECLARE  @RETURN_CODE       integer
DECLARE  @SRC_PROJ_TYPE     integer
DECLARE  @SRC_WPROJ_ID      integer
DECLARE  @SRC_WPROJ_NAME    varchar(255)
DECLARE  @DEST_PROJ_TYPE    integer
DECLARE  @DEST_PROJ_NAME    varchar(255)
DECLARE  @DEST_CHECKED_OUT  integer
DECLARE  @DEST_WPROJ_ID     integer
DECLARE  @DEST_WPROJ_NAME   varchar(255)
DECLARE  @ROW_COUNT         integer
DECLARE  @NO_WEB_DATA       integer
DECLARE  @SQLString         varchar(4000)
DECLARE  @Result            integer

-- Init
set @ROW_COUNT = 0
set @RETURN_CODE = 0
set @NO_WEB_DATA = 0


-- Validation

-- Validate source db
exec @Result = sp_helpdb @SRC_DB
if (@Result <> 0)
begin 
	set @RETURN_CODE = 5001
	goto DONE
end 

-- Validate destination db
exec @Result = sp_helpdb @DEST_DB
if (@Result <> 0)
begin 
	set @RETURN_CODE = 5002
	goto DONE
end 

-- Handle spaces within database names
if (charindex(' ', @SRC_DB, 1) > 0 and left(@SRC_DB, 1) <> '[' and right(@SRC_DB, 1) <> ']')
	set @SRC_DB = '[' + @SRC_DB + ']'
if (charindex(' ', @DEST_DB, 1) > 0 and left(@DEST_DB, 1) <> '[' and right(@DEST_DB, 1) <> ']')
	set @DEST_DB = '[' + @DEST_DB + ']'

-- Obtain source project data
create table #msp_tmp_src1 ( PROJ_ID integer, PROJ_NAME varchar(255), PROJ_TYPE integer)
set @SQLString = 'insert into #msp_tmp_src1 (PROJ_ID, PROJ_NAME, PROJ_TYPE)
	select PROJ_ID, PROJ_NAME, PROJ_TYPE
	from  ' + @SRC_DB + '.dbo.MSP_PROJECTS where PROJ_ID = ' +	CONVERT(VARCHAR(255),@SRC_PROJ_ID)
exec(@SQLString)

-- Validate source proj_id
select @ROW_COUNT = count(*) from #msp_tmp_src1
if (@ROW_COUNT = 0) 
begin
	set @RETURN_CODE = 5003
	drop table #msp_tmp_src1
	goto DONE
end

select @SRC_PROJ_TYPE = PROJ_TYPE
from #msp_tmp_src1
where PROJ_ID = @SRC_PROJ_ID

drop table #msp_tmp_src1

-- Validate source proj_type - global(2) or resglobal (3)
if (@SRC_PROJ_TYPE = 2) 
begin
	set @RETURN_CODE = 5004
	goto DONE
end
if (@SRC_PROJ_TYPE = 3) 
begin
	set @RETURN_CODE = 5005
	goto DONE
end

-- Obtain source web project data
create table #msp_tmp_src2 (WPROJ_ID integer, PROJ_NAME varchar(255))
set @SQLString = 'insert into #msp_tmp_src2 (WPROJ_ID, PROJ_NAME)
	select WPROJ_ID, PROJ_NAME
	from  ' + @DEST_DB + '.dbo.MSP_WEB_PROJECTS where PROJ_ID = ' +	CONVERT(VARCHAR(255),@SRC_PROJ_ID)
exec(@SQLString)
	
select @ROW_COUNT = count(*) from #msp_tmp_src2
if(@ROW_COUNT > 0) 
begin
	select
		@SRC_WPROJ_ID = WPROJ_ID,
		@SRC_WPROJ_NAME = PROJ_NAME
	from #msp_tmp_src2
end

drop table #msp_tmp_src2

-- Obtain destination project data
create table #msp_tmp_dest1 ( PROJ_NAME varchar(255), PROJ_TYPE integer, PROJ_CHECKEDOUT integer)
set @SQLString = 'insert into #msp_tmp_dest1 (PROJ_NAME, PROJ_TYPE, PROJ_CHECKEDOUT) '
	+ 'select PROJ_NAME, PROJ_TYPE, PROJ_CHECKEDOUT '
	+ 'from  ' + @DEST_DB + '.dbo.MSP_PROJECTS where PROJ_ID = ' +	CONVERT(VARCHAR(255),@SRC_PROJ_ID)
exec(@SQLString)

select
	@DEST_PROJ_NAME    = PROJ_NAME, 
	@DEST_PROJ_TYPE    = PROJ_TYPE,
	@DEST_CHECKED_OUT  = PROJ_CHECKEDOUT
from #msp_tmp_dest1

drop table #msp_tmp_dest1

-- Obtain destination web project data
-- Set flag if no web data exists
create table #msp_tmp_dest2 (WPROJ_ID integer, PROJ_NAME varchar(255))
set @SQLString = 'insert into #msp_tmp_dest2 (WPROJ_ID, PROJ_NAME)
	select WPROJ_ID, PROJ_NAME
	from  ' + @DEST_DB + '.dbo.MSP_WEB_PROJECTS where PROJ_ID = ' +	CONVERT(VARCHAR(255),@SRC_PROJ_ID)
exec(@SQLString)
	
select @ROW_COUNT = count(*) from #msp_tmp_dest2
if(@ROW_COUNT > 0) 
begin
	select
		@DEST_WPROJ_ID = WPROJ_ID,
		@DEST_WPROJ_NAME = PROJ_NAME
	from #msp_tmp_dest2
	set @NO_WEB_DATA = 0
end
else
begin
	set @NO_WEB_DATA = 1
end

drop table #msp_tmp_dest2

-- Verify matching of wproj_id
if (@NO_WEB_DATA = 0)
begin
	if (@SRC_WPROJ_ID <> @DEST_WPROJ_ID) 
	begin
		set @RETURN_CODE = 5010
		goto DONE
	end
end

-- Currently restoring project functionality is not supported prior P11
set @SQLString = 'select count(*) from ' + @SRC_DB + '.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = ''MSP_PROJECTS'' and COLUMN_NAME = ''PROJ_CREATION_DATE_EX'''
exec(@SQLString)
select @ROW_COUNT = @@rowcount
if @ROW_COUNT = 0
begin
	set @RETURN_CODE = 5011
	goto DONE
end
set @SQLString = 'select count(*) from ' + @DEST_DB + '.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = ''MSP_PROJECTS'' and COLUMN_NAME = ''PROJ_CREATION_DATE_EX'''
exec(@SQLString)
select @ROW_COUNT = @@rowcount
if @ROW_COUNT = 0
begin
	set @RETURN_CODE = 5011
	goto DONE
end

if(@FORCE_RESTORE = 0) 
begin
	-- Checking for proj-tbls consistency
	if(@DEST_PROJ_NAME is null) 
	BEGIN
		set @RETURN_CODE = 5006
		goto DONE
	END
	if(@DEST_PROJ_NAME <> @SRC_PROJ_NAME) 
	BEGIN
		set @RETURN_CODE = 5007
		goto DONE
	END
	if(@DEST_CHECKED_OUT <> 0) 
	BEGIN
		set @RETURN_CODE = 5008
		goto DONE
	END      
  
	-- Checking for web and view tables consistency
	-- Do the web based checks only if the project has web data
	if(@NO_WEB_DATA =0 ) 
	BEGIN 
		if(@DEST_WPROJ_NAME <> @SRC_WPROJ_NAME) 
		BEGIN
			set @RETURN_CODE = 5009
			goto DONE
		END
	END

END /*FORCE RESTORE*/

   /*
    * first we shall copy the data from msp_projects table 
    * into the destination database
    */
    /* msp_projects table copy  */
   set @SQLString = 'delete from ' + @DEST_DB + '.dbo.MSP_PROJECTS where PROJ_ID = ' + convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)
   set @SQLString = 'insert into ' + @DEST_DB + '.dbo.MSP_PROJECTS ' +
                 'select * from ' + @SRC_DB + '.dbo.MSP_PROJECTS where PROJ_ID = ' + convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)

    /* msp_tasks table copy  */
   set @SQLString = 'delete from ' + @DEST_DB + '.dbo.MSP_TASKS where PROJ_ID = ' + convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)
   set @SQLString = 'insert into ' + @DEST_DB + '.dbo.MSP_TASKS ' +
		'select * from ' + @SRC_DB + '.dbo.MSP_TASKS where PROJ_ID = ' + convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)

    /* msp_resources copy  */
   set @SQLString = 'delete from ' + @DEST_DB + '.dbo.MSP_RESOURCES where PROJ_ID = ' + convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)
   set @SQLString = 'insert into ' + @DEST_DB + '.dbo.MSP_RESOURCES ' +
                 'select * from ' + @SRC_DB + '.dbo.MSP_RESOURCES where PROJ_ID = ' + convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)

    /* msp_assignments copy  */
   set @SQLString = 'delete from ' + @DEST_DB + '.dbo.MSP_ASSIGNMENTS where PROJ_ID = ' + convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)
   set @SQLString = 'insert into ' + @DEST_DB + '.dbo.MSP_ASSIGNMENTS ' +
                 'select * from ' + @SRC_DB + '.dbo.MSP_ASSIGNMENTS where PROJ_ID = ' + convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)

    /* msp_calendars copy  */
   set @SQLString = 'delete from ' + @DEST_DB + '.dbo.MSP_CALENDARS where PROJ_ID = ' + convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)
   set @SQLString = 'insert into ' + @DEST_DB + '.dbo.MSP_CALENDARS ' +
                 'select * from ' + @SRC_DB + '.dbo.MSP_CALENDARS where PROJ_ID = ' + convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)

    /* msp_outlinecodes copy  */
   set @SQLString = 'delete from ' + @DEST_DB + '.dbo.MSP_OUTLINE_CODES where PROJ_ID = ' + convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)
   set @SQLString = 'insert into ' + @DEST_DB + '.dbo.MSP_OUTLINE_CODES ' +
                 'select * from ' + @SRC_DB + '.dbo.MSP_OUTLINE_CODES where PROJ_ID = ' + convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)

    /* msp_links copy  */
   set @SQLString = 'delete from ' + @DEST_DB + '.dbo.MSP_LINKS where PROJ_ID = ' + convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)
   set @SQLString = 'insert into ' + @DEST_DB + '.dbo.MSP_LINKS ' +
                 'select * from ' + @SRC_DB + '.dbo.MSP_LINKS where PROJ_ID = ' + convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)

     /* msp_assn_enterprise */ 
     /*
      * note that here we are copying the dummy sharer
      * assignments from the source the the dest db
      * the proj_id is not the key here the 
      * ent_sumprojid is the key here
      */
   set @SQLString = 'delete from ' + @DEST_DB + 
                '.dbo.MSP_ASSN_ENTERPRISE where ASSN_ENTSUMPROJID = ' +
	         convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)
   set @SQLString = 'insert into ' + @DEST_DB + 
                 '.dbo.MSP_ASSN_ENTERPRISE ' +
                 'select * from ' +
                 @SRC_DB +
                 '.dbo.MSP_ASSN_ENTERPRISE where ASSN_ENTSUMPROJID = '
                 + convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)

    /* msp_calendar_data copy  */
   set @SQLString = 'delete from ' + @DEST_DB + 
                '.dbo.MSP_CALENDAR_DATA where PROJ_ID = ' +
	         convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)
   set @SQLString = 'insert into ' + @DEST_DB + 
                 '.dbo.MSP_CALENDAR_DATA ' +
                 'select * from ' +
                 @SRC_DB +
                 '.dbo.MSP_CALENDAR_DATA where PROJ_ID = '
                 + convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)

    /* msp_timephased_data copy  */
   set @SQLString = 'delete from ' + @DEST_DB + 
                '.dbo.MSP_TIMEPHASED_DATA where PROJ_ID = ' +
	         convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)
   set @SQLString = 'insert into ' + @DEST_DB + 
                 '.dbo.MSP_TIMEPHASED_DATA ' +
                 'select * from ' +
                 @SRC_DB +
                 '.dbo.MSP_TIMEPHASED_DATA where PROJ_ID = '
                 + convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)

    /* msp_resource_rates copy  */
   set @SQLString = 'delete from ' + @DEST_DB + 
                '.dbo.MSP_RESOURCE_RATES where PROJ_ID = ' +
	         convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)
   set @SQLString = 'insert into ' + @DEST_DB + 
                 '.dbo.MSP_RESOURCE_RATES ' +
                 'select * from ' +
                 @SRC_DB +
                 '.dbo.MSP_RESOURCE_RATES where PROJ_ID = '
                 + convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)

    /* msp_availability copy  */
   set @SQLString = 'delete from ' + @DEST_DB + 
                '.dbo.MSP_AVAILABILITY where PROJ_ID = ' +
	         convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)
   set @SQLString = 'insert into ' + @DEST_DB + 
                 '.dbo.MSP_AVAILABILITY ' +
                 'select * from ' +
                 @SRC_DB +
                 '.dbo.MSP_AVAILABILITY where PROJ_ID = '
                 + convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)

    /* msp_res_global_binary copy  */
   set @SQLString = 'delete from ' + @DEST_DB + 
                '.dbo.MSP_RES_GLOBAL_BINARY where PROJ_ID = ' +
	         convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)
   set @SQLString = 'insert into ' + @DEST_DB + 
                 '.dbo.MSP_RES_GLOBAL_BINARY ' +
                 'select * from ' +
                 @SRC_DB +
                 '.dbo.MSP_RES_GLOBAL_BINARY where PROJ_ID = '
                 + convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)

    /* msp_field_attributes copy  */
   set @SQLString = 'delete from ' + @DEST_DB + 
                '.dbo.MSP_FIELD_ATTRIBUTES where PROJ_ID = ' +
	         convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)
   set @SQLString = 'insert into ' + @DEST_DB + 
                 '.dbo.MSP_FIELD_ATTRIBUTES ' +
                 'select * from ' +
                 @SRC_DB +
                 '.dbo.MSP_FIELD_ATTRIBUTES where PROJ_ID = '
                 + convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)

    /* msp_attribute_strings copy  */
   set @SQLString = 'delete from ' + @DEST_DB + 
                '.dbo.MSP_ATTRIBUTE_STRINGS where PROJ_ID = ' +
	         convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)
   set @SQLString = 'insert into ' + @DEST_DB + 
                 '.dbo.MSP_ATTRIBUTE_STRINGS ' +
                 'select * from ' +
                 @SRC_DB +
                 '.dbo.MSP_ATTRIBUTE_STRINGS where PROJ_ID = '
                 + convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)

    /* msp_text_fields copy  */
   set @SQLString = 'delete from ' + @DEST_DB + 
                '.dbo.MSP_TEXT_FIELDS where PROJ_ID = ' +
	         convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)
   set @SQLString = 'insert into ' + @DEST_DB + 
                 '.dbo.MSP_TEXT_FIELDS ' +
                 'select * from ' +
                 @SRC_DB +
                 '.dbo.MSP_TEXT_FIELDS where PROJ_ID = '
                 + convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)

    /* msp_number_fields copy  */
   set @SQLString = 'delete from ' + @DEST_DB + 
                '.dbo.MSP_NUMBER_FIELDS where PROJ_ID = ' +
	         convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)
   set @SQLString = 'insert into ' + @DEST_DB + 
                 '.dbo.MSP_NUMBER_FIELDS ' +
                 'select * from ' +
                 @SRC_DB +
                 '.dbo.MSP_NUMBER_FIELDS where PROJ_ID = '
                 + convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)

    /* msp_date_fields copy  */
   set @SQLString = 'delete from ' + @DEST_DB + 
                '.dbo.MSP_DATE_FIELDS where PROJ_ID = ' +
	         convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)
   set @SQLString = 'insert into ' + @DEST_DB + 
                 '.dbo.MSP_DATE_FIELDS ' +
                 'select * from ' +
                 @SRC_DB +
                 '.dbo.MSP_DATE_FIELDS where PROJ_ID = '
                 + convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)

    /* msp_flag_fields copy  */
   set @SQLString = 'delete from ' + @DEST_DB + 
                '.dbo.MSP_FLAG_FIELDS where PROJ_ID = ' +
	         convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)
   set @SQLString = 'insert into ' + @DEST_DB + 
                 '.dbo.MSP_FLAG_FIELDS ' +
                 'select * from ' +
                 @SRC_DB +
                 '.dbo.MSP_FLAG_FIELDS where PROJ_ID = '
                 + convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)

    /* msp_duration_fields copy  */
   set @SQLString = 'delete from ' + @DEST_DB + 
                '.dbo.MSP_DURATION_FIELDS where PROJ_ID = ' +
	         convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)
   set @SQLString = 'insert into ' + @DEST_DB + 
                 '.dbo.MSP_DURATION_FIELDS ' +
                 'select * from ' +
                 @SRC_DB +
                 '.dbo.MSP_DURATION_FIELDS where PROJ_ID = '
                 + convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)

    /* msp_code_fields copy  */
   set @SQLString = 'delete from ' + @DEST_DB + 
                '.dbo.MSP_CODE_FIELDS where PROJ_ID = ' +
	         convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)
   set @SQLString = 'insert into ' + @DEST_DB + 
                 '.dbo.MSP_CODE_FIELDS ' +
                 'select * from ' +
                 @SRC_DB +
                 '.dbo.MSP_CODE_FIELDS where PROJ_ID = '
                 + convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)

    /* msp_task_baselines copy  */
   set @SQLString = 'delete from ' + @DEST_DB + 
                '.dbo.MSP_TASK_BASELINES where PROJ_ID = ' +
	         convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)
   set @SQLString = 'insert into ' + @DEST_DB + 
                 '.dbo.MSP_TASK_BASELINES ' +
                 'select * from ' +
                 @SRC_DB +
                 '.dbo.MSP_TASK_BASELINES where PROJ_ID = '
                 + convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)

    /* msp_resource_baselines copy  */
   set @SQLString = 'delete from ' + @DEST_DB + 
                '.dbo.MSP_RESOURCE_BASELINES where PROJ_ID = ' +
	         convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)
   set @SQLString = 'insert into ' + @DEST_DB + 
                 '.dbo.MSP_RESOURCE_BASELINES ' +
                 'select * from ' +
                 @SRC_DB +
                 '.dbo.MSP_RESOURCE_BASELINES where PROJ_ID = '
                 + convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)

    /* msp_assignment_baselines copy  */
   set @SQLString = 'delete from ' + @DEST_DB + 
                '.dbo.MSP_ASSIGNMENT_BASELINES where PROJ_ID = ' +
	         convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)
   set @SQLString = 'insert into ' + @DEST_DB + 
                 '.dbo.MSP_ASSIGNMENT_BASELINES ' +
                 'select * from ' +
                 @SRC_DB +
                 '.dbo.MSP_ASSIGNMENT_BASELINES where PROJ_ID = '
                 + convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)

    /* msp_mv_fields copy  */
   set @SQLString = 'delete from ' + @DEST_DB + 
                '.dbo.MSP_MV_FIELDS where PROJ_ID = ' +
	         convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)
   set @SQLString = 'insert into ' + @DEST_DB + 
                 '.dbo.MSP_MV_FIELDS ' +
                 'select * from ' +
                 @SRC_DB +
                 '.dbo.MSP_MV_FIELDS where PROJ_ID = '
                 + convert(varchar(255),@SRC_PROJ_ID)
   exec (@SQLString)
	
   if(@NO_WEB_DATA = 1) 
   BEGIN
     goto done
   END
   /* 
    * now we will copy the web tables 
    */
    /* msp_web_projects copy  - enable identity_insert */
   set @SQLString = 'delete from ' + @DEST_DB + 
                '.dbo.MSP_WEB_PROJECTS where WPROJ_ID = ' +
	         convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)
   set IDENTITY_INSERT dbo.MSP_WEB_PROJECTS ON
   set @SQLString = 'insert into ' + @DEST_DB + 
                 '.dbo.MSP_WEB_PROJECTS (WPROJ_ID, PROJ_NAME, PROJ_TIMESTAMP, WPROJ_DESCRIPTION, WLOCAL_PATH, WPATH, WRES_ID, WDSN_ID, WPROJ_DELEG_ALLOWED, WPROJ_IS_NONWORKING, WPROJ_SCOPE, WPROJ_IS_CONSOLIDATED_PROJECT, WPROJ_RES_CAN_DECLINE, WPROJ_DEFAULT_WPROJ_ID, WPROJ_TRACKING_METHOD, WPROJ_LAST_PUB, PROJ_ID, WPROJ_TYPE, OPT_DEF_START_TIME, OPT_DEF_FINISH_TIME, WPROJ_STS_SUBWEB_NAME, WSTS_SERVER_ID, WPROJ_ISSUE_LIST_NAME, WPROJ_RISK_LIST_NAME, WPROJ_ADMINPROJECT) ' +
                 'select WPROJ_ID, PROJ_NAME, PROJ_TIMESTAMP, WPROJ_DESCRIPTION, WLOCAL_PATH, WPATH, WRES_ID, WDSN_ID, WPROJ_DELEG_ALLOWED, WPROJ_IS_NONWORKING, WPROJ_SCOPE, WPROJ_IS_CONSOLIDATED_PROJECT, WPROJ_RES_CAN_DECLINE, WPROJ_DEFAULT_WPROJ_ID, WPROJ_TRACKING_METHOD, WPROJ_LAST_PUB, PROJ_ID, WPROJ_TYPE, OPT_DEF_START_TIME, OPT_DEF_FINISH_TIME, WPROJ_STS_SUBWEB_NAME, WSTS_SERVER_ID, WPROJ_ISSUE_LIST_NAME, WPROJ_RISK_LIST_NAME, WPROJ_ADMINPROJECT from ' +
                 @SRC_DB +
                 '.dbo.MSP_WEB_PROJECTS where WPROJ_ID = '
                 + convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)
   set IDENTITY_INSERT dbo.MSP_WEB_PROJECTS OFF


    /* msp_web_assignments copy  - enable identity_insert */
   set @SQLString = 'delete from ' + @DEST_DB + 
                '.dbo.MSP_WEB_ASSIGNMENTS where WPROJ_ID = ' +
	         convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)
   set IDENTITY_INSERT dbo.MSP_WEB_ASSIGNMENTS ON
   set @SQLString = 'insert into ' + @DEST_DB + 
                 '.dbo.MSP_WEB_ASSIGNMENTS (WASSN_ID, WRES_ID, WASSN_PARENT_ID, WPROJ_ID, WRES_ID_MGR, ASSN_UID, TASK_UID, TASK_ID, TASK_UID_SUMMARY, TASK_NAME, ASSN_START_DATE, ASSN_FINISH_DATE, ASSN_WORK, ASSN_ACT_WORK, ASSN_REM_WORK, WASSN_LAST_WORK, WASSN_COMMENTS, WASSN_HISTORY_NOTES, WASSN_NOTE_STATUS, TASK_IS_MILESTONE, TASK_IS_SUMMARY, WASSN_IS_CONFIRMED, WASSN_MGR_UPDATED, WASSN_CREATED_BY_RES, WASSN_REMOVED_BY_RESOURCE, WASSN_CURRENT_TRACKING_MODE, WASSN_UPDATE_TRACKING_MODE, WASSN_SEND_UPDATE_NEEDED, WASSN_DELETED_IN_PROJ, WASSN_RESOURCE_UPDATE, WASSN_REQUEST_UPDATE, WASSN_UPDATES_ACCEPTED, WASSN_DELEG_ACCEPTED, WASSN_DELEG_APPROVED, WASSN_ACTUALS_PENDING, WASSN_DELEG_PENDING, WASSN_IS_DELEGATED, WASSN_IS_NEW_ASSN, WASSN_UPDATE_STATUS, WASSN_LAST_DELEG_ID, WASSN_PCT_COMP, WASSN_SEND_UPDATE_DATE, WASSN_SUMMARY_PROGRESS, WRES_ID_TEAM_LEAD, WNWRK_ID, WNWRK_ENTRY_ID, RESERVED_DATA1, RESERVED_DATA2, RESERVED_DATA3, ASSN_BOOKING_TYPE, WASSN_ASSIGNED_TO_EXISTING) ' +
                 'select WASSN_ID, WRES_ID, WASSN_PARENT_ID, WPROJ_ID, WRES_ID_MGR, ASSN_UID, TASK_UID, TASK_ID, TASK_UID_SUMMARY, TASK_NAME, ASSN_START_DATE, ASSN_FINISH_DATE, ASSN_WORK, ASSN_ACT_WORK, ASSN_REM_WORK, WASSN_LAST_WORK, WASSN_COMMENTS, WASSN_HISTORY_NOTES, WASSN_NOTE_STATUS, TASK_IS_MILESTONE, TASK_IS_SUMMARY, WASSN_IS_CONFIRMED, WASSN_MGR_UPDATED, WASSN_CREATED_BY_RES, WASSN_REMOVED_BY_RESOURCE, WASSN_CURRENT_TRACKING_MODE, WASSN_UPDATE_TRACKING_MODE, WASSN_SEND_UPDATE_NEEDED, WASSN_DELETED_IN_PROJ, WASSN_RESOURCE_UPDATE, WASSN_REQUEST_UPDATE, WASSN_UPDATES_ACCEPTED, WASSN_DELEG_ACCEPTED, WASSN_DELEG_APPROVED, WASSN_ACTUALS_PENDING, WASSN_DELEG_PENDING, WASSN_IS_DELEGATED, WASSN_IS_NEW_ASSN, WASSN_UPDATE_STATUS, WASSN_LAST_DELEG_ID, WASSN_PCT_COMP, WASSN_SEND_UPDATE_DATE, WASSN_SUMMARY_PROGRESS, WRES_ID_TEAM_LEAD, WNWRK_ID, WNWRK_ENTRY_ID, RESERVED_DATA1, RESERVED_DATA2, RESERVED_DATA3, ASSN_BOOKING_TYPE, WASSN_ASSIGNED_TO_EXISTING  from ' +
                 @SRC_DB +
                 '.dbo.MSP_WEB_ASSIGNMENTS where WPROJ_ID = '
                 + convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)
   set IDENTITY_INSERT dbo.MSP_WEB_ASSIGNMENTS OFF
   
    /* msp_web_messages copy  */
   set @SQLString = 'delete from ' + @DEST_DB + 
                '.dbo.MSP_WEB_MESSAGES where WPROJ_ID = ' +
	         convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)
   set IDENTITY_INSERT dbo.MSP_WEB_MESSAGES ON
   set @SQLString = 'insert into ' + @DEST_DB + 
                 '.dbo.MSP_WEB_MESSAGES (WMSG_ID, WMSG_PROJ_TYPE, WRES_ID_SENDER, WRES_ID_RECEIVER, WMSG_SUBJECT, WMSG_BODY, WMSG_TIME, WPROJ_ID, WMSG_ACTUAL_TYPE, WMSG_PERIOD_START, WMSG_PERIOD_FINISH, WMSG_PERIOD_BROKEN_BY, WMSG_PERIOD_WEEK_STARTS_ON, WMSG_WAS_READ, WMSG_PROCESS_ST, WMSG_RES_CAN_DECLINE) ' +
                 'select WMSG_ID, WMSG_PROJ_TYPE, WRES_ID_SENDER, WRES_ID_RECEIVER, WMSG_SUBJECT, WMSG_BODY, WMSG_TIME, WPROJ_ID, WMSG_ACTUAL_TYPE, WMSG_PERIOD_START, WMSG_PERIOD_FINISH, WMSG_PERIOD_BROKEN_BY, WMSG_PERIOD_WEEK_STARTS_ON, WMSG_WAS_READ, WMSG_PROCESS_ST, WMSG_RES_CAN_DECLINE  from ' +
                 @SRC_DB +
                 '.dbo.MSP_WEB_MESSAGES where WPROJ_ID = '
                 + convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)
   set IDENTITY_INSERT dbo.MSP_WEB_MESSAGES OFF

    /* msp_web_project_workgroup_info copy  */
   set @SQLString = 'delete from ' + @DEST_DB + 
                '.dbo.MSP_WEB_PROJECT_WORKGROUP_INFO where WPROJ_ID = ' +
	         convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)
   set @SQLString = 'insert into ' + @DEST_DB + 
                 '.dbo.MSP_WEB_PROJECT_WORKGROUP_INFO ' +
                 'select * from ' +
                 @SRC_DB +
                 '.dbo.MSP_WEB_PROJECT_WORKGROUP_INFO where WPROJ_ID = '
                 + convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)

    /* msp_web_view_favorites copy  */
   set @SQLString = 'delete from ' + @DEST_DB + 
                '.dbo.MSP_WEB_VIEW_FAVORITES where WPROJ_ID = ' +
	         convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)
   set IDENTITY_INSERT dbo.MSP_WEB_VIEW_FAVORITES ON
   set @SQLString = 'insert into ' + @DEST_DB + 
                 '.dbo.MSP_WEB_VIEW_FAVORITES (WVIEW_FAV_ID, WRES_ID, WVIEW_TYPE, WPROJ_ID, WVIEW_OWC_PIVOT_XML, WVIEW_OWC_CHART_XML, WVIEW_FAV_NAME, WVIEW_FAV_URL, WVIEW_FAV_COLUMN_ORDER, WVIEW_FAV_AUTOFILTER, WSEC_MENU_ID, WVIEW_DEFAULT_SETTINGS, WVIEW_TIMESTAMP) ' +
                 'select WVIEW_FAV_ID, WRES_ID, WVIEW_TYPE, WPROJ_ID, WVIEW_OWC_PIVOT_XML, WVIEW_OWC_CHART_XML, WVIEW_FAV_NAME, WVIEW_FAV_URL, WVIEW_FAV_COLUMN_ORDER, WVIEW_FAV_AUTOFILTER, WSEC_MENU_ID, WVIEW_DEFAULT_SETTINGS, WVIEW_TIMESTAMP  from ' +
                 @SRC_DB +
                 '.dbo.MSP_WEB_VIEW_FAVORITES where WPROJ_ID = '
                 + convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)
   set IDENTITY_INSERT dbo.MSP_WEB_VIEW_FAVORITES OFF

   /*
    * Copying the views tables 
    * 
    */

    /* msp_view_proj_project_std  */
   set @SQLString = 'delete from ' + @DEST_DB + 
                '.dbo.MSP_VIEW_PROJ_PROJECTS_STD where WPROJ_ID = ' +
	         convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)
   set @SQLString = 'insert into ' + @DEST_DB + 
                 '.dbo.MSP_VIEW_PROJ_PROJECTS_STD ' +
                 'select * from ' +
                 @SRC_DB +
                 '.dbo.MSP_VIEW_PROJ_PROJECTS_STD where WPROJ_ID = '
                 + convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)

    /* msp_view_proj_project_ent  */
   set @SQLString = 'delete from ' + @DEST_DB + 
                '.dbo.MSP_VIEW_PROJ_PROJECTS_ENT where WPROJ_ID = ' +
	         convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)
   set @SQLString = 'insert into ' + @DEST_DB + 
                 '.dbo.MSP_VIEW_PROJ_PROJECTS_ENT ' +
                 'select * from ' +
                 @SRC_DB +
                 '.dbo.MSP_VIEW_PROJ_PROJECTS_ENT where WPROJ_ID = '
                 + convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)

    /* msp_view_proj_tasks_std  */
   set @SQLString = 'delete from ' + @DEST_DB + 
                '.dbo.MSP_VIEW_PROJ_TASKS_STD where WPROJ_ID = ' +
	         convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)
   set @SQLString = 'insert into ' + @DEST_DB + 
                 '.dbo.MSP_VIEW_PROJ_TASKS_STD ' +
                 'select * from ' +
                 @SRC_DB +
                 '.dbo.MSP_VIEW_PROJ_TASKS_STD where WPROJ_ID = '
                 + convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)

    /* msp_view_proj_tasks_cf  */
   set @SQLString = 'delete from ' + @DEST_DB + 
                '.dbo.MSP_VIEW_PROJ_TASKS_CF where WPROJ_ID = ' +
	         convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)
   set @SQLString = 'insert into ' + @DEST_DB + 
                 '.dbo.MSP_VIEW_PROJ_TASKS_CF ' +
                 'select * from ' +
                 @SRC_DB +
                 '.dbo.MSP_VIEW_PROJ_TASKS_CF where WPROJ_ID = '
                 + convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)

    /* msp_view_proj_tasks_ent  */
   set @SQLString = 'delete from ' + @DEST_DB + 
                '.dbo.MSP_VIEW_PROJ_TASKS_ENT where WPROJ_ID = ' +
	         convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)
   set @SQLString = 'insert into ' + @DEST_DB + 
                 '.dbo.MSP_VIEW_PROJ_TASKS_ENT ' +
                 'select * from ' +
                 @SRC_DB +
                 '.dbo.MSP_VIEW_PROJ_TASKS_ENT where WPROJ_ID = '
                 + convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)

    /* msp_view_proj_res_std  */
   set @SQLString = 'delete from ' + @DEST_DB + 
                '.dbo.MSP_VIEW_PROJ_RES_STD where WPROJ_ID = ' +
	         convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)
   set @SQLString = 'insert into ' + @DEST_DB + 
                 '.dbo.MSP_VIEW_PROJ_RES_STD ' +
                 'select * from ' +
                 @SRC_DB +
                 '.dbo.MSP_VIEW_PROJ_RES_STD where WPROJ_ID = '
                 + convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)

    /* msp_view_proj_res_cf  */
   set @SQLString = 'delete from ' + @DEST_DB + 
                '.dbo.MSP_VIEW_PROJ_RES_CF where WPROJ_ID = ' +
	         convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)
   set @SQLString = 'insert into ' + @DEST_DB + 
                 '.dbo.MSP_VIEW_PROJ_RES_CF ' +
                 'select * from ' +
                 @SRC_DB +
                 '.dbo.MSP_VIEW_PROJ_RES_CF where WPROJ_ID = '
                 + convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)

    /* msp_view_proj_res_ent  */
   set @SQLString = 'delete from ' + @DEST_DB + 
                '.dbo.MSP_VIEW_PROJ_RES_ENT where WPROJ_ID = ' +
	         convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)
   set @SQLString = 'insert into ' + @DEST_DB + 
                 '.dbo.MSP_VIEW_PROJ_RES_ENT ' +
                 'select * from ' +
                 @SRC_DB +
                 '.dbo.MSP_VIEW_PROJ_RES_ENT where WPROJ_ID = '
                 + convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)

    /* msp_view_proj_assn_std  */
   set @SQLString = 'delete from ' + @DEST_DB + 
                '.dbo.MSP_VIEW_PROJ_ASSN_STD where WPROJ_ID = ' +
	         convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)
   set @SQLString = 'insert into ' + @DEST_DB + 
                 '.dbo.MSP_VIEW_PROJ_ASSN_STD ' +
                 'select * from ' +
                 @SRC_DB +
                 '.dbo.MSP_VIEW_PROJ_ASSN_STD where WPROJ_ID = '
                 + convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)

    /* msp_view_proj_assn_cf  */
   set @SQLString = 'delete from ' + @DEST_DB + 
                '.dbo.MSP_VIEW_PROJ_ASSN_CF where WPROJ_ID = ' +
	         convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)
   set @SQLString = 'insert into ' + @DEST_DB + 
                 '.dbo.MSP_VIEW_PROJ_ASSN_CF ' +
                 'select * from ' +
                 @SRC_DB +
                 '.dbo.MSP_VIEW_PROJ_ASSN_CF where WPROJ_ID = '
                 + convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)

    /* msp_view_proj_assn_ent  */
   set @SQLString = 'delete from ' + @DEST_DB + 
                '.dbo.MSP_VIEW_PROJ_ASSN_ENT where WPROJ_ID = ' +
	         convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)
   set @SQLString = 'insert into ' + @DEST_DB + 
                 '.dbo.MSP_VIEW_PROJ_ASSN_ENT ' +
                 'select * from ' +
                 @SRC_DB +
                 '.dbo.MSP_VIEW_PROJ_ASSN_ENT where WPROJ_ID = '
                 + convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)

    /* msp_view_proj_tasksplits  */
   set @SQLString = 'delete from ' + @DEST_DB + 
                '.dbo.MSP_VIEW_PROJ_TASKSPLITS where WPROJ_ID = ' +
	         convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)
   set @SQLString = 'insert into ' + @DEST_DB + 
                 '.dbo.MSP_VIEW_PROJ_TASKSPLITS ' +
                 'select * from ' +
                 @SRC_DB +
                 '.dbo.MSP_VIEW_PROJ_TASKSPLITS where WPROJ_ID = '
                 + convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)

    /* msp_view_proj_pred  */
   set @SQLString = 'delete from ' + @DEST_DB + 
                '.dbo.MSP_VIEW_PROJ_PRED where WPROJ_ID = ' +
	         convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)
   set @SQLString = 'insert into ' + @DEST_DB + 
                 '.dbo.MSP_VIEW_PROJ_PRED ' +
                 'select * from ' +
                 @SRC_DB +
                 '.dbo.MSP_VIEW_PROJ_PRED where WPROJ_ID = '
                 + convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)

    /* msp_view_proj_succ  */
   set @SQLString = 'delete from ' + @DEST_DB + 
                '.dbo.MSP_VIEW_PROJ_SUCC where WPROJ_ID = ' +
	         convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)
   set @SQLString = 'insert into ' + @DEST_DB + 
                 '.dbo.MSP_VIEW_PROJ_SUCC ' +
                 'select * from ' +
                 @SRC_DB +
                 '.dbo.MSP_VIEW_PROJ_SUCC where WPROJ_ID = '
                 + convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)

    /* msp_view_proj_task_tp_by_day  */
   set @SQLString = 'delete from ' + @DEST_DB + 
                '.dbo.MSP_VIEW_PROJ_TASK_TP_BY_DAY where WPROJ_ID = ' +
	         convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)
   set @SQLString = 'insert into ' + @DEST_DB + 
                 '.dbo.MSP_VIEW_PROJ_TASK_TP_BY_DAY ' +
                 'select * from ' +
                 @SRC_DB +
                 '.dbo.MSP_VIEW_PROJ_TASK_TP_BY_DAY where WPROJ_ID = '
                 + convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)

    /* msp_view_proj_assn_tp_by_day  */
   set @SQLString = 'delete from ' + @DEST_DB + 
                '.dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY where WPROJ_ID = ' +
	         convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)
   set @SQLString = 'insert into ' + @DEST_DB + 
                 '.dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY ' +
                 'select * from ' +
                 @SRC_DB +
                 '.dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY where WPROJ_ID = '
                 + convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)

    /* msp_view_proj_cal  */
   set @SQLString = 'delete from ' + @DEST_DB + 
                '.dbo.MSP_VIEW_PROJ_CAL where WPROJ_ID = ' +
	         convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)
   set @SQLString = 'insert into ' + @DEST_DB + 
                 '.dbo.MSP_VIEW_PROJ_CAL ' +
                 'select * from ' +
                 @SRC_DB +
                 '.dbo.MSP_VIEW_PROJ_CAL where WPROJ_ID = '
                 + convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)

    /* msp_view_proj_cal_data  */
   set @SQLString = 'delete from ' + @DEST_DB + 
                '.dbo.MSP_VIEW_PROJ_CAL_DATA where WPROJ_ID = ' +
	         convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)
   set @SQLString = 'insert into ' + @DEST_DB + 
                 '.dbo.MSP_VIEW_PROJ_CAL_DATA ' +
                 'select * from ' +
                 @SRC_DB +
                 '.dbo.MSP_VIEW_PROJ_CAL_DATA where WPROJ_ID = '
                 + convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)

    /* msp_view_proj_cal_exceptions  */
   set @SQLString = 'delete from ' + @DEST_DB + 
                '.dbo.MSP_VIEW_PROJ_CAL_EXCEPTIONS where WPROJ_ID = ' +
	         convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)
   set @SQLString = 'insert into ' + @DEST_DB + 
                 '.dbo.MSP_VIEW_PROJ_CAL_EXCEPTIONS ' +
                 'select * from ' +
                 @SRC_DB +
                 '.dbo.MSP_VIEW_PROJ_CAL_EXCEPTIONS where WPROJ_ID = '
                 + convert(varchar(255),@DEST_WPROJ_ID)
   exec (@SQLString)


DONE:
   return @RETURN_CODE
go        
