SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Removing schema binding from [dbo].[v_FarmDescriptions]'
GO
ALTER VIEW [dbo].[v_FarmDescriptions]
AS 
WITH SecurityEnabled (RBSecurityEnabled)
      AS
      (
            SELECT case WHEN OptionValue = 'False' THEN 0
						ELSE 1
						END 
            FROM [dbo].[Options] o
			WHERE o.OptionID = 2000     
      ),
	 IsAdminTable (IsAdmin) 
	 AS
	 (
			SELECT TOP 1 v.IsAdmin 
			FROM [dbo].[v_IsUserAdmin] v
	 ),
	 MySPDocKitRoleFarms 
	 AS
	 (
			SELECT DISTINCT rf.FarmID
			FROM [dbo].[SPDocKitRoleFarms] rf
			JOIN [dbo].[v_MySPDocKitRoles] r ON r.ID = rf.RoleID
	 )
  SELECT fd.[ID]
      ,fd.[Name]
      ,fd.[FarmData]
      ,fd.[IsSnapshot]
      ,fd.[IsManual]
      ,fd.[IsBackup]
      ,fd.[DifferencesDetected]
      ,fd.[CreatedBy]
      ,fd.[LoadingInfoID]
      ,fd.[IsGoodConfiguration]
	  ,fd.[SnapshotId]
	  ,f.[ID] as FarmID
	  ,f.[FarmGuid]
	  ,l.[CreatedOn]
	  ,f.[FarmType]
	  ,l.[LoadDuration]
	  ,l.[MarkedForDeletion]
	  ,l.[InProgress]
  FROM SecurityEnabled sec, IsAdminTable a, [dbo].[FarmDescriptions] fd
  INNER JOIN [dbo].[LoadingInfos] l ON l.ID = fd.LoadingInfoID
  INNER JOIN [dbo].[Farms] f ON f.ID = l.FarmID
  WHERE sec.RBSecurityEnabled = 0 OR a.IsAdmin = 1 OR f.ID IN (SELECT myFarms.FarmID FROM MySPDocKitRoleFarms myFarms)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Removing schema binding from [dbo].[v_WebApplications]'
GO
ALTER VIEW [dbo].[v_WebApplications]
AS 
  WITH SecurityEnabled (RBSecurityEnabled)
      AS
      (
            SELECT case WHEN OptionValue = 'False' THEN 0
						ELSE 1
						END 
            FROM [dbo].[Options] o
			WHERE o.OptionID = 2000     
      ),
	  MyWebApplications
	  AS
	  (
	        SELECT DISTINCT a.WebApplicationID
			FROM [dbo].[v_SPObjectsWithAccess] a
	  )
	  SELECT [ID]
		  ,[Name]
		  ,[SPGuid]
		  ,[Url]
		  ,[Username]
		  ,[Password]
		  ,[Hash]
		  ,[BusinessPlanID]
		  ,[IsPartner]
		  ,[UseOrganizationalAccount]
		  ,w.[FarmID]
		  ,[FarmType]
	  FROM SecurityEnabled sec, [dbo].[v_HasAccessToAllSPObjects] acc, [dbo].[WebApplications] w
	  LEFT OUTER JOIN MyWebApplications a ON a.WebApplicationID = w.ID
	  WHERE sec.RBSecurityEnabled = 0 OR acc.HasAccess = 1 OR a.WebApplicationID IS NOT NULL
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Removing schema binding from [dbo].[v_Farms]'
GO
ALTER VIEW [dbo].[v_Farms]
AS 
  WITH SecurityEnabled (RBSecurityEnabled)
      AS
      (
            SELECT case WHEN OptionValue = 'False' THEN 0
						ELSE 1
						END 
            FROM [dbo].[Options] o
			WHERE o.OptionID = 2000     
      ),
	  MyFarms
	  AS
	  (
	        SELECT DISTINCT a.FarmID
			FROM [dbo].[v_SPObjectsWithAccess] a
	  ),
	 MySPDocKitRoleFarms 
	 AS
	 (
			SELECT DISTINCT rf.FarmID 
			FROM [dbo].[SPDocKitRoleFarms] rf
			JOIN [dbo].[v_MySPDocKitRoles] r ON r.ID = rf.RoleID
	 )
	SELECT [ID]
		  ,[FarmGuid]
		  ,[Name]
		  ,[LSE]
		  ,[LEE]
		  ,[LicenseValidTo]
		  ,[Hash]
		  ,[CentralAdministrationUrl]
		  ,[Version]
		  ,[UsageDatabaseConnectionString]
		  ,[DiagnosticsLogLocation]
		  ,[FarmType]
	  FROM SecurityEnabled sec,  [dbo].[v_HasAccessToAllSPObjects] acc, [dbo].[Farms] f
	  WHERE f.MarkedForDeletion = 0 AND 
	  (sec.RBSecurityEnabled = 0 OR 
		acc.HasAccess = 1 OR 
		f.ID IN (SELECT a.FarmID FROM MyFarms a) OR 
		f.ID IN (SELECT myFarms.FarmID FROM MySPDocKitRoleFarms myFarms))
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Removing schema binding from [EventCollection].[v_SPDocKitUserFarms]'
GO
ALTER VIEW [EventCollection].[v_SPDocKitUserFarms]
AS	
WITH SecurityEnabled (RBSecurityEnabled)
      AS
      (
            SELECT CAST(case WHEN OptionValue = 'False' THEN 0
						ELSE 1
						END AS BIT)
            FROM [dbo].[Options] o
			WHERE o.OptionID = 2000     
      ),
RoleFarms AS (select  u.LoginName, u.PrincipalTypeID,  f.[ID]
					  ,f.[FarmGuid]
					  ,f.[Name]
					  ,f.[LSE]
					  ,f.[Hash]
					  ,f.[CentralAdministrationUrl]
					  ,f.[Version]
					  ,f.[UsageDatabaseConnectionString]
					  ,f.[DiagnosticsLogLocation]
			FROM [dbo].[SPDocKitUsers] u
			INNER JOIN [dbo].[SPDocKitUserRoles] ur ON u.ID = ur.UserID   
			INNER JOIN [dbo].[SPDocKitRoles] r ON ur.RoleID = r.ID   
			INNER JOIN [dbo].[SPDocKitRoleFarms] rf ON rf.RoleID = r.ID
			INNER JOIN [dbo].[Farms] f ON rf.FarmID = f.ID
			UNION 
			SELECT u.LoginName, u.PrincipalTypeID, f.[ID]
					  ,f.[FarmGuid]
					  ,f.[Name]
					  ,f.[LSE]
					  ,f.[Hash]
					  ,f.[CentralAdministrationUrl]
					  ,f.[Version]
					  ,f.[UsageDatabaseConnectionString]
					  ,f.[DiagnosticsLogLocation]
			FROM[dbo].[SPDocKitUsers] u
			INNER JOIN [dbo].[SPDocKitUserRoles] ur ON u.ID = ur.UserID   
			INNER JOIN [dbo].[SPDocKitRoles] r ON ur.RoleID = r.ID   
			CROSS APPLY [dbo].[Farms] AS f
			WHERE r.Name ='Admins'),
