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'Dropping [SQL].[InsertRoleInfos]'
GO
DROP PROCEDURE [SQL].[InsertRoleInfos]
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 [SQL].[InsertKeyInfos]'
GO
DROP PROCEDURE [SQL].[InsertKeyInfos]
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 [SQL].[InsertColumnInfos]'
GO
DROP PROCEDURE [SQL].[InsertColumnInfos]
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 [SQL].[KeyInfoType]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
DROP TYPE [SQL].[RoleInfoType]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
DROP TYPE [SQL].[ColumnInfoType]
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 [SQL].[KeyInfoType] AS TABLE
(
[LoadedObjectId] [int] NULL,
[KeyName] [nvarchar] (400) NULL,
[ColumnName] [nvarchar] (400) NULL,
[KeyType] [int] NULL,
[DeleteAction] [nvarchar] (max) NULL,
[UpdateAction] [nvarchar] (max) NULL,
[TargetTable] [nvarchar] (max) NULL,
[TargetTableColumn] [nvarchar] (400) NULL,
[TargetTableObjectId] [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
CREATE TYPE [SQL].[RoleInfoType] AS TABLE
(
[RoleID] [int] NULL,
[RoleName] [nvarchar] (max) NULL,
[Owner] [nvarchar] (max) NULL,
[CreateDate] [datetime] NULL,
[ModifyDate] [datetime] NULL,
[AuthenticationType] [nvarchar] (100) 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
CREATE TYPE [SQL].[ColumnInfoType] AS TABLE
(
[LoadedObjectId] [int] NULL,
[ColumnId] [int] NULL,
[ColumnName] [nvarchar] (max) NULL,
[MaxLength] [int] NULL,
[Precision] [int] NULL,
[CollationName] [nvarchar] (max) NULL,
[IsNullable] [bit] NULL,
[IsIdentity] [bit] NULL,
[Type] [nvarchar] (max) NULL,
[KeyType] [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 [PerformanceCounters].[ServerNetworkPerformance]'
GO
ALTER PROCEDURE [PerformanceCounters].[ServerNetworkPerformance]
	@TerminalServerID int,
	@NetworkAdapterID int,
	@StartedOn datetime,
	@EndedOn datetime,
	@PerformanceCounterType int
AS
DECLARE	@NetworkReceivedCounterID INT = 15;--DONT_TOUCH_ME
DECLARE @NetworkSentCounterID INT = 16;--DONT_TOUCH_ME
	BEGIN
		SET NOCOUNT ON
		IF @PerformanceCounterType = @NetworkReceivedCounterID
			BEGIN
				IF (EXISTS (SELECT 1
					FROM
						PerformanceCounters.NetworkReceived
					WHERE 
						CollectionDate >= @StartedOn AND CollectionDate <= @EndedOn AND
						TerminalServerID = @TerminalServerID AND
						NetworkAdapterID = @NetworkAdapterID))
				BEGIN
					SELECT CollectionDate, Value
					FROM
						PerformanceCounters.NetworkReceived
					WHERE 
						CollectionDate >= @StartedOn AND CollectionDate <= @EndedOn AND
						TerminalServerID = @TerminalServerID AND
						NetworkAdapterID = @NetworkAdapterID
					ORDER BY CollectionDate ASC
				END
				ELSE 
				BEGIN 
					SELECT TOP 1 CollectionDate, Value
					FROM PerformanceCounters.NetworkReceived 
					WHERE 
						TerminalServerID = @TerminalServerID AND
						NetworkAdapterID = @NetworkAdapterID
					ORDER BY CollectionDate DESC
				END
			END
		ELSE IF @PerformanceCounterType = @NetworkSentCounterID
			BEGIN
				IF (EXISTS (SELECT 1
					FROM
						PerformanceCounters.NetworkSent
					WHERE 
						CollectionDate >= @StartedOn AND CollectionDate <= @EndedOn AND
						TerminalServerID = @TerminalServerID AND
						NetworkAdapterID = @NetworkAdapterID))
				BEGIN
					SELECT CollectionDate, Value
					FROM
						PerformanceCounters.NetworkSent
					WHERE 
						CollectionDate >= @StartedOn AND CollectionDate <= @EndedOn AND
						TerminalServerID = @TerminalServerID AND
						NetworkAdapterID = @NetworkAdapterID
					ORDER BY CollectionDate ASC
				END
				ELSE 
				BEGIN 
					SELECT TOP 1 CollectionDate, Value
					FROM PerformanceCounters.NetworkSent 
					WHERE 
						TerminalServerID = @TerminalServerID AND
						NetworkAdapterID = @NetworkAdapterID
					ORDER BY CollectionDate DESC
				END
			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 [SQL].[LoadedObjects]'
GO
ALTER TABLE [SQL].[LoadedObjects] ALTER COLUMN [CreateTime] [datetime] NOT NULL
ALTER TABLE [SQL].[LoadedObjects] ALTER COLUMN [ModifyTime] [datetime] 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 [SQL].[LoadedRoles]'
GO
ALTER TABLE [SQL].[LoadedRoles] ALTER COLUMN [CreateDate] [datetime] NULL
ALTER TABLE [SQL].[LoadedRoles] ALTER COLUMN [ModifyDate] [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'Altering [SQL].[InsertViewInfos]'
GO
ALTER PROCEDURE [SQL].[InsertViewInfos]
	@LoadingInfoID int,
	@DatabaseID int,
	@TableType int,
	@TableVariable [SQL].[TableObjectType] READONLY

AS
BEGIN
	SET NOCOUNT ON	
	
	CREATE TABLE #newViewInfos (
		[LoadedObjectsID] INT NULL,
		[ObjectID] INT NOT NULL,
		[ObjectName]  NVARCHAR(MAX) NOT NULL,
		[SchemaName]  NVARCHAR(MAX) NOT NULL,
		[CreateTime]  datetime NOT NULL,
		[ModifyTime] datetime NOT NULL,

		[IsReplicated] bit,
	) 

	INSERT INTO #newViewInfos (
		[LoadedObjectsID],
		[ObjectID],
		[ObjectName],
		[SchemaName],
		[CreateTime],
		[ModifyTime],		
		[IsReplicated])
	SELECT NULL,
		   ti.ObjectId,
		   ti.TableName,
		   ti.SchemaName,
		   ti.CreateTime,
		   ti.ModifyTime,
		   ti.IsReplicated
	FROM @TableVariable ti

	INSERT INTO [SQL].[LoadedObjects](			
		[LoadingInfoID],
		[LoadedDatabaseID],
		[ObjectID],
		[ObjectName],
		[SchemaName],
		[CreateTime],
		[ModifyTime],
		[ObjectType])
	SELECT @LoadingInfoID,
		   @DatabaseID,
		   nti.ObjectID,
		   nti.ObjectName,
		   nti.SchemaName,
		   nti.CreateTime,
		   nti.ModifyTime,
		   @TableType
	FROM #newViewInfos nti

	UPDATE nvi
	   SET nvi.LoadedObjectsID = obj.LoadedObjectsID  
	FROM #newViewInfos nvi
	JOIN [SQL].[LoadedObjects] obj ON nvi.ObjectID = obj.ObjectID AND obj.LoadingInfoID = @LoadingInfoID AND obj.LoadedDatabaseID = @DatabaseID

	SELECT nvi.LoadedObjectsID,
		   nvi.ObjectID
	FROM #newViewInfos nvi
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].[LoadedTableKeys]'
GO
ALTER TABLE [SQL].[LoadedTableKeys] ALTER COLUMN [KeyType] [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].[LoadedColumns]'
GO
ALTER TABLE [SQL].[LoadedColumns] ADD
[KeyType] [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 [PerformanceCounters].[TopNServerCPUData]'
GO
ALTER PROCEDURE [PerformanceCounters].[TopNServerCPUData]
	@start datetime,
	@end datetime,
	@topN INT,
	@terminalServerIDs FilterIDsType READONLY
AS
BEGIN
	SET NOCOUNT ON
	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE	@Loc_start datetime
	DECLARE	@Loc_end datetime
	DECLARE	@Loc_topN int
	DECLARE	@Loc_terminalServerIDs FilterIDsType	

	IF OBJECT_ID('tempdb..#tempTopNCPUData') IS NOT NULL	
	DROP TABLE #tempTopNCPUData

	SELECT @Loc_start  = @start,
		  @Loc_end = @end,
		  @Loc_topN = @topN

	INSERT INTO @Loc_terminalServerIDs
	SELECT * FROM @terminalServerIDs;--DONT_TOUCH_ME
		  
	WITH lastValuesBeforeStart AS
	(
		SELECT T.ID AS TerminalServerID, A.LastCollectionDate AS CollectionDate, 
		(SELECT Value FROM PerformanceCounters.[CPU] WHERE CollectionDate = A.LastCollectionDate AND TerminalServerID = T.ID) AS Value
		FROM @Loc_terminalServerIDs AS T
		CROSS APPLY
		(
			SELECT TOP 1 CollectionDate as LastCollectionDate
			FROM [PerformanceCounters].[CPU]
			WHERE TerminalServerID = T.ID AND CollectionDate < @Loc_start 
			ORDER BY CollectionDate DESC
		) AS A			
		WHERE A.LastCollectionDate IS NOT NULL
	),
	query AS
	(
		SELECT TerminalServerID, CollectionDate, Value, ROW_NUMBER() OVER(PARTITION BY TerminalServerID ORDER BY CollectionDate) AS rnk
		FROM
		( SELECT TerminalServerID, CollectionDate, Value FROM [PerformanceCounters].[CPU]
		  JOIN @Loc_terminalServerIDs AS ts ON ts.ID = TerminalServerID		
		  WHERE CollectionDate>=@Loc_start AND CollectionDate<=@Loc_end AND  Value IS NOT NULL
		  UNION ALL
		  --u konacnici zelimo da se prikaze start koji je dan kao pocetna vrijednost
		  SELECT TerminalServerID, @Loc_start, Value FROM lastValuesBeforeStart
		  WHERE Value IS NOT NULL
		) AS data		
	),	
	weightedQuery AS
	(
		select q1.TerminalServerID, q1.CollectionDate, q1.Value, q1.Value * DATEDIFF(SECOND, q1.CollectionDate, ISNULL(q2.CollectionDate, @Loc_end)) AS weightedValue,  ISNULL(q2.CollectionDate, @Loc_end) UntilDate from query AS q1
		LEFT JOIN query AS q2 ON q1.TerminalServerID = q2.TerminalServerID AND q1.rnk= q2.rnk- 1
	)
	SELECT * INTO #tempTopNCPUData
	FROM weightedQuery;--DONT_TOUCH_ME

	SELECT data.TerminalServerID, CollectionDate, Value FROM #tempTopNCPUData AS data
	JOIN (
		SELECT TOP (@Loc_topN) TerminalServerID, ROW_NUMBER() OVER(Order by SUM(weightedValue)/NULLIF(DATEDIFF(SECOND, MIN(CollectionDate), MAX(UntilDate)), 0) DESC) AS rnk
		FROM #tempTopNCPUData 
		GROUP BY TerminalServerID	
	) AS topN ON topN.TerminalServerID = data.TerminalServerID
	Order by rnk, CollectionDate
	DROP TABLE #tempTopNCPUData
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 [PerformanceCounters].[ServerPerformanceHistorical]'
GO
ALTER PROCEDURE [PerformanceCounters].[ServerPerformanceHistorical]
	@TerminalServerID int,
	@StartedOn datetime,
	@EndedOn datetime,
	@PerformanceCounterType int
AS
DECLARE	@CpuCounterID INT = 2;--DONT_TOUCH_ME
DECLARE @MemoryCounterID INT = 4;--DONT_TOUCH_ME
	BEGIN
		SET NOCOUNT ON
		IF @PerformanceCounterType = @CpuCounterID
			BEGIN
				IF (EXISTS (SELECT 1
					FROM
						PerformanceCounters.CPU PC
					WHERE 
						CollectionDate >= @StartedOn AND CollectionDate<= @EndedOn AND
						TerminalServerID = @TerminalServerID))
				BEGIN
					SELECT CollectionDate, Value
					FROM
						PerformanceCounters.CPU PC
					WHERE 
						CollectionDate >= @StartedOn AND CollectionDate<= @EndedOn AND
						TerminalServerID = @TerminalServerID
					ORDER BY CollectionDate ASC
				END
				ELSE
				BEGIN
					SELECT TOP 1 CollectionDate, Value
					FROM
						PerformanceCounters.CPU PC
					WHERE 
						TerminalServerID = @TerminalServerID
					ORDER BY CollectionDate DESC
				END
			END
		ELSE
			BEGIN
				IF (EXISTS (SELECT 1
					FROM
						PerformanceCounters.Memory PC
					WHERE 
						CollectionDate >= @StartedOn AND CollectionDate<= @EndedOn AND
						TerminalServerID = @TerminalServerID))
				BEGIN
					SELECT	CollectionDate, Value
					FROM
						PerformanceCounters.Memory PC
					WHERE 
						CollectionDate >= @StartedOn AND CollectionDate<= @EndedOn AND
						TerminalServerID = @TerminalServerID
					ORDER BY CollectionDate ASC
				END
				ELSE
				BEGIN
					SELECT TOP 1 CollectionDate, Value
					FROM
						PerformanceCounters.Memory PC
					WHERE 
						TerminalServerID = @TerminalServerID
					ORDER BY CollectionDate DESC
				END
			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 [PerformanceCounters].[ServersDiskLatestPerformance]'
GO
ALTER PROCEDURE [PerformanceCounters].[ServersDiskLatestPerformance]
	@PerformanceCounterID INT,
	@TerminalServerID INT
AS
DECLARE @DiskQueueCounterID INT=27;--DONT_TOUCH_ME
DECLARE @DiskReadCounterID INT=28;--DONT_TOUCH_ME
DECLARE @DiskWriteCounterID INT=29;--DONT_TOUCH_ME
DECLARE @DiskTransferCounterID INT=30;--DONT_TOUCH_ME
DECLARE @DiskFreeSpaceCounterID INT=31;--DONT_TOUCH_ME

BEGIN
	SET NOCOUNT ON
	IF @PerformanceCounterID = @DiskQueueCounterID
	BEGIN
		select TerminalServerID, DiskID, MaxCollectionDate, Value = (select TOP 1(Value) from [PerformanceCounters].[DiskQueue] where CollectionDate = T.MaxCollectionDate AND TerminalServerID = @TerminalServerID AND DiskID = T.DiskID)
		from
		(SELECT TerminalServerID, DiskID, MAX(CollectionDate) AS MaxCollectionDate
		FROM [PerformanceCounters].[DiskQueue] WHERE TerminalServerID = @TerminalServerID
		GROUP BY TerminalServerId, DiskID ) AS T
	END
	ELSE IF @PerformanceCounterID = @DiskReadCounterID
	BEGIN
		select TerminalServerID, DiskID, MaxCollectionDate, Value = (select TOP 1(Value) from [PerformanceCounters].[DiskRead] where CollectionDate = T.MaxCollectionDate AND TerminalServerID = @TerminalServerID AND DiskID = T.DiskID)
		from
		(SELECT TerminalServerID, DiskID, MAX(CollectionDate) AS MaxCollectionDate
		FROM [PerformanceCounters].[DiskRead] WHERE TerminalServerID = @TerminalServerID
		GROUP BY TerminalServerId, DiskID ) AS T
	END
	ELSE IF @PerformanceCounterID = @DiskWriteCounterID
	BEGIN
		select TerminalServerID, DiskID, MaxCollectionDate, Value = (select TOP 1(Value) from [PerformanceCounters].[DiskWrite] where CollectionDate = T.MaxCollectionDate AND TerminalServerID = @TerminalServerID AND DiskID = T.DiskID)
		from
		(SELECT TerminalServerID, DiskID, MAX(CollectionDate) AS MaxCollectionDate
		FROM [PerformanceCounters].[DiskWrite] WHERE TerminalServerID = @TerminalServerID
		GROUP BY TerminalServerId, DiskID ) AS T
	END
	ELSE IF @PerformanceCounterID = @DiskTransferCounterID
	BEGIN
		select TerminalServerID, DiskID, MaxCollectionDate, Value = (select TOP 1(Value) from [PerformanceCounters].[DiskTransfer] where CollectionDate = T.MaxCollectionDate AND TerminalServerID = @TerminalServerID AND DiskID = T.DiskID)
		from
		(SELECT TerminalServerID, DiskID, MAX(CollectionDate) AS MaxCollectionDate
		FROM [PerformanceCounters].[DiskTransfer] WHERE TerminalServerID = @TerminalServerID
		GROUP BY TerminalServerId, DiskID ) AS T
	END
	ELSE IF @PerformanceCounterID = @DiskFreeSpaceCounterID
	BEGIN
		select TerminalServerID, DiskID, MaxCollectionDate, Value = (select TOP 1(Value) from [PerformanceCounters].[DiskFreeSpace] where CollectionDate = T.MaxCollectionDate AND TerminalServerID = @TerminalServerID AND DiskID = T.DiskID)
		from
		(SELECT TerminalServerID, DiskID, MAX(CollectionDate) AS MaxCollectionDate
		FROM [PerformanceCounters].[DiskFreeSpace] WHERE TerminalServerID = @TerminalServerID
		GROUP BY TerminalServerId, DiskID ) AS T
	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 [PerformanceCounters].[TopNServerDiskUsageData]'
GO
ALTER PROCEDURE [PerformanceCounters].[TopNServerDiskUsageData]	
	@topN INT,
	@terminalServerIDs FilterIDsType READONLY
AS
BEGIN
	SET NOCOUNT ON
	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne	
	DECLARE	@Loc_topN int
	DECLARE	@Loc_terminalServerIDs FilterIDsType	

	SELECT @Loc_topN = @topN

	INSERT INTO @Loc_terminalServerIDs
	SELECT * FROM @terminalServerIDs;--DONT_TOUCH_ME
		  
	WITH query AS
	(	
		SELECT D.TerminalServerID AS TerminalServerID, D.DiscID AS DiskID, A.LastCollectionDate, 
		(SELECT Value FROM PerformanceCounters.DiskFreeSpace WHERE CollectionDate = A.LastCollectionDate AND TerminalServerID = D.TerminalServerID AND DiskID = D.DiscID) AS Value
		FROM dbo.Discs AS D
		CROSS APPLY
		(
			SELECT TOP 1 CollectionDate as LastCollectionDate
			FROM [PerformanceCounters].[DiskFreeSpace] AS dfs
			WHERE TerminalServerID = D.TerminalServerID AND dfs.DiskID = D.DiscID AND Value IS NOT NULL
			ORDER BY CollectionDate DESC			
		) AS A	
		WHERE A.LastCollectionDate IS NOT NULL
	)
	
	
		  
	SELECT TOP (@Loc_topN) query.TerminalServerID, DiskID, Value AS FreeSpaceGB, CAST(d.Size AS real) AS TotalSizeGB, CAST(Value/d.Size * 100 AS real) AS FreeSpacePct FROM 
	query
	JOIN dbo.Discs d ON  d.TerminalServerID=query.TerminalServerID AND d.DiscID=query.DiskID
	JOIN @Loc_terminalServerIDs servers ON servers.ID = query.TerminalServerID
	ORDER BY FreeSpacePct
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 [PerformanceCounters].[ServerDiskPerformance]'
GO
ALTER PROCEDURE [PerformanceCounters].[ServerDiskPerformance]
	@TerminalServerID int,
	@DiskID int,
	@StartedOn datetime,
	@EndedOn datetime,
	@PerformanceCounterType int
AS
DECLARE @DiskQueueCounterID INT=27;--DONT_TOUCH_ME
DECLARE @DiskReadCounterID INT=28;--DONT_TOUCH_ME
DECLARE @DiskWriteCounterID INT=29;--DONT_TOUCH_ME
DECLARE @DiskTransferCounterID INT=30;--DONT_TOUCH_ME
DECLARE @DiskFreeSpaceCounterID INT=31;--DONT_TOUCH_ME
	BEGIN
		SET NOCOUNT ON
		IF @PerformanceCounterType = @DiskQueueCounterID
			BEGIN
				IF (EXISTS (SELECT 1
					FROM
						PerformanceCounters.DiskQueue
					WHERE 
						CollectionDate >= @StartedOn AND CollectionDate <= @EndedOn AND
						TerminalServerID = @TerminalServerID AND
						DiskID = @DiskID))
				BEGIN
					SELECT CollectionDate, Value
					FROM
						PerformanceCounters.DiskQueue
					WHERE 
						CollectionDate >= @StartedOn AND CollectionDate <= @EndedOn AND
						TerminalServerID = @TerminalServerID AND
						DiskID = @DiskID
					ORDER BY CollectionDate ASC
				END
				ELSE
				BEGIN
					SELECT TOP 1 CollectionDate, Value
					FROM
						PerformanceCounters.DiskQueue
					WHERE 
						TerminalServerID = @TerminalServerID AND
						DiskID = @DiskID
					ORDER BY CollectionDate DESC
				END
			END
		ELSE IF @PerformanceCounterType = @DiskReadCounterID
			BEGIN
				IF (EXISTS (SELECT 1
					FROM
						PerformanceCounters.DiskRead
					WHERE 
						CollectionDate >= @StartedOn AND CollectionDate <= @EndedOn AND
						TerminalServerID = @TerminalServerID AND
						DiskID = @DiskID))
				BEGIN
					SELECT CollectionDate, Value
					FROM
						PerformanceCounters.DiskRead
					WHERE 
						CollectionDate >= @StartedOn AND CollectionDate <= @EndedOn AND
						TerminalServerID = @TerminalServerID AND
						DiskID = @DiskID
					ORDER BY CollectionDate ASC
				END
				ELSE
				BEGIN
					SELECT TOP 1 CollectionDate, Value
					FROM
						PerformanceCounters.DiskRead
					WHERE 
						TerminalServerID = @TerminalServerID AND
						DiskID = @DiskID
					ORDER BY CollectionDate DESC
				END
			END
		ELSE IF @PerformanceCounterType = @DiskWriteCounterID
			BEGIN
				IF (EXISTS (SELECT 1
					FROM
						PerformanceCounters.DiskWrite
					WHERE 
						CollectionDate >= @StartedOn AND CollectionDate <= @EndedOn AND
						TerminalServerID = @TerminalServerID AND
						DiskID = @DiskID))
				BEGIN
					SELECT CollectionDate, Value
					FROM
						PerformanceCounters.DiskWrite
					WHERE 
						CollectionDate >= @StartedOn AND CollectionDate <= @EndedOn AND
						TerminalServerID = @TerminalServerID AND
						DiskID = @DiskID
					ORDER BY CollectionDate ASC
				END
				ELSE
				BEGIN
					SELECT TOP 1 CollectionDate, Value
					FROM
						PerformanceCounters.DiskWrite
					WHERE 
						TerminalServerID = @TerminalServerID AND
						DiskID = @DiskID
					ORDER BY CollectionDate DESC
				END
			END
		ELSE IF @PerformanceCounterType = @DiskTransferCounterID
			BEGIN
				IF (EXISTS (SELECT 1
					FROM
						PerformanceCounters.DiskTransfer
					WHERE 
						CollectionDate >= @StartedOn AND CollectionDate <= @EndedOn AND
						TerminalServerID = @TerminalServerID AND
						DiskID = @DiskID))
				BEGIN
					SELECT CollectionDate, Value
					FROM
						PerformanceCounters.DiskTransfer
					WHERE 
						CollectionDate >= @StartedOn AND CollectionDate <= @EndedOn AND
						TerminalServerID = @TerminalServerID AND
						DiskID = @DiskID
					ORDER BY CollectionDate ASC
				END
				ELSE
				BEGIN
					SELECT TOP 1 CollectionDate, Value
					FROM
						PerformanceCounters.DiskTransfer
					WHERE 
						TerminalServerID = @TerminalServerID AND
						DiskID = @DiskID
					ORDER BY CollectionDate DESC
				END
			END
		ELSE IF @PerformanceCounterType = @DiskFreeSpaceCounterID
		BEGIN
				IF (EXISTS (SELECT 1
					FROM
						PerformanceCounters.DiskFreeSpace
					WHERE 
						CollectionDate >= @StartedOn AND CollectionDate <= @EndedOn AND
						TerminalServerID = @TerminalServerID AND
						DiskID = @DiskID))
				BEGIN
					SELECT CollectionDate, Value
					FROM
						PerformanceCounters.DiskFreeSpace
					WHERE 
						CollectionDate >= @StartedOn AND CollectionDate <= @EndedOn AND
						TerminalServerID = @TerminalServerID AND
						DiskID = @DiskID
					ORDER BY CollectionDate ASC
				END
				ELSE
				BEGIN
					SELECT TOP 1 CollectionDate, Value
					FROM
						PerformanceCounters.DiskFreeSpace
					WHERE 
						TerminalServerID = @TerminalServerID AND
						DiskID = @DiskID
					ORDER BY CollectionDate DESC
				END
			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 [PerformanceCounters].[ServersLatestPerformance]'
GO
ALTER PROCEDURE [PerformanceCounters].[ServersLatestPerformance]
	@PerformanceCounterID INT,
	@TerminalServerID INT
AS
DECLARE @CPUCounterID INT = 2;--DONT_TOUCH_ME
DECLARE @MemoryCounterID INT = 4;--DONT_TOUCH_ME
BEGIN
	SET NOCOUNT ON
	IF @PerformanceCounterID = @CPUCounterID
	BEGIN
		select TerminalServerID, MaxCollectionDate, Value = (select TOP 1(Value) from [PerformanceCounters].CPU where CollectionDate = T.MaxCollectionDate AND TerminalServerID = @TerminalServerID)
		from
		(select TerminalServerID, MAX(CollectionDate) as MaxCollectionDate
		from [PerformanceCounters].CPU 
		group by TerminalServerID) as T
	END
	ELSE IF @PerformanceCounterID = @MemoryCounterID
	BEGIN
		select TerminalServerID, MaxCollectionDate, Value = (select TOP 1(Value) from [PerformanceCounters].Memory where CollectionDate = T.MaxCollectionDate AND TerminalServerID = @TerminalServerID)
		from
		(select TerminalServerID, MAX(CollectionDate) as MaxCollectionDate
		from [PerformanceCounters].Memory 
		group by TerminalServerID) as T
	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 [PerformanceCounters].[ServersNetworkLatestPerformance]'
GO
ALTER PROCEDURE [PerformanceCounters].[ServersNetworkLatestPerformance]
	@PerformanceCounterID INT,
	@TerminalServerID INT
AS
DECLARE	@NetworkReceivedCounterID INT = 15;--DONT_TOUCH_ME
DECLARE @NetworkSentCounterID INT = 16;--DONT_TOUCH_ME
BEGIN
	SET NOCOUNT ON
	IF @PerformanceCounterID = @NetworkReceivedCounterID
	BEGIN
		select TerminalServerID, NetworkAdapterID, MaxCollectionDate, Value = (select TOP 1(Value) from [PerformanceCounters].[NetworkReceived] where CollectionDate = T.MaxCollectionDate AND TerminalServerID = @TerminalServerID AND NetworkAdapterID = T.NetworkAdapterID)
		from
		(SELECT TerminalServerID, NetworkAdapterID, MAX(CollectionDate) AS MaxCollectionDate
		FROM [PerformanceCounters].[NetworkReceived] WHERE TerminalServerID = @TerminalServerID
		GROUP BY TerminalServerId, NetworkAdapterID ) AS T
	END
	ELSE IF @PerformanceCounterID = @NetworkSentCounterID
	BEGIN
		select TerminalServerID, NetworkAdapterID, MaxCollectionDate, Value = (select TOP 1(Value) from [PerformanceCounters].[NetworkSent] where CollectionDate = T.MaxCollectionDate AND TerminalServerID = @TerminalServerID AND NetworkAdapterID = T.NetworkAdapterID)
		from
		(SELECT TerminalServerID, NetworkAdapterID, MAX(CollectionDate) AS MaxCollectionDate
		FROM [PerformanceCounters].[NetworkSent] WHERE TerminalServerID = @TerminalServerID
		GROUP BY TerminalServerId, NetworkAdapterID ) AS T
	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 [PerformanceCounters].[ServersLastServiceCollectionStartTime]'
GO
ALTER PROCEDURE [PerformanceCounters].[ServersLastServiceCollectionStartTime]
	@terminalServerIDs FilterIDsType READONLY
AS
BEGIN
	SET NOCOUNT ON
	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE	@Loc_terminalServerIDs FilterIDsType	
	INSERT INTO @Loc_terminalServerIDs
	SELECT * FROM @terminalServerIDs;--DONT_TOUCH_ME

	SELECT TerminalServerID,			
	CASE WHEN T1.LastNullDate IS NULL
	THEN (select MIN(CollectionDate) from PerformanceCounters.Memory where TerminalServerID = T1.TerminalServerID)
	ELSE (select MIN(CollectionDate) from PerformanceCounters.Memory where CollectionDate> T1.LastNullDate and TerminalServerID = T1.TerminalServerID and Value is not null)
	END AS ServiceCollectionStartTime
	FROM (
    SELECT T.ID AS TerminalServerID, A.LastNullDate from @Loc_terminalServerIDs AS T
	OUTER APPLY
	(
		SELECT MAX(CollectionDate) as LastNullDate
		FROM PerformanceCounters.Memory	
		WHERE  VALUE IS  NULL AND TerminalServerID = T.ID) A
	) as T1
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 [PerformanceCounters].[GroupServersPerformanceAVGLast]'
GO
ALTER PROCEDURE [PerformanceCounters].[GroupServersPerformanceAVGLast]
	@StartTime datetime,
	@PerformanceCounterID int,
	@TerminalServerID int
AS
DECLARE	@CpuCounterID INT = 2;--DONT_TOUCH_ME
DECLARE @MemoryCounterID INT = 4;--DONT_TOUCH_ME
	BEGIN
		SET NOCOUNT ON
		IF @PerformanceCounterID = @CpuCounterID
			BEGIN
			SELECT @PerformanceCounterID as CounterID,
				[PerformanceCounters].[CPU].[TerminalServerID],
				y.AverageValue,
			   [PerformanceCounters].[CPU].Value as LastValue,
			   y.MinValue,
			   y.MaxValue,
			   y.NumberOfRows
	   
			FROM [PerformanceCounters].[CPU]
			INNER JOIN [EventCollection].[MonitoringServers] ON [EventCollection].[MonitoringServers].[ID] = [PerformanceCounters].[CPU].TerminalServerID AND [PerformanceCounters].[CPU].CollectionDate = (
				SELECT TOP 1
					CollectionDate AS MaxDate
				FROM
					[PerformanceCounters].[CPU]
				WHERE TerminalServerID = [EventCollection].[MonitoringServers].[ID]
				ORDER BY CollectionDate DESC				
			)

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

			ON [PerformanceCounters].[CPU].[TerminalServerID] = y.TerminalServerID
			WHERE [EventCollection].[MonitoringServers].[PerformanceCountersEnabled] = 1 AND (@TerminalServerID = 0 OR [EventCollection].[MonitoringServers].[ID] = @TerminalServerID)
			END
		ELSE IF @PerformanceCounterID = @MemoryCounterID
			BEGIN
			SELECT @PerformanceCounterID as CounterID,
				[PerformanceCounters].[Memory].[TerminalServerID],
				y.AverageValue,
			   [PerformanceCounters].[Memory].Value as LastValue,
			   y.MinValue,
			   y.MaxValue,
			   y.NumberOfRows
	   
			FROM [PerformanceCounters].[Memory]
			INNER JOIN [EventCollection].[MonitoringServers] ON [EventCollection].[MonitoringServers].[ID] = [PerformanceCounters].[Memory].TerminalServerID AND [PerformanceCounters].[Memory].CollectionDate = (
				SELECT TOP 1
					CollectionDate AS MaxDate
				FROM
					[PerformanceCounters].[Memory]
				WHERE TerminalServerID = [EventCollection].[MonitoringServers].[ID]
				ORDER BY CollectionDate DESC				
			)

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

			ON [PerformanceCounters].[Memory].[TerminalServerID] = y.TerminalServerID
			WHERE [EventCollection].[MonitoringServers].[PerformanceCountersEnabled] = 1 AND (@TerminalServerID = 0 OR [EventCollection].[MonitoringServers].[ID] = @TerminalServerID)
			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 [PerformanceCounters].[GroupServersNetworkAVGLast]'
GO
ALTER PROCEDURE [PerformanceCounters].[GroupServersNetworkAVGLast]
	@StartTime datetime,
	@PerformanceCounterID int,
	@TerminalServerID int
AS
DECLARE	@NetworkReceivedCounterID INT = 15;--DONT_TOUCH_ME
DECLARE @NetworkSentCounterID INT = 16;--DONT_TOUCH_ME
	BEGIN
		SET NOCOUNT ON
		IF @PerformanceCounterID = @NetworkReceivedCounterID
			BEGIN
			SELECT @PerformanceCounterID as CounterID,
				[PerformanceCounters].[NetworkReceived].[TerminalServerID],
				[PerformanceCounters].[NetworkReceived].[NetworkAdapterID],
				y.AverageValue,
			   [PerformanceCounters].[NetworkReceived].Value as LastValue,
			   y.MinValue,
			   y.MaxValue,
			   y.NumberOfRows
	   
			FROM [PerformanceCounters].[NetworkReceived]
			INNER JOIN [EventCollection].[MonitoringServers] on [EventCollection].[MonitoringServers].[ID] = [PerformanceCounters].[NetworkReceived].[TerminalServerID]
			INNER JOIN 
			(
				SELECT
					TerminalServerID,
					NetworkAdapterID,
					MAX(CollectionDate) AS MaxDate
				FROM
					[PerformanceCounters].[NetworkReceived]

				GROUP BY  
					TerminalServerID, NetworkAdapterID
			) AS x
			ON	[PerformanceCounters].[NetworkReceived].[TerminalServerID] = x.TerminalServerID AND [PerformanceCounters].[NetworkReceived].[NetworkAdapterID] = x.NetworkAdapterID AND [PerformanceCounters].[NetworkReceived].CollectionDate = x.MaxDate

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

			ON [PerformanceCounters].[NetworkReceived].[TerminalServerID] = y.TerminalServerID AND [PerformanceCounters].[NetworkReceived].[NetworkAdapterID] = y.NetworkAdapterID
			Where [EventCollection].[MonitoringServers].[PerformanceCountersEnabled] = 1 AND (@TerminalServerID = 0 OR [EventCollection].[MonitoringServers].[ID] = @TerminalServerID)
			END
		ELSE IF @PerformanceCounterID = @NetworkSentCounterID
			BEGIN
			SELECT @PerformanceCounterID as CounterID,
				[PerformanceCounters].[NetworkSent].[TerminalServerID],
				[PerformanceCounters].[NetworkSent].[NetworkAdapterID],
				y.AverageValue,
			   [PerformanceCounters].[NetworkSent].Value as LastValue,
			   y.MinValue,
			   y.MaxValue,
			   y.NumberOfRows
	   
			FROM [PerformanceCounters].[NetworkSent]
			INNER JOIN [EventCollection].[MonitoringServers] on [EventCollection].[MonitoringServers].[ID] = [PerformanceCounters].[NetworkSent].[TerminalServerID]
			INNER JOIN 
			(
				SELECT
					TerminalServerID,
					NetworkAdapterID,
					MAX(CollectionDate) AS MaxDate
				FROM
					[PerformanceCounters].[NetworkSent]

				GROUP BY  
					TerminalServerID, NetworkAdapterID
			) AS x
			ON	[PerformanceCounters].[NetworkSent].[TerminalServerID] = x.TerminalServerID AND [PerformanceCounters].[NetworkSent].[NetworkAdapterID] = x.NetworkAdapterID AND [PerformanceCounters].[NetworkSent].CollectionDate = x.MaxDate

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

			ON [PerformanceCounters].[NetworkSent].[TerminalServerID] = y.TerminalServerID AND [PerformanceCounters].[NetworkSent].[NetworkAdapterID] = y.NetworkAdapterID
			Where [EventCollection].[MonitoringServers].[PerformanceCountersEnabled] = 1 AND (@TerminalServerID = 0 OR [EventCollection].[MonitoringServers].[ID] = @TerminalServerID)
			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 [SQL].[InsertTableInfos]'
GO
ALTER PROCEDURE [SQL].[InsertTableInfos]
	@LoadingInfoID int,
	@DatabaseID int,
	@TableType int,
	@TableVariable [SQL].[TableObjectType] READONLY

AS
BEGIN
	SET NOCOUNT ON	
	
	CREATE TABLE #newTableInfos (
		[LoadedObjectsID] INT NULL,
		[ObjectID] INT NOT NULL,
		[ObjectName]  NVARCHAR(MAX) NOT NULL,
		[SchemaName]  NVARCHAR(MAX) NOT NULL,
		[CreateTime]  datetime NOT NULL,
		[ModifyTime] datetime NOT NULL,
		[IsReplicated] bit,
		[RowNumber] int,
		[TotalSpaceKB] int,
		[DataSizeKB] int,
		[IndexSizeKB] int
	) 

	INSERT INTO #newTableInfos (
		[LoadedObjectsID],
		[ObjectID],
		[ObjectName],
		[SchemaName],
		[CreateTime],
		[ModifyTime],		
		[IsReplicated],
		[RowNumber],
		[TotalSpaceKB],
		[DataSizeKB],
		[IndexSizeKB])
	SELECT NULL,
		   ti.ObjectId,
		   ti.TableName,
		   ti.SchemaName,
		   ti.CreateTime,
		   ti.ModifyTime,
		   ti.IsReplicated,
		   ti.RowNumber,
		   ti.TotalSpaceKB,
		   ti.DataSizeKB,
		   ti.IndexSizeKB
	FROM @TableVariable ti

	INSERT INTO [SQL].[LoadedObjects](			
		[LoadingInfoID],
		[LoadedDatabaseID],
		[ObjectID],
		[ObjectName],
		[SchemaName],
		[CreateTime],
		[ModifyTime],
		[ObjectType])
	SELECT @LoadingInfoID,
		   @DatabaseID,
		   nti.ObjectID,
		   nti.ObjectName,
		   nti.SchemaName,
		   nti.CreateTime,
		   nti.ModifyTime,
		   @TableType
	FROM #newTableInfos nti

	UPDATE nti
	   SET nti.LoadedObjectsID = obj.LoadedObjectsID  
	FROM #newTableInfos nti
	JOIN [SQL].[LoadedObjects] obj ON nti.ObjectID = obj.ObjectID AND obj.LoadingInfoID = @LoadingInfoID AND obj.LoadedDatabaseID = @DatabaseID

	INSERT INTO [SQL].[LoadedTables](			
		[LoadedObjectsID],
		[IsReplicated],
		[RowNumber],
		[TotalSpaceKB],
		[DataSizeKB],
		[IndexSizeKB])
	SELECT nti.LoadedObjectsID,
		   nti.IsReplicated,
		   nti.RowNumber,
		   nti.TotalSpaceKB,
		   nti.DataSizeKB,
		   nti.IndexSizeKB
	FROM #newTableInfos nti

	SELECT nti.LoadedObjectsID,
		   nti.ObjectID
	FROM #newTableInfos nti
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].[InsertProgrammibilityInfos]'
GO
ALTER PROCEDURE [SQL].[InsertProgrammibilityInfos]
	@LoadingInfoID int,
	@DatabaseID int,
	@TableType int,
	@ProgrammInfos [SQL].[ProgrammInfoType] READONLY
AS
BEGIN
	SET NOCOUNT ON	
	
	CREATE TABLE #newPrgrammInfos (
		[LoadedObjectsID] INT NULL,
		[ObjectID] INT NOT NULL,
		[ObjectName]  NVARCHAR(MAX) NOT NULL,
		[SchemaName]  NVARCHAR(MAX) NOT NULL,
		[CreateTime]  datetime NOT NULL,
		[ModifyTime] datetime NOT NULL,		
		[ProgramType] int,
		[IsEncrypted] bit
	) 

	INSERT INTO #newPrgrammInfos (
		[LoadedObjectsID],
		[ObjectID],
		[ObjectName],
		[SchemaName],
		[CreateTime],
		[ModifyTime],		
		[ProgramType],
		[IsEncrypted])
	SELECT NULL,
		   pii.ObjectId,
		   pii.ObjectName,
		   pii.SchemaName,
		   pii.CreateTime,
		   pii.ModifyTime,
		   pii.ProgramType,
		   pii.IsEncrypted		  
	FROM @ProgrammInfos pii

	INSERT INTO [SQL].[LoadedObjects](			
		[LoadingInfoID],
		[LoadedDatabaseID],
		[ObjectID],
		[ObjectName],
		[SchemaName],
		[CreateTime],
		[ModifyTime],
		[ObjectType])
	SELECT @LoadingInfoID,
		   @DatabaseID,
		   npi.ObjectID,
		   npi.ObjectName,
		   npi.SchemaName,
		   npi.CreateTime,
		   npi.ModifyTime,
		   @TableType
	FROM #newPrgrammInfos npi

	UPDATE npi
	   SET npi.LoadedObjectsID = obj.LoadedObjectsID  
	FROM #newPrgrammInfos npi
	JOIN [SQL].[LoadedObjects] obj ON npi.ObjectID = obj.ObjectID AND obj.LoadingInfoID = @LoadingInfoID AND obj.LoadedDatabaseID = @DatabaseID

	INSERT INTO [SQL].[LoadedProgrammabilities](			
		[LoadedObjectsID],
		[ProgramType],
		[IsEncrypted])
	SELECT npi.LoadedObjectsID,
			npi.ProgramType,
			npi.IsEncrypted
	FROM #newPrgrammInfos npi

	SELECT npi.LoadedObjectsID,
		   npi.ObjectID
	FROM #newPrgrammInfos npi
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 [SQL].[InsertRoleInfos]'
GO
CREATE PROCEDURE [SQL].[InsertRoleInfos]
	@DatabaseID int,
	@RoleInfos [SQL].[RoleInfoType] READONLY,
	@RoleSecurables [SQL].[RoleSecurablesInfoType] READONLY
AS
BEGIN
	SET NOCOUNT ON	
	
	CREATE TABLE #newRoleInfos (
		[LoadedRoleID] INT NULL,
		[RoleID] int,
		[RoleName] nvarchar(MAX),
		[Owner] nvarchar(MAX),
		[CreateDate] datetime,
		[ModifyDate] datetime,
		[AuthenticationType] nvarchar(100)
	) 

	INSERT INTO #newRoleInfos (
		[LoadedRoleID],
		[RoleID],
		[RoleName],
		[Owner],
		[CreateDate],
		[ModifyDate],
		[AuthenticationType])
	SELECT NULL,
		   [RoleID],
		   [RoleName],
		   [Owner],
		   [CreateDate],
		   [ModifyDate],
           [AuthenticationType]
	FROM @RoleInfos ri

	INSERT INTO [SQL].[LoadedRoles](			
			[LoadedDatabaseID],
			[RoleID],
			[RoleName],
			[Owner],
			[CreateDate],
			[ModifyDate],
			[AuthenticationType])
	SELECT @DatabaseID,
		  [RoleID],
		  [RoleName],
		  [Owner],
		  [CreateDate],
		  [ModifyDate],
          [AuthenticationType]
	FROM #newRoleInfos nri

	UPDATE nri
	   SET nri.LoadedRoleID = roles.LoadedRoleID  
	FROM #newRoleInfos nri
	JOIN [SQL].[LoadedRoles] roles ON nri.RoleID = roles.RoleID AND roles.LoadedDatabaseID = @DatabaseID

	INSERT INTO [SQL].[LoadedRoleSecurables](			
			[LoadedRoleID],
			[LoadedObjectID],
			[PermissionName],
			[PermissionState],
			[PermissionScope])
	SELECT nri.[LoadedRoleID],	
		   rs.[LoadedObjectID],
		   rs.[PermissionName],
		   rs.[PermissionState],
		   rs.[PermissionScope]
	FROM @RoleSecurables rs
	JOIN #newRoleInfos nri ON nri.RoleID = rs.RoleID

	SELECT nri.LoadedRoleID,
		   nri.RoleName
	FROM #newRoleInfos nri
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 [SQL].[InsertKeyInfos]'
GO
CREATE PROCEDURE [SQL].[InsertKeyInfos]
	@TableVariable [SQL].KeyInfoType READONLY
