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]
  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'Creating types'
GO
CREATE TYPE [SQL].[StatisticInfoType] AS TABLE
(
[LoadedObjectId] [int] NULL,
[StatisticID] [int] NULL,
[StatisticName] [nvarchar] (max) NULL,
[ColumnNames] [nvarchar] (max) NULL,
[IsAutoCreated] [bit] NULL,
[FilterDefinition] [nvarchar] (max) NULL,
[IsTemporary] [bit] 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].[ProgramEntryInfoType] AS TABLE
(
[ParentObjectId] [int] NULL,
[ParameterName] [nvarchar] (max) NULL,
[ParameterId] [int] NULL,
[HasDefaultValue] [bit] NULL,
[IsOutput] [bit] NULL,
[TypeName] [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
CREATE TYPE [SQL].[IndexColumnsType] AS TABLE
(
[LoadedObjectId] [int] NULL,
[IndexId] [int] NULL,
[ColumnIndexID] [int] NULL,
[ColumnName] [nvarchar] (max) NULL,
[IsIncludedColumn] [bit] NULL,
[IsIdentity] [bit] NULL,
[AllowNulls] [bit] NULL,
[IsAscending] [bit] NULL,
[DataType] [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
CREATE TYPE [SQL].[TableObjectType] AS TABLE
(
[ObjectId] [int] NULL,
[TableName] [nvarchar] (max) NULL,
[SchemaName] [nvarchar] (max) NULL,
[CreateTime] [datetime] NULL,
[ModifyTime] [datetime] NULL,
[IsReplicated] [bit] NULL,
[RowNumber] [int] NULL,
[TotalSpaceKB] [int] NULL,
[DataSizeKB] [int] NULL,
[IndexSizeKB] [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].[RoleSecurablesInfoType] AS TABLE
(
[RoleID] [int] NULL,
[LoadedObjectID] [int] NULL,
[PermissionName] [nvarchar] (100) NULL,
[PermissionState] [nvarchar] (50) NULL,
[PermissionScope] [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].[DatabaseInfoType] AS TABLE
(
[SQLInstanceName] [nvarchar] (max) NULL,
[DatabaseName] [nvarchar] (max) NULL,
[DatabaseId] [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].[ConstraintInfoType] AS TABLE
(
[LoadedObjectId] [int] NULL,
[ConstraintName] [nvarchar] (max) NULL,
[ColumnName] [nvarchar] (max) NULL,
[ConstraintDefinition] [nvarchar] (max) NULL,
[ConstraintType] [nvarchar] (max) NULL,
[CreateTime] [datetime] NULL,
[ModifyTime] [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
CREATE TYPE [SQL].[TriggerInfoType] AS TABLE
(
[LoadedObjectId] [int] NULL,
[TriggerId] [int] NULL,
[TriggerName] [nvarchar] (max) NULL,
[CreateDate] [datetime] NULL,
[ModifyDate] [datetime] NULL,
[TriggerType] [nvarchar] (max) NULL,
[IsDisabled] [bit] NULL,
[TriggerExecutionOrder] [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
CREATE TYPE [SQL].[KeyInfoType] AS TABLE
(
[LoadedObjectId] [int] NULL,
[KeyName] [nvarchar] (400) NULL,
[ColumnName] [nvarchar] (400) NULL,
[KeyType] [nvarchar] (max) 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].[RolePrincipalsType] AS TABLE
(
[LoadedRoleID] [int] NULL,
[UserName] [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
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
)
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].[IndexInfoType] AS TABLE
(
[LoadedObjectId] [int] NULL,
[IndexId] [int] NULL,
[IndexName] [nvarchar] (max) NULL,
[Type] [nvarchar] (max) NULL,
[IsUnique] [bit] NULL,
[IsPrimaryKey] [bit] NULL,
[IndexSize] [int] NULL,
[FillFactor] [decimal] (18, 0) 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].[PrincipalInfoType] AS TABLE
(
[UserName] [nvarchar] (max) NULL,
[MappedToLoggin] [nvarchar] (max) NULL,
[PrincipalType] [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
CREATE TYPE [SQL].[ProgrammInfoType] AS TABLE
(
[ObjectId] [int] NULL,
[ObjectName] [nvarchar] (max) NULL,
[SchemaName] [nvarchar] (max) NULL,
[CreateTime] [datetime] NULL,
[ModifyTime] [datetime] NULL,
[ProgramType] [int] NULL,
[IsEncrypted] [bit] 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] [date] NULL,
[ModifyDate] [date] 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
PRINT N'Creating [PerformanceCounters].[ServerNetworkPerformance]'
GO
CREATE 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'Creating [SQL].[LoadedRoleSecurables]'
GO
CREATE TABLE [SQL].[LoadedRoleSecurables]
(
[LoadedRoleID] [int] NOT NULL,
[LoadedObjectID] [int] NOT NULL,
[PermissionName] [nvarchar] (100) NOT NULL,
[PermissionState] [nvarchar] (50) NULL,
[PermissionScope] [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
PRINT N'Creating primary key [PK_LoadedRoleSecurables] on [SQL].[LoadedRoleSecurables]'
GO
ALTER TABLE [SQL].[LoadedRoleSecurables] ADD CONSTRAINT [PK_LoadedRoleSecurables] PRIMARY KEY CLUSTERED  ([LoadedRoleID], [LoadedObjectID], [PermissionName])
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].[LoadedRoles]'
GO
CREATE TABLE [SQL].[LoadedRoles]
(
[LoadedRoleID] [int] NOT NULL IDENTITY(1, 1),
[LoadedDatabaseID] [int] NOT NULL,
[RoleID] [int] NULL,
[RoleName] [nvarchar] (max) NULL,
[Owner] [nvarchar] (max) NULL,
[CreateDate] [date] NULL,
[ModifyDate] [date] 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
PRINT N'Creating primary key [PK_LoadedRoles] on [SQL].[LoadedRoles]'
GO
ALTER TABLE [SQL].[LoadedRoles] ADD CONSTRAINT [PK_LoadedRoles] PRIMARY KEY CLUSTERED  ([LoadedRoleID])
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].[LoadedDatabases]'
GO
CREATE TABLE [SQL].[LoadedDatabases]
(
[LoadedDatabaseID] [int] NOT NULL IDENTITY(1, 1),
[LoadingInfoID] [int] NOT NULL,
[SQLInstanceName] [nvarchar] (max) NOT NULL,
[DatabaseName] [nvarchar] (max) NOT NULL,
[DatabaseId] [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_LoadedDatabases] on [SQL].[LoadedDatabases]'
GO
ALTER TABLE [SQL].[LoadedDatabases] ADD CONSTRAINT [PK_LoadedDatabases] PRIMARY KEY CLUSTERED  ([LoadedDatabaseID])
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].[RolePrincipals]'
GO
CREATE TABLE [SQL].[RolePrincipals]
(
[PrincipalID] [int] NOT NULL,
[LoadedRoleID] [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_RolePrincipals] on [SQL].[RolePrincipals]'
GO
ALTER TABLE [SQL].[RolePrincipals] ADD CONSTRAINT [PK_RolePrincipals] PRIMARY KEY CLUSTERED  ([LoadedRoleID], [PrincipalID])
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].[LoadedPrincipals]'
GO
CREATE TABLE [SQL].[LoadedPrincipals]
(
[PrincipalID] [int] NOT NULL IDENTITY(1, 1),
[LoadedDatabaseID] [int] NOT NULL,
[UserName] [nvarchar] (max) NULL,
[MappedToLoggin] [nvarchar] (max) NULL,
[PrincipalType] [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_LoadedPrincipals] on [SQL].[LoadedPrincipals]'
GO
ALTER TABLE [SQL].[LoadedPrincipals] ADD CONSTRAINT [PK_LoadedPrincipals] PRIMARY KEY CLUSTERED  ([PrincipalID])
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].[LoadedConstraints]'
GO
CREATE TABLE [SQL].[LoadedConstraints]
(
[LoadedObjectsID] [int] NOT NULL,
[ConstraintName] [nvarchar] (200) NOT NULL,
[ColumnName] [nvarchar] (max) NULL,
[ConstraintDefinition] [nvarchar] (max) NULL,
[ConstraintType] [nvarchar] (max) NULL,
[CreateTime] [datetime] NULL,
[ModifyTime] [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_LoadedConstraints] on [SQL].[LoadedConstraints]'
GO
ALTER TABLE [SQL].[LoadedConstraints] ADD CONSTRAINT [PK_LoadedConstraints] PRIMARY KEY CLUSTERED  ([LoadedObjectsID], [ConstraintName])
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].[LoadedIndexes]'
GO
CREATE TABLE [SQL].[LoadedIndexes]
(
[LoadedIndexID] [int] NOT NULL IDENTITY(1, 1),
[LoadedObjectId] [int] NULL,
[IndexId] [int] NULL,
[IndexName] [nvarchar] (max) NULL,
[Type] [nvarchar] (max) NULL,
[IsUnique] [bit] NULL,
[IsPrimaryKey] [bit] NULL,
[IndexSize] [int] NULL,
[FillFactor] [decimal] (18, 0) 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_LoadedIndexes] on [SQL].[LoadedIndexes]'
GO
ALTER TABLE [SQL].[LoadedIndexes] ADD CONSTRAINT [PK_LoadedIndexes] PRIMARY KEY CLUSTERED  ([LoadedIndexID])
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].[LoadedIndexColumns]'
GO
CREATE TABLE [SQL].[LoadedIndexColumns]
(
[LoadedIndexID] [int] NOT NULL,
[ColumnIndexID] [int] NOT NULL,
[ColumnName] [nvarchar] (max) NULL,
[IsIncludedColumn] [bit] NULL,
[IsIdentity] [bit] NULL,
[AllowNulls] [bit] NULL,
[IsAscending] [bit] NULL,
[DataType] [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_LoadedIndexColumns] on [SQL].[LoadedIndexColumns]'
GO
ALTER TABLE [SQL].[LoadedIndexColumns] ADD CONSTRAINT [PK_LoadedIndexColumns] PRIMARY KEY CLUSTERED  ([LoadedIndexID], [ColumnIndexID])
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].[LoadedStatistics]'
GO
CREATE TABLE [SQL].[LoadedStatistics]
(
[LoadedObjectId] [int] NOT NULL,
[StatisticID] [int] NOT NULL,
[StatisticName] [nvarchar] (200) NULL,
[ColumnNames] [nvarchar] (max) NULL,
[IsAutoCreated] [bit] NULL,
[FilterDefinition] [nvarchar] (max) NULL,
[IsTemporary] [bit] 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_LoadedStatistics] on [SQL].[LoadedStatistics]'
GO
ALTER TABLE [SQL].[LoadedStatistics] ADD CONSTRAINT [PK_LoadedStatistics] PRIMARY KEY CLUSTERED  ([LoadedObjectId], [StatisticID])
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].[LoadedColumns]'
GO
CREATE TABLE [SQL].[LoadedColumns]
(
[LoadedObjectsID] [int] NOT NULL,
[ColumnId] [int] NOT 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
)
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_LoadedColumns] on [SQL].[LoadedColumns]'
GO
ALTER TABLE [SQL].[LoadedColumns] ADD CONSTRAINT [PK_LoadedColumns] PRIMARY KEY CLUSTERED  ([LoadedObjectsID], [ColumnId])
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].[LoadedTriggers]'
GO
CREATE TABLE [SQL].[LoadedTriggers]
(
[LoadedObjectId] [int] NOT NULL,
[TriggerId] [int] NOT NULL,
[TriggerName] [nvarchar] (200) NULL,
[CreateDate] [datetime] NULL,
[ModifyDate] [datetime] NULL,
[TriggerType] [nvarchar] (50) NOT NULL,
[IsDisabled] [bit] NULL,
[TriggerExecutionOrder] [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_LoadedTriggers] on [SQL].[LoadedTriggers]'
GO
ALTER TABLE [SQL].[LoadedTriggers] ADD CONSTRAINT [PK_LoadedTriggers] PRIMARY KEY CLUSTERED  ([LoadedObjectId], [TriggerId], [TriggerType])
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].[LoadedProgrammabilities]'
GO
CREATE TABLE [SQL].[LoadedProgrammabilities]
(
[LoadedObjectsID] [int] NOT NULL,
[ProgramType] [int] NULL,
[IsEncrypted] [bit] 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_LoadedProgrammabilities] on [SQL].[LoadedProgrammabilities]'
GO
ALTER TABLE [SQL].[LoadedProgrammabilities] ADD CONSTRAINT [PK_LoadedProgrammabilities] PRIMARY KEY CLUSTERED  ([LoadedObjectsID])
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].[LoadedObjects]'
GO
CREATE TABLE [SQL].[LoadedObjects]
(
[LoadedObjectsID] [int] NOT NULL IDENTITY(1, 1),
[LoadingInfoID] [int] NOT NULL,
[LoadedDatabaseID] [int] NOT NULL,
[ObjectID] [int] NOT NULL,
[ObjectName] [nvarchar] (max) NOT NULL,
[SchemaName] [nvarchar] (max) NOT NULL,
[CreateTime] [date] NOT NULL,
[ModifyTime] [date] 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_LoadedObjects] on [SQL].[LoadedObjects]'
GO
ALTER TABLE [SQL].[LoadedObjects] ADD CONSTRAINT [PK_LoadedObjects] PRIMARY KEY CLUSTERED  ([LoadedObjectsID])
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_LoadingInfo_LoadedDatabaseID_ObjectType] on [SQL].[LoadedObjects]'
GO
CREATE NONCLUSTERED INDEX [IX_LoadingInfo_LoadedDatabaseID_ObjectType] ON [SQL].[LoadedObjects] ([LoadingInfoID], [LoadedDatabaseID], [ObjectType])
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].[LoadedTableKeys]'
GO
CREATE TABLE [SQL].[LoadedTableKeys]
(
[LoadedObjectId] [int] NOT NULL,
[KeyName] [nvarchar] (400) NOT NULL,
[ColumnName] [nvarchar] (400) NOT NULL,
[KeyType] [nvarchar] (max) 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
PRINT N'Creating primary key [PK_LoadedTableKeys] on [SQL].[LoadedTableKeys]'
GO
ALTER TABLE [SQL].[LoadedTableKeys] ADD CONSTRAINT [PK_LoadedTableKeys] PRIMARY KEY CLUSTERED  ([LoadedObjectId], [KeyName], [ColumnName])
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].[LoadedProgrammParameters]'
GO
CREATE TABLE [SQL].[LoadedProgrammParameters]
(
[LoadedObjectsID] [int] NOT NULL,
[ParameterId] [int] NOT NULL,
[ParameterName] [nvarchar] (max) NULL,
[HasDefaultValue] [bit] NULL,
[IsOutput] [bit] NULL,
[TypeName] [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_LoadedProgrammParameters] on [SQL].[LoadedProgrammParameters]'
GO
ALTER TABLE [SQL].[LoadedProgrammParameters] ADD CONSTRAINT [PK_LoadedProgrammParameters] PRIMARY KEY CLUSTERED  ([LoadedObjectsID], [ParameterId])
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].[InsertViewInfos]'
GO
CREATE 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]  date NOT NULL,
		[ModifyTime] date 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'Creating [SQL].[LoadedTables]'
GO
CREATE TABLE [SQL].[LoadedTables]
(
[LoadedObjectsID] [int] NOT NULL,
[IsReplicated] [bit] NULL,
[RowNumber] [int] NULL,
[TotalSpaceKB] [int] NULL,
[DataSizeKB] [int] NULL,
[IndexSizeKB] [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 primary key [PK_LoadedTables] on [SQL].[LoadedTables]'
GO
ALTER TABLE [SQL].[LoadedTables] ADD CONSTRAINT [PK_LoadedTables] PRIMARY KEY CLUSTERED  ([LoadedObjectsID])
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].[TopNServerCPUData]'
GO
CREATE 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'Creating [PerformanceCounters].[ServerPerformanceHistorical]'
GO
CREATE 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'Creating [PerformanceCounters].[TopNServerDiskUsageData]'
GO
CREATE 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'Creating [PerformanceCounters].[ServerDiskPerformance]'
GO
CREATE 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'Creating [dbo].[AdministrativeActionsLoadTimes]'
GO
CREATE TABLE [dbo].[AdministrativeActionsLoadTimes]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[FarmID] [int] NOT NULL,
[EndTime] [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'Creating primary key [PK_AdministrativeActionsLoadTimes] on [dbo].[AdministrativeActionsLoadTimes]'
GO
ALTER TABLE [dbo].[AdministrativeActionsLoadTimes] ADD CONSTRAINT [PK_AdministrativeActionsLoadTimes] PRIMARY KEY CLUSTERED  ([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'Creating [EventCollection].[CollectionDisabledReasons]'
GO
CREATE TABLE [EventCollection].[CollectionDisabledReasons]
(
[ID] [int] NOT NULL,
[Name] [nvarchar] (255) 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_CollectionDisabledReasons] on [EventCollection].[CollectionDisabledReasons]'
GO
ALTER TABLE [EventCollection].[CollectionDisabledReasons] ADD CONSTRAINT [PK_CollectionDisabledReasons] PRIMARY KEY CLUSTERED  ([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'Creating [PerformanceCounters].[ServersLastServiceCollectionStartTime]'
GO
CREATE 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 [EventCollection].[MonitoringServers]'
GO
ALTER TABLE [EventCollection].[MonitoringServers] ADD
[FQDN] [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 [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] date,
		[ModifyDate] date,
		[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].[InsertDatabaseInfos]'
GO
CREATE PROCEDURE [SQL].[InsertDatabaseInfos]	
	@LoadingInfoID int,
	@TableVariable DatabaseInfoType READONLY
AS
BEGIN
	SET NOCOUNT ON	
	
	CREATE TABLE #newDatabaseInfos (
		[LoadedDatabaseId] int NULL,
		[SQLInstanceName] NVARCHAR (MAX),
		[DatabaseName] NVARCHAR(MAX),
		[DatabaseId] int
	) 

	INSERT INTO #newDatabaseInfos (
			[LoadedDatabaseId],
			[SQLInstanceName],
			[DatabaseName],
			[DatabaseId])
	SELECT NULL,
		   di.SQLInstanceName,
		   di.DatabaseName,
		   di.DatabaseId
	FROM @TableVariable di

	INSERT INTO [SQL].[LoadedDatabases](
			SQLInstanceName,
			DatabaseName,
			LoadingInfoID,
			DatabaseId)
	SELECT ndi.SQLInstanceName,
		   ndi.DatabaseName,
		   @LoadingInfoID,
		   ndi.DatabaseId
	FROM #newDatabaseInfos ndi

	UPDATE ndi
	   SET ndi.LoadedDatabaseId = db.LoadedDatabaseID	  
	FROM #newDatabaseInfos ndi
	JOIN [SQL].[LoadedDatabases] db ON ndi.DatabaseId = db.DatabaseId AND ndi.SQLInstanceName = db.SQLInstanceName AND db.LoadingInfoID = @LoadingInfoID

	SELECT di.LoadedDatabaseId
		  ,di.DatabaseId      
	FROM #newDatabaseInfos di
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].[InsertRolePrincipals]'
GO
CREATE PROCEDURE [SQL].[InsertRolePrincipals]
	@DatabaseID int,
	@Principals [SQL].[PrincipalInfoType] READONLY,
	@RolePrincipals [SQL].[RolePrincipalsType] READONLY
AS
BEGIN
	SET NOCOUNT ON	
	
	CREATE TABLE #newPrincipalInfos (
		[PrincipalID] int,
		[UserName] nvarchar(MAX),
		[MappedToLoggin] nvarchar(MAX),
		[PrincipalType] nvarchar(MAX)
	) 

	INSERT INTO #newPrincipalInfos (
		[PrincipalID],
		[UserName],
		[MappedToLoggin],
		[PrincipalType])
	SELECT NULL,
		   [UserName],
		   [MappedToLoggin],
	       [PrincipalType]
	FROM @Principals p

	INSERT INTO [SQL].[LoadedPrincipals](			
			[LoadedDatabaseID],
			[UserName],
			[MappedToLoggin],
			[PrincipalType])
	SELECT @DatabaseID,
		  [UserName],
		  [MappedToLoggin],
	      [PrincipalType]
	FROM #newPrincipalInfos npi

	UPDATE npi
	   SET npi.PrincipalID = principals.PrincipalID
	FROM #newPrincipalInfos npi
	JOIN [SQL].[LoadedPrincipals] principals ON principals.UserName = npi.UserName AND principals.LoadedDatabaseID = @DatabaseID

	INSERT INTO [SQL].[RolePrincipals](			
			[PrincipalID],
			[LoadedRoleID])
	SELECT npi.PrincipalID,
		   rp.LoadedRoleID
	FROM @RolePrincipals rp
	JOIN #newPrincipalInfos npi ON rp.UserName = npi.UserName
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].[InsertConstraintInfos]'
GO
CREATE PROCEDURE [SQL].[InsertConstraintInfos]
	@TableVariable [SQL].ConstraintInfoType READONLY
AS
BEGIN
	SET NOCOUNT ON	
	
	INSERT INTO [SQL].[LoadedConstraints](
			[LoadedObjectsID],
			[ConstraintName],
			[ColumnName],
			[ConstraintDefinition],
			[ConstraintType],
			[CreateTime],
			[ModifyTime])
	SELECT  [LoadedObjectId],
			[ConstraintName],
			[ColumnName],
			[ConstraintDefinition],
			[ConstraintType],
			[CreateTime],
			[ModifyTime]
	FROM @TableVariable conInfo
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].[InsertIndexInfos]'
GO
CREATE PROCEDURE [SQL].[InsertIndexInfos]
	@IndexInformation [SQL].[IndexInfoType] READONLY,
	@IndexColumns [SQL].[IndexColumnsType] READONLY
AS
BEGIN
	SET NOCOUNT ON	
	
	CREATE TABLE #newIndexInfos (
		[LoadedIndexId] int NULL,
		[LoadedObjectId] int,
		[IndexId] int,
	)
	
	CREATE TABLE #newIndexColInfos (
		[LoadedObjectId] int,
		[IndexId] int,
		[ColumnIndexID] int,
		[ColumnName]  NVARCHAR (MAX),
		[IsIncludedColumn] bit,
		[IsIdentity] bit,
		[AllowNulls] bit,
		[IsAscending] bit,
		[DataType] NVARCHAR(MAX)
	)  

	INSERT INTO #newIndexInfos (
			[LoadedIndexId],
			[LoadedObjectId],
			[IndexId])
	SELECT NULL,
		   ii.LoadedObjectId,
		   ii.IndexId
	FROM @IndexInformation ii

	INSERT INTO #newIndexColInfos (
			[LoadedObjectId],
			[IndexId],
			[ColumnIndexID],
			[ColumnName],
			[IsIncludedColumn],
			[IsIdentity],
			[AllowNulls],
			[IsAscending],
			[DataType])
	SELECT  ic.[LoadedObjectId],
			ic.[IndexId],
			ic.[ColumnIndexID],
			ic.[ColumnName],
			ic.[IsIncludedColumn],
			ic.[IsIdentity],
			ic.[AllowNulls],
			ic.[IsAscending],
			ic.[DataType]
	FROM @IndexColumns ic

	INSERT INTO [SQL].[LoadedIndexes](
			[LoadedObjectId],
			[IndexId],
			[IndexName],
			[Type],
			[IsUnique],
			[IsPrimaryKey],
			[IndexSize],
			[FillFactor])
	SELECT  [LoadedObjectId],
			[IndexId],
			[IndexName],
			[Type],
			[IsUnique],
			[IsPrimaryKey],
			[IndexSize],
			[FillFactor]
	FROM @IndexInformation

	UPDATE nii
	   SET nii.LoadedIndexId = li.LoadedIndexID  
	FROM #newIndexInfos nii
	JOIN [SQL].[LoadedIndexes] li ON nii.IndexId = li.IndexId AND nii.LoadedObjectId = li.LoadedObjectId

	INSERT INTO [SQL].LoadedIndexColumns(
		[LoadedIndexID],
		[ColumnIndexID],
		[ColumnName],
		[IsIncludedColumn],
		[IsIdentity],
		[AllowNulls],
		[IsAscending],
		[DataType]
	)
	SELECT  nii.LoadedIndexId,
			ic.[ColumnIndexID],
			ic.[ColumnName],
			ic.[IsIncludedColumn],
			ic.[IsIdentity],
			ic.[AllowNulls],
			ic.[IsAscending],
			ic.[DataType]
	FROM #newIndexColInfos ic
	JOIN #newIndexInfos nii ON nii.IndexId = ic.IndexId AND nii.LoadedObjectId = ic.LoadedObjectId
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].[InsertStatisticInfos]'
GO
CREATE PROCEDURE [SQL].[InsertStatisticInfos]
	@TableVariable [SQL].StatisticInfoType READONLY
AS
BEGIN
	SET NOCOUNT ON	
	
	INSERT INTO [SQL].[LoadedStatistics](
			[LoadedObjectId],
			[StatisticID],
			[StatisticName],
			[ColumnNames],
			[IsAutoCreated],
			[FilterDefinition],
			[IsTemporary])
	SELECT  [LoadedObjectId],
			[StatisticID],
			[StatisticName],
			[ColumnNames],
			[IsAutoCreated],
			[FilterDefinition],
			[IsTemporary]
	FROM @TableVariable statInfo
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])
	SELECT  colInfo.[LoadedObjectId],
		    colInfo.[ColumnId],
			colInfo.[ColumnName],
			colInfo.[MaxLength],
			colInfo.[Precision],
			colInfo.[CollationName],
			colInfo.[IsNullable],
			colInfo.[IsIdentity],
			colInfo.[Type]
	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'Creating [SQL].[InsertTriggerInfos]'