AllFarms AS (
 SELECT NULL AS 'LoginName', NULL AS 'PrincipalTypeID',  f.[ID]
					  ,f.[FarmGuid]
					  ,f.[Name]
					  ,f.[LSE]
					  ,f.[Hash]
					  ,f.[CentralAdministrationUrl]
					  ,f.[Version]
					  ,f.[UsageDatabaseConnectionString]
					  ,f.[DiagnosticsLogLocation]
		FROM [dbo].[Farms] AS f)

SELECT  f.LoginName, f.PrincipalTypeID, f.[ID]
					  ,f.[FarmGuid]
					  ,f.[Name]
					  ,f.[LSE]
					  ,f.[Hash]
					  ,f.[CentralAdministrationUrl]
					  ,f.[Version]
					  ,f.[UsageDatabaseConnectionString]
					  ,f.[DiagnosticsLogLocation]
FROM(
	SELECT f.LoginName, f.PrincipalTypeID, f.[ID]
					  ,f.[FarmGuid]
					  ,f.[Name]
					  ,f.[LSE]
					  ,f.[Hash]
					  ,f.[CentralAdministrationUrl]
					  ,f.[Version]
					  ,f.[UsageDatabaseConnectionString]
					  ,f.[DiagnosticsLogLocation]
					  , s.RBSecurityEnabled AS 'ResFlag' FROM RoleFarms AS f CROSS APPLY SecurityEnabled as s
	UNION 
	SELECT f.LoginName, f.PrincipalTypeID, f.[ID]
					  ,f.[FarmGuid]
					  ,f.[Name]
					  ,f.[LSE]
					  ,f.[Hash]
					  ,f.[CentralAdministrationUrl]
					  ,f.[Version]
					  ,f.[UsageDatabaseConnectionString]
					  ,f.[DiagnosticsLogLocation]
					  , ~s.RBSecurityEnabled AS 'ResFlag' FROM AllFarms AS f CROSS APPLY SecurityEnabled as s
	)AS f 
