--**************************************************************************************************************************
--		CreateLinkedViewsSP
--**************************************************************************************************************************

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


CREATE PROCEDURE CreateLinkedViewsSP
	@RemoteServerName 		varchar(255),
	@RemoteDatabaseName 		varchar(255),
	@RemoteTableName 		varchar(255)

AS

  -- Drop existing view if it exists locally
  IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[' + @RemoteTableName + ']') AND OBJECTPROPERTY(id, N'IsView') = 1)
  EXEC ('DROP VIEW [dbo].[' + @RemoteTableName + ']')

  -- Drop existing table if it exists locally
  IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[' + @RemoteTableName + ']') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
  EXEC ('DROP TABLE [dbo].[' + @RemoteTableName + ']')

  -- Create view locally on remote table
  EXEC ('CREATE VIEW [dbo].[' + @RemoteTableName + '] AS SELECT * FROM [' + @RemoteServerName + '].[' + @RemoteDatabaseName + '].dbo.[' + @RemoteTableName + ']')

  -- Set permissions
  EXEC ('GRANT SELECT,UPDATE,INSERT,DELETE ON dbo.[' + @RemoteTableName + '] TO MSProjectServerRole')
  EXEC ('GRANT SELECT ON dbo.[' + @RemoteTableName + '] TO MSProjectRole')

RETURN
GO

--**************************************************************************************************************************
--		ExecLinkedViewsSP
--**************************************************************************************************************************

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

CREATE PROCEDURE ExecLinkedViewsSP
	@RemoteServerName 		varchar(255),
	@RemoteDatabaseName 		varchar(255)

AS

  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_VIEW_PROJ_ASSN_CF'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_VIEW_PROJ_ASSN_ENT'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_VIEW_PROJ_ASSN_STD'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_VIEW_PROJ_ASSN_TP_BY_DAY'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_VIEW_PROJ_CAL'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_VIEW_PROJ_CAL_DATA'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_VIEW_PROJ_CAL_EXCEPTIONS'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_VIEW_PROJ_PRED'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_VIEW_PROJ_PROJECTS_ENT'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_VIEW_PROJ_PROJECTS_STD'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_VIEW_PROJ_RES_CF'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_VIEW_PROJ_RES_ENT'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_VIEW_PROJ_RES_STD'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_VIEW_PROJ_SUCC'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_VIEW_PROJ_TASK_TP_BY_DAY'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_VIEW_PROJ_TASKS_CF'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_VIEW_PROJ_TASKS_ENT'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_VIEW_PROJ_TASKS_STD'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_VIEW_PROJ_TASKSPLITS'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_VIEW_RES_CF'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_VIEW_RES_ENT'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_VIEW_RES_STD'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_VIEW_RES_TP_BY_DAY'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_VIEW_SERVICE_FILE_DELETE_PENDING'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_VIEW_SERVICE_NEW_DROPS'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_VIEW_SERVICE_PUBLISH_STATUS'

GO