GO
CREATE PROCEDURE [SQL].[InsertTriggerInfos]
	@TableVariable [SQL].TriggerInfoType READONLY
AS
BEGIN
	SET NOCOUNT ON	
	
	INSERT INTO [SQL].[LoadedTriggers](
			[LoadedObjectId],
			[TriggerId],
			[TriggerName],
			[CreateDate],
			[ModifyDate],
			[TriggerType],
			[IsDisabled],
			[TriggerExecutionOrder])
	SELECT  [LoadedObjectId],
			[TriggerId],
			[TriggerName],
			[CreateDate],
			[ModifyDate],
			[TriggerType],
			[IsDisabled],
			[TriggerExecutionOrder]
	FROM @TableVariable trigInfo
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].[InsertProgrammibilityInfos]'
GO
CREATE 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]  date NOT NULL,
		[ModifyTime] date 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].[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].[InsertProgrammabilityParameters]'
GO
CREATE PROCEDURE [SQL].[InsertProgrammabilityParameters]
	@TableVariable [SQL].[ProgramEntryInfoType] READONLY
AS
BEGIN
	SET NOCOUNT ON	
	
	INSERT INTO [SQL].[LoadedProgrammParameters](			
		[LoadedObjectsID],
		[ParameterId],
		[ParameterName],	
		[HasDefaultValue],
		[IsOutput],
		[TypeName])
	SELECT	proen.[ParentObjectId],
			proen.[ParameterId],	
			proen.[ParameterName],
			proen.[HasDefaultValue],
			proen.[IsOutput],
			proen.[TypeName]
	FROM @TableVariable proen	
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].[InsertTableInfos]'
GO
CREATE 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]  date NOT NULL,
		[ModifyTime] date 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'Creating [dbo].[GetSiteCollectionAuthSettingsByWebAppGuid]'