WHERE ResFlag = 1
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping constraints from [EventCollection].[MonitoringServers]'
GO
ALTER TABLE [EventCollection].[MonitoringServers] DROP CONSTRAINT [DF_MonitoringServers_ServerRoleFlags]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping constraints from [EventCollection].[MonitoringServers]'
GO
ALTER TABLE [EventCollection].[MonitoringServers] DROP CONSTRAINT [DF_MonitoringServers_IISMonitoringEnabled]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping constraints from [EventCollection].[MonitoringServers]'
GO
ALTER TABLE [EventCollection].[MonitoringServers] DROP CONSTRAINT [DF_MonitoringServers_HTTPErrorMonitoringEnabled]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping constraints from [EventCollection].[MonitoringServers]'
GO
ALTER TABLE [EventCollection].[MonitoringServers] DROP CONSTRAINT [DF_TerminalServers_PerformanceCountersEnabled]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping [dbo].[InsertOrUpdateSharePointObjects]'
GO
DROP PROCEDURE [dbo].[InsertOrUpdateSharePointObjects]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping types'
GO
DROP TYPE [dbo].[SharePointObjectType]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating types'
GO
CREATE TYPE [dbo].[SharePointObjectType] AS TABLE
(
[Name] [nvarchar] (255) NOT NULL,
[SPObjectTypeID] [int] NOT NULL,
[SPGuid] [uniqueidentifier] NOT NULL,
[ParentID] [int] NOT NULL,
[ParentScopeID] [int] NOT NULL,
[IsRootWeb] [bit] NOT NULL,
[HasUniqueRoleAssignments] [bit] NOT NULL,
[Url] [nvarchar] (2048) NOT NULL,
[ContentTypeID] [int] NULL,
[LastUpdated] [datetime] NULL,
[CreatedOn] [datetime] NULL,
[CreatedBy] [int] NULL,
[ModifiedBy] [int] NULL,
[SharePointContentTypeID] [int] NULL,
[ExternalSharingCapability] [int] NULL,
[AnonymousViewLink] [nvarchar] (2048) NULL,
[AnonymousEditLink] [nvarchar] (2048) NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[AnonymousAccessLinks]'
GO
ALTER TABLE [dbo].[AnonymousAccessLinks] ALTER COLUMN [ViewLink] [nvarchar] (2048) NULL
ALTER TABLE [dbo].[AnonymousAccessLinks] ALTER COLUMN [EditLink] [nvarchar] (2048) NULL
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [SQL].[LoadedObjects]'
GO
ALTER TABLE [SQL].[LoadedObjects] ADD
[DatabaseObjectId] [int] NULL
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [SQL].[LoadedRoles]'
GO
ALTER TABLE [SQL].[LoadedRoles] ADD
[DatabaseRoleId] [int] NULL
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[GetPermissionLevelsForSiteCollection]'
GO
CREATE PROCEDURE [dbo].[GetPermissionLevelsForSiteCollection]
	@SiteID int

	AS
	SET NOCOUNT ON
	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE @Loc_SiteID int

	SELECT @Loc_SiteID = @SiteID

	SELECT r.RoleID, r.Title, r.SiteID  
	FROM Roles r
    WHERE r.SiteID = @Loc_SiteID
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [SQL].[AdministrationGroups]'
GO
CREATE TABLE [SQL].[AdministrationGroups]
(
[AdministrationGroupId] [int] NOT NULL IDENTITY(1, 1),
[Name] [nvarchar] (256) NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_AdministrationGroups] on [SQL].[AdministrationGroups]'
GO
ALTER TABLE [SQL].[AdministrationGroups] ADD CONSTRAINT [PK_AdministrationGroups] PRIMARY KEY CLUSTERED  ([AdministrationGroupId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [SQL].[AdminGroupMembers]'
GO
CREATE TABLE [SQL].[AdminGroupMembers]
(
[AdministrationGroupId] [int] NOT NULL,
[InstanceId] [int] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_AdminGroupMembers] on [SQL].[AdminGroupMembers]'
GO
ALTER TABLE [SQL].[AdminGroupMembers] ADD CONSTRAINT [PK_AdminGroupMembers] PRIMARY KEY CLUSTERED  ([AdministrationGroupId], [InstanceId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [SQL].[Databases]'
GO
CREATE TABLE [SQL].[Databases]
(
[DatabaseId] [int] NOT NULL IDENTITY(1, 1),
[MSDatabaseId] [int] NOT NULL,
[LoadedServerId] [int] NOT NULL,
[DatabaseName] [nvarchar] (max) NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_Databases] on [SQL].[Databases]'
GO
ALTER TABLE [SQL].[Databases] ADD CONSTRAINT [PK_Databases] PRIMARY KEY CLUSTERED  ([DatabaseId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [SQL].[DatabaseObjects]'
GO
CREATE TABLE [SQL].[DatabaseObjects]
(
[DatabaseObjectId] [int] NOT NULL IDENTITY(1, 1),
[DatabaseId] [int] NOT NULL,
[ObjectID] [int] NOT NULL,
[ObjectName] [nvarchar] (max) NOT NULL,
[SchemaName] [nvarchar] (max) NOT NULL,
[CreateTime] [datetime] NOT NULL,
[ObjectType] [int] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_DatabaseObjects] on [SQL].[DatabaseObjects]'
GO
ALTER TABLE [SQL].[DatabaseObjects] ADD CONSTRAINT [PK_DatabaseObjects] PRIMARY KEY CLUSTERED  ([DatabaseObjectId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [SQL].[DatabaseRoles]'
GO
CREATE TABLE [SQL].[DatabaseRoles]
(
[DatabaseRoleId] [int] NOT NULL IDENTITY(1, 1),
[DatabaseId] [int] NOT NULL,
[RoleID] [int] NOT NULL,
[RoleName] [nvarchar] (max) NOT NULL,
[Owner] [nvarchar] (max) NULL,
[CreateDate] [datetime] NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_DatabaseRoles] on [SQL].[DatabaseRoles]'
GO
ALTER TABLE [SQL].[DatabaseRoles] ADD CONSTRAINT [PK_DatabaseRoles] PRIMARY KEY CLUSTERED  ([DatabaseRoleId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [SQL].[LoadedServers]'
GO
CREATE TABLE [SQL].[LoadedServers]
(
[LoadedServerId] [int] NOT NULL IDENTITY(1, 1),
[ServiceTypeId] [int] NOT NULL,
[Name] [nvarchar] (512) NOT NULL,
[Domain] [nvarchar] (512) NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_LoadServers] on [SQL].[LoadedServers]'
GO
ALTER TABLE [SQL].[LoadedServers] ADD CONSTRAINT [PK_LoadServers] PRIMARY KEY CLUSTERED  ([LoadedServerId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [SQL].[ReportComments]'
GO
CREATE TABLE [SQL].[ReportComments]
(
[CommentId] [int] NOT NULL IDENTITY(1, 1),
[ReportId] [nvarchar] (4000) NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_ReportComments] on [SQL].[ReportComments]'
GO
ALTER TABLE [SQL].[ReportComments] ADD CONSTRAINT [PK_ReportComments] PRIMARY KEY CLUSTERED  ([CommentId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating index [IX_ReportId_RowId] on [SQL].[ReportComments]'
GO
CREATE NONCLUSTERED INDEX [IX_ReportId_RowId] ON [SQL].[ReportComments] ([ReportId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [SQL].[ReportCommentsData]'
GO
CREATE TABLE [SQL].[ReportCommentsData]
(
[CommentId] [int] NOT NULL,
[Owner] [nvarchar] (1000) NULL,
[LastUpdated] [datetime] NOT NULL,
[Data] [nvarchar] (max) NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_ReportCommentsData] on [SQL].[ReportCommentsData]'
GO
ALTER TABLE [SQL].[ReportCommentsData] ADD CONSTRAINT [PK_ReportCommentsData] PRIMARY KEY CLUSTERED  ([CommentId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[InsertOrUpdateSharePointObjects]'
GO
CREATE PROCEDURE [dbo].[InsertOrUpdateSharePointObjects]
	@LoadingInfoID int,
	@FarmID int,
	@WebAppID int,
	@SiteID int,
	@SPSiteGuid uniqueidentifier,
	@CreateScopeAssignments bit,
	@TableVariable SharePointObjectType READONLY
AS
BEGIN
	SET NOCOUNT ON	
	
	CREATE TABLE #newSpObjects (
		[ID] [int] NULL,
		[Name] nvarchar(255) NOT NULL,
		[SPObjectTypeID] [int] NOT NULL,
		[SPGuid] [uniqueidentifier] NOT NULL,
		[ParentID] [int] NOT NULL,
		[ParentScopeID] [int] NOT NULL,
		[IsRootWeb] [bit] NOT NULL,
		[HasUniqueRoleAssignments] [bit] NOT NULL,
		[Url] nvarchar(2048) NOT NULL,
		[ContentTypeID] [int] NULL,
		[LastUpdated] [datetime] NULL,
		[CreatedOn] [datetime] NULL,
		[CreatedBy] [int] NULL,
		[ModifiedBy] [int] NULL,
		[ExternalSharingCapability] [int] NULL,
		[AnonymousViewLink] nvarchar (2048)  NULL,
		[AnonymousEditLink] nvarchar (2048)  NULL,
		[SharePointContentTypeID] [int] NULL
	) 

	INSERT INTO #newSpObjects ([ID], [Name], [SPObjectTypeID], [SPGuid], [ParentID], [ParentScopeID], [IsRootWeb], [HasUniqueRoleAssignments], [Url], [ContentTypeID], [LastUpdated], [CreatedOn], [CreatedBy], [ModifiedBy], [ExternalSharingCapability], [AnonymousViewLink], [AnonymousEditLink], [SharePointContentTypeID])
	SELECT spo.ID,
	    tv.[Name],
		tv.[SPObjectTypeID],
		tv.[SPGuid],
		tv.[ParentID],
		tv.[ParentScopeID],
		tv.[IsRootWeb],
		tv.[HasUniqueRoleAssignments],
		tv.[Url],
		tv.[ContentTypeID],
		tv.[LastUpdated],
		tv.[CreatedOn],
		tv.[CreatedBy],
		tv.[ModifiedBy],
		tv.[ExternalSharingCapability],
		tv.[AnonymousViewLink],
		tv.[AnonymousEditLink],
		tv.[SharePointContentTypeID]
	  FROM @TableVariable tv
	  LEFT OUTER JOIN SharePointObjects spo ON spo.SPGuid = tv.SPGuid AND spo.SiteID = @SiteID

	INSERT INTO SharePointObjects ([Name], [SPObjectTypeID], [SPSiteGuid], [SPGuid], [IsRootWeb], [WebAppID], [Url], [SiteID]
           ,[ContentTypeID], [LastUpdated], [CreatedOn], [CreatedBy], [ModifiedBy], [SiteSize], [MaxQuota], [FarmID])
	SELECT n.[Name]
		  ,n.[SPObjectTypeID]
		  ,@SPSiteGuid
		  ,n.[SPGuid]
		  ,n.[IsRootWeb]
		  ,@WebAppID	  		  
		  ,n.[Url]
		  ,@SiteID
		  ,n.[ContentTypeID]
		  ,n.[LastUpdated]
		  ,n.[CreatedOn]
		  ,n.[CreatedBy]
		  ,n.[ModifiedBy]
		  ,NULL
		  ,NULL
		  ,@FarmID
	FROM #newSpObjects n
	WHERE n.ID IS NULL

	UPDATE spo
		SET [Name] = n.[Name]		
		,[Url] = n.[Url]
		,[ContentTypeID] = n.[ContentTypeID]
		,[LastUpdated] = n.[LastUpdated]
		,[ModifiedBy] = n.[ModifiedBy]
	FROM SharePointObjects spo
	JOIN #newSpObjects n ON n.ID = spo.ID
	WHERE n.ID IS NOT NULL

	UPDATE n
	   SET n.[ID] = spo.[ID] 		  
	FROM #newSpObjects n
	JOIN SharePointObjects spo ON spo.SPGuid = n.SPGuid AND spo.SiteID = @SiteID
	WHERE n.[ID] IS NULL

	INSERT INTO [dbo].[SharePointObjectMemberships]
           ([LoadingInfoID]
           ,[ParentID]
           ,[ChildID]
		   ,[ChildObjectTypeID])
	 SELECT @LoadingInfoID, n.ParentID, n.ID, n.SPObjectTypeID
	 FROM #newSpObjects n

	 INSERT INTO [dbo].[SharePointObjectContentType]
           ([LoadingInfoID]
           ,[ContentTypeID]
           ,[SharePointObjectID])
	 SELECT @LoadingInfoID, n.SharePointContentTypeID, n.ID
	 FROM #newSpObjects n
	 WHERE n.SharePointContentTypeID IS NOT NULL

	 INSERT INTO [dbo].[AnonymousAccessLinks]
           ([LoadingInfoID]
           ,[SharePointObjectID]
           ,[ViewLink]
		   ,[EditLink])
	 SELECT @LoadingInfoID, n.ID, n.AnonymousViewLink, n.AnonymousEditLink
	 FROM #newSpObjects n
	 WHERE n.AnonymousViewLink IS NOT NULL OR n.AnonymousEditLink IS NOT NULL

	 INSERT INTO [dbo].[SiteCollectionProperties]
           ([LoadingInfoID]
           ,[SiteCollectionID]
           ,[ExternalSharingCapability])
	 SELECT @LoadingInfoID, n.ID, n.ExternalSharingCapability
	 FROM #newSpObjects n
	 WHERE n.ExternalSharingCapability IS NOT NULL

	 IF @CreateScopeAssignments = 1
	 BEGIN
		
		 INSERT INTO [dbo].[ScopeAssignments]
			   ([LoadingInfoID]
			   ,[ScopeID]
			   ,[SPObjectID]
			   ,[HasUniqueRoleAssignments])
		 SELECT @LoadingInfoID, n.ParentScopeID, n.ID, 0
		 FROM #newSpObjects n
		 WHERE n.HasUniqueRoleAssignments = 0
		 		
		 DECLARE @newScopes TABLE (ScopeID int, SPObjectID int)
		 DECLARE @SpObjectID INT
		 DECLARE @PermissionScopeID INT
		 DECLARE @RowCursor CURSOR
	 
		 SET @RowCursor = CURSOR FOR
		 SELECT n.ID
		 FROM #newSpObjects n
		 WHERE n.HasUniqueRoleAssignments = 1

		 OPEN @RowCursor

		FETCH NEXT FROM @RowCursor INTO @SpObjectID
		WHILE @@FETCH_STATUS = 0
		BEGIN	
			INSERT INTO [dbo].[PermissionScopes] DEFAULT VALUES
			INSERT INTO @newScopes VALUES (SCOPE_IDENTITY(), @SpObjectID)
			FETCH NEXT FROM @RowCursor INTO @SpObjectID	
		END
		CLOSE @RowCursor
		DEALLOCATE @RowCursor
           
		 INSERT INTO [dbo].[ScopeAssignments]
			   ([LoadingInfoID]
			   ,[ScopeID]
			   ,[SPObjectID]
			   ,[HasUniqueRoleAssignments])
		 SELECT @LoadingInfoID, s.ScopeId, s.SPObjectId, 1
		 FROM @newScopes s 

		SELECT n.SPGuid, n.ID, s.ScopeID
		FROM #newSpObjects n
		INNER JOIN [dbo].[ScopeAssignments] s ON s.LoadingInfoID = @LoadingInfoID AND s.SPObjectID = n.ID
	 END
	 ELSE
	 BEGIN
	 	SELECT n.SPGuid, n.ID, -1 as ScopeID
		FROM #newSpObjects n
	 END
END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[WebApplications]'
GO
ALTER TABLE [dbo].[WebApplications] ADD
[MarkedForDeletion] [bit] NOT NULL CONSTRAINT [DF_WebApplications_MarkedForDeletion] DEFAULT ((0))
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[LoadingInfos]'
GO
ALTER TABLE [dbo].[LoadingInfos] ADD
[ADGroupMembers] [bit] NOT NULL CONSTRAINT [DF_LoadingInfos_ADGroupMembers] DEFAULT ((1))
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[Farms]'
GO
ALTER TABLE [dbo].[Farms] ADD
[SPDocKitWorkstationID] [int] NULL
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[ConnectedSiteCollections]'
GO
ALTER TABLE [dbo].[ConnectedSiteCollections] ADD
[TemplateName] [nvarchar] (50) NOT NULL CONSTRAINT [DF_ConnectedSiteCollections_TemplateName] DEFAULT (''),
[Title] [nvarchar] (512) NOT NULL CONSTRAINT [DF_ConnectedSiteCollections_Title] DEFAULT ('')
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [EventCollection].[v_SPDocKitUserFarms]'
GO
ALTER VIEW [EventCollection].[v_SPDocKitUserFarms]
WITH SCHEMABINDING
AS	
WITH SecurityEnabled (RBSecurityEnabled)
      AS
      (
            SELECT CAST(case WHEN OptionValue = 'False' THEN 0
						ELSE 1
						END AS BIT)
            FROM [dbo].[Options] o
			WHERE o.OptionID = 2000     
      ),
RoleFarms AS (select  u.LoginName, u.PrincipalTypeID,  f.[ID]
					  ,f.[FarmGuid]
					  ,f.[Name]
					  ,f.[LSE]
					  ,f.[Hash]
					  ,f.[CentralAdministrationUrl]
					  ,f.[Version]
					  ,f.[UsageDatabaseConnectionString]
					  ,f.[DiagnosticsLogLocation]
			FROM [dbo].[SPDocKitUsers] u
			INNER JOIN [dbo].[SPDocKitUserRoles] ur ON u.ID = ur.UserID   
			INNER JOIN [dbo].[SPDocKitRoles] r ON ur.RoleID = r.ID   
			INNER JOIN [dbo].[SPDocKitRoleFarms] rf ON rf.RoleID = r.ID
			INNER JOIN [dbo].[Farms] f ON rf.FarmID = f.ID
			UNION 
			SELECT u.LoginName, u.PrincipalTypeID, f.[ID]
					  ,f.[FarmGuid]
					  ,f.[Name]
					  ,f.[LSE]
					  ,f.[Hash]
					  ,f.[CentralAdministrationUrl]
					  ,f.[Version]
					  ,f.[UsageDatabaseConnectionString]
					  ,f.[DiagnosticsLogLocation]
			FROM[dbo].[SPDocKitUsers] u
			INNER JOIN [dbo].[SPDocKitUserRoles] ur ON u.ID = ur.UserID   
			INNER JOIN [dbo].[SPDocKitRoles] r ON ur.RoleID = r.ID   
			CROSS APPLY [dbo].[Farms] AS f
			WHERE r.Name ='Admins'),
AllFarms AS (
 SELECT NULL AS 'LoginName', NULL AS 'PrincipalTypeID',  f.[ID]
					  ,f.[FarmGuid]
					  ,f.[Name]
					  ,f.[LSE]
					  ,f.[Hash]
					  ,f.[CentralAdministrationUrl]
					  ,f.[Version]
					  ,f.[UsageDatabaseConnectionString]
					  ,f.[DiagnosticsLogLocation]
		FROM [dbo].[Farms] AS f)

SELECT  f.LoginName, f.PrincipalTypeID, f.[ID]
					  ,f.[FarmGuid]
					  ,f.[Name]
					  ,f.[LSE]
					  ,f.[Hash]
					  ,f.[CentralAdministrationUrl]
					  ,f.[Version]
					  ,f.[UsageDatabaseConnectionString]
					  ,f.[DiagnosticsLogLocation]
FROM(
	SELECT f.LoginName, f.PrincipalTypeID, f.[ID]
					  ,f.[FarmGuid]
					  ,f.[Name]
					  ,f.[LSE]
					  ,f.[Hash]
					  ,f.[CentralAdministrationUrl]
					  ,f.[Version]
					  ,f.[UsageDatabaseConnectionString]
					  ,f.[DiagnosticsLogLocation]
					  , s.RBSecurityEnabled AS 'ResFlag' FROM RoleFarms AS f CROSS APPLY SecurityEnabled as s
	UNION 
	SELECT f.LoginName, f.PrincipalTypeID, f.[ID]
					  ,f.[FarmGuid]
					  ,f.[Name]
					  ,f.[LSE]
					  ,f.[Hash]
					  ,f.[CentralAdministrationUrl]
					  ,f.[Version]
					  ,f.[UsageDatabaseConnectionString]
					  ,f.[DiagnosticsLogLocation]
					  , ~s.RBSecurityEnabled AS 'ResFlag' FROM AllFarms AS f CROSS APPLY SecurityEnabled as s
	)AS f 
WHERE ResFlag = 1
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[v_WebApplications]'
GO
ALTER VIEW [dbo].[v_WebApplications]
WITH SCHEMABINDING
AS 
  WITH SecurityEnabled (RBSecurityEnabled)
      AS
      (
            SELECT case WHEN OptionValue = 'False' THEN 0
						ELSE 1
						END 
            FROM [dbo].[Options] o
			WHERE o.OptionID = 2000     
      ),
	  MyWebApplications
	  AS
	  (
	        SELECT DISTINCT a.WebApplicationID
			FROM [dbo].[v_SPObjectsWithAccess] a
	  )
	  SELECT [ID]
		  ,[Name]
		  ,[SPGuid]
		  ,[Url]
		  ,[Username]
		  ,[Password]
		  ,[Hash]
		  ,[BusinessPlanID]
		  ,[IsPartner]
		  ,[UseOrganizationalAccount]
		  ,webApplications.[FarmID]
		  ,[FarmType]
	  FROM SecurityEnabled sec, [dbo].[v_HasAccessToAllSPObjects] acc, [dbo].[WebApplications] webApplications
	  LEFT OUTER JOIN MyWebApplications webAppsWithAccess ON webAppsWithAccess.WebApplicationID = webApplications.ID
	  WHERE (sec.RBSecurityEnabled = 0 OR acc.HasAccess = 1 OR webAppsWithAccess.WebApplicationID IS NOT NULL) AND webApplications.MarkedForDeletion = 0
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [EventCollection].[MonitoringServers]'
GO
ALTER TABLE [EventCollection].[MonitoringServers] DROP
COLUMN [FarmID],
COLUMN [ServerRoleFlags],
COLUMN [IISMonitoringEnabled],
COLUMN [IISLogsLocation],
COLUMN [HTTPErrorLogMonitoringEnabled],
COLUMN [HTTPErrorLogLocation]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
UPDATE [EventCollection].[MonitoringServers] SET [PerformanceCountersEnabled]=((1)) WHERE [PerformanceCountersEnabled] IS NULL
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
ALTER TABLE [EventCollection].[MonitoringServers] ALTER COLUMN [PerformanceCountersEnabled] [bit] NOT NULL
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[v_Farms]'
GO
ALTER VIEW [dbo].[v_Farms]
WITH SCHEMABINDING
AS 
  WITH SecurityEnabled (RBSecurityEnabled)
      AS
      (
            SELECT case WHEN OptionValue = 'False' THEN 0
						ELSE 1
						END 
            FROM [dbo].[Options] o
			WHERE o.OptionID = 2000     
      ),
	  MyFarms
	  AS
	  (
	        SELECT DISTINCT a.FarmID
			FROM [dbo].[v_SPObjectsWithAccess] a
	  ),
	 MySPDocKitRoleFarms 
	 AS
	 (
			SELECT DISTINCT rf.FarmID 
			FROM [dbo].[SPDocKitRoleFarms] rf
			JOIN [dbo].[v_MySPDocKitRoles] r ON r.ID = rf.RoleID
	 )
	SELECT [ID]
		  ,[FarmGuid]
		  ,[Name]
		  ,[LSE]
		  ,[LEE]
		  ,[LicenseValidTo]
		  ,[Hash]
		  ,[CentralAdministrationUrl]
		  ,[Version]
		  ,[UsageDatabaseConnectionString]
		  ,[DiagnosticsLogLocation]
		  ,[FarmType]
		  ,[SPDocKitWorkstationID]
	  FROM SecurityEnabled sec,  [dbo].[v_HasAccessToAllSPObjects] acc, [dbo].[Farms] f
	  WHERE f.MarkedForDeletion = 0 AND 
	  (sec.RBSecurityEnabled = 0 OR 
		acc.HasAccess = 1 OR 
		f.ID IN (SELECT a.FarmID FROM MyFarms a) OR 
		f.ID IN (SELECT myFarms.FarmID FROM MySPDocKitRoleFarms myFarms))
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [PerformanceCounters].[GroupServersCountersAVGLast]'
GO
ALTER PROCEDURE [PerformanceCounters].[GroupServersCountersAVGLast]
	@StartTime datetime,
	@CounterID int,
	@InstanceID int,
	@TerminalServerID int,
	@CategoryID int
AS
BEGIN
	SET NOCOUNT ON;--DONT_TOUCH_ME	

	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE	@Loc_StartTime datetime
	DECLARE	@Loc_CounterID int
	DECLARE	@Loc_InstanceID int
	DECLARE	@Loc_TerminalServerID int
	DECLARE	@Loc_CategoryID int

	SELECT @Loc_StartTime  = @StartTime,
		  @Loc_CounterID = @CounterID,
		  @Loc_InstanceID = @InstanceID,
		  @Loc_TerminalServerID = @TerminalServerID,
		  @Loc_CategoryID = @CategoryID;--DONT_TOUCH_ME

	WITH counterAVGs AS
	(
		SELECT [PerformanceCounters].[CounterValues].[TerminalServerID],
				[PerformanceCounters].[CounterValues].[CounterID],
				[PerformanceCounters].[CounterValues].[InstanceID],
				Instances.CategoryID,
				y.AverageValue,
				[PerformanceCounters].[CounterValues].Value as LastValue,
				y.MinValue,
				y.MaxValue,
				y.NumberOfRows
	   
			FROM [PerformanceCounters].[CounterValues]
			INNER JOIN [EventCollection].[MonitoringServers] on [EventCollection].[MonitoringServers].[ID] = [PerformanceCounters].[CounterValues].[TerminalServerID]
			INNER JOIN 
			(
				SELECT
					TerminalServerID,
					CounterID,
					InstanceID,
					MAX(CollectionDate) AS MaxDate
				FROM
					[PerformanceCounters].[CounterValues]
				WHERE CollectionDate > DATEADD(d, -1, GETDATE())
				GROUP BY TerminalServerID, CounterID, InstanceID
			) AS x
			ON	[PerformanceCounters].[CounterValues].[TerminalServerID] = x.TerminalServerID 
				AND [PerformanceCounters].[CounterValues].[CounterID] = x.CounterID 
				AND [PerformanceCounters].[CounterValues].[InstanceID] = x.InstanceID 
				AND [PerformanceCounters].[CounterValues].CollectionDate = x.MaxDate

			LEFT JOIN
			(
			SELECT [TerminalServerID],
					CounterID,
					InstanceID,
					AVG([Value]) as AverageValue,
					MIN([Value]) as MinValue,
					Max([Value]) as MaxValue,
					Count(*) as NumberOfRows
				FROM [PerformanceCounters].[CounterValues]
				WHERE CollectionDate > @Loc_StartTime
				GROUP BY [TerminalServerID], [CounterID], [InstanceID] ) as y

			ON [PerformanceCounters].[CounterValues].[TerminalServerID] = y.TerminalServerID 
				AND [PerformanceCounters].[CounterValues].[CounterID] = y.CounterID
				AND [PerformanceCounters].[CounterValues].[InstanceID] = y.InstanceID

			INNER JOIN [PerformanceCounters].Instances ON Instances.InstanceID = CounterValues.InstanceID

			Where [EventCollection].[MonitoringServers].[PerformanceCountersEnabled] = 1
			AND PerformanceCounters.CounterValues.CounterID = @Loc_CounterID
			AND (@Loc_TerminalServerID = 0 OR [EventCollection].[MonitoringServers].[ID] = @Loc_TerminalServerID)
	)

	SELECT  [TerminalServerID],
		[CounterID],
		[InstanceID],
		AverageValue,
		LastValue,
		MinValue,
		MaxValue,
		NumberOfRows	   
	FROM counterAVGs 
	WHERE @Loc_InstanceID > 0 AND InstanceID = @Loc_InstanceID
		
	UNION ALL 

	SELECT  [TerminalServerID],
		[CounterID],
		[InstanceID],
		AverageValue,
		LastValue,
		MinValue,
		MaxValue,
		NumberOfRows	   
	FROM counterAVGs 
	WHERE @Loc_InstanceID = 0 AND CategoryID = @Loc_CategoryID   		

END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [SQL].[LoadedDatabases]'
GO
ALTER TABLE [SQL].[LoadedDatabases] ADD
[DatabaseIdTemp] [int] NULL
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [SQL].[GetLoadedColumnSettings]'
GO
ALTER PROCEDURE [SQL].[GetLoadedColumnSettings]
	@SourceLoadedDatabaseID int,
	@TargetLoadedDatabaseID int
AS
BEGIN
	SELECT ld.LoadedDatabaseID,
	   ld.SQLInstanceName,
	   ld.DatabaseName,
	   lc.LoadedObjectsID,
	   lo.ObjectName,
	   lo.SchemaName,
	   lcs.Type,
	   lcs.ColumnName,
	   lcs.MaxLength,
	   lcs.IsNullable,
	   lcs.IsIdentity,
	   lcs.CollationName,
	   lcs.Precision,
	   lcs.KeyType,
	   lo.ObjectType
	FROM [SQL].LoadedColumns lc
	JOIN [SQL].LoadedObjects lo ON lc.LoadedObjectsID = lo.LoadedObjectsID
	JOIN [SQL].LoadedDatabases ld ON lo.LoadedDatabaseID = ld.LoadedDatabaseID
	JOIN [SQL].[LoadedColumnSettings] lcs ON lc.ColumnSettingsID = lcs.ColumnSettingsID
	WHERE ld.LoadedDatabaseID = @SourceLoadedDatabaseID OR ld.LoadedDatabaseID = @TargetLoadedDatabaseID
END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [SQL].[GetLoadedProgrammParameters]'
GO
ALTER PROCEDURE [SQL].[GetLoadedProgrammParameters]
	@SourceLoadedDatabaseID int,
	@TargetLoadedDatabaseID int

AS
BEGIN
	SELECT ld.LoadedDatabaseID,
		   ld.SQLInstanceName,
		   ld.DatabaseName,
		   lp.LoadedObjectsID,
		   lo.ObjectName,
		   lo.SchemaName,
		   lps.TypeName,
		   lps.ParameterName,
		   lps.IsOutput,
		   lp.ParameterId,
		   lps.HasDefaultValue,
		   lo.ObjectType
	FROM [SQL].LoadedProgrammParameters lp
	JOIN [SQL].LoadedObjects lo ON lp.LoadedObjectsID = lo.LoadedObjectsID
	JOIN [SQL].LoadedDatabases ld ON lo.LoadedDatabaseID = ld.LoadedDatabaseID
	JOIN [SQL].LoadedProgrammParamSettings lps ON lps.ParametersSettingsID = lp.ParametersSettingsID
	WHERE ld.LoadedDatabaseID = @SourceLoadedDatabaseID OR ld.LoadedDatabaseID = @TargetLoadedDatabaseID
END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[GetConnectedSiteCollections]'
GO
ALTER PROCEDURE [dbo].[GetConnectedSiteCollections]
	@FarmID int,
	@WorkstationID int,
	@WebApplicationID int
	AS
	SET NOCOUNT ON	
	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE @Loc_WorkstationID int
	DECLARE	@Loc_FarmID int
	DECLARE	@Loc_WebApplicationID int

	SELECT @Loc_WorkstationID = @WorkstationID,
		   @Loc_FarmID = @FarmID,
		   @Loc_WebApplicationID = @WebApplicationID
		   	
	SELECT sc.Url, sc.TemplateName, sc.Title
	FROM ConnectedSiteCollections sc
	WHERE sc.FarmID = @Loc_FarmID AND sc.SPDocKitWorkstationID = @Loc_WorkstationID AND sc.WebApplicationID = @Loc_WebApplicationID
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[GetDetailedPermissions]'
GO
ALTER PROCEDURE [dbo].[GetDetailedPermissions]
	@ScopeIDs FilterIDsType READONLY,
	@FarmID int,
	@LoadingInfoID int,
	@PrincipalTypeIDs FilterIDsType READONLY,
	@PermissionTypeIDs FilterIDsType READONLY,
	@MaxDepth int = 32,
	@MaxItems int = 100000	
AS
	SET NOCOUNT ON
	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE	@Loc_ScopeIDs FilterIDsType
	DECLARE	@Loc_FarmID int
	DECLARE	@Loc_LoadingInfoID int
	DECLARE	@Loc_PrincipalTypeIDs FilterIDsType
	DECLARE	@Loc_PermissionTypeIDs FilterIDsType

	SELECT @Loc_FarmID  = @FarmID,
		  @Loc_LoadingInfoID = @LoadingInfoID 

	INSERT INTO @Loc_ScopeIDs
	SELECT * FROM @ScopeIDs
	
	INSERT INTO @Loc_PrincipalTypeIDs
	SELECT * FROM @PrincipalTypeIDs
	
	INSERT INTO @Loc_PermissionTypeIDs
	SELECT * FROM @PermissionTypeIDs	
	
    IF OBJECT_ID('tempdb..#OrganisationDescendant') IS NOT NULL
		DROP TABLE #OrganisationDescendant
		
	CREATE TABLE #OrganisationDescendant(
						[PrincipalID] INT, 
						[ContextID] INT,
						[PathLevel] INT
                    )

    DECLARE @Depth          INT
    DECLARE @NumberOfNewRows   INT    

    SET @Depth = 0
    SET @NumberOfNewRows = 1
    WHILE @Depth <= @MaxDepth and @NumberOfNewRows > 0 
	    BEGIN
		    IF @Depth = 0
			    BEGIN
				    INSERT INTO #OrganisationDescendant([PrincipalID],[ContextID], [PathLevel])
				    SELECT principals.PrincipalID, principals.PrincipalID,  0 AS PathLevel
				    FROM dbo.Principals AS principals 
                    JOIN RoleAssignments ON RoleAssignments.PrincipalID=principals.PrincipalID AND RoleAssignments.LoadingInfoID = @Loc_LoadingInfoID	
					JOIN @Loc_ScopeIDs scopes ON scopes.ID = RoleAssignments.ScopeID        
			    END
		    ELSE
			    BEGIN
				    INSERT INTO #OrganisationDescendant([PrincipalID],[ContextID], [PathLevel])
					    SELECT principals.PrincipalID, descendant.ContextID AS ContextId, @Depth
					    FROM PrincipalMemberships 
					    JOIN #OrganisationDescendant as descendant on PrincipalMemberships.GroupID = descendant.PrincipalID and PrincipalMemberships.LoadingInfoID = @Loc_LoadingInfoID and descendant.PathLevel = (@Depth-1)
					    JOIN Principals AS principals on PrincipalMemberships.UserID = principals.PrincipalID                       
                        WHERE descendant.ContextID <> principals.PrincipalID
                END
		    SET @NumberOfNewRows = @@ROWCOUNT								
		    SET @Depth = @Depth + 1                                        
        END;--DONT_TOUCH_ME
							

	WITH baseQuery AS (
		SELECT DISTINCT TOP (@MaxItems)
			scope.ID as [ScopeID],
			org.PrincipalID AS PrincipalID,
			currentPrincipal.DisplayName As PrincipalName,
			currentPrincipal.PrincipalTypeID as PrincipalTypeID,
			currentPrincipalType.Name As PrincipalType,
			currentPrincipalLoaded.AccountEnabled,
			Roles.RoleID AS [RoleID],
			Roles.Title AS RoleTitle,	
			PermissionTypes.Name AS PermissionType,                                            
			org.ContextID AS ParentPrincipalID,     
			currentPrincipal.Email as PrincipalEmail,
			currentPrincipal.Name as PrincipalLogin,
			currentPrincipal.ExternalUser,
			GivenThroughPrincipals.DisplayName AS GivenThroughPrincipalsDisplayName		
		FROM @Loc_ScopeIDs scope
		JOIN RoleAssignments ON RoleAssignments.ScopeID = scope.ID AND RoleAssignments.LoadingInfoID = @Loc_LoadingInfoID
		Join #OrganisationDescendant as org on org.ContextID=RoleAssignments.PrincipalID
		JOIN Roles ON RoleAssignments.RoleID=Roles.RoleID
		JOIN RolePermissions ON Roles.RoleID=RolePermissions.RoleID AND RolePermissions.LoadingInfoID = @Loc_LoadingInfoID
		JOIN PermissionTypes ON RolePermissions.PermissionTypeID=PermissionTypes.PermissionTypeID    
		JOIN Principals GivenThroughPrincipals ON org.ContextID=GivenThroughPrincipals.PrincipalID
		JOIN Principals currentPrincipal ON org.PrincipalID = currentPrincipal.PrincipalID
		JOIN PrincipalTypes currentPrincipalType ON currentPrincipalType.PrincipalTypeID = currentPrincipal.PrincipalTypeID 
		JOIN LoadedPrincipals currentPrincipalLoaded ON currentPrincipalLoaded.PrincipalID = currentPrincipal.PrincipalID AND currentPrincipalLoaded.LoadingInfoID = @Loc_LoadingInfoID

		WHERE (NOT EXISTS (SELECT 1 FROM @Loc_PrincipalTypeIDs) OR EXISTS ( SELECT 1 FROM @Loc_PrincipalTypeIDs pt WHERE pt.ID = currentPrincipal.PrincipalTypeID))
			AND (NOT EXISTS (SELECT 1 FROM @Loc_PermissionTypeIDs) OR EXISTS ( SELECT 1 FROM @Loc_PermissionTypeIDs pt WHERE pt.ID = PermissionTypes.PermissionTypeID))  
	) 

	 SELECT [ScopeID]
			,[PrincipalID]
			,PrincipalName
			,PrincipalTypeID
			,PrincipalType
			,AccountEnabled
			,[RoleID]
			,RoleTitle
			,PermissionType
			,ParentPrincipalID
			,CASE
				WHEN (PrincipalID = ParentPrincipalID) 
				THEN 'Given Directly'
				ELSE GivenThroughPrincipalsDisplayName
			END AS ParentDisplayName,
			PrincipalEmail,
			PrincipalLogin,
			ExternalUser 
	  FROM baseQuery
	  ORDER BY PrincipalName
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[MarkFarmForDeletion]'
GO
ALTER PROCEDURE [dbo].[MarkFarmForDeletion]
	@FarmID int,
	@WebApplicationID int
	AS
	
	SET NOCOUNT ON	

	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE	@Loc_FarmID int
	DECLARE @Loc_WebApplicationID int

	SELECT @Loc_FarmID = @FarmID
	SELECT @Loc_WebApplicationID = @WebApplicationID
		   	
	UPDATE WebApplications
		SET MarkedForDeletion = 1
	WHERE ID = @Loc_WebApplicationID

	DELETE FROM ConnectedSiteCollections WHERE WebApplicationID = @Loc_WebApplicationID

	UPDATE Farms 
		SET MarkedForDeletion = (CASE 
								WHEN EXISTS(SELECT * FROM Farms JOIN WebApplications ON Farms.ID = WebApplications.FarmID WHERE Farms.ID = @Loc_FarmID AND WebApplications.MarkedForDeletion = 0) 
									THEN 0 
								ELSE 1
								END)
	WHERE ID = @Loc_FarmID

	UPDATE LoadingInfos 
		SET MarkedForDeletion = (SELECT Farms.MarkedForDeletion FROM Farms WHERE Farms.ID = @Loc_FarmID)
	WHERE FarmID = @Loc_FarmID
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [PerformanceCounters].[v_AllAgentServerPCFarmAssignments]'
GO
CREATE VIEW [PerformanceCounters].[v_AllAgentServerPCFarmAssignments]
WITH SCHEMABINDING
AS

WITH coreQuery AS
(
	SELECT  afa.AgentID AS AgentDbID
			,a.AgentID AS AgentID
			,fr.FarmID
			,f.FarmGuid AS FarmGuid
			,fr.ServerID
			,fr.ServerRoleFlags
			,CASE WHEN fps.Enabled = 1 AND fr.IsActive = 1 THEN 1 ELSE 0 END AS IsActive
			,Rank() OVER (Partition by ServerID Order BY afa.AgentID) AS AgentRegisteredRanking 
	FROM EventCollection.MonitoringServersFarmRoles fr
	JOIN dbo.Farms f ON fr.FarmID = f.ID
	JOIN PerformanceCounters.FarmPerformanceCountersState fps ON fr.FarmID = fps.FarmID
	JOIN EventCollection.AgentFarmAssignments afa ON afa.FarmID = fr.FarmID
	JOIN EventCollection.Agents a ON a.ID = afa.AgentID
)
SELECT AgentDbID, AgentID, FarmID, FarmGuid, ServerID, ServerRoleFlags, IsActive FROM coreQuery WHERE AgentRegisteredRanking = 1
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [PerformanceCounters].[v_ActiveAgentServerPCFarmAssignments]'
GO
CREATE VIEW [PerformanceCounters].[v_ActiveAgentServerPCFarmAssignments]
WITH SCHEMABINDING
AS

SELECT AgentDbID, AgentID, FarmID, FarmGuid, ServerID, ServerRoleFlags from [PerformanceCounters].[v_AllAgentServerPCFarmAssignments] fa
JOIN [EventCollection].[MonitoringServers] s ON s.ID = fa.ServerID
WHERE s.PerformanceCountersEnabled = 1 AND s.IsActive = 1
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[v_FarmDescriptions]'
GO
ALTER VIEW [dbo].[v_FarmDescriptions]
WITH SCHEMABINDING
AS 
WITH SecurityEnabled (RBSecurityEnabled)
      AS
      (
            SELECT case WHEN OptionValue = 'False' THEN 0
						ELSE 1
						END 
            FROM [dbo].[Options] o
			WHERE o.OptionID = 2000     
      ),
	 IsAdminTable (IsAdmin) 
	 AS
	 (
			SELECT TOP 1 v.IsAdmin 
			FROM [dbo].[v_IsUserAdmin] v
	 ),
	 MySPDocKitRoleFarms 
	 AS
	 (
			SELECT DISTINCT rf.FarmID
			FROM [dbo].[SPDocKitRoleFarms] rf
			JOIN [dbo].[v_MySPDocKitRoles] r ON r.ID = rf.RoleID
	 )
  SELECT fd.[ID]
      ,fd.[Name]
      ,fd.[FarmData]
      ,fd.[IsSnapshot]
      ,fd.[IsManual]
      ,fd.[IsBackup]
      ,fd.[DifferencesDetected]
      ,fd.[CreatedBy]
      ,fd.[LoadingInfoID]
      ,fd.[IsGoodConfiguration]
	  ,fd.[SnapshotId]
	  ,f.[ID] as FarmID
	  ,f.[FarmGuid]
	  ,l.[CreatedOn]
	  ,f.[FarmType]
	  ,l.[LoadDuration]
	  ,l.[MarkedForDeletion]
	  ,l.[InProgress]
  FROM SecurityEnabled sec, IsAdminTable a, [dbo].[FarmDescriptions] fd
  INNER JOIN [dbo].[LoadingInfos] l ON l.ID = fd.LoadingInfoID
  INNER JOIN [dbo].[Farms] f ON f.ID = l.FarmID
  WHERE sec.RBSecurityEnabled = 0 OR a.IsAdmin = 1 OR f.ID IN (SELECT myFarms.FarmID FROM MySPDocKitRoleFarms myFarms)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [SQL].[GetLoadedTableKeys]'
GO
ALTER PROCEDURE [SQL].[GetLoadedTableKeys]
	@SourceLoadedDatabaseID int,
	@TargetLoadedDatabaseID int
AS
BEGIN
	SELECT ld.LoadedDatabaseID,
		   ld.SQLInstanceName,
		   ld.DatabaseName,
		   ltk.LoadedObjectId,
		   lo.ObjectName,
		   lo.SchemaName,
		   ltk.ColumnName,
		   ltk.DeleteAction,
		   ltk.UpdateAction,
		   ltk.KeyName,
		   ltk.KeyType,
		   lto.ObjectName,
		   lto.SchemaName,
		   ltk.TargetTableColumn,
		   lto.LoadedObjectsID,
		   lo.ObjectType
	FROM [SQL].LoadedTableKeys ltk
	JOIN [SQL].LoadedObjects lo ON ltk.LoadedObjectId = lo.LoadedObjectsID
	JOIN [SQL].LoadedDatabases ld ON lo.LoadedDatabaseID = ld.LoadedDatabaseID
	LEFT JOIN [SQL].LoadedObjects lto ON ltk.TargetTableObjectId = lto.LoadedObjectsID
	WHERE ld.LoadedDatabaseID = @SourceLoadedDatabaseID OR ld.LoadedDatabaseID = @TargetLoadedDatabaseID
END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating index [IX_Roles_SiteID] on [dbo].[Roles]'
GO
CREATE NONCLUSTERED INDEX [IX_Roles_SiteID] ON [dbo].[Roles] ([SiteID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding constraints to [EventCollection].[MonitoringServers]'
GO
ALTER TABLE [EventCollection].[MonitoringServers] ADD CONSTRAINT [UC_MonitoringServers_Name] UNIQUE NONCLUSTERED  ([Name])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
ALTER TABLE [EventCollection].[MonitoringServers] ADD CONSTRAINT [DF_TerminalServers_PerformanceCountersEnabled] DEFAULT ((1)) FOR [PerformanceCountersEnabled]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding constraints to [SQL].[AdministrationGroups]'
GO
ALTER TABLE [SQL].[AdministrationGroups] ADD CONSTRAINT [UC_AdministrationGroups_Name] UNIQUE NONCLUSTERED  ([Name])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [dbo].[Farms]'
GO
ALTER TABLE [dbo].[Farms] ADD CONSTRAINT [FK_Farms_SPDocKitWorkstationID] FOREIGN KEY ([SPDocKitWorkstationID]) REFERENCES [dbo].[SPDocKitWorkstations] ([ID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [SQL].[AdminGroupMembers]'
GO
ALTER TABLE [SQL].[AdminGroupMembers] ADD CONSTRAINT [FK_AdminGroupMembers_AdministrationGroups] FOREIGN KEY ([AdministrationGroupId]) REFERENCES [SQL].[AdministrationGroups] ([AdministrationGroupId])
ALTER TABLE [SQL].[AdminGroupMembers] ADD CONSTRAINT [FK_AdminGroupMembers_Instances] FOREIGN KEY ([InstanceId]) REFERENCES [SQL].[Instances] ([InstanceId]) ON DELETE CASCADE
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [SQL].[DatabaseObjects]'
GO
ALTER TABLE [SQL].[DatabaseObjects] ADD CONSTRAINT [FK_DatabaseObjects_Databases] FOREIGN KEY ([DatabaseId]) REFERENCES [SQL].[Databases] ([DatabaseId]) ON DELETE CASCADE
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [SQL].[DatabaseRoles]'
GO
ALTER TABLE [SQL].[DatabaseRoles] ADD CONSTRAINT [FK_DatabaseRoles_Databases] FOREIGN KEY ([DatabaseId]) REFERENCES [SQL].[Databases] ([DatabaseId]) ON DELETE CASCADE
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [SQL].[Databases]'
GO
ALTER TABLE [SQL].[Databases] ADD CONSTRAINT [FK_Databases_LoadedServers] FOREIGN KEY ([LoadedServerId]) REFERENCES [SQL].[LoadedServers] ([LoadedServerId]) ON DELETE CASCADE
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [SQL].[ReportCommentsData]'
GO
ALTER TABLE [SQL].[ReportCommentsData] ADD CONSTRAINT [FK_ReportCommentsData_ReportComments] FOREIGN KEY ([CommentId]) REFERENCES [SQL].[ReportComments] ([CommentId]) ON DELETE CASCADE
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering permissions on [dbo].[GetPermissionLevelsForSiteCollection]'
GO
GRANT EXECUTE ON  [dbo].[GetPermissionLevelsForSiteCollection] TO [spdockit_report_viewer]
GO
UPDATE f SET f.SPDocKitWorkstationID = cs.SPDocKitWorkstationID
FROM dbo.Farms f 
CROSS APPLY (
SELECT TOP 1 SPDocKitWorkstationID from dbo.ConnectedSiteCollections WHERE ConnectedSiteCollections.FarmID = f.ID
) cs
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
UPDATE [PerformanceCounters].[CounterThresholds] 
SET CriticalThreshold = 3072, WarningThreshold = 5120
WHERE CounterID = 31
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
UPDATE Options SET OptionValue = '7.4.1' WHERE OptionID = 14
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
UPDATE Options SET OptionValue = '[7.2,7.3)' WHERE OptionID = 1003
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO

