--**************************************************************************************************************************
--		MigrateDataSP
--**************************************************************************************************************************

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[MigrateDataSP]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
EXEC ('DROP PROCEDURE [dbo].[MigrateDataSP]')
GO

CREATE PROCEDURE dbo.MigrateDataSP

	@MainServerName 			varchar(255),
	@MainDatabaseName 		varchar(255),
  @MainSALogin					varchar(255),
	@MainSAPassword				varchar(255),
	@RemoteServerName			varchar(255),
	@RemoteDatabaseName		varchar(255),
  @RemoteSALogin				varchar(255),
	@RemoteSAPassword			varchar(255),
	@FilePath							varchar(255),
	@TableName						varchar(255)
	
AS

  DECLARE @BCPOutString	varchar(1000)
  DECLARE @BCPInString	varchar(1000)
  DECLARE @DeleteString varchar(2000)

  PRINT @MainServerName
  PRINT @MainDatabaseName
  PRINT @RemoteServerName
  PRINT @RemoteDatabaseName
  
  -- Extract the data for the tables being migrated from the Main SQL Server (WinProj or View Tables)
  SELECT @BCPOutString = 'bcp "' + @MainDatabaseName + '..' + @TableName + '" out ' + @FilePath + '\' + @TableName + '.TBL -c -S ' + @MainServerName + ' -U ' + @MainSALogin + ' -P ' + @MainSAPassword + ' -e ' + @FilePath + '\' + @TableName + '_OUT.ERR'
  PRINT @BCPOutString
  EXEC master..xp_cmdshell @BCPOutString

  -- Load the data for the tables being migrated onto the remote SQL Server (WinProj or View Tables)
  SELECT @BCPInString = 'bcp "' + @RemoteDatabaseName + '..' + @TableName + '" in ' + @FilePath + '\' + @TableName + '.TBL -c -S ' + @RemoteServerName + ' -U ' + @RemoteSALogin + ' -P ' + @RemoteSAPassword + ' -e ' + @FilePath + '\' + @TableName + '_IN.ERR'
  PRINT @BCPInString
  EXEC master..xp_cmdshell @BCPInString

RETURN
GO

--**************************************************************************************************************************
--		MigrateViewTablesSP
--**************************************************************************************************************************

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[MigrateViewTablesSP]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
EXEC ('DROP PROCEDURE [dbo].[MigrateViewTablesSP]')
GO

CREATE PROCEDURE dbo.MigrateViewTablesSP

	@MainServerName 			varchar(255),
	@MainDatabaseName 		varchar(255),
  @MainSALogin					varchar(255),
	@MainSAPassword				varchar(255),
	@RemoteServerName			varchar(255),
	@RemoteDatabaseName		varchar(255),
  @RemoteSALogin				varchar(255),
	@RemoteSAPassword			varchar(255),
	@FilePath							varchar(255)

AS

  DECLARE @Query varchar(4000)

  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_VIEW_PROJ_ASSN_CF'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_VIEW_PROJ_ASSN_ENT'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_VIEW_PROJ_ASSN_STD'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_VIEW_PROJ_ASSN_TP_BY_DAY'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_VIEW_PROJ_CAL'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_VIEW_PROJ_CAL_DATA'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_VIEW_PROJ_CAL_EXCEPTIONS'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_VIEW_PROJ_PRED'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_VIEW_PROJ_PROJECTS_ENT'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_VIEW_PROJ_PROJECTS_STD'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_VIEW_PROJ_RES_CF'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_VIEW_PROJ_RES_ENT'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_VIEW_PROJ_RES_STD'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_VIEW_PROJ_SUCC'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_VIEW_PROJ_TASK_TP_BY_DAY'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_VIEW_PROJ_TASKS_CF'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_VIEW_PROJ_TASKS_ENT'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_VIEW_PROJ_TASKS_STD'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_VIEW_PROJ_TASKSPLITS'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_VIEW_RES_CF'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_VIEW_RES_ENT'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_VIEW_RES_STD'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_VIEW_RES_TP_BY_DAY'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_VIEW_SERVICE_FILE_DELETE_PENDING'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_VIEW_SERVICE_NEW_DROPS'

  SELECT @Query = 'INSERT INTO "' +  @RemoteServerName + '"."' + @RemoteDatabaseName + '".dbo.MSP_VIEW_SERVICE_PUBLISH_STATUS
                   SELECT * FROM dbo.MSP_VIEW_SERVICE_PUBLISH_STATUS'
  EXEC (@Query)

GO

--**************************************************************************************************************************
--		MigrateProjTablesSP
--**************************************************************************************************************************

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[MigrateProjTablesSP]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
EXEC ('DROP PROCEDURE [dbo].[MigrateProjTablesSP]')
GO

CREATE PROCEDURE dbo.MigrateProjTablesSP

	@MainServerName 			varchar(255),
	@MainDatabaseName 		varchar(255),
  @MainSALogin					varchar(255),
	@MainSAPassword				varchar(255),
	@RemoteServerName			varchar(255),
	@RemoteDatabaseName		varchar(255),
  @RemoteSALogin				varchar(255),
	@RemoteSAPassword			varchar(255),
	@FilePath							varchar(255)

AS
  
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_ASSIGNMENTS'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_ATTRIBUTE_STRINGS'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_ASSN_ENTERPRISE'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_AVAILABILITY'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_CALENDAR_DATA'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_CALENDARS'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_CODE_FIELDS'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_CONVERSIONS'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_DATE_FIELDS'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_DURATION_FIELDS'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_FIELD_ATTRIBUTES'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_FLAG_FIELDS'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_LINKS'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_NUMBER_FIELDS'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_OUTLINE_CODES'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_PROJ_SECURITY'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_PROJECTS'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_RES_GLOBAL_BINARY'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_RES_SECURITY'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_RESOURCE_RATES'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_RESOURCES'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_STRING_TYPES'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_TASKS'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_TEXT_FIELDS'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_TIMEPHASED_DATA'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_ASSIGNMENT_BASELINES'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_RESOURCE_BASELINES'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_TASK_BASELINES'
  EXEC MigrateDataSP @MainServerName, @MainDatabaseName, @MainSALogin, @MainSAPassword, @RemoteServerName, @RemoteDatabaseName, @RemoteSALogin, @RemoteSAPassword, @FilePath, 'MSP_MV_FIELDS'

GO