GO
CREATE PROCEDURE [dbo].[GetSiteCollectionAuthSettingsByWebAppGuid]
	@FarmID int,
	@WorkstationID int,
	@WebApplicationGuid uniqueidentifier,
	@SiteUrl nvarchar(2048)
	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_WebApplicationGuid uniqueidentifier
	DECLARE	@Loc_SiteUrl nvarchar(2048)

	SELECT @Loc_WorkstationID = @WorkstationID,
		   @Loc_FarmID = @FarmID,
		   @Loc_WebApplicationGuid = @WebApplicationGuid,
		   @Loc_SiteUrl = @SiteUrl
		   	
	SELECT sc.IntegratedSecurity,
		f.FarmType,
		sc.Version,
		c.Username,
		c.Password,
		sc.ID	
	FROM ConnectedSiteCollections sc
	INNER JOIN Farms f ON f.ID = sc.FarmID
	INNER JOIN WebApplications wa ON wa.ID = sc.WebApplicationID
	LEFT OUTER JOIN SPDocKitCredentials c ON c.ID = sc.SPDocKitCredentialID
	WHERE sc.FarmID = @Loc_FarmID AND sc.SPDocKitWorkstationID = @Loc_WorkstationID AND sc.Url = @Loc_SiteUrl AND wa.SPGuid = @Loc_WebApplicationGuid
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
[LoadDuration] [int] NOT NULL CONSTRAINT [DF_LoadingInfos_LoadDuration] 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'Creating [PerformanceCounters].[ServersMaxThresholdPeriod]'
GO
CREATE PROCEDURE [PerformanceCounters].[ServersMaxThresholdPeriod]
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @defaultTemplateID INT
	SET @defaultTemplateID = (SELECT TOP 1 (TemplateID) FROM Template.Templates WHERE Name = 'Default Template')

	SELECT TerminalServerID, max(ThresholdPeriod) 'MaxThresholdPeriod'
	FROM
	(SELECT s.ID AS TerminalServerID, TemplateID
	FROM EventCollection.MonitoringServers AS s
	JOIN Template.ServerSubscriptions on Template.ServerSubscriptions.TerminalServerID = s.ID	
	UNION
	SELECT s.ID AS TerminalServerID, TemplateID = @defaultTemplateID
	FROM EventCollection.MonitoringServers AS s) as T
	JOIN [PerformanceCounters].[CounterThresholds] on T.TemplateID = [PerformanceCounters].[CounterThresholds].TemplateID
	GROUP BY TerminalServerID
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 [PerformanceCounters].[TopNServerMemoryData]'
GO
CREATE 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 [SQL].[Instances]'
GO
ALTER TABLE [SQL].[Instances] ADD
[ServiceName] [nvarchar] (512) NULL,
[Excluded] [bit] NOT NULL CONSTRAINT [DF_Instances_Excluded] DEFAULT ((0)),
[AoClusterName] [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
ALTER TABLE [SQL].[Instances] ALTER COLUMN [Port] [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 [EventCollection].[Agents]'
GO
ALTER TABLE [EventCollection].[Agents] ADD
[CollectionDisabledReasonId] [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].[GetFarmPrincipals]'
GO
CREATE PROCEDURE [dbo].[GetFarmPrincipals]
	@FarmID int
	AS
	
	SET NOCOUNT ON	

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

	SELECT @Loc_FarmID = @FarmID
		   	
	SELECT [PrincipalID]
		  ,[Name]
		  ,[PrincipalTypeID]
		  ,[DisplayName]
		  ,[Email]
  FROM [dbo].[v_Principals]
  WHERE FarmID = @Loc_FarmID
  ORDER BY [DisplayName] ASC
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].[GetFullReadPrincipalsForWebApplication]'
GO
CREATE PROCEDURE dbo.GetFullReadPrincipalsForWebApplication
	@Principals [dbo].[FilterIDsType] READONLY,
	@LoadingInfoID int,
	@WebApplicationID int,
	@EnabledState [dbo].[BooleanFilterType] READONLY
	AS
	SET NOCOUNT ON	
	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE	@Loc_LoadingInfoID int
	DECLARE	@Loc_Principals FilterIDsType
	DECLARE @Loc_WebApplicationID int
	DECLARE @Loc_EnabledState [dbo].[BooleanFilterType]

	SELECT @Loc_LoadingInfoID = @LoadingInfoID,
		   @Loc_WebApplicationID = @WebApplicationID

	INSERT INTO @Loc_Principals
	SELECT * FROM @Principals

	INSERT INTO @Loc_EnabledState
	SELECT * FROM @EnabledState

	DECLARE	@FilterPrincipals bit
	SELECT @FilterPrincipals = CASE WHEN EXISTS(SELECT 1 FROM @Loc_Principals)THEN 1
									ELSE 0
									END


SELECT 
	[Principals].[PrincipalID] AS [PrincipalID], 
	[Principals].[Name] AS [Name], 
	[Principals].[DisplayName] AS [DisplayName], 
	[LoadedPrincipals].[AccountEnabled] AS [AccountEnabled], 
	[Principals].[PrincipalTypeID] AS [PrincipalTypeID], 
	[Principals].[Email] AS [Email], 	
	cast(0 as bit) AS [C1]
		FROM  [dbo].[v_Principals] AS [Principals]
		INNER JOIN [dbo].[LoadedPrincipals] AS [LoadedPrincipals] ON [Principals].[PrincipalID] = [LoadedPrincipals].[PrincipalID]
		LEFT OUTER JOIN @Loc_Principals principalFilter ON principalFilter.ID = [Principals].PrincipalID 
		WHERE (@FilterPrincipals = 0 OR principalFilter.ID IS NOT NULL)
			  AND ((NOT EXISTS(SELECT 1 FROM @Loc_EnabledState)) OR (EXISTS(SELECT 1 FROM @Loc_EnabledState as [Enabled] WHERE [LoadedPrincipals].[AccountEnabled] = [Enabled].[BitValue])))
			  AND (EXISTS (
			        SELECT 1 AS [C1]
						FROM  [dbo].[WebApplicationPermissions] AS [WebApplicationPermissions]
						INNER JOIN [dbo].[PolicyLevels] AS [PolicyLevels] ON [WebApplicationPermissions].[PolicyLevelID] = [PolicyLevels].[PolicyLevelID]
						WHERE ([Principals].[PrincipalID] = [WebApplicationPermissions].[PrincipalID]) 
							AND ([WebApplicationPermissions].[LoadingInfoID] = @Loc_LoadingInfoID) 
							AND (N'Full Read' = [PolicyLevels].[Name]) 
							AND [WebApplicationPermissions].[WebApplicationID] = @Loc_WebApplicationID) 
							AND ([LoadedPrincipals].[LoadingInfoID] = @Loc_LoadingInfoID))
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].[GetSiteCollectionPrincipals]'
GO
CREATE PROCEDURE [dbo].[GetSiteCollectionPrincipals]
	@LoadingInfoId int,
	@SiteCollectionIDs FilterIDsType READONLY