AS
BEGIN
	SET NOCOUNT ON	
	
	INSERT INTO [SQL].[LoadedTableKeys](
			[LoadedObjectId],
			[KeyName],
			[ColumnName],
			[KeyType],
			[DeleteAction],
			[UpdateAction],
			[TargetTable],
			[TargetTableColumn],
			[TargetTableObjectId])
	SELECT  [LoadedObjectId],
			[KeyName],
			[ColumnName],
			[KeyType],
			[DeleteAction],
			[UpdateAction],
			[TargetTable],
			[TargetTableColumn],
			[TargetTableObjectId]
	FROM @TableVariable keyInfo
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 [SQL].[InsertColumnInfos]'
GO
CREATE PROCEDURE [SQL].[InsertColumnInfos]	
	@TableVariable [SQL].ColumnInfoType READONLY
AS
BEGIN
	SET NOCOUNT ON	
	
	INSERT INTO [SQL].[LoadedColumns](
			[LoadedObjectsID],
			[ColumnId],
			[ColumnName],
			[MaxLength],
			[Precision],
			[CollationName],
			[IsNullable],
			[IsIdentity],
			[Type],
			[KeyType])
	SELECT  colInfo.[LoadedObjectId],
		    colInfo.[ColumnId],
			colInfo.[ColumnName],
			colInfo.[MaxLength],
			colInfo.[Precision],
			colInfo.[CollationName],
			colInfo.[IsNullable],
			colInfo.[IsIdentity],
			colInfo.[Type],
			colInfo.[KeyType]
	FROM @TableVariable colInfo
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 [PerformanceCounters].[GroupServersDiskAVGLast]'
GO
ALTER PROCEDURE [PerformanceCounters].[GroupServersDiskAVGLast]
	@StartTime datetime,
	@PerformanceCounterID int,
	@TerminalServerID int
AS
DECLARE @DiskQueueCounterID INT=27;--DONT_TOUCH_ME
DECLARE @DiskReadCounterID INT=28;--DONT_TOUCH_ME
DECLARE @DiskWriteCounterID INT=29;--DONT_TOUCH_ME
DECLARE @DiskTransferCounterID INT=30;--DONT_TOUCH_ME
DECLARE @DiskFreeSpaceCounterID INT=31;--DONT_TOUCH_ME
	BEGIN
		SET NOCOUNT ON
		IF @PerformanceCounterID = @DiskQueueCounterID
			BEGIN
			SELECT @PerformanceCounterID as CounterID,
				[PerformanceCounters].[DiskQueue].[TerminalServerID],
				[PerformanceCounters].[DiskQueue].[DiskID],
				y.AverageValue,
			   [PerformanceCounters].[DiskQueue].Value as LastValue,
			   y.MinValue,
			   y.MaxValue,
			   y.NumberOfRows
	   
			FROM [PerformanceCounters].[DiskQueue]
			INNER JOIN [EventCollection].[MonitoringServers] on [EventCollection].[MonitoringServers].[ID] = [PerformanceCounters].[DiskQueue].[TerminalServerID]
			INNER JOIN 
			(
				SELECT
					TerminalServerID,
					DiskID,
					MAX(CollectionDate) AS MaxDate
				FROM
					[PerformanceCounters].[DiskQueue]

				GROUP BY  
					TerminalServerID, DiskID
			) AS x
			ON	[PerformanceCounters].[DiskQueue].[TerminalServerID] = x.TerminalServerID AND [PerformanceCounters].[DiskQueue].[DiskID] = x.DiskID AND [PerformanceCounters].[DiskQueue].CollectionDate = x.MaxDate

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

			ON [PerformanceCounters].[DiskQueue].[TerminalServerID] = y.TerminalServerID AND [PerformanceCounters].[DiskQueue].[DiskID] = y.DiskID
			Where [EventCollection].[MonitoringServers].[PerformanceCountersEnabled] = 1 AND (@TerminalServerID = 0 OR [EventCollection].[MonitoringServers].[ID] = @TerminalServerID)
			END
		ELSE IF @PerformanceCounterID = @DiskReadCounterID
			BEGIN
			SELECT @PerformanceCounterID as CounterID,
				[PerformanceCounters].[DiskRead].[TerminalServerID],
				[PerformanceCounters].[DiskRead].[DiskID],
				y.AverageValue,
			   [PerformanceCounters].[DiskRead].Value as LastValue,
			   y.MinValue,
			   y.MaxValue,
			   y.NumberOfRows
	   
			FROM [PerformanceCounters].[DiskRead]
			INNER JOIN [EventCollection].[MonitoringServers] on [EventCollection].[MonitoringServers].[ID] = [PerformanceCounters].[DiskRead].[TerminalServerID]
			INNER JOIN 
			(
				SELECT
					TerminalServerID,
					DiskID,
					MAX(CollectionDate) AS MaxDate
				FROM
					[PerformanceCounters].[DiskRead]

				GROUP BY  
					TerminalServerID, DiskID
			) AS x
			ON	[PerformanceCounters].[DiskRead].[TerminalServerID] = x.TerminalServerID AND [PerformanceCounters].[DiskRead].[DiskID] = x.DiskID AND [PerformanceCounters].[DiskRead].CollectionDate = x.MaxDate

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

			ON [PerformanceCounters].[DiskRead].[TerminalServerID] = y.TerminalServerID AND [PerformanceCounters].[DiskRead].[DiskID] = y.DiskID
			Where [EventCollection].[MonitoringServers].[PerformanceCountersEnabled] = 1 AND (@TerminalServerID = 0 OR [EventCollection].[MonitoringServers].[ID] = @TerminalServerID)
			END
		ELSE IF @PerformanceCounterID = @DiskWriteCounterID
			BEGIN
			SELECT @PerformanceCounterID as CounterID,
				[PerformanceCounters].[DiskWrite].[TerminalServerID],
				[PerformanceCounters].[DiskWrite].[DiskID],
				y.AverageValue,
			   [PerformanceCounters].[DiskWrite].Value as LastValue,
			   y.MinValue,
			   y.MaxValue,
			   y.NumberOfRows
	   
			FROM [PerformanceCounters].[DiskWrite]
			INNER JOIN [EventCollection].[MonitoringServers] on [EventCollection].[MonitoringServers].[ID] = [PerformanceCounters].[DiskWrite].[TerminalServerID]
			INNER JOIN 
			(
				SELECT
					TerminalServerID,
					DiskID,
					MAX(CollectionDate) AS MaxDate
				FROM
					[PerformanceCounters].[DiskWrite]

				GROUP BY  
					TerminalServerID, DiskID
			) AS x
			ON	[PerformanceCounters].[DiskWrite].[TerminalServerID] = x.TerminalServerID AND [PerformanceCounters].[DiskWrite].[DiskID] = x.DiskID AND [PerformanceCounters].[DiskWrite].CollectionDate = x.MaxDate

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

			ON [PerformanceCounters].[DiskWrite].[TerminalServerID] = y.TerminalServerID AND [PerformanceCounters].[DiskWrite].[DiskID] = y.DiskID
			Where [EventCollection].[MonitoringServers].[PerformanceCountersEnabled] = 1 AND (@TerminalServerID = 0 OR [EventCollection].[MonitoringServers].[ID] = @TerminalServerID)
			END
		ELSE IF @PerformanceCounterID = @DiskTransferCounterID
			BEGIN
			SELECT @PerformanceCounterID as CounterID,
				[PerformanceCounters].[DiskTransfer].[TerminalServerID],
				[PerformanceCounters].[DiskTransfer].[DiskID],
				y.AverageValue,
			   [PerformanceCounters].[DiskTransfer].Value as LastValue,
			   y.MinValue,
			   y.MaxValue,
			   y.NumberOfRows
	   
			FROM [PerformanceCounters].[DiskTransfer]
			INNER JOIN [EventCollection].[MonitoringServers] on [EventCollection].[MonitoringServers].[ID] = [PerformanceCounters].[DiskTransfer].[TerminalServerID]
			INNER JOIN 
			(
				SELECT
					TerminalServerID,
					DiskID,
					MAX(CollectionDate) AS MaxDate
				FROM
					[PerformanceCounters].[DiskTransfer]

				GROUP BY  
					TerminalServerID, DiskID
			) AS x
			ON	[PerformanceCounters].[DiskTransfer].[TerminalServerID] = x.TerminalServerID AND [PerformanceCounters].[DiskTransfer].[DiskID] = x.DiskID AND [PerformanceCounters].[DiskTransfer].CollectionDate = x.MaxDate

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

			ON [PerformanceCounters].[DiskTransfer].[TerminalServerID] = y.TerminalServerID AND [PerformanceCounters].[DiskTransfer].[DiskID] = y.DiskID
			WHERE [EventCollection].[MonitoringServers].[PerformanceCountersEnabled] = 1 AND (@TerminalServerID = 0 OR [EventCollection].[MonitoringServers].[ID] = @TerminalServerID)
			END
		ELSE IF @PerformanceCounterID = @DiskFreeSpaceCounterID
			BEGIN
			SELECT @PerformanceCounterID as CounterID,
				[PerformanceCounters].[DiskFreeSpace].[TerminalServerID],
				[PerformanceCounters].[DiskFreeSpace].[DiskID],
				y.AverageValue,
			   [PerformanceCounters].[DiskFreeSpace].Value as LastValue,
			   y.MinValue,
			   y.MaxValue,
			   y.NumberOfRows
	   
			FROM [PerformanceCounters].[DiskFreeSpace]
			INNER JOIN [EventCollection].[MonitoringServers] ON [EventCollection].[MonitoringServers].[ID] = [PerformanceCounters].[DiskFreeSpace].[TerminalServerID]
			INNER JOIN 
			(
				SELECT
					TerminalServerID,
					DiskID,
					MAX(CollectionDate) AS MaxDate
				FROM
					[PerformanceCounters].[DiskFreeSpace]

				GROUP BY  
					TerminalServerID, DiskID
			) AS x
			ON	[PerformanceCounters].[DiskFreeSpace].[TerminalServerID] = x.TerminalServerID AND [PerformanceCounters].[DiskFreeSpace].[DiskID] = x.DiskID AND [PerformanceCounters].[DiskFreeSpace].CollectionDate = x.MaxDate

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

			ON [PerformanceCounters].[DiskFreeSpace].[TerminalServerID] = y.TerminalServerID AND [PerformanceCounters].[DiskFreeSpace].[DiskID] = y.DiskID
			Where [EventCollection].[MonitoringServers].[PerformanceCountersEnabled] = 1 AND (@TerminalServerID = 0 OR [EventCollection].[MonitoringServers].[ID] = @TerminalServerID)
			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 [PerformanceCounters].[TopNServerMemoryData]'
