--**************************************************************************************************************************
--		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_ASSN_ENTERPRISE'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_ASSIGNMENTS'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_ATTRIBUTE_STRINGS'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_AVAILABILITY'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_CALENDAR_DATA'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_CALENDARS'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_CODE_FIELDS'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_CONVERSIONS'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_DATE_FIELDS'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_DURATION_FIELDS'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_FIELD_ATTRIBUTES'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_FLAG_FIELDS'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_LINKS'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_NUMBER_FIELDS'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_OUTLINE_CODES'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_PROJ_SECURITY'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_PROJECTS'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_RES_GLOBAL_BINARY'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_RES_SECURITY'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_RESOURCE_RATES'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_RESOURCES'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_STRING_TYPES'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_TASKS'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_TEXT_FIELDS'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_TIMEPHASED_DATA'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_ASSIGNMENT_BASELINES'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_RESOURCE_BASELINES'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_TASK_BASELINES'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_MV_FIELDS'
  EXEC CreateLinkedViewsSP @RemoteServerName, @RemoteDatabaseName, 'MSP_RESGLOBAL_SECURITY'

GO