AS
	SET NOCOUNT ON

	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE	@Loc_LoadingInfoID int
	DECLARE	@Loc_SiteCollectionIDs FilterIDsType

	SELECT @Loc_LoadingInfoID = @LoadingInfoID 

	INSERT INTO @Loc_SiteCollectionIDs
	SELECT * FROM @SiteCollectionIDs
	                                
    SELECT DISTINCT spo.ID AS SiteCollectionID,
		spo.Name AS SiteCollectionName, 			
		p.[DisplayName],
		p.[Name] AS Username,
		p.[Email],
		p.[PrincipalID], 
		p.[PrincipalTypeID],
		lp.[AccountEnabled],
        p.[ExternalUser]
    FROM Principals p
    JOIN LoadedPrincipals lp ON lp.PrincipalID = p.PrincipalID AND lp.LoadingInfoID = @Loc_LoadingInfoID
    JOIN RoleAssignments ON RoleAssignments.PrincipalID = p.PrincipalID AND RoleAssignments.LoadingInfoID = @Loc_LoadingInfoID
    JOIN @Loc_SiteCollectionIDs sc ON sc.ID = RoleAssignments.SiteID
    JOIN v_SharePointObjects spo ON spo.ID = sc.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 [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].[ScheduledAction]'
GO
ALTER TABLE [SQL].[ScheduledAction] ALTER COLUMN [Name] [nvarchar] (1000) 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 [dbo].[DeleteDataFromTable]'
GO
CREATE PROCEDURE [dbo].[DeleteDataFromTable]
	@tableName NVARCHAR(255),
	@dateColumnName NVARCHAR(255),
	@serverColumnName NVARCHAR(255),
	@fromDate DATETIME,
	@endDate DATETIME,
	@farmId INT,
	@serverId INT