GO
ALTER PROCEDURE [PerformanceCounters].[TopNServerMemoryData]
	@start datetime,
	@end datetime,
	@topN INT,
	@terminalServerIDs FilterIDsType READONLY
AS
BEGIN
	SET NOCOUNT ON
	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE	@Loc_start datetime
	DECLARE	@Loc_end datetime
	DECLARE	@Loc_topN int
	DECLARE	@Loc_terminalServerIDs FilterIDsType	

	IF OBJECT_ID('tempdb..#tempTopNMemoryData') IS NOT NULL	
	DROP TABLE #tempTopNMemoryData

	SELECT @Loc_start  = @start,
		  @Loc_end = @end,
		  @Loc_topN = @topN

	INSERT INTO @Loc_terminalServerIDs
	SELECT * FROM @terminalServerIDs;--DONT_TOUCH_ME

	WITH lastValuesBeforeStart AS
	(
		SELECT T.ID AS TerminalServerID, A.LastCollectionDate AS CollectionDate, 
		(SELECT Value FROM PerformanceCounters.[Memory] WHERE CollectionDate = A.LastCollectionDate AND TerminalServerID = T.ID) AS Value
		FROM @Loc_terminalServerIDs AS T
		CROSS APPLY
		(
			SELECT TOP 1 CollectionDate as LastCollectionDate
			FROM [PerformanceCounters].[Memory]
			WHERE TerminalServerID = T.ID AND CollectionDate < @Loc_start 
			ORDER BY CollectionDate DESC
		) AS A			
		WHERE A.LastCollectionDate IS NOT NULL
	),
	query AS
	(
		SELECT TerminalServerID, CollectionDate, Value/servers.TotalPhysicalMemory *100 AS PctUsed, ROW_NUMBER() OVER(PARTITION BY TerminalServerID ORDER BY CollectionDate) AS rnk
		FROM
		( SELECT TerminalServerID, CollectionDate, Value FROM [PerformanceCounters].[Memory]
		  JOIN @Loc_terminalServerIDs AS ts ON ts.ID = TerminalServerID		
		  WHERE CollectionDate>=@Loc_start AND CollectionDate<=@Loc_end AND  Value IS NOT NULL
		  UNION ALL
		  --u konacnici zelimo da se prikaze start koji je dan kao pocetna vrijednost
		  SELECT TerminalServerID, @Loc_start, Value FROM lastValuesBeforeStart
		  WHERE Value IS NOT NULL
		) AS data
		JOIN EventCollection.MonitoringServers as servers ON servers.ID = data.TerminalServerID
	),	
	weightedQuery AS
	(
		select q1.TerminalServerID, q1.CollectionDate, q1.PctUsed, q1.PctUsed * DATEDIFF(SECOND, q1.CollectionDate, ISNULL(q2.CollectionDate, @Loc_end)) AS weightedValue,  ISNULL(q2.CollectionDate, @Loc_end) UntilDate from query AS q1
		LEFT JOIN query AS q2 ON q1.TerminalServerID = q2.TerminalServerID AND q1.rnk= q2.rnk- 1
	)
	SELECT * INTO #tempTopNMemoryData
	FROM weightedQuery;--DONT_TOUCH_ME

	
	SELECT data.TerminalServerID, CollectionDate, PctUsed AS Value FROM #tempTopNMemoryData AS data
	JOIN (
		SELECT TOP (@Loc_topN) TerminalServerID, ROW_NUMBER() OVER(Order by SUM(weightedValue)/NULLIF(DATEDIFF(SECOND, MIN(CollectionDate), MAX(UntilDate)), 0) DESC) AS rnk
		FROM #tempTopNMemoryData 
		GROUP BY TerminalServerID	
	) AS topN ON topN.TerminalServerID = data.TerminalServerID
	Order by rnk, CollectionDate
	DROP TABLE #tempTopNMemoryData
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].[GetUnusedContent]'
GO
ALTER PROCEDURE [dbo].[GetUnusedContent]
	@RootObjectIDs FilterIDsType READONLY,
	@FarmID int,
	@LoadingInfoID int,
	@ContentTypeIDs FilterIDsType READONLY,
	@UnmodifiedSince datetime,
	@MaxDepth int = 32
AS
	SET NOCOUNT ON

	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE	@Loc_RootObjectIDs FilterIDsType
	DECLARE	@Loc_FarmID int
	DECLARE	@Loc_LoadingInfoID int
	DECLARE	@Loc_UnmodifiedSince datetime
	DECLARE	@Loc_ContentTypeIDs FilterIDsType

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

	INSERT INTO @Loc_RootObjectIDs
	SELECT * FROM @RootObjectIDs
	
	INSERT INTO @Loc_ContentTypeIDs
	SELECT * FROM @ContentTypeIDs

	IF OBJECT_ID('tempdb..#OrganisationDescendantUnusedContent') IS NOT NULL
		DROP TABLE #OrganisationDescendantUnusedContent

	CREATE TABLE #OrganisationDescendantUnusedContent ( 
                                        [Name][nvarchar](max),
                                        [ID] INT, 
                                        [SharePointObjectTypeID] INT,
                                        [ParentID] INT NULL,
                                        [PathLevel] INT,
                                        [HasUniqueRoleAssignments] bit,
                                        [IsRootWeb] bit,  
										[PermissionScopeID] INT,
                                        [SiteID] INT,
                                        [LastUpdated] DateTime,
                                        [WebApplicationID] INT,
                                        [Url][nvarchar](max)  ,
                                        [SPGuid] [uniqueidentifier],
										[CreatedOn] DateTime,
										[ContentTypeId] 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 #OrganisationDescendantUnusedContent([Name], [ID], [SharePointObjectTypeID], [ParentID], [PathLevel], [HasUniqueRoleAssignments], [IsRootWeb], [PermissionScopeID], [SiteID], [LastUpdated], [WebApplicationID], [Url], [SPGuid], [CreatedOn], [ContentTypeId])
				    SELECT o.Name, o.ID, o.SPObjectTypeID, NULL, 0 AS PathLevel, scope.[HasUniqueRoleAssignments], o.[IsRootWeb], scope.ScopeID, o.[SiteID], o.[LastUpdated], o.[WebAppID], o.Url, o.[SPGuid], o.[CreatedOn], o.[ContentTypeID]
				    FROM dbo.v_SharePointObjects AS o  
					JOIN dbo.ScopeAssignments as scope ON scope.SPObjectID = o.ID AND scope.LoadingInfoID = @Loc_LoadingInfoID  
					WHERE o.ID in (SELECT r.ID FROM @Loc_RootObjectIDs r) AND o.FarmID = @Loc_FarmID                                       				
			    END
		    ELSE
			    BEGIN
				    INSERT INTO #OrganisationDescendantUnusedContent([Name], [ID], [SharePointObjectTypeID], [ParentID], [PathLevel], [HasUniqueRoleAssignments], [IsRootWeb], [PermissionScopeID], [SiteID], [LastUpdated], [WebApplicationID], [Url], [SPGuid], [CreatedOn], [ContentTypeId])
				        SELECT o.Name, o.ID, o.SPObjectTypeID, m.ParentID, @Depth, scope.[HasUniqueRoleAssignments], o.[IsRootWeb], scope.ScopeID, o.[SiteID], o.[LastUpdated], o.[WebAppID], o.Url, o.[SPGuid], o.[CreatedOn], o.[ContentTypeID]
					    FROM SharePointObjectMemberships as m 
					    JOIN #OrganisationDescendantUnusedContent as d on m.ParentID = d.ID and m.LoadingInfoID = @Loc_LoadingInfoID and d.PathLevel = (@Depth-1)
					    JOIN dbo.SharePointObjects AS o on m.ChildID = o.ID 	
						JOIN dbo.ScopeAssignments as scope ON scope.SPObjectID = o.ID AND scope.LoadingInfoID = @Loc_LoadingInfoID 	
						WHERE o.SPObjectTypeID <> 4 OR (o.SPObjectTypeID = 4 AND o.LastUpdated < @Loc_UnmodifiedSince)
		        END
		    SET @NumberOfNewRows = @@ROWCOUNT								
		    SET @Depth = @Depth + 1                                        
        END
        SELECT	
            [ID],
			[Name],
            [SharePointObjectTypeID],
            [ParentID],
            [PathLevel],
            [HasUniqueRoleAssignments],
            [IsRootWeb],  
			[PermissionScopeID],
            [SiteID],
            [LastUpdated],
            [WebApplicationID],
            [Url],
            [SPGuid],
			[CreatedOn],
			[ContentTypeId] 
		FROM  #OrganisationDescendantUnusedContent as org
		WHERE org.SharePointObjectTypeID <> 4 OR (org.SharePointObjectTypeID = 4 
		AND (NOT EXISTS (SELECT 1 FROM @Loc_ContentTypeIDs) 
		OR EXISTS (SELECT 1 FROM @Loc_ContentTypeIDs ct								
								 WHERE ct.ID = -2 AND NOT EXISTS (SELECT 1 FROM SharePointObjectContentType spct2 
																           WHERE spct2.LoadingInfoID = @Loc_LoadingInfoID AND spct2.SharePointObjectID = org.ID))
		OR EXISTS (SELECT 1 FROM @Loc_ContentTypeIDs ct 
							JOIN SharePointObjectContentType spct ON spct.ContentTypeID = ct.ID AND spct.LoadingInfoID = @Loc_LoadingInfoID AND spct.SharePointObjectID = org.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'Altering [EventCollection].[UpdateCollectionHistory]'
GO
ALTER PROCEDURE [EventCollection].[UpdateCollectionHistory]	
	@Date date,	
	@TableVariable EventCollection.CollectionHistoryTableType READONLY


AS
BEGIN
	SET NOCOUNT ON	

	MERGE EventCollection.CollectionHistory AS target
	USING (
		SELECT ServerID, EventSourceID, [Level], CollectedEventCount FROM @TableVariable
		) AS source
	ON (target.[Date] = @Date AND target.ServerID = source.ServerID AND target.[Level] = source.[Level] AND target.EventSourceID = source.EventSourceID)
	WHEN MATCHED THEN
		UPDATE SET CollectedEventCount = target.CollectedEventCount + source.CollectedEventCount
	WHEN NOT MATCHED THEN
		INSERT ([Date], ServerID, EventSourceID, [Level], CollectedEventCount) VALUES(@Date, source.ServerID, source.EventSourceID, source.[Level], source.CollectedEventCount);--DONT_TOUCH_ME
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 [PerformanceCounters].[AllServersPerformanceHistorical]'
GO
ALTER PROCEDURE [PerformanceCounters].[AllServersPerformanceHistorical]
	@StartedOn datetime,
	@EndedOn datetime,
	@TerminalServerIds NVARCHAR(MAX),
	@PerformanceCounterType int
AS
DECLARE	@CpuCounterID INT = 2;--DONT_TOUCH_ME
DECLARE @MemoryCounterID INT = 4;--DONT_TOUCH_ME
	BEGIN
		SET NOCOUNT ON
		DECLARE @SQLQuery AS NVARCHAR(MAX)
		DECLARE @ParamDefinition AS NVarchar(2000)
		Set @ParamDefinition = ' @StartedOn datetime,
								 @EndedOn datetime'
		IF @PerformanceCounterType = @CpuCounterID
			BEGIN
			SET @SQLQuery = N'
				SELECT S.Name AS Series, CollectionDate, Value
				FROM
					PerformanceCounters.CPU PC INNER JOIN
					[EventCollection].[MonitoringServers] S on PC.TerminalServerID = S.ID and S.PerformanceCountersEnabled = 1
				WHERE 
					CollectionDate >= @StartedOn AND CollectionDate <= @EndedOn'
			END
		ELSE
			BEGIN
			SET @SQLQuery = N'
			SELECT S.Name AS Series, CollectionDate, (Value/1024)  AS Value
				FROM
					PerformanceCounters.Memory PC INNER JOIN
					[EventCollection].[MonitoringServers] S on PC.TerminalServerID = S.ID and S.PerformanceCountersEnabled = 1
				WHERE 
					CollectionDate >= @StartedOn AND CollectionDate <= @EndedOn'
			END
		IF(@TerminalServerIds IS NOT NULL AND @TerminalServerIds != '')
			BEGIN
				SET @SQLQuery = @SQLQuery + N' AND S.ID IN(' + @TerminalServerIds + ')'
			END
		EXEC sp_executesql @SQLQuery, @ParamDefinition, @StartedOn, @EndedOn
	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 [PerformanceCounters].[AllDiskPerformanceHistorical]'
GO
ALTER PROCEDURE [PerformanceCounters].[AllDiskPerformanceHistorical]
	@StartedOn datetime,
	@EndedOn datetime,
	@TerminalServerIds NVARCHAR(MAX),
	@DiskIds NVARCHAR(MAX),
	@PerformanceCounterType int
AS
DECLARE @DiskQueueCounterID INT=27;--DONT_TOUCH_ME
DECLARE @DiskReadCounterID INT=28;--DONT_TOUCH_ME
DECLARE @DiskWriteCounterID INT=29;--DONT_TOUCH_ME
DECLARE @DiskTransferCounterID INT=30;--DONT_TOUCH_ME
DECLARE @DiskFreeSpaceCounterID INT=31;--DONT_TOUCH_ME
DECLARE @SQLQuery AS NVARCHAR(MAX)
		DECLARE @ParamDefinition AS NVarchar(2000)
		Set @ParamDefinition = ' @StartedOn datetime,
								 @EndedOn datetime'
	IF @PerformanceCounterType = @DiskFreeSpaceCounterID
		BEGIN
			SET @SQLQuery = N'SELECT 
					CollectionDate
					,(D.Size - Value) AS Value
					,(S.Name + '' - '' + D.VolumeName + ''('' + D.Name + '')'') AS Series
				FROM [PerformanceCounters].[DiskFreeSpace] PC INNER JOIN
					[EventCollection].[MonitoringServers] S on PC.TerminalServerID = S.ID and S.PerformanceCountersEnabled = 1 INNER JOIN
					dbo.Discs D ON PC.DiskID = D.DiscID
				WHERE CollectionDate >= @StartedOn AND CollectionDate <= @EndedOn'
		END
	ELSE IF @PerformanceCounterType = @DiskQueueCounterID
		BEGIN
			SET @SQLQuery = N'SELECT 
				CollectionDate
				,Value
				,(S.Name + '' - '' + D.VolumeName + ''('' + D.Name + '')'') AS Series
			FROM [PerformanceCounters].[DiskQueue] PC INNER JOIN
				[EventCollection].[MonitoringServers] S on PC.TerminalServerID = S.ID and S.PerformanceCountersEnabled = 1 INNER JOIN
				dbo.Discs D ON PC.DiskID = D.DiscID
			WHERE CollectionDate >= @StartedOn AND CollectionDate <= @EndedOn'
		END
	ELSE IF @PerformanceCounterType = @DiskTransferCounterID
		BEGIN
			SET @SQLQuery = N'SELECT 
				CollectionDate
				,Value
				,(S.Name + '' - '' + D.VolumeName + ''('' + D.Name + '')'') AS Series
			FROM [PerformanceCounters].[DiskTransfer] PC INNER JOIN
				[EventCollection].[MonitoringServers] S on PC.TerminalServerID = S.ID and S.PerformanceCountersEnabled = 1 INNER JOIN
				dbo.Discs D ON PC.DiskID = D.DiscID
			WHERE CollectionDate >= @StartedOn AND CollectionDate <= @EndedOn'
		END
	ELSE IF @PerformanceCounterType = @DiskReadCounterID
		BEGIN
			SET @SQLQuery = N'SELECT 
				CollectionDate
				,Value
				,(S.Name + '' - '' + D.VolumeName + ''('' + D.Name + '')'') AS Series
			FROM [PerformanceCounters].[DiskRead] PC INNER JOIN
				[EventCollection].[MonitoringServers] S on PC.TerminalServerID = S.ID and S.PerformanceCountersEnabled = 1 INNER JOIN
				[dbo].[Discs] D ON PC.DiskID = D.DiscID
			WHERE CollectionDate >= @StartedOn AND CollectionDate <= @EndedOn'
		END
	ELSE IF @PerformanceCounterType = @DiskWriteCounterID
		BEGIN
			SET @SQLQuery = N'SELECT 
				CollectionDate
				,Value
				,(S.Name + '' - '' + D.VolumeName + ''('' + D.Name + '')'') AS Series
			FROM [PerformanceCounters].[DiskWrite] PC INNER JOIN
				[EventCollection].[MonitoringServers] S on PC.TerminalServerID = S.ID and S.PerformanceCountersEnabled = 1 INNER JOIN
				dbo.Discs D ON PC.DiskID = D.DiscID
			WHERE CollectionDate >= @StartedOn AND CollectionDate <= @EndedOn'
		END
	IF(@TerminalServerIds IS NOT NULL AND @TerminalServerIds != '')
		BEGIN
			SET @SQLQuery = @SQLQuery + N' AND S.ID IN(' + @TerminalServerIds + ')'
		END
	IF(@DiskIds IS NOT NULL AND @DiskIds != '')
		BEGIN
			SET @SQLQuery = @SQLQuery + N' AND PC.DiskID IN(' + @DiskIds + ')'
		END
	EXEC sp_executesql @SQLQuery, @ParamDefinition, @StartedOn, @EndedOn
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].[AllNetworkPerformanceHistorical]'
GO
ALTER PROCEDURE [PerformanceCounters].[AllNetworkPerformanceHistorical]
	@StartedOn datetime,
	@EndedOn datetime,
	@TerminalServerIds NVARCHAR(MAX),
	@NetworkAdapterIds NVARCHAR(MAX),
	@PerformanceCounterType int
AS
DECLARE	@NetworkReceivedCounterID INT = 15;--DONT_TOUCH_ME
DECLARE @NetworkSentCounterID INT = 16;--DONT_TOUCH_ME
BEGIN
	SET NOCOUNT ON
	DECLARE @SQLQuery AS NVARCHAR(MAX)
	DECLARE @ParamDefinition AS NVarchar(2000)
	Set @ParamDefinition = ' @StartedOn datetime,
								@EndedOn datetime'
	IF @PerformanceCounterType = @NetworkReceivedCounterID
		BEGIN
			SET @SQLQuery = N'SELECT 
					CollectionDate
					,Value
					,(S.Name + '' ('' + COALESCE(N.NetConnectionID, N.Name) + '')'') AS Series
				FROM [PerformanceCounters].[NetworkReceived] PC INNER JOIN
					[EventCollection].[MonitoringServers] S on PC.TerminalServerID = S.ID and S.PerformanceCountersEnabled = 1 INNER JOIN
					dbo.NetworkAdapters N ON PC.NetworkAdapterID = N.NetworkAdapterID
				WHERE CollectionDate >= @StartedOn AND CollectionDate <= @EndedOn'
		END
	ELSE IF @PerformanceCounterType = @NetworkSentCounterID
		BEGIN
			SET @SQLQuery = N'SELECT 
					CollectionDate
					,Value
					,(S.Name + '' ('' + COALESCE(N.NetConnectionID, N.Name) + '')'') AS Series
				FROM [PerformanceCounters].[NetworkSent] PC INNER JOIN
					[EventCollection].[MonitoringServers] S on PC.TerminalServerID = S.ID and S.PerformanceCountersEnabled = 1 INNER JOIN
					dbo.NetworkAdapters N ON PC.NetworkAdapterID = N.NetworkAdapterID
				WHERE CollectionDate >= @StartedOn AND CollectionDate <= @EndedOn'
		END
	IF(@TerminalServerIds IS NOT NULL AND @TerminalServerIds != '')
		BEGIN
			SET @SQLQuery = @SQLQuery + N' AND S.ID IN(' + @TerminalServerIds + ')'
		END
	IF(@NetworkAdapterIds IS NOT NULL AND @NetworkAdapterIds != '')
		BEGIN
			SET @SQLQuery = @SQLQuery + N' AND PC.NetworkAdapterID IN(' + @NetworkAdapterIds + ')'
		END
	EXEC sp_executesql @SQLQuery, @ParamDefinition, @StartedOn, @EndedOn
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
UPDATE Options SET OptionValue = '7.0.2' 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.0,7.1)' 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