AS

	DECLARE @Loc_tableName NVARCHAR(255)
	DECLARE @Loc_dateColumnName NVARCHAR(255)			 
	DECLARE @Loc_serverColumnName NVARCHAR(255)			 
	DECLARE @Loc_fromDate DATETIME
	DECLARE @Loc_endDate DATETIME
	DECLARE @Loc_farmId INT
	DECLARE @Loc_serverId INT

	SELECT @Loc_tableName=@tableName, @Loc_dateColumnName=@dateColumnName, @Loc_fromDate=@fromDate, @Loc_endDate=@endDate, @Loc_farmId = @farmId

	IF @Loc_farmId IS NOT NULL AND @Loc_serverId IS NOT NULL
		RAISERROR ('Supplying both @farmId and @serverId is not supported',10,1)

	IF OBJECT_ID(@Loc_tableName, 'U') IS NULL 
	  RETURN

	DECLARE @rc BIGINT
	DECLARE @sqlCommand NVARCHAR(2000)
	DECLARE @paramDefinition NVARCHAR(500) 
	SET @rc = 1
	SET @sqlCommand = N'DELETE TOP(50000) FROM '+@Loc_tableName+' WHERE '+@Loc_dateColumnName+' >= @fromDate  AND '+ @Loc_dateColumnName+' <  @endDate'
	SET @paramDefinition=N'@fromDate DATETIME, @endDate DATETIME'
	IF @Loc_serverId IS NOT NULL
	BEGIN
		SET @sqlCommand = @sqlCommand+' FarmID = @farmId'
		SET @paramDefinition = @paramDefinition+ ', @farmId'
	END
	IF @Loc_farmId IS NOT NULL
	BEGIN
		SET @sqlCommand = @sqlCommand+' FarmID = @farmId'
		SET @paramDefinition = @paramDefinition+ ', @farmId'
	END
	IF @Loc_serverId IS NOT NULL
	BEGIN
		SET @sqlCommand = @sqlCommand+' '+ @Loc_serverColumnName+' = @serverId'
		SET @paramDefinition = @paramDefinition+ ', @serverId'
	END


	WHILE (@rc>0)
	BEGIN
		IF @Loc_farmId IS NULL
			EXECUTE sp_executesql @sqlCommand, @paramDefinition, @Loc_fromDate, @Loc_endDate
		ELSE IF @Loc_farmId IS NOT NULL
			EXECUTE sp_executesql @sqlCommand, @paramDefinition, @Loc_fromDate, @Loc_endDate, @Loc_farmId  
		ELSE 
			EXECUTE sp_executesql @sqlCommand, @paramDefinition, @Loc_fromDate, @Loc_endDate, @Loc_serverId  
		set @rc=@@ROWCOUNT
	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'Adding foreign keys to [dbo].[AdministrativeActionsLoadTimes]'
GO
ALTER TABLE [dbo].[AdministrativeActionsLoadTimes] ADD CONSTRAINT [FK_AdministrativeActionsLoadTimes_Farms] FOREIGN KEY ([FarmID]) REFERENCES [dbo].[Farms] ([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 [EventCollection].[Agents]'
GO
ALTER TABLE [EventCollection].[Agents] ADD CONSTRAINT [FK_Agents_CollectionDisabledReasons] FOREIGN KEY ([CollectionDisabledReasonId]) REFERENCES [EventCollection].[CollectionDisabledReasons] ([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].[LoadedColumns]'
GO
ALTER TABLE [SQL].[LoadedColumns] ADD CONSTRAINT [FK_LoadedColumns_LoadedObjects] FOREIGN KEY ([LoadedObjectsID]) REFERENCES [SQL].[LoadedObjects] ([LoadedObjectsID]) 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].[LoadedConstraints]'
GO
ALTER TABLE [SQL].[LoadedConstraints] ADD CONSTRAINT [FK_LoadedConstraints_LoadedObjects] FOREIGN KEY ([LoadedObjectsID]) REFERENCES [SQL].[LoadedObjects] ([LoadedObjectsID]) 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].[LoadedObjects]'
GO
ALTER TABLE [SQL].[LoadedObjects] ADD CONSTRAINT [FK_LoadedObjects_LoadedDatabases] FOREIGN KEY ([LoadedDatabaseID]) REFERENCES [SQL].[LoadedDatabases] ([LoadedDatabaseID]) ON DELETE CASCADE
ALTER TABLE [SQL].[LoadedObjects] ADD CONSTRAINT [FK_LoadedObjects_LoadingInfos] FOREIGN KEY ([LoadingInfoID]) REFERENCES [dbo].[LoadingInfos] ([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].[LoadedPrincipals]'
GO
ALTER TABLE [SQL].[LoadedPrincipals] ADD CONSTRAINT [FK_LoadedPrincipals_LoadedDatabases] FOREIGN KEY ([LoadedDatabaseID]) REFERENCES [SQL].[LoadedDatabases] ([LoadedDatabaseID]) 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].[LoadedRoles]'
GO
ALTER TABLE [SQL].[LoadedRoles] ADD CONSTRAINT [FK_LoadedRoles_LoadedDatabases] FOREIGN KEY ([LoadedDatabaseID]) REFERENCES [SQL].[LoadedDatabases] ([LoadedDatabaseID]) 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].[LoadedDatabases]'
GO
ALTER TABLE [SQL].[LoadedDatabases] ADD CONSTRAINT [FK_LoadedDatabases_LoadingInfos] FOREIGN KEY ([LoadingInfoID]) REFERENCES [dbo].[LoadingInfos] ([ID]) 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].[LoadedIndexColumns]'
GO
ALTER TABLE [SQL].[LoadedIndexColumns] ADD CONSTRAINT [FK_LoadedIndexColumns_LoadedIndexes] FOREIGN KEY ([LoadedIndexID]) REFERENCES [SQL].[LoadedIndexes] ([LoadedIndexID]) 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].[LoadedIndexes]'
GO
ALTER TABLE [SQL].[LoadedIndexes] ADD CONSTRAINT [FK_LoadedIndexes_LoadedObjects] FOREIGN KEY ([LoadedObjectId]) REFERENCES [SQL].[LoadedObjects] ([LoadedObjectsID]) 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].[LoadedProgrammabilities]'
GO
ALTER TABLE [SQL].[LoadedProgrammabilities] ADD CONSTRAINT [FK_LoadedProgrammabilities_LoadedObjects] FOREIGN KEY ([LoadedObjectsID]) REFERENCES [SQL].[LoadedObjects] ([LoadedObjectsID]) 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].[LoadedProgrammParameters]'
GO
ALTER TABLE [SQL].[LoadedProgrammParameters] ADD CONSTRAINT [FK_LoadedProgrammParameters_LoadedObjects] FOREIGN KEY ([LoadedObjectsID]) REFERENCES [SQL].[LoadedObjects] ([LoadedObjectsID]) 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].[LoadedRoleSecurables]'
GO
ALTER TABLE [SQL].[LoadedRoleSecurables] ADD CONSTRAINT [FK_LoadedRoleSecurables_LoadedObjects] FOREIGN KEY ([LoadedObjectID]) REFERENCES [SQL].[LoadedObjects] ([LoadedObjectsID])
ALTER TABLE [SQL].[LoadedRoleSecurables] ADD CONSTRAINT [FK_LoadedRoleSecurables_LoadingRoles] FOREIGN KEY ([LoadedRoleID]) REFERENCES [SQL].[LoadedRoles] ([LoadedRoleID]) 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].[LoadedStatistics]'
GO
ALTER TABLE [SQL].[LoadedStatistics] ADD CONSTRAINT [FK_LoadedStatistics_LoadedObjects] FOREIGN KEY ([LoadedObjectId]) REFERENCES [SQL].[LoadedObjects] ([LoadedObjectsID]) 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].[LoadedTableKeys]'
GO
ALTER TABLE [SQL].[LoadedTableKeys] ADD CONSTRAINT [FK_LoadedTableKeys_LoadedObjects] FOREIGN KEY ([LoadedObjectId]) REFERENCES [SQL].[LoadedObjects] ([LoadedObjectsID]) ON DELETE CASCADE
ALTER TABLE [SQL].[LoadedTableKeys] ADD CONSTRAINT [FK_LoadedTableKeysTarget_LoadedObjects] FOREIGN KEY ([TargetTableObjectId]) REFERENCES [SQL].[LoadedObjects] ([LoadedObjectsID])
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].[LoadedTables]'
GO
ALTER TABLE [SQL].[LoadedTables] ADD CONSTRAINT [FK_LoadedTables_LoadedObjects] FOREIGN KEY ([LoadedObjectsID]) REFERENCES [SQL].[LoadedObjects] ([LoadedObjectsID]) 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].[LoadedTriggers]'
GO
ALTER TABLE [SQL].[LoadedTriggers] ADD CONSTRAINT [FK_LoadedTriggers_LoadedObjects] FOREIGN KEY ([LoadedObjectId]) REFERENCES [SQL].[LoadedObjects] ([LoadedObjectsID]) 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].[RolePrincipals]'
GO
ALTER TABLE [SQL].[RolePrincipals] ADD CONSTRAINT [FK_RolePrincipals_LoadedPrincipals] FOREIGN KEY ([PrincipalID]) REFERENCES [SQL].[LoadedPrincipals] ([PrincipalID])
ALTER TABLE [SQL].[RolePrincipals] ADD CONSTRAINT [FK_RolePrincipals_LoadedRoles] FOREIGN KEY ([LoadedRoleID]) REFERENCES [SQL].[LoadedRoles] ([LoadedRoleID]) 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].[AdministrativeActionsLoadTimes]'
GO
GRANT SELECT ON  [dbo].[AdministrativeActionsLoadTimes] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[AuditActionTypes]'
GO
GRANT SELECT ON  [dbo].[AuditActionTypes] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[GetFarmPrincipals]'
GO
GRANT EXECUTE ON  [dbo].[GetFarmPrincipals] TO [spdockit_report_viewer]
GO
UPDATE Options SET OptionValue = '7.0.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.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
INSERT INTO EventCollection.CollectionDisabledReasons (ID, Name) VALUES (1, 'Index too large')
INSERT INTO EventCollection.CollectionDisabledReasons (ID, Name) VALUES (2, 'Disabled by user')
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

