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'Creating schemata'
GO
CREATE SCHEMA [PerformanceCounters]
AUTHORIZATION [dbo]
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 SCHEMA [Template]
AUTHORIZATION [dbo]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Removing schema binding from [dbo].[v_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]
  FROM SecurityEnabled sec, IsAdminTable a, [dbo].[FarmDescriptions] fd
  INNER JOIN [dbo].[LoadingInfos] l ON l.ID = fd.LoadingInfoID
  INNER JOIN [dbo].[Farms] f ON f.ID = l.FarmID
  WHERE sec.RBSecurityEnabled = 0 OR a.IsAdmin = 1 OR f.ID IN (SELECT myFarms.FarmID FROM MySPDocKitRoleFarms myFarms)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Removing schema binding from [dbo].[v_Principals]'
GO
ALTER VIEW [dbo].[v_Principals]
AS 
WITH SecurityEnabled (RBSecurityEnabled)
      AS
      (
            SELECT case WHEN OptionValue = 'False' THEN 0
						ELSE 1
						END 
            FROM [dbo].[Options] o
			WHERE o.OptionID = 2000     
      )
	SELECT [PrincipalID]
		  ,[Name]
		  ,[PrincipalTypeID]
		  ,[SiteID]
		  ,[TenantID]
		  ,[DisplayName]
		  ,[Email]
		  ,[ExternalUser]
		  ,p.[FarmID]
	  FROM SecurityEnabled sec, [dbo].[v_HasAccessToAllSPObjects] acc, [dbo].[Principals] p
	  LEFT OUTER JOIN [dbo].[v_SPObjectsWithAccess] a ON a.SiteCollectionID = p.SiteID
	  WHERE SiteID IS NULL OR sec.RBSecurityEnabled = 0 OR acc.HasAccess = 1 OR a.FarmID IS NOT NULL
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Removing schema binding from [dbo].[v_WebApplications]'
GO
ALTER VIEW [dbo].[v_WebApplications]
AS 
  WITH SecurityEnabled (RBSecurityEnabled)
      AS
      (
            SELECT case WHEN OptionValue = 'False' THEN 0
						ELSE 1
						END 
            FROM [dbo].[Options] o
			WHERE o.OptionID = 2000     
      ),
	  MyWebApplications
	  AS
	  (
	        SELECT DISTINCT a.WebApplicationID
			FROM [dbo].[v_SPObjectsWithAccess] a
	  )
	  SELECT [ID]
		  ,[Name]
		  ,[SPGuid]
		  ,[Url]
		  ,[Username]
		  ,[Password]
		  ,[Hash]
		  ,[BusinessPlanID]
		  ,[IsPartner]
		  ,[UseOrganizationalAccount]
		  ,w.[FarmID]
	  FROM SecurityEnabled sec, [dbo].[v_HasAccessToAllSPObjects] acc, [dbo].[WebApplications] w
	  LEFT OUTER JOIN MyWebApplications a ON a.WebApplicationID = w.ID
	  WHERE sec.RBSecurityEnabled = 0 OR acc.HasAccess = 1 OR a.WebApplicationID IS NOT NULL
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Removing schema binding from [dbo].[v_SharePointObjects]'
GO
ALTER VIEW [dbo].[v_SharePointObjects]
AS 
  WITH SecurityEnabled (RBSecurityEnabled)
      AS
      (
            SELECT case WHEN OptionValue = 'False' THEN 0
						ELSE 1
						END 
            FROM [dbo].[Options] o
			WHERE o.OptionID = 2000     
      )
	SELECT spo.[ID]
		  ,spo.[Name]
		  ,[SPObjectTypeID]
		  ,[SPSiteGuid]
		  ,[SPGuid]
		  ,[IsRootWeb]
		  ,[WebAppID]
		  ,[HasUniqueRoleAssignments]
		  ,[Url]
		  ,[SiteID]
		  ,[ContentTypeID]
		  ,[LastUpdated]
		  ,[CreatedOn]
		  ,[CreatedBy]
		  ,[ModifiedBy]
		  ,[SiteSize]
		  ,[MaxQuota]
		  ,spo.[FarmID]
	  FROM SecurityEnabled sec,  [dbo].[v_HasAccessToAllSPObjects] acc, [dbo].[SharePointObjects] spo
	  LEFT OUTER JOIN [dbo].[v_SPObjectsWithAccess] a ON a.SiteCollectionID = spo.SiteID
	  WHERE sec.RBSecurityEnabled = 0 OR acc.HasAccess = 1 OR a.WebApplicationID IS NOT NULL
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Removing schema binding from [dbo].[v_Farms]'
GO
ALTER VIEW [dbo].[v_Farms]
AS 
  WITH SecurityEnabled (RBSecurityEnabled)
      AS
      (
            SELECT case WHEN OptionValue = 'False' THEN 0
						ELSE 1
						END 
            FROM [dbo].[Options] o
			WHERE o.OptionID = 2000     
      ),
	  MyFarms
	  AS
	  (
	        SELECT DISTINCT a.FarmID
			FROM [dbo].[v_SPObjectsWithAccess] a
	  ),
	 MySPDocKitRoleFarms 
	 AS
	 (
			SELECT DISTINCT rf.FarmID 
			FROM [dbo].[SPDocKitRoleFarms] rf
			JOIN [dbo].[v_MySPDocKitRoles] r ON r.ID = rf.RoleID
	 )
	SELECT [ID]
		  ,[FarmGuid]
		  ,[Name]
		  ,[LSE]
		  ,[LEE]
		  ,[LicenseValidTo]
		  ,[Hash]
		  ,[CentralAdministrationUrl]
		  ,[Version]
		  ,[UsageDatabaseConnectionString]
		  ,[DiagnosticsLogLocation]
	  FROM SecurityEnabled sec,  [dbo].[v_HasAccessToAllSPObjects] acc, [dbo].[Farms] f
	  WHERE sec.RBSecurityEnabled = 0 OR acc.HasAccess = 1
		  OR f.ID IN (SELECT a.FarmID FROM MyFarms a)
		  OR f.ID IN (SELECT myFarms.FarmID FROM MySPDocKitRoleFarms myFarms)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Removing schema binding from [dbo].[v_WebApplicationConfigs]'
GO
ALTER VIEW [dbo].[v_WebApplicationConfigs]
AS 
 WITH SecurityEnabled (RBSecurityEnabled)
      AS
      (
            SELECT case WHEN OptionValue = 'False' THEN 0
						ELSE 1
						END 
            FROM [dbo].[Options] o
			WHERE o.OptionID = 2000     
      ),
	  MyWebApplications
	  AS
	  (
	        SELECT DISTINCT a.WebApplicationID
			FROM [dbo].[v_SPObjectsWithAccess] a
	  )
	  SELECT wc.[ID]
		  ,wc.[WebApplicationID]
		  ,wc.[WebApplicationZoneID]
		  ,wc.[ServerID]
		  ,wc.[LoadingInfoID]
		  ,wc.[FileContent]
		  ,s.[Name] as ServerName
		  ,z.[ZoneName] as ZoneName
	  FROM SecurityEnabled sec, [dbo].[v_HasAccessToAllSPObjects] acc, [dbo].[WebApplicationConfigs] wc
	  INNER JOIN [dbo].[MonitoringServers] s ON s.ID = wc.ServerID
	  INNER JOIN [dbo].[WebApplicationZones] z ON z.ZoneID = wc.WebApplicationZoneID
	  LEFT OUTER JOIN MyWebApplications a ON a.WebApplicationID = wc.WebApplicationID
	  WHERE sec.RBSecurityEnabled = 0 OR acc.HasAccess = 1 OR a.WebApplicationID IS NOT NULL
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Removing schema binding from [dbo].[v_SPObjectsWithAccess]'
GO
ALTER VIEW [dbo].[v_SPObjectsWithAccess]
AS 
	SELECT DISTINCT rsc.FarmID,
		   rsc.WebApplicationID,
		   rsc.SiteCollectionID
	FROM	[dbo].[SPDocKitUsers] u
	INNER JOIN [dbo].[SPDocKitUserRoles] ur ON u.ID = ur.UserID   
	INNER JOIN [dbo].[SPDocKitRoles] r ON ur.RoleID = r.ID   
	INNER JOIN [dbo].[SPDocKitRoleSiteCollections] rsc ON r.ID = rsc.RoleID
	WHERE IS_MEMBER (u.LoginName) = 1 AND r.SPObjectSharingPolicy = 3

	-- bitno je da ovo bude DISTINCT
	UNION 

	SELECT DISTINCT sca.FarmID,
		   sca.WebApplicationID,
		   sca.SiteID
	FROM	[dbo].[SPDocKitUsers] u
	INNER JOIN [dbo].[SPDocKitUserRoles] ur ON u.ID = ur.UserID   
	INNER JOIN [dbo].[SPDocKitRoles] r ON ur.RoleID = r.ID   
	CROSS JOIN [dbo].[SPDocKitSiteCollectionAdministrators] sca 
	INNER JOIN [dbo].[Principals] p ON sca.PrincipalID = p.PrincipalID
	WHERE IS_MEMBER (u.LoginName) = 1 AND r.SPObjectSharingPolicy = 2 AND p.Name = SUSER_NAME()
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Removing schema binding from [EventCollection].[v_SPDocKitUserFarms]'
GO
ALTER VIEW [EventCollection].[v_SPDocKitUserFarms]
AS	
WITH SecurityEnabled (RBSecurityEnabled)
      AS
      (
            SELECT CAST(case WHEN OptionValue = 'False' THEN 0
						ELSE 1
						END AS BIT)
            FROM [dbo].[Options] o
			WHERE o.OptionID = 2000     
      ),
RoleFarms AS (select  u.LoginName, u.PrincipalTypeID,  f.[ID]
					  ,f.[FarmGuid]
					  ,f.[Name]
					  ,f.[LSE]
					  ,f.[Hash]
					  ,f.[CentralAdministrationUrl]
					  ,f.[Version]
					  ,f.[UsageDatabaseConnectionString]
					  ,f.[DiagnosticsLogLocation]
			FROM [dbo].[SPDocKitUsers] u
			INNER JOIN [dbo].[SPDocKitUserRoles] ur ON u.ID = ur.UserID   
			INNER JOIN [dbo].[SPDocKitRoles] r ON ur.RoleID = r.ID   
			INNER JOIN [dbo].[SPDocKitRoleFarms] rf ON rf.RoleID = r.ID
			INNER JOIN [dbo].[Farms] f ON rf.FarmID = f.ID
			UNION 
			SELECT u.LoginName, u.PrincipalTypeID, f.[ID]
					  ,f.[FarmGuid]
					  ,f.[Name]
					  ,f.[LSE]
					  ,f.[Hash]
					  ,f.[CentralAdministrationUrl]
					  ,f.[Version]
					  ,f.[UsageDatabaseConnectionString]
					  ,f.[DiagnosticsLogLocation]
			FROM[dbo].[SPDocKitUsers] u
			INNER JOIN [dbo].[SPDocKitUserRoles] ur ON u.ID = ur.UserID   
			INNER JOIN [dbo].[SPDocKitRoles] r ON ur.RoleID = r.ID   
			CROSS APPLY [dbo].[Farms] AS f
			WHERE r.Name ='Admins'),
AllFarms AS (
 SELECT NULL AS 'LoginName', NULL AS 'PrincipalTypeID',  f.[ID]
					  ,f.[FarmGuid]
					  ,f.[Name]
					  ,f.[LSE]
					  ,f.[Hash]
					  ,f.[CentralAdministrationUrl]
					  ,f.[Version]
					  ,f.[UsageDatabaseConnectionString]
					  ,f.[DiagnosticsLogLocation]
		FROM [dbo].[Farms] AS f)

SELECT  f.LoginName, f.PrincipalTypeID, f.[ID]
					  ,f.[FarmGuid]
					  ,f.[Name]
					  ,f.[LSE]
					  ,f.[Hash]
					  ,f.[CentralAdministrationUrl]
					  ,f.[Version]
					  ,f.[UsageDatabaseConnectionString]
					  ,f.[DiagnosticsLogLocation]
FROM(
	SELECT f.LoginName, f.PrincipalTypeID, f.[ID]
					  ,f.[FarmGuid]
					  ,f.[Name]
					  ,f.[LSE]
					  ,f.[Hash]
					  ,f.[CentralAdministrationUrl]
					  ,f.[Version]
					  ,f.[UsageDatabaseConnectionString]
					  ,f.[DiagnosticsLogLocation]
					  , s.RBSecurityEnabled AS 'ResFlag' FROM RoleFarms AS f CROSS APPLY SecurityEnabled as s
	UNION 
	SELECT f.LoginName, f.PrincipalTypeID, f.[ID]
					  ,f.[FarmGuid]
					  ,f.[Name]
					  ,f.[LSE]
					  ,f.[Hash]
					  ,f.[CentralAdministrationUrl]
					  ,f.[Version]
					  ,f.[UsageDatabaseConnectionString]
					  ,f.[DiagnosticsLogLocation]
					  , ~s.RBSecurityEnabled AS 'ResFlag' FROM AllFarms AS f CROSS APPLY SecurityEnabled as s
	)AS f 
WHERE ResFlag = 1
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Removing schema binding from [EventCollection].[v_SPDocKitAdmins]'
GO
ALTER VIEW [EventCollection].[v_SPDocKitAdmins]
AS

WITH SecurityEnabled (RBSecurityEnabled)
      AS
      (
            SELECT CAST(case WHEN OptionValue = 'False' THEN 0
						ELSE 1
						END AS BIT)
            FROM [dbo].[Options] o
			WHERE o.OptionID = 2000     
      )

SELECT i.LoginName, i.PrincipalTypeId FROM(
	SELECT DISTINCT u.LoginName, u.PrincipalTypeID, s.RBSecurityEnabled AS 'ResFlag'
			FROM[dbo].[SPDocKitUsers] u
			INNER JOIN [dbo].[SPDocKitUserRoles] ur ON u.ID = ur.UserID   
			INNER JOIN [dbo].[SPDocKitRoles] r ON ur.RoleID = r.ID   
			CROSS APPLY SecurityEnabled	AS s
			WHERE r.Name ='Admins'
	UNION 
	 SELECT NULL AS LoginName, NULL AS PrincipalTypeID, ~s.RBSecurityEnabled AS 'ResFlag'
	FROM SecurityEnabled AS s) AS i
WHERE ResFlag = 1
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping foreign keys from [dbo].[Principals]'
GO
ALTER TABLE [dbo].[Principals] DROP CONSTRAINT[FK_Principals_WebApplications]
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 foreign keys from [dbo].[MonitoringEventLogs]'
GO
ALTER TABLE [dbo].[MonitoringEventLogs] DROP CONSTRAINT[FK_MonitoringEventLogs_MonitoringServers]
ALTER TABLE [dbo].[MonitoringEventLogs] DROP CONSTRAINT[FK_MonitoringEventLogs_Farms]
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 foreign keys from [dbo].[MonitoringOptions]'
GO
ALTER TABLE [dbo].[MonitoringOptions] DROP CONSTRAINT[FK_MonitoringOptions_Farms]
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 foreign keys from [dbo].[MonitoringUlsEvents]'
GO
ALTER TABLE [dbo].[MonitoringUlsEvents] DROP CONSTRAINT[FK_MonitoringUlsEvents_MonitoringServers]
ALTER TABLE [dbo].[MonitoringUlsEvents] DROP CONSTRAINT[FK_MonitoringUlsEvents_Farms]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping constraints from [dbo].[MonitoringEventLogs]'
GO
ALTER TABLE [dbo].[MonitoringEventLogs] DROP CONSTRAINT [PK_MonitoringEventLogs]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping constraints from [dbo].[MonitoringOptions]'
GO
ALTER TABLE [dbo].[MonitoringOptions] DROP CONSTRAINT [PK_MonitoringOptions]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping constraints from [dbo].[MonitoringUlsEvents]'
GO
ALTER TABLE [dbo].[MonitoringUlsEvents] DROP CONSTRAINT [PK_MonitoringUlsEvents]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping [dbo].[MonitoringUlsEvents]'
GO
DROP TABLE [dbo].[MonitoringUlsEvents]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping [dbo].[MonitoringOptions]'
GO
DROP TABLE [dbo].[MonitoringOptions]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping [dbo].[MonitoringEventLogs]'
GO
DROP TABLE [dbo].[MonitoringEventLogs]
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 [PerformanceCounters].[DiskCounterTableType] AS TABLE
(
[CollectionDate] [datetime] NOT NULL,
[TerminalServerID] [int] NOT NULL,
[DiskID] [int] NOT NULL,
[Value] [real] 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 [PerformanceCounters].[NetworkCounterTableType] AS TABLE
(
[CollectionDate] [datetime] NOT NULL,
[TerminalServerID] [int] NOT NULL,
[NetworkAdapterID] [int] NOT NULL,
[Value] [real] 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 [PerformanceCounters].[ProcessCounterTableType] AS TABLE
(
[CollectionDate] [datetime] NOT NULL,
[TerminalServerID] [int] NOT NULL,
[ProcessID] [int] NOT NULL,
[PID] [int] NOT NULL,
[UserID] [int] NULL,
[Value] [real] 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 [PerformanceCounters].[ServerCounterTableType] AS TABLE
(
[CollectionDate] [datetime] NOT NULL,
[TerminalServerID] [int] NOT NULL,
[Value] [real] 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 [dbo].[GroupSettingsType] AS TABLE
(
[PrincipalID] [int] NOT NULL,
[GroupName] [nvarchar] (max) NULL,
[AllowMembersEditMembership] [bit] NULL,
[OnlyAllowMembersViewMembership] [bit] NULL,
[AutoAcceptRequestToJoinLeave] [bit] NULL,
[MembershipRequestEmails] [nvarchar] (max) NULL,
[GroupDescription] [nvarchar] (max) NULL,
[SharepointID] [int] NULL,
[PrincipalOwnerID] [int] NULL,
[AllowRequestToJoinLeave] [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 [PerformanceCounters].[DiskRead]'
GO
CREATE TABLE [PerformanceCounters].[DiskRead]
(
[CollectionDate] [datetime] NOT NULL,
[TerminalServerID] [int] NOT NULL,
[DiskID] [int] NOT NULL,
[Value] [real] 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_DiskRead] on [PerformanceCounters].[DiskRead]'
GO
ALTER TABLE [PerformanceCounters].[DiskRead] ADD CONSTRAINT [PK_DiskRead] PRIMARY KEY CLUSTERED  ([CollectionDate], [TerminalServerID], [DiskID])
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_DiskRead_Id] on [PerformanceCounters].[DiskRead]'
GO
CREATE NONCLUSTERED INDEX [IX_DiskRead_Id] ON [PerformanceCounters].[DiskRead] ([TerminalServerID], [DiskID], [CollectionDate]) INCLUDE ([Value])
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].[DiskTransfer]'
GO
CREATE TABLE [PerformanceCounters].[DiskTransfer]
(
[CollectionDate] [datetime] NOT NULL,
[TerminalServerID] [int] NOT NULL,
[DiskID] [int] NOT NULL,
[Value] [real] 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_DiskTransfer] on [PerformanceCounters].[DiskTransfer]'
GO
ALTER TABLE [PerformanceCounters].[DiskTransfer] ADD CONSTRAINT [PK_DiskTransfer] PRIMARY KEY CLUSTERED  ([CollectionDate], [TerminalServerID], [DiskID])
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_DiskTransfer_Id] on [PerformanceCounters].[DiskTransfer]'
GO
CREATE NONCLUSTERED INDEX [IX_DiskTransfer_Id] ON [PerformanceCounters].[DiskTransfer] ([TerminalServerID], [DiskID], [CollectionDate]) INCLUDE ([Value])
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].[InsertDiskTransfer]'
GO
CREATE PROCEDURE[PerformanceCounters].[InsertDiskTransfer]
@TableVariable PerformanceCounters.DiskCounterTableType READONLY
AS
BEGIN
   SET NOCOUNT ON


   INSERT INTO [PerformanceCounters].DiskTransfer
           (CollectionDate,
          TerminalServerID,
		  DiskID,
          Value)

     SELECT CollectionDate, TerminalServerID, DiskID, Value

     FROM @TableVariable
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].[DiskWrite]'
GO
CREATE TABLE [PerformanceCounters].[DiskWrite]
(
[CollectionDate] [datetime] NOT NULL,
[TerminalServerID] [int] NOT NULL,
[DiskID] [int] NOT NULL,
[Value] [real] 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_DiskWrite] on [PerformanceCounters].[DiskWrite]'
GO
ALTER TABLE [PerformanceCounters].[DiskWrite] ADD CONSTRAINT [PK_DiskWrite] PRIMARY KEY CLUSTERED  ([CollectionDate], [TerminalServerID], [DiskID])
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_DiskWrite_Id] on [PerformanceCounters].[DiskWrite]'
GO
CREATE NONCLUSTERED INDEX [IX_DiskWrite_Id] ON [PerformanceCounters].[DiskWrite] ([TerminalServerID], [DiskID], [CollectionDate]) INCLUDE ([Value])
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].[InsertDiskWrite]'
GO
CREATE PROCEDURE[PerformanceCounters].[InsertDiskWrite]
@TableVariable PerformanceCounters.DiskCounterTableType READONLY
AS
BEGIN
   SET NOCOUNT ON


   INSERT INTO [PerformanceCounters].DiskWrite
           (CollectionDate,
          TerminalServerID,
		  DiskID,
          Value)

     SELECT CollectionDate, TerminalServerID, DiskID, Value

     FROM @TableVariable
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].[Memory]'
GO
CREATE TABLE [PerformanceCounters].[Memory]
(
[CollectionDate] [datetime] NOT NULL,
[TerminalServerID] [int] NOT NULL,
[Value] [real] 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_PerformanceCounter_Memory] on [PerformanceCounters].[Memory]'
GO
ALTER TABLE [PerformanceCounters].[Memory] ADD CONSTRAINT [PK_PerformanceCounter_Memory] PRIMARY KEY CLUSTERED  ([CollectionDate], [TerminalServerID])
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_Memory_Id] on [PerformanceCounters].[Memory]'
GO
CREATE NONCLUSTERED INDEX [IX_Memory_Id] ON [PerformanceCounters].[Memory] ([TerminalServerID], [CollectionDate]) INCLUDE ([Value])
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].[NetworkReceived]'
GO
CREATE TABLE [PerformanceCounters].[NetworkReceived]
(
[CollectionDate] [datetime] NOT NULL,
[TerminalServerID] [int] NOT NULL,
[NetworkAdapterID] [int] NOT NULL,
[Value] [real] 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_PerformanceCounter_NetworkReceived] on [PerformanceCounters].[NetworkReceived]'
GO
ALTER TABLE [PerformanceCounters].[NetworkReceived] ADD CONSTRAINT [PK_PerformanceCounter_NetworkReceived] PRIMARY KEY CLUSTERED  ([CollectionDate], [TerminalServerID], [NetworkAdapterID])
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_NetworkReceived_Id] on [PerformanceCounters].[NetworkReceived]'
GO
CREATE NONCLUSTERED INDEX [IX_NetworkReceived_Id] ON [PerformanceCounters].[NetworkReceived] ([TerminalServerID], [NetworkAdapterID], [CollectionDate]) INCLUDE ([Value])
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].[NetworkSent]'
GO
CREATE TABLE [PerformanceCounters].[NetworkSent]
(
[CollectionDate] [datetime] NOT NULL,
[TerminalServerID] [int] NOT NULL,
[NetworkAdapterID] [int] NOT NULL,
[Value] [real] 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_PerformanceCounter_NetworkSent] on [PerformanceCounters].[NetworkSent]'
GO
ALTER TABLE [PerformanceCounters].[NetworkSent] ADD CONSTRAINT [PK_PerformanceCounter_NetworkSent] PRIMARY KEY CLUSTERED  ([CollectionDate], [TerminalServerID], [NetworkAdapterID])
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_NetworkSent_Id] on [PerformanceCounters].[NetworkSent]'
GO
CREATE NONCLUSTERED INDEX [IX_NetworkSent_Id] ON [PerformanceCounters].[NetworkSent] ([TerminalServerID], [NetworkAdapterID], [CollectionDate]) INCLUDE ([Value])
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].[InsertNetworkSent]'
GO
CREATE PROCEDURE[PerformanceCounters].[InsertNetworkSent]
@TableVariable PerformanceCounters.NetworkCounterTableType READONLY
AS
BEGIN
   SET NOCOUNT ON


   INSERT INTO [PerformanceCounters].[NetworkSent]
           (CollectionDate,
          TerminalServerID,
		  NetworkAdapterID,
          Value)

     SELECT CollectionDate, TerminalServerID, NetworkAdapterID, Value

     FROM @TableVariable
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].[InsertProcessCPU]'
GO
CREATE PROCEDURE[PerformanceCounters].[InsertProcessCPU]
@TableVariable PerformanceCounters.ProcessCounterTableType READONLY
AS
BEGIN
   SET NOCOUNT ON


   INSERT INTO [PerformanceCounters].ProcessCPU
           (CollectionDate,
          TerminalServerID,
          ProcessID,
          PID,
          UserID,
          Value)

     SELECT CollectionDate, TerminalServerID, ProcessID, PID, UserID, Value

     FROM @TableVariable
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].[InsertProcessIOOperations]'
GO
CREATE PROCEDURE[PerformanceCounters].[InsertProcessIOOperations]
@TableVariable PerformanceCounters.ProcessCounterTableType READONLY
AS
BEGIN
   SET NOCOUNT ON


   INSERT INTO [PerformanceCounters].ProcessIOOperations
           (CollectionDate,
          TerminalServerID,
          ProcessID,
          PID,
          UserID,
          Value)

     SELECT CollectionDate, TerminalServerID, ProcessID, PID, UserID, Value

     FROM @TableVariable
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].[InsertProcessIOReads]'
GO
CREATE PROCEDURE[PerformanceCounters].[InsertProcessIOReads]
@TableVariable PerformanceCounters.ProcessCounterTableType READONLY
AS
BEGIN
   SET NOCOUNT ON


   INSERT INTO [PerformanceCounters].ProcessIOReads
           (CollectionDate,
          TerminalServerID,
          ProcessID,
          PID,
          UserID,
          Value)

     SELECT CollectionDate, TerminalServerID, ProcessID, PID, UserID, Value

     FROM @TableVariable
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].[InsertProcessIOWrites]'
GO
CREATE PROCEDURE[PerformanceCounters].[InsertProcessIOWrites]
@TableVariable PerformanceCounters.ProcessCounterTableType READONLY
AS
BEGIN
   SET NOCOUNT ON


   INSERT INTO [PerformanceCounters].ProcessIOWrites
           (CollectionDate,
          TerminalServerID,
          ProcessID,
          PID,
          UserID,
          Value)

     SELECT CollectionDate, TerminalServerID, ProcessID, PID, UserID, Value

     FROM @TableVariable
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].[InsertProcessMemory]'
GO
CREATE PROCEDURE[PerformanceCounters].[InsertProcessMemory]
@TableVariable PerformanceCounters.ProcessCounterTableType READONLY
AS
BEGIN
   SET NOCOUNT ON


   INSERT INTO [PerformanceCounters].ProcessMemory
           (CollectionDate,
          TerminalServerID,
          ProcessID,
          PID,
          UserID,
          Value)

     SELECT CollectionDate, TerminalServerID, ProcessID, PID, UserID, Value

     FROM @TableVariable
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].[CounterValues]'
GO
CREATE TABLE [PerformanceCounters].[CounterValues]
(
[CollectionDate] [datetime] NOT NULL,
[TerminalServerID] [int] NOT NULL,
[CounterID] [int] NOT NULL,
[InstanceID] [int] NOT NULL,
[Value] [real] 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_CounterValues] on [PerformanceCounters].[CounterValues]'
GO
ALTER TABLE [PerformanceCounters].[CounterValues] ADD CONSTRAINT [PK_CounterValues] PRIMARY KEY CLUSTERED  ([CollectionDate], [TerminalServerID], [CounterID], [InstanceID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating index [IX_CounterValues_Id] on [PerformanceCounters].[CounterValues]'
GO
CREATE NONCLUSTERED INDEX [IX_CounterValues_Id] ON [PerformanceCounters].[CounterValues] ([TerminalServerID], [CounterID], [InstanceID], [CollectionDate]) INCLUDE ([Value])
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].[LastCounterValueForServerAndCounter]'
GO
CREATE PROCEDURE [PerformanceCounters].[LastCounterValueForServerAndCounter]
	@TerminalServerID int
AS
	SET NOCOUNT ON

	SELECT MAX(CollectionDate) as CollectionDate,
		CounterID,
		InstanceID,
		Value
	FROM (SELECT CounterID,
				InstanceID,
				CollectionDate,
				Value
			FROM PerformanceCounters.CounterValues PC
			WHERE PC.CollectionDate = (
					SELECT MAX(CollectionDate)
					FROM PerformanceCounters.CounterValues CV
					WHERE PC.TerminalServerID=CV.TerminalServerID
					AND PC.CounterID=CV.CounterID
					AND PC.InstanceID=CV.InstanceID)
			AND PC.TerminalServerID=@TerminalServerID
		) as InstanceTable
	GROUP BY CounterID, InstanceID, Value
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].[CPU]'
GO
CREATE TABLE [PerformanceCounters].[CPU]
(
[CollectionDate] [datetime] NOT NULL,
[TerminalServerID] [int] NOT NULL,
[Value] [real] 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_PerformanceCounter_CPU] on [PerformanceCounters].[CPU]'
GO
ALTER TABLE [PerformanceCounters].[CPU] ADD CONSTRAINT [PK_PerformanceCounter_CPU] PRIMARY KEY CLUSTERED  ([CollectionDate], [TerminalServerID])
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_CPU_Id] on [PerformanceCounters].[CPU]'
GO
CREATE NONCLUSTERED INDEX [IX_CPU_Id] ON [PerformanceCounters].[CPU] ([TerminalServerID], [CollectionDate]) INCLUDE ([Value])
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].[LastCPUValueForServer]'
GO
CREATE PROCEDURE [PerformanceCounters].[LastCPUValueForServer]
	@TerminalServerID int
AS
BEGIN

	SET NOCOUNT ON

    SELECT
		CollectionDate, 
		Value
	FROM       
		[PerformanceCounters].[CPU]
		INNER JOIN 
		(
			SELECT TOP(1)
				TerminalServerID,
				CollectionDate AS MaxDate
			FROM
				[PerformanceCounters].[CPU]
			WHERE 
				TerminalServerID = @TerminalServerID
			GROUP BY  
				TerminalServerID, CollectionDate		 
			ORDER BY
				CollectionDate DESC
		) AS x
		ON     
			[PerformanceCounters].[CPU].TerminalServerID = x.TerminalServerID AND [PerformanceCounters].[CPU].CollectionDate = x.MaxDate
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].[DiskQueue]'
GO
CREATE TABLE [PerformanceCounters].[DiskQueue]
(
[CollectionDate] [datetime] NOT NULL,
[TerminalServerID] [int] NOT NULL,
[DiskID] [int] NOT NULL,
[Value] [real] 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_DiskQueue] on [PerformanceCounters].[DiskQueue]'
GO
ALTER TABLE [PerformanceCounters].[DiskQueue] ADD CONSTRAINT [PK_DiskQueue] PRIMARY KEY CLUSTERED  ([CollectionDate], [TerminalServerID], [DiskID])
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_DiskQueue_Id] on [PerformanceCounters].[DiskQueue]'
GO
CREATE NONCLUSTERED INDEX [IX_DiskQueue_Id] ON [PerformanceCounters].[DiskQueue] ([TerminalServerID], [DiskID], [CollectionDate]) INCLUDE ([Value])
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].[DiskFreeSpace]'
GO
CREATE TABLE [PerformanceCounters].[DiskFreeSpace]
(
[CollectionDate] [datetime] NOT NULL,
[TerminalServerID] [int] NOT NULL,
[DiskID] [int] NOT NULL,
[Value] [real] 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_DiskFreeSpace] on [PerformanceCounters].[DiskFreeSpace]'
GO
ALTER TABLE [PerformanceCounters].[DiskFreeSpace] ADD CONSTRAINT [PK_DiskFreeSpace] PRIMARY KEY CLUSTERED  ([CollectionDate], [TerminalServerID], [DiskID])
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_DiskFreeSpace_Id] on [PerformanceCounters].[DiskFreeSpace]'
GO
CREATE NONCLUSTERED INDEX [IX_DiskFreeSpace_Id] ON [PerformanceCounters].[DiskFreeSpace] ([TerminalServerID], [DiskID], [CollectionDate]) INCLUDE ([Value])
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].[LastMemoryValueForServer]'
GO

CREATE PROCEDURE [PerformanceCounters].[LastMemoryValueForServer]
	@TerminalServerID int
AS
BEGIN

	SET NOCOUNT ON

	SELECT
		CollectionDate, 
		Value
	FROM
		[PerformanceCounters].[Memory]
		INNER JOIN 
		(
			SELECT TOP(1)
				TerminalServerID,
				CollectionDate AS MaxDate
			FROM
				[PerformanceCounters].[Memory]
			WHERE 
				TerminalServerID = @TerminalServerID
			GROUP BY  
				TerminalServerID, CollectionDate	
			ORDER BY
				CollectionDate DESC
		) AS x
		ON	[PerformanceCounters].[Memory].TerminalServerID = x.TerminalServerID AND [PerformanceCounters].[Memory].CollectionDate = x.MaxDate
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].[LastNetworkDateForServer]'
GO
CREATE PROCEDURE [PerformanceCounters].[LastNetworkDateForServer]
	@TerminalServerID int
AS
BEGIN

	SET NOCOUNT ON

	SELECT MAX(CollectionDate) as CollectionDate,
		NetworkAdapterID
	FROM (SELECT NetworkAdapterID, CollectionDate 
			FROM PerformanceCounters.NetworkReceived PC
			WHERE PC.CollectionDate = (
				SELECT MAX(CollectionDate)
				FROM PerformanceCounters.NetworkReceived N
				WHERE PC.TerminalServerID=N.TerminalServerID
				AND PC.NetworkAdapterID=N.NetworkAdapterID)
			AND PC.TerminalServerID=@TerminalServerID
			AND PC.Value IS NOT NULL
		UNION ALL
			SELECT NetworkAdapterID, CollectionDate 
			FROM PerformanceCounters.NetworkSent PC
			WHERE PC.CollectionDate = (
				SELECT MAX(CollectionDate)
				FROM PerformanceCounters.NetworkSent N
				WHERE PC.TerminalServerID=N.TerminalServerID
				AND PC.NetworkAdapterID=N.NetworkAdapterID)
			AND PC.TerminalServerID=@TerminalServerID
			AND PC.Value IS NOT NULL) as NetworkTable
	GROUP BY NetworkAdapterID
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].[ConnectedSiteCollections]'
GO
CREATE TABLE [dbo].[ConnectedSiteCollections]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[SPDocKitWorkstationID] [int] NOT NULL,
[FarmID] [int] NOT NULL,
[IntegratedSecurity] [bit] NOT NULL,
[SPDocKitCredentialID] [int] NULL,
[Url] [nvarchar] (2048) NOT NULL,
[WebApplicationID] [int] NOT NULL,
[Version] [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_ConnectedSiteCollections] on [dbo].[ConnectedSiteCollections]'
GO
ALTER TABLE [dbo].[ConnectedSiteCollections] ADD CONSTRAINT [PK_ConnectedSiteCollections] 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 index [IX_ConnectedSiteCollections] on [dbo].[ConnectedSiteCollections]'
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_ConnectedSiteCollections] ON [dbo].[ConnectedSiteCollections] ([SPDocKitWorkstationID], [FarmID], [WebApplicationID], [Url]) INCLUDE ([IntegratedSecurity], [SPDocKitCredentialID], [Version])
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].[GetConnectedSiteCollections]'
GO
CREATE PROCEDURE [dbo].[GetConnectedSiteCollections]
	@FarmID int,
	@WorkstationID int,
	@WebApplicationID int
	AS
	SET NOCOUNT ON	
	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE @Loc_WorkstationID int
	DECLARE	@Loc_FarmID int
	DECLARE	@Loc_WebApplicationID int

	SELECT @Loc_WorkstationID = @WorkstationID,
		   @Loc_FarmID = @FarmID,
		   @Loc_WebApplicationID = @WebApplicationID
		   	
	SELECT sc.Url
	FROM ConnectedSiteCollections sc
	WHERE sc.FarmID = @Loc_FarmID AND sc.SPDocKitWorkstationID = @Loc_WorkstationID AND sc.WebApplicationID = @Loc_WebApplicationID
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[Options]'
GO
ALTER TABLE [dbo].[Options] ALTER COLUMN [OptionValue] [nvarchar] (max) NOT NULL
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [EventCollection].[v_SPDocKitAdmins]'
GO
ALTER VIEW [EventCollection].[v_SPDocKitAdmins]
WITH SCHEMABINDING
AS

WITH SecurityEnabled (RBSecurityEnabled)
      AS
      (
            SELECT CAST(case WHEN OptionValue = 'False' THEN 0
						ELSE 1
						END AS BIT)
            FROM [dbo].[Options] o
			WHERE o.OptionID = 2000     
      )

SELECT i.LoginName, i.PrincipalTypeId FROM(
	SELECT DISTINCT u.LoginName, u.PrincipalTypeID, s.RBSecurityEnabled AS 'ResFlag'
			FROM[dbo].[SPDocKitUsers] u
			INNER JOIN [dbo].[SPDocKitUserRoles] ur ON u.ID = ur.UserID   
			INNER JOIN [dbo].[SPDocKitRoles] r ON ur.RoleID = r.ID   
			CROSS APPLY SecurityEnabled	AS s
			WHERE r.Name ='Admins'
	UNION 
	 SELECT NULL AS LoginName, NULL AS PrincipalTypeID, ~s.RBSecurityEnabled AS 'ResFlag'
	FROM SecurityEnabled AS s) AS i
WHERE ResFlag = 1
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [PerformanceCounters].[InsertCPU]'
GO
CREATE PROCEDURE [PerformanceCounters].[InsertCPU]
@TableVariable PerformanceCounters.ServerCounterTableType READONLY
AS
BEGIN
   SET NOCOUNT ON


   INSERT INTO [PerformanceCounters].CPU
           (CollectionDate,
          TerminalServerID,
          Value)

     SELECT CollectionDate, TerminalServerID, Value

     FROM @TableVariable
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].[InsertDiskFreeSpace]'
GO
CREATE PROCEDURE[PerformanceCounters].[InsertDiskFreeSpace]
@TableVariable PerformanceCounters.DiskCounterTableType READONLY
AS
BEGIN
   SET NOCOUNT ON


   INSERT INTO [PerformanceCounters].[DiskFreeSpace]
           (CollectionDate,
          TerminalServerID,
		  DiskID,
          Value)

     SELECT CollectionDate, TerminalServerID, DiskID, Value

     FROM @TableVariable
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].[InsertDiskQueue]'
GO
CREATE PROCEDURE[PerformanceCounters].[InsertDiskQueue]
@TableVariable PerformanceCounters.DiskCounterTableType READONLY
AS
BEGIN
   SET NOCOUNT ON


   INSERT INTO [PerformanceCounters].DiskQueue
           (CollectionDate,
          TerminalServerID,
		  DiskID,
          Value)

     SELECT CollectionDate, TerminalServerID, DiskID, Value

     FROM @TableVariable
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].[ServerInstances]'
GO
CREATE TABLE [PerformanceCounters].[ServerInstances]
(
[TerminalServerID] [int] NOT NULL,
[CounterID] [int] NOT NULL,
[InstanceID] [int] NOT NULL,
[CounterStatusID] [int] NOT NULL CONSTRAINT [DF_ServerInstances_CounterStatusID] DEFAULT ((1))
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_ServerInstances] on [PerformanceCounters].[ServerInstances]'
GO
ALTER TABLE [PerformanceCounters].[ServerInstances] ADD CONSTRAINT [PK_ServerInstances] PRIMARY KEY CLUSTERED  ([TerminalServerID], [CounterID], [InstanceID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [SQL].[Instances]'
GO
ALTER TABLE [SQL].[Instances] ADD
[Version] [nvarchar] (50) 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].[SPDocKitWorkstations]'
GO
CREATE TABLE [dbo].[SPDocKitWorkstations]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[Name] [nvarchar] (1000) NOT NULL,
[LastSeenAlive] [datetime] NOT NULL,
[LSE] [nvarchar] (255) NOT NULL,
[LEE] [nvarchar] (255) NOT NULL,
[LicenseValidTo] [datetime] NULL,
[Hash] [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_SPDocKitWorkstations] on [dbo].[SPDocKitWorkstations]'
GO
ALTER TABLE [dbo].[SPDocKitWorkstations] ADD CONSTRAINT [PK_SPDocKitWorkstations] 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].[Categories]'
GO
CREATE TABLE [PerformanceCounters].[Categories]
(
[CategoryID] [int] NOT NULL IDENTITY(1, 1),
[ClassName] [nvarchar] (500) NOT NULL,
[Singleton] [bit] NOT NULL,
[DisplayName] [nvarchar] (500) NULL,
[Description] [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_PerformanceCounter.Category] on [PerformanceCounters].[Categories]'
GO
ALTER TABLE [PerformanceCounters].[Categories] ADD CONSTRAINT [PK_PerformanceCounter.Category] PRIMARY KEY CLUSTERED  ([CategoryID])
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].[Counters]'
GO
CREATE TABLE [PerformanceCounters].[Counters]
(
[CounterID] [int] NOT NULL IDENTITY(1, 1),
[CategoryID] [int] NOT NULL,
[Name] [nvarchar] (500) NOT NULL,
[DisplayName] [nvarchar] (500) NULL,
[Description] [nvarchar] (max) NULL,
[CounterType] [bigint] 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_Counters] on [PerformanceCounters].[Counters]'
GO
ALTER TABLE [PerformanceCounters].[Counters] ADD CONSTRAINT [PK_Counters] PRIMARY KEY CLUSTERED  ([CounterID])
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].[CounterThresholds]'
GO
CREATE TABLE [PerformanceCounters].[CounterThresholds]
(
[CounterID] [int] NOT NULL,
[InstanceID] [int] NOT NULL,
[TemplateID] [int] NOT NULL,
[CriticalThreshold] [float] NOT NULL,
[WarningThreshold] [float] NOT NULL,
[ThresholdPeriod] [int] NOT NULL,
[SendAlert] [bit] NOT NULL,
[SendWarningAlert] [bit] NOT NULL,
[IgnorePeriod] [int] NOT NULL,
[ThresholdTypeID] [int] NOT NULL,
[Tolerance] [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_CounterThresholds] on [PerformanceCounters].[CounterThresholds]'
GO
ALTER TABLE [PerformanceCounters].[CounterThresholds] ADD CONSTRAINT [PK_CounterThresholds] PRIMARY KEY CLUSTERED  ([CounterID], [InstanceID], [TemplateID])
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].[Instances]'
GO
CREATE TABLE [PerformanceCounters].[Instances]
(
[InstanceID] [int] NOT NULL IDENTITY(1, 1),
[CategoryID] [int] NOT NULL,
[Name] [nvarchar] (500) 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_Instances] on [PerformanceCounters].[Instances]'
GO
ALTER TABLE [PerformanceCounters].[Instances] ADD CONSTRAINT [PK_Instances] PRIMARY KEY CLUSTERED  ([InstanceID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [Template].[Templates]'
GO
CREATE TABLE [Template].[Templates]
(
[TemplateID] [int] NOT NULL IDENTITY(1, 1),
[Name] [nvarchar] (500) NOT NULL,
[Description] [nvarchar] (500) NULL,
[OnlineScriptEntity] [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_Templates] on [Template].[Templates]'
GO
ALTER TABLE [Template].[Templates] ADD CONSTRAINT [PK_Templates] PRIMARY KEY CLUSTERED  ([TemplateID])
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].[ThresholdTypes]'
GO
CREATE TABLE [PerformanceCounters].[ThresholdTypes]
(
[ThresholdTypeID] [int] NOT NULL,
[Name] [nvarchar] (500) 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_ThresholdTypes] on [PerformanceCounters].[ThresholdTypes]'
GO
ALTER TABLE [PerformanceCounters].[ThresholdTypes] ADD CONSTRAINT [PK_ThresholdTypes] PRIMARY KEY CLUSTERED  ([ThresholdTypeID])
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].[Discs]'
GO
CREATE TABLE [dbo].[Discs]
(
[DiscID] [int] NOT NULL IDENTITY(1, 1),
[TerminalServerID] [int] NOT NULL,
[Name] [nvarchar] (255) NOT NULL,
[VolumeName] [nvarchar] (255) NOT NULL,
[Size] [decimal] (18, 2) NOT NULL,
[FreeSpace] [decimal] (18, 2) NOT NULL,
[SnapshotID] [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_Discs] on [dbo].[Discs]'
GO
ALTER TABLE [dbo].[Discs] ADD CONSTRAINT [PK_Discs] PRIMARY KEY CLUSTERED  ([DiscID])
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].[NetworkAdapters]'
GO
CREATE TABLE [dbo].[NetworkAdapters]
(
[NetworkAdapterID] [int] NOT NULL IDENTITY(1, 1),
[TerminalServerID] [int] NOT NULL,
[Name] [nvarchar] (255) NOT NULL,
[Description] [nvarchar] (max) NOT NULL,
[InterfaceType] [nvarchar] (50) NOT NULL,
[DHCPEnabled] [bit] NULL,
[Address] [nvarchar] (50) NULL,
[AddressIPv6] [nvarchar] (50) NULL,
[SubnetMask] [nvarchar] (50) NULL,
[DefaultGateway] [nvarchar] (50) NULL,
[DHCPServer] [nvarchar] (50) NULL,
[DNSServers] [nvarchar] (255) NULL,
[MAC] [nvarchar] (255) NULL,
[Speed] [bigint] NULL,
[AdapterManufacturer] [nvarchar] (255) NULL,
[HostName] [nvarchar] (255) NULL,
[DNSSuffix] [nvarchar] (255) NULL,
[WINSPrimaryServer] [nvarchar] (255) NULL,
[WINSSecundaryServer] [nvarchar] (255) NULL,
[NetConnectionID] [nvarchar] (255) NULL,
[SnapshotID] [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_NetworkAdapters] on [dbo].[NetworkAdapters]'
GO
ALTER TABLE [dbo].[NetworkAdapters] ADD CONSTRAINT [PK_NetworkAdapters] PRIMARY KEY CLUSTERED  ([NetworkAdapterID])
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].[AlertLevels]'
GO
CREATE TABLE [dbo].[AlertLevels]
(
[ID] [int] NOT NULL,
[Level] [nvarchar] (50) 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_AlertLevels] on [dbo].[AlertLevels]'
GO
ALTER TABLE [dbo].[AlertLevels] ADD CONSTRAINT [PK_AlertLevels] 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 [dbo].[PerformanceCountersAlerts]'
GO
CREATE TABLE [dbo].[PerformanceCountersAlerts]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[TerminalServerID] [int] NOT NULL,
[AlertLevelID] [int] NOT NULL,
[AlertStatusID] [int] NOT NULL,
[Time] [datetime] NOT NULL,
[NetworkAdapterID] [int] NULL,
[DiskID] [int] NULL,
[ProcessID] [int] NULL,
[InstanceID] [int] NULL,
[CounterID] [int] NULL,
[CounterValue] [float] NULL,
[PID] [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_PerformanceCountersAlerts] on [dbo].[PerformanceCountersAlerts]'
GO
ALTER TABLE [dbo].[PerformanceCountersAlerts] ADD CONSTRAINT [PK_PerformanceCountersAlerts] 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 [dbo].[AlertStatuses]'
GO
CREATE TABLE [dbo].[AlertStatuses]
(
[ID] [int] NOT NULL,
[Status] [nvarchar] (50) 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_AlertStatuses] on [dbo].[AlertStatuses]'
GO
ALTER TABLE [dbo].[AlertStatuses] ADD CONSTRAINT [PK_AlertStatuses] 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].[ServerCounterStates]'
GO
CREATE TABLE [PerformanceCounters].[ServerCounterStates]
(
[TerminalServerID] [int] NOT NULL,
[CounterID] [int] NOT NULL,
[CounterStateTypeID] [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_ServerCounterStates] on [PerformanceCounters].[ServerCounterStates]'
GO
ALTER TABLE [PerformanceCounters].[ServerCounterStates] ADD CONSTRAINT [PK_ServerCounterStates] PRIMARY KEY CLUSTERED  ([TerminalServerID], [CounterID])
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].[CounterStateTypes]'
GO
CREATE TABLE [PerformanceCounters].[CounterStateTypes]
(
[CounterStateTypeID] [int] NOT NULL,
[Name] [nvarchar] (500) 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_CounterStateTypes] on [PerformanceCounters].[CounterStateTypes]'
GO
ALTER TABLE [PerformanceCounters].[CounterStateTypes] ADD CONSTRAINT [PK_CounterStateTypes] PRIMARY KEY CLUSTERED  ([CounterStateTypeID])
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].[CounterStatusByThreshold]'
GO
CREATE TABLE [PerformanceCounters].[CounterStatusByThreshold]
(
[CounterStatusID] [int] NOT NULL,
[Name] [nvarchar] (500) 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_CounterStatusByThreshold] on [PerformanceCounters].[CounterStatusByThreshold]'
GO
ALTER TABLE [PerformanceCounters].[CounterStatusByThreshold] ADD CONSTRAINT [PK_CounterStatusByThreshold] PRIMARY KEY CLUSTERED  ([CounterStatusID])
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 [Template].[ServerSubscriptions]'
GO
CREATE TABLE [Template].[ServerSubscriptions]
(
[TemplateID] [int] NOT NULL,
[TerminalServerID] [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_ServerSubscriptions] on [Template].[ServerSubscriptions]'
GO
ALTER TABLE [Template].[ServerSubscriptions] ADD CONSTRAINT [PK_ServerSubscriptions] PRIMARY KEY CLUSTERED  ([TemplateID], [TerminalServerID])
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].[InsertDiskRead]'
GO
CREATE PROCEDURE[PerformanceCounters].[InsertDiskRead]
@TableVariable PerformanceCounters.DiskCounterTableType READONLY
AS
BEGIN
   SET NOCOUNT ON


   INSERT INTO [PerformanceCounters].DiskRead
           (CollectionDate,
          TerminalServerID,
		  DiskID,
          Value)

     SELECT CollectionDate, TerminalServerID, DiskID, Value

     FROM @TableVariable
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].[InsertMemory]'
GO
CREATE PROCEDURE [PerformanceCounters].[InsertMemory]
@TableVariable PerformanceCounters.ServerCounterTableType READONLY
AS
BEGIN
   SET NOCOUNT ON


   INSERT INTO [PerformanceCounters].Memory
           (CollectionDate,
          TerminalServerID,
          Value)

     SELECT CollectionDate, TerminalServerID, Value

     FROM @TableVariable
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].[InsertNetworkReceived]'
GO
CREATE PROCEDURE[PerformanceCounters].[InsertNetworkReceived]
@TableVariable PerformanceCounters.NetworkCounterTableType READONLY
AS
BEGIN
   SET NOCOUNT ON


   INSERT INTO [PerformanceCounters].[NetworkReceived]
           (CollectionDate,
          TerminalServerID,
		  NetworkAdapterID,
          Value)

     SELECT CollectionDate, TerminalServerID, NetworkAdapterID, Value

     FROM @TableVariable
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].[LastDiskDateForServer]'
GO
CREATE PROCEDURE [PerformanceCounters].[LastDiskDateForServer]
	@TerminalServerID int
AS
BEGIN

	SET NOCOUNT ON

	SELECT MAX(CollectionDate) as CollectionDate,
		DiskID
	FROM (SELECT DiskID,
				CollectionDate
			FROM PerformanceCounters.DiskTransfer PC
			WHERE PC.CollectionDate = (
					SELECT MAX(CollectionDate)
					FROM PerformanceCounters.DiskTransfer T
					WHERE PC.TerminalServerID=T.TerminalServerID
					AND PC.DiskID=T.DiskID)
			AND PC.TerminalServerID=@TerminalServerID
			AND PC.Value IS NOT NULL
		UNION ALL
			SELECT DiskID,
				CollectionDate
			FROM PerformanceCounters.DiskQueue PC
			WHERE PC.CollectionDate = (
					SELECT MAX(CollectionDate)
					FROM PerformanceCounters.DiskQueue T
					WHERE PC.TerminalServerID=T.TerminalServerID
					AND PC.DiskID=T.DiskID)
					AND PC.TerminalServerID=@TerminalServerID
			AND PC.Value IS NOT NULL
		UNION ALL
			SELECT DiskID,
				CollectionDate
			FROM PerformanceCounters.DiskRead PC
			WHERE PC.CollectionDate = (
					SELECT MAX(CollectionDate)
					FROM PerformanceCounters.DiskRead T
					WHERE PC.TerminalServerID=T.TerminalServerID
					AND PC.DiskID=T.DiskID)
			AND PC.TerminalServerID=@TerminalServerID
			AND PC.Value IS NOT NULL
		UNION ALL
			SELECT DiskID,
				CollectionDate
			FROM PerformanceCounters.DiskWrite PC
			WHERE PC.CollectionDate = (
					SELECT MAX(CollectionDate)
					FROM PerformanceCounters.DiskWrite T
					WHERE PC.TerminalServerID=T.TerminalServerID
					AND PC.DiskID=T.DiskID)
			AND PC.TerminalServerID=@TerminalServerID
			AND PC.Value IS NOT NULL
		UNION ALL
			SELECT DiskID,
				CollectionDate
			FROM PerformanceCounters.DiskFreeSpace PC
			WHERE PC.CollectionDate = (
					SELECT MAX(CollectionDate)
					FROM PerformanceCounters.DiskFreeSpace T
					WHERE PC.TerminalServerID=T.TerminalServerID
					AND PC.DiskID=T.DiskID)
			AND PC.TerminalServerID=@TerminalServerID
			AND PC.Value IS NOT NULL
		) as DiskTable
	GROUP BY DiskID
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].[Principals]'
GO
ALTER TABLE [dbo].[Principals] DROP
COLUMN [TenantID]
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].[SPDocKitCredentials]'
GO
CREATE TABLE [dbo].[SPDocKitCredentials]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[SPDocKitWorkstationID] [int] NOT NULL,
[DisplayName] [nvarchar] (2048) NOT NULL,
[Username] [nvarchar] (2048) NOT NULL,
[Password] [nvarchar] (2048) NOT NULL,
[IsSharePointOnline] [bit] NOT NULL CONSTRAINT [DF_SPDocKitCredentials_IsSharePointOnline] 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 primary key [PK_SPDocKitCredentials] on [dbo].[SPDocKitCredentials]'
GO
ALTER TABLE [dbo].[SPDocKitCredentials] ADD CONSTRAINT [PK_SPDocKitCredentials] 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'Altering [dbo].[Farms]'
GO
ALTER TABLE [dbo].[Farms] ADD
[FarmType] [int] NOT NULL CONSTRAINT [DF_Farms_FarmType] 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 [dbo].[GetLoadedGroups]'
GO
CREATE PROCEDURE [dbo].[GetLoadedGroups]
	@FarmID int,
	@LoadingInfoID int,
	@GroupTypeID int
	AS
	SET NOCOUNT ON	
	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE @Loc_LoadingInfoID  int
	DECLARE	@Loc_FarmID int
	DECLARE	@Loc_GroupTypeID int

	SELECT @Loc_LoadingInfoID = @LoadingInfoID,
		   @Loc_FarmID = @FarmID,
		   @Loc_GroupTypeID = @GroupTypeID
		   	
	SELECT p.PrincipalID,
		   p.Name,
		   p.DisplayName
	FROM Principals p
	INNER JOIN LoadedPrincipals lp ON lp.PrincipalID = p.PrincipalID AND lp.LoadingInfoID = @Loc_LoadingInfoID
	WHERE p.FarmID = @Loc_FarmID AND  p.PrincipalTypeID = @GroupTypeID
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].[GetSiteCollectionAuthSettings]'
GO
CREATE PROCEDURE [dbo].[GetSiteCollectionAuthSettings]
	@FarmID int,
	@WorkstationID int,
	@WebApplicationID int,
	@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_WebApplicationID int
	DECLARE	@Loc_SiteUrl nvarchar(2048)

	SELECT @Loc_WorkstationID = @WorkstationID,
		   @Loc_FarmID = @FarmID,
		   @Loc_WebApplicationID = @WebApplicationID,
		   @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
	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 sc.WebApplicationID = @Loc_WebApplicationID
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[LoadedGroupSettings]'
GO
CREATE TABLE [dbo].[LoadedGroupSettings]
(
[LoadingInfoID] [int] NOT NULL,
[PrincipalID] [int] NOT NULL,
[AllowMembersEditMembership] [bit] NOT NULL CONSTRAINT [DF_LoadedGroupSettings_AllowMembersEditMembership] DEFAULT ((1)),
[OnlyAllowMembersViewMembership] [bit] NOT NULL CONSTRAINT [DF_LoadedGroupSettings_OnlyAllowMembersViewMembership] DEFAULT ((1)),
[AutoAcceptRequestToJoinLeave] [bit] NOT NULL CONSTRAINT [DF_LoadedGroupSettings_AutoAcceptRequestToJoinLeave] DEFAULT ((1)),
[MembershipRequestEmails] [nvarchar] (max) NULL,
[Description] [nvarchar] (max) NULL,
[PrincipalOwnerID] [int] NULL,
[SharepointID] [int] NOT NULL,
[GroupName] [nvarchar] (max) NOT NULL,
[AllowRequestToJoinLeave] [bit] NOT NULL CONSTRAINT [DF_LoadedGroupSettings_AllowRequestToJoinLeave] DEFAULT ((1))
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_LoadedGroupSettings] on [dbo].[LoadedGroupSettings]'
GO
ALTER TABLE [dbo].[LoadedGroupSettings] ADD CONSTRAINT [PK_LoadedGroupSettings] PRIMARY KEY CLUSTERED  ([LoadingInfoID], [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'Altering [dbo].[WebApplications]'
GO
ALTER TABLE [dbo].[WebApplications] ADD
[FarmType] [int] NOT NULL CONSTRAINT [DF_WebApplications_FarmType] 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 [dbo].[InsertAllLoadedGroupSettings]'
GO
CREATE PROCEDURE [dbo].[InsertAllLoadedGroupSettings]
	@LoadingInfoID int,
	@GroupSettings GroupSettingsType READONLY
AS
BEGIN
	SET NOCOUNT ON	
	
	INSERT INTO [dbo].[LoadedGroupSettings]
           ([GroupName],
		    [Description],
			[AllowMembersEditMembership],
			[AutoAcceptRequestToJoinLeave],
			[MembershipRequestEmails],
			[OnlyAllowMembersViewMembership],
			[PrincipalID],
			[LoadingInfoID],
			[SharepointID],
			[PrincipalOwnerID],
			[AllowRequestToJoinLeave])
	SELECT gs.GroupName,
		   gs.GroupDescription,
		   gs.AllowMembersEditMembership,
		   gs.AutoAcceptRequestToJoinLeave,
		   gs.MembershipRequestEmails,
		   gs.OnlyAllowMembersViewMembership,
		   gs.PrincipalID,
		   @LoadingInfoID,
		   gs.SharepointID,
		   gs.PrincipalOwnerID,
		   gs.AllowRequestToJoinLeave
	FROM @GroupSettings gs
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].[InsertLoadedGroupSettings]'
GO
CREATE PROCEDURE [dbo].[InsertLoadedGroupSettings]
	@PrincipalID int ,
	@LoadingInfoID int,
	@GroupName nvarchar(MAX),
	@AllowMembersEditMembership bit,
	@OnlyAllowMembersViewMembership bit,
	@AutoAcceptRequestToJoinLeave bit,
	@MembershipRequestEmails nvarchar(MAX),
	@Description nvarchar(MAX),
	@SharepointID int,
	@PrincipalOwnerID int,
	@AllowRequestToJoinLeave bit
	
AS
BEGIN
	DECLARE @PrincipalAdded AS INT
	SET @PrincipalAdded = (SELECT COUNT(PrincipalID) FROM [LoadedGroupSettings] WHERE @PrincipalID = PrincipalID AND @LoadingInfoID = LoadingInfoID)

	IF @PrincipalAdded = 0
	BEGIN
		INSERT INTO [dbo].[LoadedGroupSettings]
           ([GroupName],
		    [Description],
			[AllowMembersEditMembership],
			[AutoAcceptRequestToJoinLeave],
			[MembershipRequestEmails],
			[OnlyAllowMembersViewMembership],
			[PrincipalID],
			[LoadingInfoID],
			[SharepointID],
			[PrincipalOwnerID],
			[AllowRequestToJoinLeave])
		VALUES(@GroupName,
			   @Description,
			   @AllowMembersEditMembership,
			   @AutoAcceptRequestToJoinLeave,
			   @MembershipRequestEmails,
			   @OnlyAllowMembersViewMembership,
			   @PrincipalID,
			   @LoadingInfoID,
			   @SharepointID,
			   @PrincipalOwnerID,
			   @AllowRequestToJoinLeave)
	END
END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[InsertOrUpdatePrincipal]'
GO
ALTER PROCEDURE [dbo].[InsertOrUpdatePrincipal]
	@PrincipalID int = NULL OUTPUT ,
	@Name nvarchar(MAX),
	@PrincipalTypeID int,
	@SiteID int = NULL,
	@DisplayName nvarchar(MAX)=NULL,
	@Email nvarchar(MAX)=NULL,
	@AccountEnabled bit,
	@ExternalUser bit,
	@FarmID int,
	@LoadingInfoID int
AS
BEGIN
	SET NOCOUNT ON	
	IF @DisplayName IS NULL OR @DisplayName = '' 
		SET @DisplayName=@Name

	IF @PrincipalID IS NULL	
	BEGIN
		IF @SiteID IS NULL	
		BEGIN
		  SELECT @PrincipalID = p.PrincipalID
		  FROM [dbo].[Principals] p
		  WHERE p.FarmID = @FarmID AND p.Name = @Name AND p.SiteID IS NULL
		END
		ELSE
		BEGIN
		  SELECT @PrincipalID = p.PrincipalID
		  FROM [dbo].[Principals] p
		  WHERE p.FarmID = @FarmID AND p.Name = @Name AND p.SiteID = @SiteID		 
		END
	END
	
	IF @PrincipalID IS NULL	
	BEGIN
		INSERT INTO [dbo].[Principals]
           ([Name]
           ,[PrincipalTypeID]
           ,[SiteID]
           ,[DisplayName]
           ,[Email]           
           ,[ExternalUser]
           ,[FarmID])
		VALUES(@Name
			,@PrincipalTypeID
			,@SiteID
			,@DisplayName
			,@Email			
			,@ExternalUser
			,@FarmID)
		SET @PrincipalID=SCOPE_IDENTITY()
	END
	ELSE
	BEGIN
		UPDATE [dbo].[Principals] SET DisplayName = @DisplayName, Email =@Email WHERE PrincipalID = @PrincipalID		
	END

	INSERT INTO LoadedPrincipals(PrincipalID, LoadingInfoID, AccountEnabled) 
	SELECT  @PrincipalID, @LoadingInfoID, @AccountEnabled
	WHERE NOT EXISTS ( SELECT 1 
						FROM LoadedPrincipals 
						WHERE LoadedPrincipals.LoadingInfoID = @LoadingInfoID AND LoadedPrincipals.PrincipalID = @PrincipalID)
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].[InsertOrUpdateSharePointGroups]'
GO
ALTER PROCEDURE [dbo].[InsertOrUpdateSharePointGroups]	
	@LoadingInfoID int,
	@FarmID int,
	@SiteID int,
	@TableVariable SharePointGroupType READONLY
AS
BEGIN
	SET NOCOUNT ON	
	
	CREATE TABLE #newSPGroups (
		[PrincipalID] [int] NULL,
		[PrincipalLoaded] [bit] NOT NULL,
		[Name] [nvarchar](max) NOT NULL,		
		[DisplayName] [nvarchar](max) NULL	
	) 

	INSERT INTO #newSPGroups ([PrincipalID]
		   ,[PrincipalLoaded]
		   ,[Name]                  
           ,[DisplayName])
	SELECT Principals.PrincipalID
	  ,PrincipalLoaded = CASE 
							WHEN lp.LoadingInfoID IS NULL THEN 0
							ELSE 1
						END
	  ,p.[Name]      
      ,p.[DisplayName]     
	  FROM @TableVariable p
	  LEFT OUTER JOIN Principals ON Principals.Name = p.Name AND Principals.SiteID = @SiteID AND Principals.FarmID = @FarmID
	  LEFT OUTER JOIN LoadedPrincipals lp ON Principals.PrincipalID = lp.PrincipalID AND lp.LoadingInfoID = @LoadingInfoID


	INSERT INTO Principals ([Name]
           ,[PrincipalTypeID]
           ,[SiteID]
           ,[DisplayName]
           ,[Email]           
           ,[ExternalUser]
           ,[FarmID])
	SELECT p.[Name]
		  ,1
		  ,@SiteID
		  ,p.[DisplayName]
		  ,NULL		  
		  ,0
		  ,@FarmID
	FROM #newSPGroups p
	WHERE p.PrincipalID IS NULL

	UPDATE t1
	   SET t1.[DisplayName] = p.[DisplayName] 		  	  
	FROM Principals t1
	JOIN #newSPGroups p ON p.PrincipalID = t1.PrincipalID
	WHERE p.PrincipalID IS NOT NULL

	UPDATE np
	   SET np.[PrincipalID] = Principals.[PrincipalID] 		  
	FROM #newSPGroups np
	JOIN Principals ON np.Name = Principals.Name AND Principals.SiteID = @SiteID AND Principals.FarmID = @FarmID
	WHERE np.[PrincipalID] IS NULL

	INSERT INTO LoadedPrincipals
		([LoadingInfoID]
		,[PrincipalID]
		,[AccountEnabled])	
	SELECT @LoadingInfoID
		,np.PrincipalID
		,1      
	FROM #newSPGroups np
	WHERE np.PrincipalLoaded = 0

	SELECT np.PrincipalID
		  ,np.Name      
	FROM #newSPGroups np	
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].[InsertOrUpdateSharePointObjects]'
GO
ALTER PROCEDURE [dbo].[InsertOrUpdateSharePointObjects]
	@LoadingInfoID int,
	@FarmID int,
	@WebAppID int,
	@SiteID int,
	@SPSiteGuid uniqueidentifier,
	@CreateScopeAssignments bit,
	@TableVariable SharePointObjectType READONLY
AS
BEGIN
	SET NOCOUNT ON	
	
	CREATE TABLE #newSpObjects (
		[ID] [int] NULL,
		[Name] nvarchar(255) NOT NULL,
		[SPObjectTypeID] [int] NOT NULL,
		[SPGuid] [uniqueidentifier] NOT NULL,
		[ParentID] [int] NOT NULL,
		[ParentScopeID] [int] NOT NULL,
		[IsRootWeb] [bit] NOT NULL,
		[HasUniqueRoleAssignments] [bit] NOT NULL,
		[Url] nvarchar(2048) NOT NULL,
		[ContentTypeID] [int] NULL,
		[LastUpdated] [datetime] NULL,
		[CreatedOn] [datetime] NULL,
		[CreatedBy] [int] NULL,
		[ModifiedBy] [int] NULL,
		[ExternalSharingCapability] [int] NULL,
		[AnonymousViewLink] nvarchar (1024)  NULL,
		[AnonymousEditLink] nvarchar (1024)  NULL,
		[SharePointContentTypeID] [int] NULL
	) 

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

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

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

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

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

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

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

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

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

		 OPEN @RowCursor

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

		SELECT n.SPGuid, n.ID, s.ScopeID
		FROM #newSpObjects n
		INNER JOIN [dbo].[ScopeAssignments] s ON s.LoadingInfoID = @LoadingInfoID AND s.SPObjectID = n.ID
	 END
	 ELSE
	 BEGIN
	 	SELECT n.SPGuid, n.ID, -1 as ScopeID
		FROM #newSpObjects n
	 END
END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[InsertOrUpdateTenantUsers]'
GO
ALTER PROCEDURE [dbo].[InsertOrUpdateTenantUsers]	
	@LoadingInfoID int,
	@FarmID int,
	@OnlyInsert bit,
	@TableVariable UserType READONLY
AS
BEGIN
	SET NOCOUNT ON	
	
	CREATE TABLE #newPrincipals (
		[PrincipalID] [int] NULL,
		[PrincipalLoaded] [bit] NOT NULL,
		[Name] [nvarchar](max) NOT NULL,
		[PrincipalTypeID] [int] NOT NULL,
		[DisplayName] [nvarchar](max) NULL,
		[Email] [nvarchar](max) NULL,
		[AccountEnabled] [bit] NOT NULL,
		[ExternalUser] [bit] NOT NULL,
	) 

	INSERT INTO #newPrincipals ([PrincipalID]
		   ,[PrincipalLoaded]
		   ,[Name]
           ,[PrincipalTypeID]           
           ,[DisplayName]
           ,[Email]
           ,[AccountEnabled]
           ,[ExternalUser])
	SELECT Principals.PrincipalID
	  ,PrincipalLoaded = CASE 
							WHEN lp.LoadingInfoID IS NULL THEN 0
							ELSE 1
						END
	  ,p.[Name]
      ,p.[PrincipalTypeID]
      ,p.[DisplayName]
      ,p.[Email]
      ,p.[AccountEnabled]
      ,p.[ExternalUser]
	  FROM @TableVariable p
	  LEFT OUTER JOIN Principals ON Principals.Name = p.Name AND Principals.SiteID IS NULL AND Principals.FarmID = @FarmID
	  LEFT OUTER JOIN LoadedPrincipals lp ON Principals.PrincipalID = lp.PrincipalID AND lp.LoadingInfoID = @LoadingInfoID


	INSERT INTO Principals ([Name]
           ,[PrincipalTypeID]
           ,[SiteID]
           ,[DisplayName]
           ,[Email]           
           ,[ExternalUser]
           ,[FarmID])
	SELECT p.[Name]
		  ,p.[PrincipalTypeID]
		  ,NULL
		  ,p.[DisplayName]
		  ,p.[Email]		  
		  ,p.[ExternalUser]
		  ,@FarmID
	FROM #newPrincipals p
	WHERE p.PrincipalID IS NULL

	IF @OnlyInsert = 0
	BEGIN
		UPDATE t1
		   SET t1.[PrincipalTypeID] = p.[PrincipalTypeID]		
			  ,t1.[DisplayName] = p.[DisplayName] 
			  ,t1.[Email] = p.[Email]		  
			  ,t1.[ExternalUser] = p.[ExternalUser]		  
		FROM Principals t1
		JOIN #newPRincipals p ON p.PrincipalID = t1.PrincipalID
		WHERE p.PrincipalID IS NOT NULL
	END

	UPDATE np
	   SET np.[PrincipalID] = Principals.[PrincipalID] 		  
	FROM #newPRincipals np
	JOIN Principals ON np.Name = Principals.Name AND Principals.SiteID IS NULL AND Principals.FarmID = @FarmID
	WHERE np.[PrincipalID] IS NULL

	INSERT INTO LoadedPrincipals
		([LoadingInfoID]
		,[PrincipalID]
		,[AccountEnabled])	
	SELECT DISTINCT @LoadingInfoID
		,np.PrincipalID
		,np.[AccountEnabled]      
	FROM #newPRincipals np
	WHERE np.PrincipalLoaded = 0

	SELECT np.PrincipalID
		  ,np.Name      
	FROM #newPRincipals np
	
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].[LoadingInfos]'
GO
ALTER TABLE [dbo].[LoadingInfos] ADD
[Version] [nvarchar] (100) NOT NULL CONSTRAINT [DF_LoadingInfos_Version] DEFAULT ('6.3.0.34906'),
[MarkedForDeletion] [bit] NOT NULL CONSTRAINT [DF_LoadingInfos_MarkedForDeletion] DEFAULT ((0)),
[InProgress] [bit] NOT NULL CONSTRAINT [DF_LoadingInfos_InProgress] DEFAULT ((0))
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [EventCollection].[v_SPDocKitUserFarms]'
GO
ALTER VIEW [EventCollection].[v_SPDocKitUserFarms]
WITH SCHEMABINDING
AS	
WITH SecurityEnabled (RBSecurityEnabled)
      AS
      (
            SELECT CAST(case WHEN OptionValue = 'False' THEN 0
						ELSE 1
						END AS BIT)
            FROM [dbo].[Options] o
			WHERE o.OptionID = 2000     
      ),
RoleFarms AS (select  u.LoginName, u.PrincipalTypeID,  f.[ID]
					  ,f.[FarmGuid]
					  ,f.[Name]
					  ,f.[LSE]
					  ,f.[Hash]
					  ,f.[CentralAdministrationUrl]
					  ,f.[Version]
					  ,f.[UsageDatabaseConnectionString]
					  ,f.[DiagnosticsLogLocation]
			FROM [dbo].[SPDocKitUsers] u
			INNER JOIN [dbo].[SPDocKitUserRoles] ur ON u.ID = ur.UserID   
			INNER JOIN [dbo].[SPDocKitRoles] r ON ur.RoleID = r.ID   
			INNER JOIN [dbo].[SPDocKitRoleFarms] rf ON rf.RoleID = r.ID
			INNER JOIN [dbo].[Farms] f ON rf.FarmID = f.ID
			UNION 
			SELECT u.LoginName, u.PrincipalTypeID, f.[ID]
					  ,f.[FarmGuid]
					  ,f.[Name]
					  ,f.[LSE]
					  ,f.[Hash]
					  ,f.[CentralAdministrationUrl]
					  ,f.[Version]
					  ,f.[UsageDatabaseConnectionString]
					  ,f.[DiagnosticsLogLocation]
			FROM[dbo].[SPDocKitUsers] u
			INNER JOIN [dbo].[SPDocKitUserRoles] ur ON u.ID = ur.UserID   
			INNER JOIN [dbo].[SPDocKitRoles] r ON ur.RoleID = r.ID   
			CROSS APPLY [dbo].[Farms] AS f
			WHERE r.Name ='Admins'),
AllFarms AS (
 SELECT NULL AS 'LoginName', NULL AS 'PrincipalTypeID',  f.[ID]
					  ,f.[FarmGuid]
					  ,f.[Name]
					  ,f.[LSE]
					  ,f.[Hash]
					  ,f.[CentralAdministrationUrl]
					  ,f.[Version]
					  ,f.[UsageDatabaseConnectionString]
					  ,f.[DiagnosticsLogLocation]
		FROM [dbo].[Farms] AS f)

SELECT  f.LoginName, f.PrincipalTypeID, f.[ID]
					  ,f.[FarmGuid]
					  ,f.[Name]
					  ,f.[LSE]
					  ,f.[Hash]
					  ,f.[CentralAdministrationUrl]
					  ,f.[Version]
					  ,f.[UsageDatabaseConnectionString]
					  ,f.[DiagnosticsLogLocation]
FROM(
	SELECT f.LoginName, f.PrincipalTypeID, f.[ID]
					  ,f.[FarmGuid]
					  ,f.[Name]
					  ,f.[LSE]
					  ,f.[Hash]
					  ,f.[CentralAdministrationUrl]
					  ,f.[Version]
					  ,f.[UsageDatabaseConnectionString]
					  ,f.[DiagnosticsLogLocation]
					  , s.RBSecurityEnabled AS 'ResFlag' FROM RoleFarms AS f CROSS APPLY SecurityEnabled as s
	UNION 
	SELECT f.LoginName, f.PrincipalTypeID, f.[ID]
					  ,f.[FarmGuid]
					  ,f.[Name]
					  ,f.[LSE]
					  ,f.[Hash]
					  ,f.[CentralAdministrationUrl]
					  ,f.[Version]
					  ,f.[UsageDatabaseConnectionString]
					  ,f.[DiagnosticsLogLocation]
					  , ~s.RBSecurityEnabled AS 'ResFlag' FROM AllFarms AS f CROSS APPLY SecurityEnabled as s
	)AS f 
WHERE ResFlag = 1
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[v_SPObjectsWithAccess]'
GO
ALTER VIEW [dbo].[v_SPObjectsWithAccess]
WITH SCHEMABINDING
AS 
	SELECT DISTINCT rsc.FarmID,
		   rsc.WebApplicationID,
		   rsc.SiteCollectionID
	FROM	[dbo].[SPDocKitUsers] u
	INNER JOIN [dbo].[SPDocKitUserRoles] ur ON u.ID = ur.UserID   
	INNER JOIN [dbo].[SPDocKitRoles] r ON ur.RoleID = r.ID   
	INNER JOIN [dbo].[SPDocKitRoleSiteCollections] rsc ON r.ID = rsc.RoleID
	WHERE IS_MEMBER (u.LoginName) = 1 AND r.SPObjectSharingPolicy = 3

	-- bitno je da ovo bude DISTINCT
	UNION 

	SELECT DISTINCT sca.FarmID,
		   sca.WebApplicationID,
		   sca.SiteID
	FROM	[dbo].[SPDocKitUsers] u
	INNER JOIN [dbo].[SPDocKitUserRoles] ur ON u.ID = ur.UserID   
	INNER JOIN [dbo].[SPDocKitRoles] r ON ur.RoleID = r.ID   
	CROSS JOIN [dbo].[SPDocKitSiteCollectionAdministrators] sca 
	INNER JOIN [dbo].[Principals] p ON sca.PrincipalID = p.PrincipalID
	WHERE IS_MEMBER (u.LoginName) = 1 AND r.SPObjectSharingPolicy = 2 AND p.Name = SUSER_NAME()
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[MonitoringServers]'
GO
ALTER TABLE [dbo].[MonitoringServers] ADD
[EnabledForLoad] [bit] NOT NULL CONSTRAINT [DF_Servers_EnabledForLoad] DEFAULT ((1)),
[Role] [nvarchar] (50) NULL,
[Builtin] [bit] NOT NULL CONSTRAINT [DF_Servers_Builtin] DEFAULT ((1))
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [EventCollection].[MonitoringServers]'
GO
ALTER TABLE [EventCollection].[MonitoringServers] ADD
[ServerRoleFlags] [int] NOT NULL CONSTRAINT [DF_MonitoringServers_ServerRoleFlags] DEFAULT ((1)),
[IISMonitoringEnabled] [bit] NOT NULL CONSTRAINT [DF_MonitoringServers_IISMonitoringEnabled] DEFAULT ((1)),
[IISLogsLocation] [nvarchar] (1024) NULL,
[SQLMonitoringEnabled] [bit] NOT NULL CONSTRAINT [DF_MonitoringServers_SQLMonitoringEnabled] DEFAULT ((1)),
[SQLLogFileLocation] [nvarchar] (1024) NULL,
[HTTPErrorLogMonitoringEnabled] [bit] NOT NULL CONSTRAINT [DF_MonitoringServers_HTTPErrorMonitoringEnabled] DEFAULT ((1)),
[HTTPErrorLogLocation] [nvarchar] (1024) NULL,
[IsActive] [bit] NOT NULL CONSTRAINT [DF_MonitoringServers_IsActive] DEFAULT ((1)),
[PerformanceCountersEnabled] [bit] NULL CONSTRAINT [DF_TerminalServers_PerformanceCountersEnabled] DEFAULT ((0)),
[PerformanceCountersStatusID] [int] NULL CONSTRAINT [DF_TerminalServers_PCStatus] DEFAULT ((1)),
[CountersStatus] [int] NOT NULL CONSTRAINT [DF_TerminalServers_CountersStatus] DEFAULT ((0)),
[PerformanceCountersCollectedOn] [datetime] NULL,
[TotalPhysicalMemory] [int] NULL CONSTRAINT [DF_TerminalServers_TotalPhysicalMemory] DEFAULT ((-1)),
[OperatingSystemVersion] [nvarchar] (255) NULL
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[v_SharePointObjects]'
GO
ALTER VIEW [dbo].[v_SharePointObjects]
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     
      )
	SELECT spo.[ID]
		  ,spo.[Name]
		  ,[SPObjectTypeID]
		  ,[SPSiteGuid]
		  ,[SPGuid]
		  ,[IsRootWeb]
		  ,[WebAppID]
		  ,[HasUniqueRoleAssignments]
		  ,[Url]
		  ,[SiteID]
		  ,[ContentTypeID]
		  ,[LastUpdated]
		  ,[CreatedOn]
		  ,[CreatedBy]
		  ,[ModifiedBy]
		  ,[SiteSize]
		  ,[MaxQuota]
		  ,spo.[FarmID]
	  FROM SecurityEnabled sec,  [dbo].[v_HasAccessToAllSPObjects] acc, [dbo].[SharePointObjects] spo
	  LEFT OUTER JOIN [dbo].[v_SPObjectsWithAccess] a ON a.SiteCollectionID = spo.SiteID
	  WHERE sec.RBSecurityEnabled = 0 OR acc.HasAccess = 1 OR a.WebApplicationID IS NOT NULL
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[GetAnonymousAccessLinks]'
GO
CREATE PROCEDURE [dbo].[GetAnonymousAccessLinks]
	@SiteCollectionIDs FilterIDsType READONLY,
	@LoadingInfoID int
AS
	SET NOCOUNT ON

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

	SELECT @Loc_LoadingInfoID = @LoadingInfoID 

	INSERT INTO @Loc_SiteCollectionIDs
	SELECT * FROM @SiteCollectionIDs
	
	SELECT sc.Name,
		s.Name,
		s.Url,
		a.ViewLink,
		a.EditLink
	FROM AnonymousAccessLinks as a 
	INNER JOIN SharePointObjects as s ON s.ID = a.SharePointObjectID AND a.LoadingInfoID = @Loc_LoadingInfoID
	INNER JOIN v_SharePointObjects sc ON sc.ID = s.SiteID
	INNER JOIN @Loc_SiteCollectionIDs scFilter on scFilter.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].[GetChildrenForParent]'
GO
ALTER PROCEDURE [dbo].[GetChildrenForParent]
	@LoadingInfoID INT,
	@FarmID INT,
	@ParentID INT
AS
BEGIN 
	SET NOCOUNT ON
	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE	@Loc_LoadingInfoID int
	DECLARE	@Loc_FarmID int
	DECLARE	@Loc_ParentID int

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

	SELECT	[SPObject].[ID],
			[SPObject].[Url], 
			[SPObject].[Name],
			[SPObject].[SPGuid],
			(CASE WHEN EXISTS 
				(
				SELECT [dbo].[SharePointObjectMemberships].[ParentID] FROM [dbo].[SharePointObjectMemberships] WHERE [dbo].[SharePointObjectMemberships].[LoadingInfoID] = @Loc_LoadingInfoID
				AND [dbo].[SharePointObjectMemberships].[ParentID] = [SPObject].[ID]
				) THEN CONVERT(bit, 1) ELSE CONVERT(bit, 0) END),
			[SPObject].[SPObjectTypeID],
			[SPObject].[HasUniqueRoleAssignments],
			[SpObject].[ContentTypeID]
	FROM [dbo].[v_SharePointObjects] AS [SPObject]
	JOIN [dbo].[SharePointObjectMemberships] AS [Membership] ON [Membership].[ParentID] = @Loc_ParentID AND [Membership].[LoadingInfoID] = @Loc_LoadingInfoID AND [Membership].[ChildID] = [SPObject].[ID]
	ORDER BY [SPObjectTypeID], [Name]
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].[v_Principals]'
GO
ALTER VIEW [dbo].[v_Principals]
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     
      )
	SELECT [PrincipalID]
		  ,[Name]
		  ,[PrincipalTypeID]
		  ,[SiteID]
		  ,[DisplayName]
		  ,[Email]
		  ,[ExternalUser]
		  ,p.[FarmID]
	  FROM SecurityEnabled sec, [dbo].[v_HasAccessToAllSPObjects] acc, [dbo].[Principals] p
	  LEFT OUTER JOIN [dbo].[v_SPObjectsWithAccess] a ON a.SiteCollectionID = p.SiteID
	  WHERE SiteID IS NULL OR sec.RBSecurityEnabled = 0 OR acc.HasAccess = 1 OR a.FarmID IS NOT NULL
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[v_Farms]'
GO
ALTER VIEW [dbo].[v_Farms]
WITH SCHEMABINDING
AS 
  WITH SecurityEnabled (RBSecurityEnabled)
      AS
      (
            SELECT case WHEN OptionValue = 'False' THEN 0
						ELSE 1
						END 
            FROM [dbo].[Options] o
			WHERE o.OptionID = 2000     
      ),
	  MyFarms
	  AS
	  (
	        SELECT DISTINCT a.FarmID
			FROM [dbo].[v_SPObjectsWithAccess] a
	  ),
	 MySPDocKitRoleFarms 
	 AS
	 (
			SELECT DISTINCT rf.FarmID 
			FROM [dbo].[SPDocKitRoleFarms] rf
			JOIN [dbo].[v_MySPDocKitRoles] r ON r.ID = rf.RoleID
	 )
	SELECT [ID]
		  ,[FarmGuid]
		  ,[Name]
		  ,[LSE]
		  ,[LEE]
		  ,[LicenseValidTo]
		  ,[Hash]
		  ,[CentralAdministrationUrl]
		  ,[Version]
		  ,[UsageDatabaseConnectionString]
		  ,[DiagnosticsLogLocation]
		  ,[FarmType]
	  FROM SecurityEnabled sec,  [dbo].[v_HasAccessToAllSPObjects] acc, [dbo].[Farms] f
	  WHERE sec.RBSecurityEnabled = 0 OR acc.HasAccess = 1
		  OR f.ID IN (SELECT a.FarmID FROM MyFarms a)
		  OR f.ID IN (SELECT myFarms.FarmID FROM MySPDocKitRoleFarms myFarms)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[GetFarmAuthSettings]'
GO
CREATE PROCEDURE [dbo].[GetFarmAuthSettings]
	@FarmID int,
	@WorkstationID int
	AS
	
	SET NOCOUNT ON	

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

	SELECT @Loc_WorkstationID = @WorkstationID,
		   @Loc_FarmID = @FarmID
		   	
	SELECT TOP 1  f.CentralAdministrationUrl,
		        c.Username,
		    	c.Password	
	FROM Farms f
	INNER JOIN ConnectedSiteCollections sc on f.ID = sc.FarmID
	INNER JOIN SPDocKitCredentials c ON c.ID = sc.SPDocKitCredentialID
	WHERE sc.FarmID = @Loc_FarmID AND sc.SPDocKitWorkstationID = @Loc_WorkstationID
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[v_WebApplications]'
GO
ALTER VIEW [dbo].[v_WebApplications]
WITH SCHEMABINDING
AS 
  WITH SecurityEnabled (RBSecurityEnabled)
      AS
      (
            SELECT case WHEN OptionValue = 'False' THEN 0
						ELSE 1
						END 
            FROM [dbo].[Options] o
			WHERE o.OptionID = 2000     
      ),
	  MyWebApplications
	  AS
	  (
	        SELECT DISTINCT a.WebApplicationID
			FROM [dbo].[v_SPObjectsWithAccess] a
	  )
	  SELECT [ID]
		  ,[Name]
		  ,[SPGuid]
		  ,[Url]
		  ,[Username]
		  ,[Password]
		  ,[Hash]
		  ,[BusinessPlanID]
		  ,[IsPartner]
		  ,[UseOrganizationalAccount]
		  ,w.[FarmID]
		  ,[FarmType]
	  FROM SecurityEnabled sec, [dbo].[v_HasAccessToAllSPObjects] acc, [dbo].[WebApplications] w
	  LEFT OUTER JOIN MyWebApplications a ON a.WebApplicationID = w.ID
	  WHERE sec.RBSecurityEnabled = 0 OR acc.HasAccess = 1 OR a.WebApplicationID IS NOT NULL
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[GetPathFromSiteCollection]'
GO
ALTER PROCEDURE [dbo].[GetPathFromSiteCollection]
    @ObjectID int,
	@LoadingInfoID int,
	@MaxDepth int
AS
	SET NOCOUNT ON
	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE	@Loc_LoadingInfoID int
	DECLARE	@Loc_ObjectID int
	DECLARE	@Loc_MaxDepth int

	SELECT @Loc_LoadingInfoID = @LoadingInfoID,
		@Loc_ObjectID = @ObjectID,
		@Loc_MaxDepth = @MaxDepth

	DECLARE @OrganisationDescendant TABLE ( 
                    [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 <= @Loc_MaxDepth and @NumberOfNewRows > 0 
	    BEGIN
		    IF @Depth = 0
			    BEGIN
				    INSERT INTO @OrganisationDescendant([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, o.[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 = @Loc_ObjectID                                    				
			    END
		    ELSE
			    BEGIN
				    INSERT INTO @OrganisationDescendant([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, o.[HasUniqueRoleAssignments], o.[IsRootWeb], scope.ScopeID, o.[SiteID], o.[LastUpdated], o.[WebAppID], o.Url, o.[SPGuid], o.[CreatedOn], o.[ContentTypeID]
					    FROM SharePointObjectMemberships as m 
					    JOIN @OrganisationDescendant as d on m.ChildID = d.ID and m.LoadingInfoID = @Loc_LoadingInfoID and d.PathLevel = (@Depth-1)
					    JOIN dbo.SharePointObjects AS o on m.ParentID = o.ID 	
						JOIN dbo.ScopeAssignments as scope ON scope.SPObjectID = o.ID AND scope.LoadingInfoID = @Loc_LoadingInfoID 	
		            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  @OrganisationDescendant as org
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].[GetSharePointStructure]'
GO
ALTER PROCEDURE [dbo].[GetSharePointStructure]
	@RootObjectIDs FilterIDsType READONLY,
	@FarmID int,
	@LoadingInfoID int,
	@SPObjectTypeIDs FilterIDsType READONLY,
	@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_SPObjectTypeIDs FilterIDsType

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

	INSERT INTO @Loc_RootObjectIDs
	SELECT * FROM @RootObjectIDs
	
	INSERT INTO @Loc_SPObjectTypeIDs
	SELECT * FROM @SPObjectTypeIDs
	
	IF OBJECT_ID('tempdb..#OrganisationDescendant') IS NOT NULL
		DROP TABLE #OrganisationDescendant

	CREATE TABLE #OrganisationDescendant ( 
                                        [ID] INT, 
                                        [ParentID] INT NULL,
                                        [PathLevel] INT
                                        )

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

	CREATE TABLE #OrganisationDescendant_OddLevel ( 
		[ID] INT, 
		[ParentID] INT NULL,
		PRIMARY KEY (ID)
    )

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

	CREATE TABLE #OrganisationDescendant_EvenLevel ( 
		[ID] INT, 
		[ParentID] INT NULL,
		PRIMARY KEY ( ID)
    )

    DECLARE @Depth          INT
    DECLARE @NumberOfNewRows   INT

    SET @Depth = 0
    SET @NumberOfNewRows = 1
    WHILE @Depth <= @MaxDepth and @NumberOfNewRows > 0 
	    BEGIN
		    IF @Depth = 0
			    BEGIN
				    INSERT INTO #OrganisationDescendant([ID],[ParentID], [PathLevel] )
				    SELECT o.ID, NULL, 0 AS PathLevel
				    FROM dbo.v_SharePointObjects AS o  
					INNER JOIN @Loc_RootObjectIDs r ON r.ID = o.ID 
					WHERE o.FarmID = @Loc_FarmID     
					
					INSERT INTO #OrganisationDescendant_EvenLevel([ID],[ParentID] )
				    SELECT d.ID, d.ParentID 
					FROM #OrganisationDescendant d                                				
			    END
		    ELSE
			    BEGIN
					IF (@Depth % 2 = 0)
						BEGIN 
							TRUNCATE TABLE #OrganisationDescendant_EvenLevel

							INSERT INTO #OrganisationDescendant_EvenLevel([ID], [ParentID])
							SELECT m.ChildID, m.ParentID
							FROM SharePointObjectMemberships as m 
							INNER JOIN #OrganisationDescendant_OddLevel as d on m.ParentID = d.ID and m.LoadingInfoID = @Loc_LoadingInfoID
							INNER JOIN @Loc_SPObjectTypeIDs spt ON spt.ID = m.ChildObjectTypeID

							INSERT INTO #OrganisationDescendant([ID], [ParentID], [PathLevel])
							SELECT d.ID, d.ParentID, @Depth
							FROM #OrganisationDescendant_EvenLevel d
						END
					ELSE
						BEGIN
							TRUNCATE TABLE #OrganisationDescendant_OddLevel

							INSERT INTO #OrganisationDescendant_OddLevel([ID], [ParentID])
							SELECT m.ChildID, m.ParentID
							FROM SharePointObjectMemberships as m 
							INNER JOIN #OrganisationDescendant_EvenLevel as d on m.ParentID = d.ID and m.LoadingInfoID = @Loc_LoadingInfoID
							INNER JOIN @Loc_SPObjectTypeIDs spt ON spt.ID = m.ChildObjectTypeID

							INSERT INTO #OrganisationDescendant([ID], [ParentID], [PathLevel])
							SELECT d.ID, d.ParentID, @Depth
							FROM #OrganisationDescendant_OddLevel d
						END
		            END
		    SET @NumberOfNewRows = @@ROWCOUNT								
		    SET @Depth = @Depth + 1                                        
        END
		
		       
	    SELECT	
            o.[ID],
			o.[Name],
            o.[SPObjectTypeID] AS SharePointObjectTypeID,
            org.[ParentID],
            org.[PathLevel],
            o.[HasUniqueRoleAssignments],
            o.[IsRootWeb],  
			sa.[ScopeID] AS PermissionScopeID,
            o.[SiteID],
            o.[LastUpdated],
            o.[WebAppID] AS WebApplicationID,
            o.[Url],
            o.[SPGuid],
			o.[CreatedOn],
			o.[ContentTypeID]
		FROM  #OrganisationDescendant as org
		INNER JOIN dbo.SharePointObjects AS o on org.ID = o.ID 	
		INNER JOIN dbo.ScopeAssignments sa ON sa.SPObjectID = o.ID AND sa.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'Altering [dbo].[GetSharePointStructureForWebApplications]'
GO
ALTER PROCEDURE [dbo].[GetSharePointStructureForWebApplications]
	@WebAppIDs FilterIDsType READONLY,
	@FarmID int,
	@LoadingInfoID int,
	@SPObjectTypeIDs FilterIDsType READONLY,
	@MaxDepth int = 32
AS
	SET NOCOUNT ON

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

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

	INSERT INTO @Loc_WebAppIDs
	SELECT * FROM @WebAppIDs
	
	INSERT INTO @Loc_SPObjectTypeIDs
	SELECT * FROM @SPObjectTypeIDs

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

	CREATE TABLE #OrganisationDescendantWebApps ( 
                    [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 #OrganisationDescendantWebApps([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, o.[HasUniqueRoleAssignments], o.[IsRootWeb], scope.ScopeID, o.[SiteID], o.[LastUpdated], o.[WebAppID], o.Url,  o.[SPGuid], o.[CreatedOn], o.[ContentTypeID]
                    FROM dbo.v_WebApplications AS w    			
                    JOIN dbo.LoadedSiteCollections AS ls ON w.ID = ls.WebApplicationID AND ls.LoadingInfoID = @Loc_LoadingInfoID 
                    JOIN dbo.v_SharePointObjects AS o ON ls.SiteCollectionID = o.ID 	                        
					JOIN dbo.ScopeAssignments as scope ON scope.SPObjectID = o.ID AND scope.LoadingInfoID = @Loc_LoadingInfoID  
					WHERE EXISTS (SELECT 1 FROM @WebAppIDs wf WHERE wf.ID = w.ID) AND o.SPObjectTypeID = 1  AND  o.FarmID = @Loc_FarmID                                  				
			    END
		    ELSE
			    BEGIN
				    INSERT INTO #OrganisationDescendantWebApps([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, o.[HasUniqueRoleAssignments], o.[IsRootWeb], scope.ScopeID, o.[SiteID], o.[LastUpdated], o.[WebAppID], o.Url, o.[SPGuid], o.[CreatedOn], o.[ContentTypeID]
					    FROM SharePointObjectMemberships as m 
					    JOIN #OrganisationDescendantWebApps 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 NOT EXISTS (SELECT 1 FROM @Loc_SPObjectTypeIDs) OR EXISTS (SELECT 1 FROM @Loc_SPObjectTypeIDs spt WHERE spt.ID = o.SPObjectTypeID)
		            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  #OrganisationDescendantWebApps as org
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].[GetSpGroupSettings]'
GO
CREATE PROCEDURE [dbo].[GetSpGroupSettings]
	@GroupSPID int ,
	@LoadingInfoID int
AS
SELECT		lgs.[GroupName],
		    lgs.[Description],
			lgs.[AllowMembersEditMembership],
			lgs.[AutoAcceptRequestToJoinLeave],
			lgs.[MembershipRequestEmails],
			lgs.[OnlyAllowMembersViewMembership],
			ownerLoadedGroupSettings.SharepointID as OwnerSPID,
			p.Name as OwnerIdentifier,
			p.PrincipalTypeID as OwnerType,
			lgs.[AllowRequestToJoinLeave]
FROM LoadedGroupSettings lgs
LEFT OUTER JOIN Principals p ON lgs.PrincipalOwnerID = p.PrincipalID
LEFT OUTER JOIN LoadedGroupSettings ownerLoadedGroupSettings ON ownerLoadedGroupSettings.LoadingInfoID = @LoadingInfoID AND ownerLoadedGroupSettings.PrincipalID = lgs.PrincipalOwnerID	
JOIN v_Principals vp ON vp.PrincipalID = lgs.PrincipalID		
WHERE @GroupSPID = lgs.SharepointID AND @LoadingInfoID = lgs.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'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, o.[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, o.[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 [dbo].[v_WebApplicationConfigs]'
GO
ALTER VIEW [dbo].[v_WebApplicationConfigs]
WITH SCHEMABINDING
AS 
 WITH SecurityEnabled (RBSecurityEnabled)
      AS
      (
            SELECT case WHEN OptionValue = 'False' THEN 0
						ELSE 1
						END 
            FROM [dbo].[Options] o
			WHERE o.OptionID = 2000     
      ),
	  MyWebApplications
	  AS
	  (
	        SELECT DISTINCT a.WebApplicationID
			FROM [dbo].[v_SPObjectsWithAccess] a
	  )
	  SELECT wc.[ID]
		  ,wc.[WebApplicationID]
		  ,wc.[WebApplicationZoneID]
		  ,wc.[ServerID]
		  ,wc.[LoadingInfoID]
		  ,wc.[FileContent]
		  ,s.[Name] as ServerName
		  ,z.[ZoneName] as ZoneName
	  FROM SecurityEnabled sec, [dbo].[v_HasAccessToAllSPObjects] acc, [dbo].[WebApplicationConfigs] wc
	  INNER JOIN [dbo].[MonitoringServers] s ON s.ID = wc.ServerID
	  INNER JOIN [dbo].[WebApplicationZones] z ON z.ZoneID = wc.WebApplicationZoneID
	  LEFT OUTER JOIN MyWebApplications a ON a.WebApplicationID = wc.WebApplicationID
	  WHERE sec.RBSecurityEnabled = 0 OR acc.HasAccess = 1 OR a.WebApplicationID IS NOT NULL
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'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]
  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 [dbo].[GetExternalUsers]'
GO
CREATE PROCEDURE [dbo].[GetExternalUsers]
	@FarmID int,
	@LoadingInfoID int
AS
SET NOCOUNT ON	
--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
DECLARE @Loc_LoadingInfoID  int
DECLARE	@Loc_FarmID int

SELECT @Loc_LoadingInfoID = @LoadingInfoID,
		@Loc_FarmID = @FarmID
		   	
SELECT p.PrincipalID,
		p.Name,
		p.DisplayName,
		p.Email
FROM v_Principals p
INNER JOIN LoadedPrincipals lp ON lp.PrincipalID = p.PrincipalID AND lp.LoadingInfoID = @Loc_LoadingInfoID
INNER JOIN v_Farms f ON f.ID = @Loc_FarmID --security zastita za RBS
WHERE p.FarmID = @Loc_FarmID AND  p.ExternalUser = 1
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[PerformanceCountersStatuses]'
GO
CREATE TABLE [dbo].[PerformanceCountersStatuses]
(
[StatusID] [int] NOT NULL,
[StatusName] [nvarchar] (100) 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_PerformanceCountersStatuses] on [dbo].[PerformanceCountersStatuses]'
GO
ALTER TABLE [dbo].[PerformanceCountersStatuses] ADD CONSTRAINT [PK_PerformanceCountersStatuses] PRIMARY KEY CLUSTERED  ([StatusID])
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].[ScheduledAction]'
GO
CREATE TABLE [SQL].[ScheduledAction]
(
[Id] [int] NOT NULL IDENTITY(1, 1),
[Date] [datetime] NOT NULL,
[ActionId] [int] NOT NULL,
[ActionData] [xml] NOT NULL,
[NotifyEmails] [nvarchar] (max) NOT NULL,
[UndoDate] [datetime] NULL,
[StatusId] [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_ScheduledAction] on [SQL].[ScheduledAction]'
GO
ALTER TABLE [SQL].[ScheduledAction] ADD CONSTRAINT [PK_ScheduledAction] 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].[LastLiveApplications]'
GO
CREATE PROCEDURE [PerformanceCounters].[LastLiveApplications]
	@TerminalServerID int
AS
BEGIN
	SET NOCOUNT ON

	SELECT 
		ProcessID,
		PID,
		UserID
	FROM PerformanceCounters.ProcessCPU PC
	WHERE CollectionDate=(
		SELECT MAX(CollectionDate) FROM PerformanceCounters.ProcessCPU T
		WHERE PC.TerminalServerID=T.TerminalServerID 
		and PC.TerminalServerID=@TerminalServerID
		AND PC.ProcessID=T.ProcessID
		AND PC.PID=T.PID)
	and PC.TerminalServerID=@TerminalServerID
	and PC.Value IS NOT NULL
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 constraints to [PerformanceCounters].[Counters]'
GO
ALTER TABLE [PerformanceCounters].[Counters] ADD CONSTRAINT [uc_Counters] UNIQUE NONCLUSTERED  ([CategoryID], [Name])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding constraints to [PerformanceCounters].[Instances]'
GO
ALTER TABLE [PerformanceCounters].[Instances] ADD CONSTRAINT [uc_Instance] UNIQUE NONCLUSTERED  ([CategoryID], [Name])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [dbo].[PerformanceCountersAlerts]'
GO
ALTER TABLE [dbo].[PerformanceCountersAlerts] ADD CONSTRAINT [FK_PerformanceCountersAlerts_AlertLevels] FOREIGN KEY ([AlertLevelID]) REFERENCES [dbo].[AlertLevels] ([ID])
ALTER TABLE [dbo].[PerformanceCountersAlerts] ADD CONSTRAINT [FK_PerformanceCountersAlerts_AlertStatuses] FOREIGN KEY ([AlertStatusID]) REFERENCES [dbo].[AlertStatuses] ([ID])
ALTER TABLE [dbo].[PerformanceCountersAlerts] ADD CONSTRAINT [FK_PerformanceCountersAlerts_Discs] FOREIGN KEY ([DiskID]) REFERENCES [dbo].[Discs] ([DiscID])
ALTER TABLE [dbo].[PerformanceCountersAlerts] ADD CONSTRAINT [FK_PerformanceCountersAlerts_NetworkAdapters] FOREIGN KEY ([NetworkAdapterID]) REFERENCES [dbo].[NetworkAdapters] ([NetworkAdapterID])
ALTER TABLE [dbo].[PerformanceCountersAlerts] ADD CONSTRAINT [FK_PerformanceCountersAlerts_TerminalServers] FOREIGN KEY ([TerminalServerID]) REFERENCES [EventCollection].[MonitoringServers] ([ID])
ALTER TABLE [dbo].[PerformanceCountersAlerts] ADD CONSTRAINT [FK_PerformanceCountersAlerts_Instances] FOREIGN KEY ([InstanceID]) REFERENCES [PerformanceCounters].[Instances] ([InstanceID])
ALTER TABLE [dbo].[PerformanceCountersAlerts] ADD CONSTRAINT [FK_PerformanceCountersAlerts_Counters] FOREIGN KEY ([CounterID]) REFERENCES [PerformanceCounters].[Counters] ([CounterID])
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].[ConnectedSiteCollections]'
GO
ALTER TABLE [dbo].[ConnectedSiteCollections] ADD CONSTRAINT [FK_ConnectedSiteCollections_SPDocKitWorkstationID] FOREIGN KEY ([SPDocKitWorkstationID]) REFERENCES [dbo].[SPDocKitWorkstations] ([ID])
ALTER TABLE [dbo].[ConnectedSiteCollections] ADD CONSTRAINT [FK_ConnectedSiteCollections_FarmID] FOREIGN KEY ([FarmID]) REFERENCES [dbo].[Farms] ([ID])
ALTER TABLE [dbo].[ConnectedSiteCollections] ADD CONSTRAINT [FK_ConnectedSiteCollections_SPDocKiCredentialID] FOREIGN KEY ([SPDocKitCredentialID]) REFERENCES [dbo].[SPDocKitCredentials] ([ID])
ALTER TABLE [dbo].[ConnectedSiteCollections] ADD CONSTRAINT [FK_ConnectedSiteCollections_WebAppID] FOREIGN KEY ([WebApplicationID]) REFERENCES [dbo].[WebApplications] ([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 [PerformanceCounters].[DiskQueue]'
GO
ALTER TABLE [PerformanceCounters].[DiskQueue] ADD CONSTRAINT [FK_DiskQueue_Discs] FOREIGN KEY ([DiskID]) REFERENCES [dbo].[Discs] ([DiscID])
ALTER TABLE [PerformanceCounters].[DiskQueue] ADD CONSTRAINT [FK_DiskQueue_TerminalServers] FOREIGN KEY ([TerminalServerID]) REFERENCES [EventCollection].[MonitoringServers] ([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 [PerformanceCounters].[DiskRead]'
GO
ALTER TABLE [PerformanceCounters].[DiskRead] ADD CONSTRAINT [FK_DiskRead_Discs] FOREIGN KEY ([DiskID]) REFERENCES [dbo].[Discs] ([DiscID])
ALTER TABLE [PerformanceCounters].[DiskRead] ADD CONSTRAINT [FK_DiskRead_TerminalServers] FOREIGN KEY ([TerminalServerID]) REFERENCES [EventCollection].[MonitoringServers] ([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 [PerformanceCounters].[DiskTransfer]'
GO
ALTER TABLE [PerformanceCounters].[DiskTransfer] ADD CONSTRAINT [FK_DiskTransfer_Discs] FOREIGN KEY ([DiskID]) REFERENCES [dbo].[Discs] ([DiscID])
ALTER TABLE [PerformanceCounters].[DiskTransfer] ADD CONSTRAINT [FK_DiskTransfer_TerminalServers] FOREIGN KEY ([TerminalServerID]) REFERENCES [EventCollection].[MonitoringServers] ([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 [PerformanceCounters].[DiskWrite]'
GO
ALTER TABLE [PerformanceCounters].[DiskWrite] ADD CONSTRAINT [FK_DiskWrite_Discs] FOREIGN KEY ([DiskID]) REFERENCES [dbo].[Discs] ([DiscID])
ALTER TABLE [PerformanceCounters].[DiskWrite] ADD CONSTRAINT [FK_DiskWrite_TerminalServers] FOREIGN KEY ([TerminalServerID]) REFERENCES [EventCollection].[MonitoringServers] ([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 [PerformanceCounters].[DiskFreeSpace]'
GO
ALTER TABLE [PerformanceCounters].[DiskFreeSpace] ADD CONSTRAINT [FK_FreeSpace_Discs] FOREIGN KEY ([DiskID]) REFERENCES [dbo].[Discs] ([DiscID])
ALTER TABLE [PerformanceCounters].[DiskFreeSpace] ADD CONSTRAINT [FK_FreeSpace_TerminalServers] FOREIGN KEY ([TerminalServerID]) REFERENCES [EventCollection].[MonitoringServers] ([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 [dbo].[Discs]'
GO
ALTER TABLE [dbo].[Discs] ADD CONSTRAINT [FK_Discs_TerminalServers] FOREIGN KEY ([TerminalServerID]) REFERENCES [EventCollection].[MonitoringServers] ([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 [dbo].[LoadedGroupSettings]'
GO
ALTER TABLE [dbo].[LoadedGroupSettings] ADD CONSTRAINT [FK_LoadedGroupSettings_LoadingInfos] FOREIGN KEY ([LoadingInfoID]) REFERENCES [dbo].[LoadingInfos] ([ID]) ON DELETE CASCADE
ALTER TABLE [dbo].[LoadedGroupSettings] ADD CONSTRAINT [FK_LoadedGroupSettings_Principals] FOREIGN KEY ([PrincipalID]) REFERENCES [dbo].[Principals] ([PrincipalID])
ALTER TABLE [dbo].[LoadedGroupSettings] ADD CONSTRAINT [FK_LoadedGroupSettings_OwnerPrincipals] FOREIGN KEY ([PrincipalOwnerID]) REFERENCES [dbo].[Principals] ([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'Adding foreign keys to [PerformanceCounters].[NetworkReceived]'
GO
ALTER TABLE [PerformanceCounters].[NetworkReceived] ADD CONSTRAINT [FK_NetworkReceived_NetworkAdapter] FOREIGN KEY ([NetworkAdapterID]) REFERENCES [dbo].[NetworkAdapters] ([NetworkAdapterID])
ALTER TABLE [PerformanceCounters].[NetworkReceived] ADD CONSTRAINT [FK_PerformanceCounters_NetworkReceived_TerminalServers] FOREIGN KEY ([TerminalServerID]) REFERENCES [EventCollection].[MonitoringServers] ([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 [PerformanceCounters].[NetworkSent]'
GO
ALTER TABLE [PerformanceCounters].[NetworkSent] ADD CONSTRAINT [FK_NetworkSent_NetworkAdapter] FOREIGN KEY ([NetworkAdapterID]) REFERENCES [dbo].[NetworkAdapters] ([NetworkAdapterID])
ALTER TABLE [PerformanceCounters].[NetworkSent] ADD CONSTRAINT [FK_PerformanceCounters_NetworkSent_TerminalServers] FOREIGN KEY ([TerminalServerID]) REFERENCES [EventCollection].[MonitoringServers] ([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 [dbo].[NetworkAdapters]'
GO
ALTER TABLE [dbo].[NetworkAdapters] ADD CONSTRAINT [FK_NetworkAdapters_TerminalServers] FOREIGN KEY ([TerminalServerID]) REFERENCES [EventCollection].[MonitoringServers] ([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 [dbo].[SPDocKitCredentials]'
GO
ALTER TABLE [dbo].[SPDocKitCredentials] ADD CONSTRAINT [FK_SPDocKitCredentials_SPDocKitWorkstationID] FOREIGN KEY ([SPDocKitWorkstationID]) REFERENCES [dbo].[SPDocKitWorkstations] ([ID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [PerformanceCounters].[Counters]'
GO
ALTER TABLE [PerformanceCounters].[Counters] ADD CONSTRAINT [FK_Counters_Categories] FOREIGN KEY ([CategoryID]) REFERENCES [PerformanceCounters].[Categories] ([CategoryID])
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 [PerformanceCounters].[Instances]'
GO
ALTER TABLE [PerformanceCounters].[Instances] ADD CONSTRAINT [FK_Instances_Categories] FOREIGN KEY ([CategoryID]) REFERENCES [PerformanceCounters].[Categories] ([CategoryID])
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 [PerformanceCounters].[CounterThresholds]'
GO
ALTER TABLE [PerformanceCounters].[CounterThresholds] ADD CONSTRAINT [FK_CounterThresholds_Counters] FOREIGN KEY ([CounterID]) REFERENCES [PerformanceCounters].[Counters] ([CounterID])
ALTER TABLE [PerformanceCounters].[CounterThresholds] ADD CONSTRAINT [FK_CounterThresholds_Instances] FOREIGN KEY ([InstanceID]) REFERENCES [PerformanceCounters].[Instances] ([InstanceID])
ALTER TABLE [PerformanceCounters].[CounterThresholds] ADD CONSTRAINT [FK_CounterThresholds_Templates] FOREIGN KEY ([TemplateID]) REFERENCES [Template].[Templates] ([TemplateID])
ALTER TABLE [PerformanceCounters].[CounterThresholds] ADD CONSTRAINT [FK_CounterThresholds_ThresholdTypes] FOREIGN KEY ([ThresholdTypeID]) REFERENCES [PerformanceCounters].[ThresholdTypes] ([ThresholdTypeID])
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 [PerformanceCounters].[CounterValues]'
GO
ALTER TABLE [PerformanceCounters].[CounterValues] ADD CONSTRAINT [FK_CounterValues_Counters] FOREIGN KEY ([CounterID]) REFERENCES [PerformanceCounters].[Counters] ([CounterID])
ALTER TABLE [PerformanceCounters].[CounterValues] ADD CONSTRAINT [FK_CounterValues_TerminalServers] FOREIGN KEY ([TerminalServerID]) REFERENCES [EventCollection].[MonitoringServers] ([ID])
ALTER TABLE [PerformanceCounters].[CounterValues] ADD CONSTRAINT [FK_CounterValues_Instances] FOREIGN KEY ([InstanceID]) REFERENCES [PerformanceCounters].[Instances] ([InstanceID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [PerformanceCounters].[ServerCounterStates]'
GO
ALTER TABLE [PerformanceCounters].[ServerCounterStates] ADD CONSTRAINT [FK_ServerCounterStates_Counters] FOREIGN KEY ([CounterID]) REFERENCES [PerformanceCounters].[Counters] ([CounterID])
ALTER TABLE [PerformanceCounters].[ServerCounterStates] ADD CONSTRAINT [FK_ServerCounterStates_CounterStateTypes] FOREIGN KEY ([CounterStateTypeID]) REFERENCES [PerformanceCounters].[CounterStateTypes] ([CounterStateTypeID])
ALTER TABLE [PerformanceCounters].[ServerCounterStates] ADD CONSTRAINT [FK_ServerCounterStates_TerminalServers] FOREIGN KEY ([TerminalServerID]) REFERENCES [EventCollection].[MonitoringServers] ([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 [PerformanceCounters].[ServerInstances]'
GO
ALTER TABLE [PerformanceCounters].[ServerInstances] ADD CONSTRAINT [FK_ServerInstances_Counters] FOREIGN KEY ([CounterID]) REFERENCES [PerformanceCounters].[Counters] ([CounterID])
ALTER TABLE [PerformanceCounters].[ServerInstances] ADD CONSTRAINT [FK_ServerInstances_CounterStatusByThreshold] FOREIGN KEY ([CounterStatusID]) REFERENCES [PerformanceCounters].[CounterStatusByThreshold] ([CounterStatusID])
ALTER TABLE [PerformanceCounters].[ServerInstances] ADD CONSTRAINT [FK_ServerInstances_Instances] FOREIGN KEY ([InstanceID]) REFERENCES [PerformanceCounters].[Instances] ([InstanceID])
ALTER TABLE [PerformanceCounters].[ServerInstances] ADD CONSTRAINT [FK_ServerInstances_TerminalServers] FOREIGN KEY ([TerminalServerID]) REFERENCES [EventCollection].[MonitoringServers] ([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 [PerformanceCounters].[CPU]'
GO
ALTER TABLE [PerformanceCounters].[CPU] ADD CONSTRAINT [FK_PerformanceCounters_CPU_TerminalServers] FOREIGN KEY ([TerminalServerID]) REFERENCES [EventCollection].[MonitoringServers] ([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 [PerformanceCounters].[Memory]'
GO
ALTER TABLE [PerformanceCounters].[Memory] ADD CONSTRAINT [FK_PerformanceCounters_Memory_TerminalServers] FOREIGN KEY ([TerminalServerID]) REFERENCES [EventCollection].[MonitoringServers] ([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 [Template].[ServerSubscriptions]'
GO
ALTER TABLE [Template].[ServerSubscriptions] ADD CONSTRAINT [FK_ServerSubscriptions_Templates] FOREIGN KEY ([TemplateID]) REFERENCES [Template].[Templates] ([TemplateID])
ALTER TABLE [Template].[ServerSubscriptions] ADD CONSTRAINT [FK_ServerSubscriptions_TerminalServers] FOREIGN KEY ([TerminalServerID]) REFERENCES [EventCollection].[MonitoringServers] ([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 permissions on [dbo].[GetAnonymousAccessLinks]'
GO
GRANT EXECUTE ON  [dbo].[GetAnonymousAccessLinks] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[GetConnectedSiteCollections]'
GO
GRANT EXECUTE ON  [dbo].[GetConnectedSiteCollections] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[GetExternalUsers]'
GO
GRANT EXECUTE ON  [dbo].[GetExternalUsers] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[GetSiteCollectionAuthSettings]'
GO
GRANT EXECUTE ON  [dbo].[GetSiteCollectionAuthSettings] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[GetSpGroupSettings]'
GO
GRANT EXECUTE ON  [dbo].[GetSpGroupSettings] TO [spdockit_report_viewer]
GO
UPDATE Options SET OptionValue = '6.4.1' WHERE OptionID = 14
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
INSERT INTO Options (OptionID, OptionValue) VALUES (2102, 'false')
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 Options (OptionID, OptionValue) VALUES (2103, 'false')
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 Options (OptionID, OptionValue) VALUES (2104, 'false')
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 Options (OptionID, OptionValue) VALUES (2105, 'true')
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 Options (OptionID, OptionValue) VALUES (2106, 'true')
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 Options (OptionID, OptionValue) VALUES (2107, 'true')
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 Options (OptionID, OptionValue) VALUES (2108, 'false')
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 Options (OptionID, OptionValue) VALUES (2109, 'true')
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 Options (OptionID, OptionValue) VALUES (2110, 'true')
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 Options (OptionID, OptionValue) VALUES (2111, 'true')
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 Options (OptionID, OptionValue) VALUES (2112, 'true')
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 Options (OptionID, OptionValue) VALUES (2113, 'true')
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 Options (OptionID, OptionValue) VALUES (2114, 'SQL Light Gray')
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 Options (OptionID, OptionValue) VALUES (2115, '8')
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 Options (OptionID, OptionValue) VALUES (2116, 'false')
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 Options (OptionID, OptionValue) VALUES (2117, '')
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 Options (OptionID, OptionValue) VALUES (2118, '80')
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 Options (OptionID, OptionValue) VALUES (2119, 'false')
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 Options (OptionID, OptionValue) VALUES (2120, '')
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 Options (OptionID, OptionValue) VALUES (2121, '')
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 Options (OptionID, OptionValue) VALUES (2122, 'false')
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 Options (OptionID, OptionValue) VALUES (2123, '6')
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 Options (OptionID, OptionValue) VALUES (2124, '2')
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 Options (OptionID, OptionValue) VALUES (2125, '10')
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 Options (OptionID, OptionValue) VALUES (2126, '')
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 Options (OptionID, OptionValue) VALUES (2127, '1')
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
INSERT INTO Options (OptionID, OptionValue) VALUES (2128, '1')
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
INSERT INTO Options (OptionID, OptionValue) VALUES (2129, '{CompanyName}||{Logo}')
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 Options (OptionID, OptionValue) VALUES (2130, 'Generated by {AppName} on {Date}|Page {Page}/{TotalPages}')
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 Options (OptionID, OptionValue) VALUES (2131, '')
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 Options (OptionID, OptionValue) VALUES (2132, '12:00:00')
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 Options (OptionID, OptionValue) VALUES (2133, '')
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 Options (OptionID, OptionValue) VALUES (2134, '')
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
-------- Init - templatei, counteri, kategorije... ---------

INSERT INTO AlertLevels (ID, [Level]) VALUES (1, 'Warning')
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 AlertLevels (ID, [Level]) VALUES (2, 'Critical')
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 AlertStatuses (ID, [Status]) VALUES (1, 'Active')
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 AlertStatuses (ID, [Status]) VALUES (2, 'Ended')
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 AlertStatuses (ID, [Status]) VALUES (3, 'ActiveAndAlertSent')

INSERT INTO PerformanceCounters.ThresholdTypes (ThresholdTypeID, Name) VALUES (1,'Above')
INSERT INTO PerformanceCounters.ThresholdTypes (ThresholdTypeID, Name) VALUES (2, 'Below')

INSERT INTO PerformanceCounters.CounterStateTypes (CounterStateTypeID, Name) VALUES (1, 'Exists')
INSERT INTO PerformanceCounters.CounterStateTypes (CounterStateTypeID, Name) VALUES (2, 'NotExists')
INSERT INTO PerformanceCounters.CounterStateTypes (CounterStateTypeID, Name) VALUES (3, 'Unknown')

INSERT INTO PerformanceCounters.CounterStatusByThreshold (CounterStatusID, Name) VALUES (1, 'Healthy')
INSERT INTO PerformanceCounters.CounterStatusByThreshold (CounterStatusID, Name) VALUES (2, 'Warning')
INSERT INTO PerformanceCounters.CounterStatusByThreshold (CounterStatusID, Name) VALUES (3, 'Critical')

INSERT INTO PerformanceCountersStatuses (StatusID, StatusName) VALUES (1, 'Unknown')
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 PerformanceCountersStatuses (StatusID, StatusName) VALUES (4, 'Started')
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 PerformanceCountersStatuses (StatusID, StatusName) VALUES (8, 'Offline or Not Accessible')
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 Template.Templates(Name, Description) VALUES ('General', 'Default template contains counters that are collected for all servers.')

DECLARE @TemplateID int
SELECT TOP 1 @TemplateID = TemplateID FROM Template.Templates WHERE Name = 'General'

INSERT INTO PerformanceCounters.Categories(ClassName, Singleton, DisplayName, Description) VALUES ('Win32_PerfRawData_PerfOS_Processor', 0, 'Processor', 'The Processor performance object consists of counters that measure aspects of processor activity. The processor is the part of the computer that performs arithmetic and logical computations, initiates operations on peripherals, and runs the threads of processes.  A computer can have multiple processors.  The processor object represents each processor as an instance of the object.')
INSERT INTO PerformanceCounters.Categories(ClassName, Singleton, DisplayName, Description) VALUES ('Win32_PerfRawData_PerfDisk_LogicalDisk', 0, 'LogicalDisk', 'The Logical Disk performance object consists of counters that monitor logical partitions of a hard or fixed disk drives.  Performance Monitor identifies logical disks by their a drive letter, such as C.')
INSERT INTO PerformanceCounters.Categories(ClassName, Singleton, DisplayName, Description) VALUES ('Win32_PerfRawData_PerfOS_Memory', 1, 'Memory', 'The Memory performance object  consists of counters that describe the behavior of physical and virtual memory on the computer.  Physical memory is the amount of random access memory on the computer.  Virtual memory consists of the space in physical memory and on disk.  Many of the memory counters monitor paging, which is the movement of pages of code and data between disk and physical memory.  Excessive paging, a symptom of a memory shortage, can cause delays which interfere with all system processes.')
INSERT INTO PerformanceCounters.Categories(ClassName, Singleton, DisplayName, Description) VALUES ('Win32_PerfRawData_Tcpip_NetworkAdapter', 0, 'Network Adapter', 'The Network Adapter performance object consists of counters that measure the rates at which bytes and packets are sent and received over a physical or virtual network connection.  It includes counters that monitor connection errors.')
INSERT INTO PerformanceCounters.Categories(ClassName, Singleton, DisplayName, Description) VALUES ('Win32_PerfRawData_PerfProc_Process', 0, 'Process', 'The Process performance object consists of counters that monitor running application program and system processes.  All the threads in a process share the same address space and have access to the same data.')

DECLARE @PercentProcessorTimeID int
DECLARE @AvailableMBytesID int

DECLARE @FreeMegabytesID int
DECLARE @AvgDiskQueueID int
DECLARE @DiskReadID int
DECLARE @DiskWriteID int
DECLARE @DiskTransfersID int

DECLARE @BytesReceivedAdapterID int
DECLARE @BytesSentAdapterID int

DECLARE @PercentProcessTimeID int
DECLARE @IOReadID int
DECLARE @IOWriteID int
DECLARE @IODataID int
DECLARE @WorkingSetID int

SET @PercentProcessorTimeID = 2
SET @AvailableMBytesID = 4

SET @FreeMegabytesID = 31
SET @AvgDiskQueueID = 27
SET @DiskReadID = 28
SET @DiskWriteID = 29
SET @DiskTransfersID = 30

SET @BytesReceivedAdapterID = 15
SET @BytesSentAdapterID = 16

SET @PercentProcessTimeID = 6
SET @IOReadID = 25
SET @IOWriteID = 26
SET @IODataID = 32
SET @WorkingSetID = 24

SET IDENTITY_INSERT PerformanceCounters.Counters ON
DECLARE @ProcessorClassID int
SELECT TOP 1 @ProcessorClassID = CategoryID FROM PerformanceCounters.Categories WHERE ClassName = 'Win32_PerfRawData_PerfOS_Processor'

INSERT INTO PerformanceCounters.Counters(CounterID, CategoryID, Name, DisplayName, Description, CounterType) VALUES (@PercentProcessorTimeID, @ProcessorClassID, 'PercentProcessorTime', '% Processor Time', '% Processor Time is the percentage of elapsed time that all of process threads used the processor to execution instructions. An instruction is the basic unit of execution in a computer, a thread is the object that executes instructions, and a process is the object created when a program is run. Code executed to handle some hardware interrupts and trap conditions are included in this count.', 558957824)
INSERT INTO PerformanceCounters.Instances(CategoryID, Name) VALUES(@ProcessorClassID, '_Total')

DECLARE @LogicalDiskClassID int
SELECT TOP 1 @LogicalDiskClassID = CategoryID FROM PerformanceCounters.Categories WHERE ClassName = 'Win32_PerfRawData_PerfDisk_LogicalDisk' 

INSERT INTO PerformanceCounters.Counters(CounterID, CategoryID, Name, DisplayName, Description, CounterType) VALUES (@FreeMegabytesID, @LogicalDiskClassID, 'FreeMegabytes', 'Free GB', 'Free GB displays the unallocated space on the disk drive in GB. Counter thresholds are defined in megabytes.', 65536)
INSERT INTO PerformanceCounters.Counters(CounterID, CategoryID, Name, DisplayName, Description, CounterType) VALUES (@AvgDiskQueueID, @LogicalDiskClassID, 'AvgDiskQueueLength', 'Avg. Disk Queue Length', 'Avg. Disk Queue Length is the average number of both read and write requests that were queued for the selected disk during the sample interval.', 5571840)
INSERT INTO PerformanceCounters.Counters(CounterID, CategoryID, Name, DisplayName, Description, CounterType) VALUES (@DiskReadID, @LogicalDiskClassID, 'DiskReadBytesPersec', 'Disk Read Bytes/sec', 'Number of bytes read from the disk in the last second', 27269320)
INSERT INTO PerformanceCounters.Counters(CounterID, CategoryID, Name, DisplayName, Description, CounterType) VALUES (@DiskWriteID, @LogicalDiskClassID, 'DiskWriteBytesPersec', 'Disk Write Bytes/sec', 'Number of bytes written to the disk in the last second', 27269320)
INSERT INTO PerformanceCounters.Counters(CounterID, CategoryID, Name, DisplayName, Description, CounterType) VALUES (@DiskTransfersID, @LogicalDiskClassID, 'DiskTransfersPersec', 'Disk Transfers/sec', 'Disk Transfers/sec is the rate of read and write operations on the disk.', 272696320)
INSERT INTO PerformanceCounters.Instances(CategoryID, Name) VALUES(@LogicalDiskClassID, '*')

DECLARE @MemoryClassID int
SELECT TOP 1 @MemoryClassID = CategoryID FROM PerformanceCounters.Categories WHERE ClassName = 'Win32_PerfRawData_PerfOS_Memory'

INSERT INTO PerformanceCounters.Counters(CounterID, CategoryID, Name, DisplayName, Description, CounterType) VALUES (@AvailableMBytesID, @MemoryClassID, 'AvailableMBytes', 'Used MB', 'Displays the amount of used memory, in GB, on a computer. Counter thresholds are defined in megabytes.', 65536)
INSERT INTO PerformanceCounters.Instances(CategoryID, Name) VALUES(@MemoryClassID, null)

DECLARE @NetworkAdapterClassID int
SELECT TOP 1 @NetworkAdapterClassID = CategoryID FROM PerformanceCounters.Categories WHERE ClassName = 'Win32_PerfRawData_Tcpip_NetworkAdapter'

INSERT INTO PerformanceCounters.Counters(CounterID, CategoryID, Name, DisplayName, Description, CounterType) VALUES (@BytesReceivedAdapterID, @NetworkAdapterClassID, 'BytesReceivedPersec', 'Bytes Received/sec', 'Bytes Received/sec is the number of bytes received per second on the network adapter.', 272696576)
INSERT INTO PerformanceCounters.Counters(CounterID, CategoryID, Name, DisplayName, Description, CounterType) VALUES (@BytesSentAdapterID, @NetworkAdapterClassID, 'BytesSentPersec', 'Bytes Sent/sec', 'Bytes Sent/sec is the number of bytes sent per second over the network adapter.', 272696576)
INSERT INTO PerformanceCounters.Instances(CategoryID, Name) VALUES(@NetworkAdapterClassID, '*')

DECLARE @ProcessClassID int
SELECT TOP 1 @ProcessClassID = CategoryID FROM PerformanceCounters.Categories WHERE ClassName = 'Win32_PerfRawData_PerfProc_Process'

INSERT INTO PerformanceCounters.Counters(CounterID, CategoryID, Name, DisplayName, Description, CounterType) VALUES (@PercentProcessTimeID, @ProcessClassID, 'PercentProcessorTime', '% Processor Time', '% Processor Time is the percentage of elapsed time that all of process threads used the processor to execution instructions. An instruction is the basic unit of execution in a computer, a thread is the object that executes instructions, and a process is the object created when a program is run. Code executed to handle some hardware interrupts and trap conditions are included in this count.', 542180608)
INSERT INTO PerformanceCounters.Counters(CounterID, CategoryID, Name, DisplayName, Description, CounterType) VALUES (@IOReadID, @ProcessClassID, 'IOReadBytesPersec', 'IO Read Bytes/sec', 'The rate at which the process is reading bytes from I/O operations. This counter counts all I/O activity generated by the process to include file, network and device I/Os.', 272696576)
INSERT INTO PerformanceCounters.Counters(CounterID, CategoryID, Name, DisplayName, Description, CounterType) VALUES (@IOWriteID, @ProcessClassID, 'IOWriteBytesPersec', 'IO Write Bytes/sec', 'The rate at which the process is writing bytes to I/O operations. This counter counts all I/O activity generated by the process to include file, network and device I/Os.', 272696576)
INSERT INTO PerformanceCounters.Counters(CounterID, CategoryID, Name, DisplayName, Description, CounterType) VALUES (@IODataID, @ProcessClassID, 'IODataOperationsPersec', 'IO Data Operations/sec', 'The rate at which the process is issuing read and write I/O operations. This counter counts all I/O activity generated by the process to include file, network and device I/Os.', 272696576)
INSERT INTO PerformanceCounters.Counters(CounterID, CategoryID, Name, DisplayName, Description, CounterType) VALUES (@WorkingSetID, @ProcessClassID, 'WorkingSet', 'Working Set', 'Working Set is the current size, in bytes, of the Working Set of this process. The Working Set is the set of memory pages touched recently by the threads in the process. If free memory in the computer is above a threshold, pages are left in the Working Set of a process even if they are not in use.  When free memory falls below a threshold, pages are trimmed from Working Sets. If they are needed they will then be soft-faulted back into the Working Set before leaving main memory.', 1)
INSERT INTO PerformanceCounters.Instances(CategoryID, Name) VALUES(@ProcessClassID, '*')

SET IDENTITY_INSERT PerformanceCounters.Counters OFF


IF NOT EXISTS (SELECT * FROM PerformanceCounters.CounterThresholds WHERE TemplateID = @TemplateID AND CounterID = @PercentProcessorTimeID)
BEGIN
INSERT INTO PerformanceCounters.CounterThresholds(TemplateID, CounterID, InstanceID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) SELECT @TemplateID, @PercentProcessorTimeID, PerformanceCounters.Instances.InstanceID, 95, 85, 15, 1, 0, 12, 1, 1 FROM PerformanceCounters.Instances WHERE PerformanceCounters.Instances.CategoryID = @ProcessorClassID
END

IF NOT EXISTS (SELECT * FROM PerformanceCounters.CounterThresholds WHERE TemplateID = @TemplateID AND CounterID = @FreeMegabytesID)
BEGIN
INSERT INTO PerformanceCounters.CounterThresholds(TemplateID, CounterID, InstanceID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) SELECT @TemplateID, @FreeMegabytesID, PerformanceCounters.Instances.InstanceID, 3072, 5120, 1, 1, 0, 12, 2, -1 FROM PerformanceCounters.Instances WHERE PerformanceCounters.Instances.CategoryID = @LogicalDiskClassID
END

IF NOT EXISTS (SELECT * FROM PerformanceCounters.CounterThresholds WHERE TemplateID = @TemplateID AND CounterID = @AvgDiskQueueID)
BEGIN
INSERT INTO PerformanceCounters.CounterThresholds(TemplateID, CounterID, InstanceID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) SELECT @TemplateID, @AvgDiskQueueID, PerformanceCounters.Instances.InstanceID, 4, 2, 15, 1, 0, 12, 1, -1 FROM PerformanceCounters.Instances WHERE PerformanceCounters.Instances.CategoryID = @LogicalDiskClassID
END


IF NOT EXISTS (SELECT * FROM PerformanceCounters.CounterThresholds WHERE TemplateID = @TemplateID AND CounterID = @DiskReadID)
BEGIN
INSERT INTO PerformanceCounters.CounterThresholds(TemplateID, CounterID, InstanceID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) SELECT @TemplateID, @DiskReadID, PerformanceCounters.Instances.InstanceID, 200, 100, 15, 1, 0, 12, 1, -1 FROM PerformanceCounters.Instances WHERE PerformanceCounters.Instances.CategoryID = @LogicalDiskClassID
END

IF NOT EXISTS (SELECT * FROM PerformanceCounters.CounterThresholds WHERE TemplateID = @TemplateID AND CounterID = @DiskWriteID)
BEGIN
INSERT INTO PerformanceCounters.CounterThresholds(TemplateID, CounterID, InstanceID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) SELECT @TemplateID, @DiskWriteID, PerformanceCounters.Instances.InstanceID, 200, 100, 15, 1, 0, 12, 1, -1 FROM PerformanceCounters.Instances WHERE PerformanceCounters.Instances.CategoryID = @LogicalDiskClassID
END

IF NOT EXISTS (SELECT * FROM PerformanceCounters.CounterThresholds WHERE TemplateID = @TemplateID AND CounterID = @DiskTransfersID)
BEGIN
INSERT INTO PerformanceCounters.CounterThresholds(TemplateID, CounterID, InstanceID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) SELECT @TemplateID, @DiskTransfersID, PerformanceCounters.Instances.InstanceID, 400, 200, 15, 1, 0, 12, 1, 5 FROM PerformanceCounters.Instances WHERE PerformanceCounters.Instances.CategoryID = @LogicalDiskClassID
END

IF NOT EXISTS (SELECT * FROM PerformanceCounters.CounterThresholds WHERE TemplateID = @TemplateID AND CounterID = @AvailableMBytesID)
BEGIN
INSERT INTO PerformanceCounters.CounterThresholds(TemplateID, CounterID, InstanceID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) SELECT @TemplateID, @AvailableMBytesID, PerformanceCounters.Instances.InstanceID, 256, 512, 15, 1, 0, 12, 2, -1 FROM PerformanceCounters.Instances WHERE PerformanceCounters.Instances.CategoryID = @MemoryClassID
END

IF NOT EXISTS (SELECT * FROM PerformanceCounters.CounterThresholds WHERE TemplateID = @TemplateID AND CounterID = @BytesReceivedAdapterID)
BEGIN
INSERT INTO PerformanceCounters.CounterThresholds(TemplateID, CounterID, InstanceID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) SELECT @TemplateID, @BytesReceivedAdapterID, PerformanceCounters.Instances.InstanceID, 10000000, 5000000, 15, 1, 0, 12, 1, 5 FROM PerformanceCounters.Instances WHERE PerformanceCounters.Instances.CategoryID = @NetworkAdapterClassID 
END

IF NOT EXISTS (SELECT * FROM PerformanceCounters.CounterThresholds WHERE TemplateID = @TemplateID AND CounterID = @BytesSentAdapterID)
BEGIN
INSERT INTO PerformanceCounters.CounterThresholds(TemplateID, CounterID, InstanceID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) SELECT @TemplateID, @BytesSentAdapterID, PerformanceCounters.Instances.InstanceID, 10000000, 5000000, 15, 1, 0, 12, 1, 5 FROM PerformanceCounters.Instances WHERE PerformanceCounters.Instances.CategoryID = @NetworkAdapterClassID 
END

IF NOT EXISTS (SELECT * FROM PerformanceCounters.CounterThresholds WHERE TemplateID = @TemplateID AND CounterID = @PercentProcessTimeID)
BEGIN
INSERT INTO PerformanceCounters.CounterThresholds(TemplateID, CounterID, InstanceID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) SELECT @TemplateID, @PercentProcessTimeID, PerformanceCounters.Instances.InstanceID, 0, 0, 15, 0, 0, 3, 2, 1 FROM PerformanceCounters.Instances WHERE PerformanceCounters.Instances.CategoryID = @ProcessClassID
END

IF NOT EXISTS (SELECT * FROM PerformanceCounters.CounterThresholds WHERE TemplateID = @TemplateID AND CounterID = @IOReadID)
BEGIN
INSERT INTO PerformanceCounters.CounterThresholds(TemplateID, CounterID, InstanceID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) SELECT @TemplateID, @IOReadID, PerformanceCounters.Instances.InstanceID, 0, 0, 15, 0, 0, 3, 2, 5 FROM PerformanceCounters.Instances WHERE PerformanceCounters.Instances.CategoryID = @ProcessClassID
END

IF NOT EXISTS (SELECT * FROM PerformanceCounters.CounterThresholds WHERE TemplateID = @TemplateID AND CounterID = @IOWriteID)
BEGIN
INSERT INTO PerformanceCounters.CounterThresholds(TemplateID, CounterID, InstanceID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) SELECT @TemplateID, @IOWriteID, PerformanceCounters.Instances.InstanceID, 0, 0, 15, 0, 0, 3, 2, 5 FROM PerformanceCounters.Instances WHERE PerformanceCounters.Instances.CategoryID = @ProcessClassID
END

IF NOT EXISTS (SELECT * FROM PerformanceCounters.CounterThresholds WHERE TemplateID = @TemplateID AND CounterID = @IODataID)
BEGIN
INSERT INTO PerformanceCounters.CounterThresholds(TemplateID, CounterID, InstanceID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) SELECT @TemplateID, @IODataID, PerformanceCounters.Instances.InstanceID, 0, 0, 15, 0, 0, 3, 2, 5 FROM PerformanceCounters.Instances WHERE PerformanceCounters.Instances.CategoryID = @ProcessClassID
END

IF NOT EXISTS (SELECT * FROM PerformanceCounters.CounterThresholds WHERE TemplateID = @TemplateID AND CounterID = @WorkingSetID)
BEGIN
INSERT INTO PerformanceCounters.CounterThresholds(TemplateID, CounterID, InstanceID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) SELECT @TemplateID, @WorkingSetID, PerformanceCounters.Instances.InstanceID, 0, 0, 15, 0, 0, 3, 2, 5 FROM PerformanceCounters.Instances WHERE PerformanceCounters.Instances.CategoryID = @ProcessClassID
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
insert into Template.Templates (Name, Description) values ('IIS','IIS template helps you keep an eye on your IIS servers in SharePoint farm. Diagnose various problems and perfomace issues with ease.')
declare @iisTemplateId int
set @iisTemplateId = (select TemplateID from Template.Templates where Name = 'IIS')

insert into Template.Templates (Name, Description) values ('SQL','SQL template enables you to efficiently monitor your SQL servers. With specified counters you can quickly find possible bottlenecks and performance issues.')
declare @sqlTemplateID int
set @sqlTemplateID = (select TemplateID from Template.Templates where Name = 'SQL')

insert into Template.Templates (Name, Description) values ('Disk','Disk template helps you keep an eye on your disks across the SharePoint farm. Diagnose possible bottlenecks and performance issues with ease.')
declare @diskTemplateId int
set @diskTemplateId = (select TemplateID from Template.Templates where Name = 'Disk')

insert into Template.Templates (Name, Description) values ('.NET','.NET template helps you track .NET counters on servers in SharePoint farm. Diagnose various problems and perfomace issues with ease.')
declare @dotNetTemplateId int
set @dotNetTemplateId = (select TemplateID from Template.Templates where Name = '.NET')

INSERT INTO PerformanceCounters.Categories (ClassName, Singleton, DisplayName, Description) Values ('Win32_PerfRawData_MSSQLSERVER_SQLServerGeneralStatistics', 1, 'SQLServer:General Statistics', 'Server General Statistics')
DECLARE @SQLServerGeneralStatisticsCatId INT
SET @SQLServerGeneralStatisticsCatId = (select CategoryID from PerformanceCounters.Categories where ClassName = 'Win32_PerfRawData_MSSQLSERVER_SQLServerGeneralStatistics')
INSERT INTO PerformanceCounters.Categories (ClassName, Singleton, DisplayName, Description) Values ('Win32_PerfRawData_MSSQLSERVER_SQLServerBufferManager', 1, 'SQLServer:Buffer Manager', 'Statistics related to SQL Servers buffer manager')
DECLARE @SQLServerBufferManagerCatId INT
SET @SQLServerBufferManagerCatId = (select CategoryID from PerformanceCounters.Categories where ClassName = 'Win32_PerfRawData_MSSQLSERVER_SQLServerBufferManager')
INSERT INTO PerformanceCounters.Categories (ClassName, Singleton, DisplayName, Description) Values ('Win32_PerfRawData_MSSQLSERVER_SQLServerSQLStatistics', 1, 'SQLServer:SQL Statistics', 'Collects statistics associated with SQL requests')
DECLARE @SQLServerSQLStatisticsCatId INT
SET @SQLServerSQLStatisticsCatId = (select CategoryID from PerformanceCounters.Categories where ClassName = 'Win32_PerfRawData_MSSQLSERVER_SQLServerSQLStatistics')
INSERT INTO PerformanceCounters.Categories (ClassName, Singleton, DisplayName, Description) Values ('Win32_PerfRawData_MSSQLSERVER_SQLServerAccessMethods', 1, 'SQLServer:Access Methods', 'Collects statistics associated with the database server access methods')
DECLARE @SQLServerAccessMethodsCatId INT
SET @SQLServerAccessMethodsCatId = (select CategoryID from PerformanceCounters.Categories where ClassName = 'Win32_PerfRawData_MSSQLSERVER_SQLServerAccessMethods')
INSERT INTO PerformanceCounters.Categories (ClassName, Singleton, DisplayName, Description) Values ('Win32_PerfRawData_MSSQLSERVER_SQLServerLatches', 1, 'SQLServer:Latches', 'Collects statistics associated with internal server latches')
DECLARE @SQLServerLatchesCatId INT
SET @SQLServerLatchesCatId = (select CategoryID from PerformanceCounters.Categories where ClassName = 'Win32_PerfRawData_MSSQLSERVER_SQLServerLatches')
INSERT INTO PerformanceCounters.Categories (ClassName, Singleton, DisplayName, Description) Values ('Win32_PerfRawData_MSSQLSERVER_SQLServerLocks', 0, 'SQLServer:Locks', 'Describes statistics for individual lock server lock requests')
DECLARE @SQLServerLocksCatId INT
SET @SQLServerLocksCatId = (select CategoryID from PerformanceCounters.Categories where ClassName = 'Win32_PerfRawData_MSSQLSERVER_SQLServerLocks')
INSERT INTO PerformanceCounters.Categories (ClassName, Singleton, DisplayName, Description) Values ('Win32_PerfRawData_MSSQLSERVER_SQLServerDatabases', 0, 'SQLServer:Databases', 'This defines a Database manager object for SQL Server')
DECLARE @SQLServerDatabasesCatId INT
SET @SQLServerDatabasesCatId = (select CategoryID from PerformanceCounters.Categories where ClassName = 'Win32_PerfRawData_MSSQLSERVER_SQLServerDatabases')
INSERT INTO PerformanceCounters.Categories (ClassName, Singleton, DisplayName, Description) Values ('Win32_PerfRawData_PerfOS_System', 1, 'System', 'The System performance object consists of counters that apply to more than one instance of a component processors on the computer.')
DECLARE @SystemCatId INT
SET @SystemCatId = (select CategoryID from PerformanceCounters.Categories where ClassName = 'Win32_PerfRawData_PerfOS_System')
insert into PerformanceCounters.Counters (CategoryID, Name, DisplayName, Description, CounterType) values (@SQLServerGeneralStatisticsCatId, 'UserConnections', 'User Connections', 'Number of users connected to the system.', 65792)
DECLARE @UserConnectionsId INT
set @UserConnectionsId = (select Min(CounterID) from PerformanceCounters.Counters where CategoryID = @SQLServerGeneralStatisticsCatId and Name = 'UserConnections')
insert into PerformanceCounters.Counters (CategoryID, Name, DisplayName, Description, CounterType) values (@SQLServerBufferManagerCatId, 'Buffercachehitratio', 'Buffer Cache Hit Ratio (%)', 'Percentage of pages that were found in the buffer pool without having to incur a read from disk.', 537003264)
DECLARE @BuffercachehitratioId INT
set @BuffercachehitratioId = (select Min(CounterID) from PerformanceCounters.Counters where CategoryID = @SQLServerBufferManagerCatId and Name = 'Buffercachehitratio')
insert into PerformanceCounters.Counters (CategoryID, Name, DisplayName, Description, CounterType) values (@SQLServerBufferManagerCatId, 'LazywritesPersec', 'Lazy Writes/sec', 'Number of buffers written by buffer manager''s lazy writer.', 272696576)
DECLARE @LazywritesPersecId INT
set @LazywritesPersecId = (select Min(CounterID) from PerformanceCounters.Counters where CategoryID = @SQLServerBufferManagerCatId and Name = 'LazywritesPersec')
insert into PerformanceCounters.Counters (CategoryID, Name, DisplayName, Description, CounterType) values (@SQLServerBufferManagerCatId, 'Pagelifeexpectancy', 'Page Life Expectancy (s)', 'Number of seconds a page will stay in the buffer pool without references.', 65792)
DECLARE @PagelifeexpectancyId INT
set @PagelifeexpectancyId = (select Min(CounterID) from PerformanceCounters.Counters where CategoryID = @SQLServerBufferManagerCatId and Name = 'Pagelifeexpectancy')
insert into PerformanceCounters.Counters (CategoryID, Name, DisplayName, Description, CounterType) values (@SQLServerSQLStatisticsCatId, 'BatchRequestsPersec', 'Batch Requests/sec', 'Number of SQL batch requests received by server.', 272696576)
DECLARE @BatchRequestsPersecId INT
set @BatchRequestsPersecId = (select Min(CounterID) from PerformanceCounters.Counters where CategoryID = @SQLServerSQLStatisticsCatId and Name = 'BatchRequestsPersec')
insert into PerformanceCounters.Counters (CategoryID, Name, DisplayName, Description, CounterType) values (@SQLServerSQLStatisticsCatId, 'SQLCompilationsPersec', 'SQL Compilations/sec', 'Number of SQL compilations.', 272696576)
DECLARE @SQLCompilationsPersecId INT
set @SQLCompilationsPersecId = (select Min(CounterID) from PerformanceCounters.Counters where CategoryID = @SQLServerSQLStatisticsCatId and Name = 'SQLCompilationsPersec')
insert into PerformanceCounters.Counters (CategoryID, Name, DisplayName, Description, CounterType) values (@SQLServerSQLStatisticsCatId, 'SQLReCompilationsPersec', 'SQL Re-Compilations/sec', 'Number of SQL re-compiles.', 272696576)
DECLARE @SQLReCompilationsPersecId INT
set @SQLReCompilationsPersecId = (select Min(CounterID) from PerformanceCounters.Counters where CategoryID = @SQLServerSQLStatisticsCatId and Name = 'SQLReCompilationsPersec')
insert into PerformanceCounters.Counters (CategoryID, Name, DisplayName, Description, CounterType) values (@SQLServerAccessMethodsCatId, 'PageSplitsPersec', 'Page Splits/sec', 'Number of page splits per second that occur as a result of overflowing index pages.', 272696576)
DECLARE @PageSplitsPersecId INT
set @PageSplitsPersecId = (select Min(CounterID) from PerformanceCounters.Counters where CategoryID = @SQLServerAccessMethodsCatId and Name = 'PageSplitsPersec')
insert into PerformanceCounters.Counters (CategoryID, Name, DisplayName, Description, CounterType) values (@SQLServerAccessMethodsCatId, 'FullScansPersec', 'Full Scans/sec', 'Number of unrestricted full scans. These can either be base table or full index scans.', 272696576)
DECLARE @FullScansPersecId INT
set @FullScansPersecId = (select Min(CounterID) from PerformanceCounters.Counters where CategoryID = @SQLServerAccessMethodsCatId and Name = 'FullScansPersec')
insert into PerformanceCounters.Counters (CategoryID, Name, DisplayName, Description, CounterType) values (@SQLServerLatchesCatId, 'LatchWaitsPersec', 'Latch Waits/sec', 'Number of latch requests that could not be granted immediately and had to wait before being granted.', 272696576)
DECLARE @LatchWaitsPersecId INT
set @LatchWaitsPersecId = (select Min(CounterID) from PerformanceCounters.Counters where CategoryID = @SQLServerLatchesCatId and Name = 'LatchWaitsPersec')
insert into PerformanceCounters.Counters (CategoryID, Name, DisplayName, Description, CounterType) values (@SQLServerLatchesCatId, 'TotalLatchWaitTimems', 'Total Latch Wait Time (ms)', 'Total latch wait time (milliseconds) for latch requests that had to wait in the last second.', 272696576)
DECLARE @TotalLatchWaitTimemsId INT
set @TotalLatchWaitTimemsId = (select Min(CounterID) from PerformanceCounters.Counters where CategoryID = @SQLServerLatchesCatId and Name = 'TotalLatchWaitTimems')
insert into PerformanceCounters.Counters (CategoryID, Name, DisplayName, Description, CounterType) values (@SQLServerLocksCatId, 'LockWaitsPersec', 'Lock Waits/sec', 'Number of lock requests that could not be satisfied immediately and required the caller to wait before being granted the lock.', 272696576)
DECLARE @LockWaitsPersecId INT
set @LockWaitsPersecId = (select Min(CounterID) from PerformanceCounters.Counters where CategoryID = @SQLServerLocksCatId and Name = 'LockWaitsPersec')
insert into PerformanceCounters.Counters (CategoryID, Name, DisplayName, Description, CounterType) values (@SQLServerDatabasesCatId, 'TransactionsPersec', 'Transactions/sec', 'Number of transactions started for the database.', 272696576)
DECLARE @TransactionsPersecId INT
set @TransactionsPersecId = (select Min(CounterID) from PerformanceCounters.Counters where CategoryID = @SQLServerDatabasesCatId and Name = 'TransactionsPersec')
insert into PerformanceCounters.Counters (CategoryID, Name, DisplayName, Description, CounterType) values (@SystemCatId, 'ProcessorQueueLength', 'Processor Queue Length', 'Processor Queue Length is the number of threads in the processor queue. Unlike the disk counters, this counter shows ready threads only, not threads that are running. There is a single queue for processor time even on computers with multiple processors. Therefore, if a computer has multiple processors, you need to divide this value by the number of processors servicing the workload. A sustained processor queue of greater than two threads generally indicates processor congestion.', 65536)
DECLARE @ProcessorQueueLengthId INT
set @ProcessorQueueLengthId = (select Min(CounterID) from PerformanceCounters.Counters where CategoryID = @SystemCatId and Name = 'ProcessorQueueLength')
INSERT INTO PerformanceCounters.Instances (CategoryID, Name) Values(@SQLServerGeneralStatisticsCatId, NULL)
DECLARE @instance6Id INT
set @instance6Id = (select Min(InstanceID) from PerformanceCounters.Instances where CategoryID = @SQLServerGeneralStatisticsCatId and Name is NULL)
INSERT INTO PerformanceCounters.Instances (CategoryID, Name) Values(@SQLServerBufferManagerCatId, NULL)
DECLARE @instance7Id INT
set @instance7Id = (select Min(InstanceID) from PerformanceCounters.Instances where CategoryID = @SQLServerBufferManagerCatId and Name is NULL)
INSERT INTO PerformanceCounters.Instances (CategoryID, Name) Values(@SQLServerSQLStatisticsCatId, NULL)
DECLARE @instance8Id INT
set @instance8Id = (select Min(InstanceID) from PerformanceCounters.Instances where CategoryID = @SQLServerSQLStatisticsCatId and Name is NULL)
INSERT INTO PerformanceCounters.Instances (CategoryID, Name) Values(@SQLServerAccessMethodsCatId, NULL)
DECLARE @instance9Id INT
set @instance9Id = (select Min(InstanceID) from PerformanceCounters.Instances where CategoryID = @SQLServerAccessMethodsCatId and Name is NULL)
INSERT INTO PerformanceCounters.Instances (CategoryID, Name) Values(@SQLServerLatchesCatId, NULL)
DECLARE @instance10Id INT
set @instance10Id = (select Min(InstanceID) from PerformanceCounters.Instances where CategoryID = @SQLServerLatchesCatId and Name is NULL)
INSERT INTO PerformanceCounters.Instances (CategoryID, Name) Values(@SQLServerLocksCatId, '_Total')
DECLARE @instance11Id INT
set @instance11Id = (select Min(InstanceID) from PerformanceCounters.Instances where CategoryID = @SQLServerLocksCatId and Name = '_Total')
INSERT INTO PerformanceCounters.Instances (CategoryID, Name) Values(@SQLServerDatabasesCatId, '*')
DECLARE @instance12Id INT
set @instance12Id = (select Min(InstanceID) from PerformanceCounters.Instances where CategoryID = @SQLServerDatabasesCatId and Name = '*')
INSERT INTO PerformanceCounters.Instances (CategoryID, Name) Values(@SystemCatId, NULL)
DECLARE @instance352Id INT
set @instance352Id = (select Min(InstanceID) from PerformanceCounters.Instances where CategoryID = @SystemCatId and Name is NULL)
insert into PerformanceCounters.CounterThresholds (CounterID, InstanceID, TemplateID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) values(@UserConnectionsId, @instance6Id, @sqlTemplateID, 0, 0, 15, 0, 0, 12, 1, -1)
insert into PerformanceCounters.CounterThresholds (CounterID, InstanceID, TemplateID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) values(@BuffercachehitratioId, @instance7Id, @sqlTemplateID, 80, 90, 15, 1, 0, 12, 2, -1)
insert into PerformanceCounters.CounterThresholds (CounterID, InstanceID, TemplateID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) values(@LazywritesPersecId, @instance7Id, @sqlTemplateID, 20, 15, 15, 1, 0, 12, 1, 1)
insert into PerformanceCounters.CounterThresholds (CounterID, InstanceID, TemplateID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) values(@PagelifeexpectancyId, @instance7Id, @sqlTemplateID, 250, 400, 15, 1, 0, 12, 2, 1)
insert into PerformanceCounters.CounterThresholds (CounterID, InstanceID, TemplateID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) values(@BatchRequestsPersecId, @instance8Id, @sqlTemplateID, 1500, 1000, 15, 1, 0, 12, 1, 1)
insert into PerformanceCounters.CounterThresholds (CounterID, InstanceID, TemplateID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) values(@SQLCompilationsPersecId, @instance8Id, @sqlTemplateID, 150, 100, 15, 1, 0, 12, 1, 1)
insert into PerformanceCounters.CounterThresholds (CounterID, InstanceID, TemplateID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) values(@SQLReCompilationsPersecId, @instance8Id, @sqlTemplateID, 2, 1, 15, 1, 0, 12, 1, 1)
insert into PerformanceCounters.CounterThresholds (CounterID, InstanceID, TemplateID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) values(@PageSplitsPersecId, @instance9Id, @sqlTemplateID, 300, 200, 15, 1, 0, 12, 1, 1)
insert into PerformanceCounters.CounterThresholds (CounterID, InstanceID, TemplateID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) values(@FullScansPersecId, @instance9Id, @sqlTemplateID, 0, 0, 15, 0, 0, 12, 1, 1)
insert into PerformanceCounters.CounterThresholds (CounterID, InstanceID, TemplateID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) values(@LatchWaitsPersecId, @instance10Id, @sqlTemplateID, 0, 0, 15, 0, 0, 12, 1, 1)
insert into PerformanceCounters.CounterThresholds (CounterID, InstanceID, TemplateID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) values(@TotalLatchWaitTimemsId, @instance10Id, @sqlTemplateID, 0, 0, 15, 0, 0, 12, 1, 1)
insert into PerformanceCounters.CounterThresholds (CounterID, InstanceID, TemplateID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) values(@LockWaitsPersecId, @instance11Id, @sqlTemplateID, 2, 1, 15, 1, 0, 12, 1, 1)
insert into PerformanceCounters.CounterThresholds (CounterID, InstanceID, TemplateID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) values(@TransactionsPersecId, @instance12Id, @sqlTemplateID, 0, 0, 15, 0, 0, 12, 1, 1)
insert into PerformanceCounters.CounterThresholds (CounterID, InstanceID, TemplateID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) values(@ProcessorQueueLengthId, @instance352Id, @sqlTemplateID, 5, 2, 15, 0, 0, 12, 1, -1)
	
DECLARE @FreeMegabytesID INT = 31
DECLARE @AvgDiskQueueID INT = 27
DECLARE @DiskTransfersID INT = 30

DECLARE @LogicalDiskClassID int
SELECT TOP 1 @LogicalDiskClassID = CategoryID FROM PerformanceCounters.Categories WHERE ClassName = 'Win32_PerfRawData_PerfDisk_LogicalDisk' 

DECLARE @MemoryClassID int
SELECT TOP 1 @MemoryClassID = CategoryID FROM PerformanceCounters.Categories WHERE ClassName = 'Win32_PerfRawData_PerfOS_Memory'

DECLARE @ProcessClassID int
SELECT TOP 1 @ProcessClassID = CategoryID FROM PerformanceCounters.Categories WHERE ClassName = 'Win32_PerfRawData_PerfProc_Process'
INSERT INTO PerformanceCounters.Categories (ClassName, Singleton, DisplayName, Description) Values ('Win32_PerfRawData_W3SVC_WebService', 0, 'Web Service', 'The Web Service object includes counters specific to the World Wide Web Publishing Service.')
DECLARE @WebServiceCatId INT
SET @WebServiceCatId = (select CategoryID from PerformanceCounters.Categories where ClassName = 'Win32_PerfRawData_W3SVC_WebService')
INSERT INTO PerformanceCounters.Categories (ClassName, Singleton, DisplayName, Description) Values ('Win32_PerfRawData_PerfOS_PagingFile', 0, 'Paging File', 'The Paging File performance object consists of counters that monitor the paging file(s) on the computer.  The paging file is a reserved space on disk that backs up committed physical memory on the computer.')
DECLARE @PagingFileCatId INT
SET @PagingFileCatId = (select CategoryID from PerformanceCounters.Categories where ClassName = 'Win32_PerfRawData_PerfOS_PagingFile')
INSERT INTO PerformanceCounters.Categories (ClassName, Singleton, DisplayName, Description) Values ('Win32_PerfRawData_PerfNet_ServerWorkQueues', 0, 'Server Work Queues', 'The Server Work Queues performance object consists of counters that monitor the length of the queues and objects in the queues.')
DECLARE @ServerWorkQueuesCatId INT
SET @ServerWorkQueuesCatId = (select CategoryID from PerformanceCounters.Categories where ClassName = 'Win32_PerfRawData_PerfNet_ServerWorkQueues')
INSERT INTO PerformanceCounters.Categories (ClassName, Singleton, DisplayName, Description) Values ('Win32_PerfRawData_NETFramework_NETCLRMemory', 0, '.NET CLR Memory', 'Counters for CLR Garbage Collected heap.')
DECLARE @NETCLRMemoryCatId INT
SET @NETCLRMemoryCatId = (select CategoryID from PerformanceCounters.Categories where ClassName = 'Win32_PerfRawData_NETFramework_NETCLRMemory')
INSERT INTO PerformanceCounters.Categories (ClassName, Singleton, DisplayName, Description) Values ('Win32_PerfRawData_APPPOOLCountersProvider_APPPOOLWAS', 0, 'APP_POOL_WAS', 'This counter set exposes WAS related counters for IIS Application Pools.')
DECLARE @APPPOOLWASCatId INT
SET @APPPOOLWASCatId = (select CategoryID from PerformanceCounters.Categories where ClassName = 'Win32_PerfRawData_APPPOOLCountersProvider_APPPOOLWAS')
DECLARE @PercentProcessorTimeId INT
set @PercentProcessorTimeId = (select Min(CounterID) from PerformanceCounters.Counters where CategoryID = @ProcessClassID and Name = 'PercentProcessorTime')
DECLARE @WorkingSetId INT
set @WorkingSetId = (select Min(CounterID) from PerformanceCounters.Counters where CategoryID = @ProcessClassID and Name = 'WorkingSet')
insert into PerformanceCounters.Counters (CategoryID, Name, DisplayName, Description, CounterType) values (@WebServiceCatId, 'CurrentConnections', 'Current Connections', 'Current Connections is the current number of connections established with the Web service.', 65536)
DECLARE @CurrentConnectionsId INT
set @CurrentConnectionsId = (select Min(CounterID) from PerformanceCounters.Counters where CategoryID = @WebServiceCatId and Name = 'CurrentConnections')
insert into PerformanceCounters.Counters (CategoryID, Name, DisplayName, Description, CounterType) values (@WebServiceCatId, 'BytesTotalPersec', 'Bytes Total/sec', 'Bytes Total/sec is the sum of Bytes Sent/sec and Bytes Received/sec. This is the total rate of bytes transferred by the Web service.', 272696576)
DECLARE @BytesTotalPersecId INT
set @BytesTotalPersecId = (select Min(CounterID) from PerformanceCounters.Counters where CategoryID = @WebServiceCatId and Name = 'BytesTotalPersec')
insert into PerformanceCounters.Counters (CategoryID, Name, DisplayName, Description, CounterType) values (@WebServiceCatId, 'GetRequestsPersec', 'Get Requests/sec', 'The rate HTTP requests using the GET method are made. Get requests are the most common HTTP request.', 272696320)
DECLARE @GetRequestsPersecId INT
set @GetRequestsPersecId = (select Min(CounterID) from PerformanceCounters.Counters where CategoryID = @WebServiceCatId and Name = 'GetRequestsPersec')
insert into PerformanceCounters.Counters (CategoryID, Name, DisplayName, Description, CounterType) values (@WebServiceCatId, 'PostRequestsPersec', 'Post Requests/sec', 'The rate HTTP requests using the POST method are made.', 272696320)
DECLARE @PostRequestsPersecId INT
set @PostRequestsPersecId = (select Min(CounterID) from PerformanceCounters.Counters where CategoryID = @WebServiceCatId and Name = 'PostRequestsPersec')
insert into PerformanceCounters.Counters (CategoryID, Name, DisplayName, Description, CounterType) values (@MemoryClassID, 'PagesPersec', 'Pages/sec', 'Pages/sec is the rate at which pages are read from or written to disk to resolve hard page faults. This counter is a primary indicator of the kinds of faults that cause system-wide delays.  It is the sum of Memory\\Pages Input/sec and Memory\\Pages Output/sec.  It is counted in numbers of pages, so it can be compared to other counts of pages, such as Memory\\Page Faults/sec, without conversion. It includes pages retrieved to satisfy faults in the file system cache (usually requested by applications) non-cached mapped memory files.', 272696320)
DECLARE @PagesPersecId INT
set @PagesPersecId = (select Min(CounterID) from PerformanceCounters.Counters where CategoryID = @MemoryClassID and Name = 'PagesPersec')
insert into PerformanceCounters.Counters (CategoryID, Name, DisplayName, Description, CounterType) values (@LogicalDiskClassID, 'DiskReadsPersec', 'Disk Reads/sec', 'Disk Reads/sec is the rate of read operations on the disk.', 272696320)
DECLARE @DiskReadsPersecId INT
set @DiskReadsPersecId = (select Min(CounterID) from PerformanceCounters.Counters where CategoryID = @LogicalDiskClassID and Name = 'DiskReadsPersec')
insert into PerformanceCounters.Counters (CategoryID, Name, DisplayName, Description, CounterType) values (@LogicalDiskClassID, 'AvgDiskReadQueueLength', 'Avg. Disk Read Queue Length', 'Avg. Disk Read Queue Length is the average number of read requests that were queued for the selected disk during the sample interval.', 5571840)
DECLARE @AvgDiskReadQueueLengthId INT
set @AvgDiskReadQueueLengthId = (select Min(CounterID) from PerformanceCounters.Counters where CategoryID = @LogicalDiskClassID and Name = 'AvgDiskReadQueueLength')
insert into PerformanceCounters.Counters (CategoryID, Name, DisplayName, Description, CounterType) values (@LogicalDiskClassID, 'AvgDiskWriteQueueLength', 'Avg. Disk Write Queue Length', 'Avg. Disk Write Queue Length is the average number of write requests that were queued for the selected disk during the sample interval.', 5571840)
DECLARE @AvgDiskWriteQueueLengthId INT
set @AvgDiskWriteQueueLengthId = (select Min(CounterID) from PerformanceCounters.Counters where CategoryID = @LogicalDiskClassID and Name = 'AvgDiskWriteQueueLength')
insert into PerformanceCounters.Counters (CategoryID, Name, DisplayName, Description, CounterType) values (@LogicalDiskClassID, 'PercentIdleTime', '% Idle Time', '% Idle Time reports the percentage of time during the sample interval that the disk was idle.', 542573824)
DECLARE @PercentIdleTimeId INT
set @PercentIdleTimeId = (select Min(CounterID) from PerformanceCounters.Counters where CategoryID = @LogicalDiskClassID and Name = 'PercentIdleTime')
insert into PerformanceCounters.Counters (CategoryID, Name, DisplayName, Description, CounterType) values (@LogicalDiskClassID, 'PercentFreeSpace', '% Free Space', '% Free Space is the percentage of total usable space on the selected logical disk drive that was free.', 537003008)
DECLARE @PercentFreeSpaceId INT
set @PercentFreeSpaceId = (select Min(CounterID) from PerformanceCounters.Counters where CategoryID = @LogicalDiskClassID and Name = 'PercentFreeSpace')
insert into PerformanceCounters.Counters (CategoryID, Name, DisplayName, Description, CounterType) values (@MemoryClassID, 'CacheFaultsPersec', 'Cache Faults/sec', 'Cache Faults/sec is the rate at which faults occur when a page sought in the file system cache is not found and must be retrieved from elsewhere in memory (a soft fault) or from disk (a hard fault). The file system cache is an area of physical memory that stores recently used pages of data for applications. Cache activity is a reliable indicator of most application I/O operations. This counter shows the number of faults, without regard for the number of pages faulted in each operation.', 272696320)
DECLARE @CacheFaultsPersecId INT
set @CacheFaultsPersecId = (select Min(CounterID) from PerformanceCounters.Counters where CategoryID = @MemoryClassID and Name = 'CacheFaultsPersec')
insert into PerformanceCounters.Counters (CategoryID, Name, DisplayName, Description, CounterType) values (@PagingFileCatId, 'PercentUsage', '% Usage', 'The amount of the Page File instance in use in percent.  See also Process\\Page File Bytes.', 537003008)
DECLARE @PercentUsageId INT
set @PercentUsageId = (select Min(CounterID) from PerformanceCounters.Counters where CategoryID = @PagingFileCatId and Name = 'PercentUsage')
insert into PerformanceCounters.Counters (CategoryID, Name, DisplayName, Description, CounterType) values (@PagingFileCatId, 'PercentUsagePeak', '% Usage Peak', 'The peak usage of the Page File instance in percent.  See also Process\\Page File Bytes Peak.', 537003008)
DECLARE @PercentUsagePeakId INT
set @PercentUsagePeakId = (select Min(CounterID) from PerformanceCounters.Counters where CategoryID = @PagingFileCatId and Name = 'PercentUsagePeak')
insert into PerformanceCounters.Counters (CategoryID, Name, DisplayName, Description, CounterType) values (@ServerWorkQueuesCatId, 'TotalBytesPersec', 'Total Bytes/sec', 'Total Bytes/sec is the rate the Server is reading and writing data to and from the files for the clients on this CPU.  This value is a measure of how busy the Server is.', 272696576)
DECLARE @TotalBytesPersecId INT
set @TotalBytesPersecId = (select Min(CounterID) from PerformanceCounters.Counters where CategoryID = @ServerWorkQueuesCatId and Name = 'TotalBytesPersec')
insert into PerformanceCounters.Counters (CategoryID, Name, DisplayName, Description, CounterType) values (@NETCLRMemoryCatId, 'NumberGen0Collections', '# Gen 0 Collections', 'This counter displays the number of times the generation 0 objects (youngest most recently allocated) are garbage collected (Gen 0 GC) since the start of the application. Gen 0 GC occurs when the available memory in generation 0 is not sufficient to satisfy an allocation request. This counter is incremented at the end of a Gen 0 GC. Higher generation GCs include all lower generation GCs. This counter is explicitly incremented when a higher generation (Gen 1 or Gen 2) GC occurs. _Global_ counter value is not accurate and should be ignored. This counter displays the last observed value.', 65792)
DECLARE @NumberGen0CollectionsId INT
set @NumberGen0CollectionsId = (select Min(CounterID) from PerformanceCounters.Counters where CategoryID = @NETCLRMemoryCatId and Name = 'NumberGen0Collections')
insert into PerformanceCounters.Counters (CategoryID, Name, DisplayName, Description, CounterType) values (@NETCLRMemoryCatId, 'NumberGen1Collections', '# Gen 1 Collections', 'This counter displays the number of times the generation 1 objects are garbage collected since the start of the application. The counter is incremented at the end of a Gen 1 GC. Higher generation GCs include all lower generation GCs. This counter is explicitly incremented when a higher generation (Gen 2) GC occurs. _Global_ counter value is not accurate and should be ignored. This counter displays the last observed value.', 65792)
DECLARE @NumberGen1CollectionsId INT
set @NumberGen1CollectionsId = (select Min(CounterID) from PerformanceCounters.Counters where CategoryID = @NETCLRMemoryCatId and Name = 'NumberGen1Collections')
insert into PerformanceCounters.Counters (CategoryID, Name, DisplayName, Description, CounterType) values (@NETCLRMemoryCatId, 'NumberGen2Collections', '# Gen 2 Collections', 'This counter displays the number of times the generation 2 objects (older) are garbage collected since the start of the application. The counter is incremented at the end of a Gen 2 GC (also called full GC). _Global_ counter value is not accurate and should be ignored. This counter displays the last observed value.', 65792)
DECLARE @NumberGen2CollectionsId INT
set @NumberGen2CollectionsId = (select Min(CounterID) from PerformanceCounters.Counters where CategoryID = @NETCLRMemoryCatId and Name = 'NumberGen2Collections')
insert into PerformanceCounters.Counters (CategoryID, Name, DisplayName, Description, CounterType) values (@NETCLRMemoryCatId, 'PercentTimeinGC', '% Time in GC', '% Time in GC is the percentage of elapsed time that was spent in performing a garbage collection (GC) since the last GC cycle. This counter is usually an indicator of the work done by the Garbage Collector on behalf of the application to collect and compact memory. This counter is updated only at the end of every GC and the counter value reflects the last observed value its not an average.', 537003008)
DECLARE @PercentTimeinGCId INT
set @PercentTimeinGCId = (select Min(CounterID) from PerformanceCounters.Counters where CategoryID = @NETCLRMemoryCatId and Name = 'PercentTimeinGC')
insert into PerformanceCounters.Counters (CategoryID, Name, DisplayName, Description, CounterType) values (@MemoryClassID, 'PageReadsPersec', 'Page Reads/sec', 'Page Reads/sec is the rate at which the disk was read to resolve hard page faults. It shows the number of reads operations, without regard to the number of pages retrieved in each operation. Hard page faults occur when a process references a page in virtual memory that is not in working set or elsewhere in physical memory, and must be retrieved from disk. This counter is a primary indicator of the kinds of faults that cause system-wide delays. It includes read operations to satisfy faults in the file system cache (usually requested by applications) and in non-cached mapped memory files. Compare the value of Memory\\Pages Reads/sec to the value of Memory\\Pages Input/sec to determine the average number of pages read during each operation.', 272696320)
DECLARE @PageReadsPersecId INT
set @PageReadsPersecId = (select Min(CounterID) from PerformanceCounters.Counters where CategoryID = @MemoryClassID and Name = 'PageReadsPersec')
insert into PerformanceCounters.Counters (CategoryID, Name, DisplayName, Description, CounterType) values (@APPPOOLWASCatId, 'TotalApplicationPoolRecycles', 'Total Application Pool Recycles', 'The number of times that the application pool has been recycled since Windows Process Activation Service (WAS) started.', 65536)
DECLARE @TotalApplicationPoolRecyclesId INT
set @TotalApplicationPoolRecyclesId = (select Min(CounterID) from PerformanceCounters.Counters where CategoryID = @APPPOOLWASCatId and Name = 'TotalApplicationPoolRecycles')
DECLARE @instance2Id INT
set @instance2Id = (select Min(InstanceID) from PerformanceCounters.Instances where CategoryID = @LogicalDiskClassID and Name = '*')
DECLARE @instance3Id INT
set @instance3Id = (select Min(InstanceID) from PerformanceCounters.Instances where CategoryID = @MemoryClassID and Name is NULL)
INSERT INTO PerformanceCounters.Instances (CategoryID, Name) Values(@PagingFileCatId, '*')
DECLARE @instance21Id INT
set @instance21Id = (select Min(InstanceID) from PerformanceCounters.Instances where CategoryID = @PagingFileCatId and Name = '*')
INSERT INTO PerformanceCounters.Instances (CategoryID, Name) Values(@ServerWorkQueuesCatId, '*')
DECLARE @instance22Id INT
set @instance22Id = (select Min(InstanceID) from PerformanceCounters.Instances where CategoryID = @ServerWorkQueuesCatId and Name = '*')
INSERT INTO PerformanceCounters.Instances (CategoryID, Name) Values(@NETCLRMemoryCatId, '*')
DECLARE @instance24Id INT
set @instance24Id = (select Min(InstanceID) from PerformanceCounters.Instances where CategoryID = @NETCLRMemoryCatId and Name = '*')
INSERT INTO PerformanceCounters.Instances (CategoryID, Name) Values(@APPPOOLWASCatId, '*')
DECLARE @instance356Id INT
set @instance356Id = (select Min(InstanceID) from PerformanceCounters.Instances where CategoryID = @APPPOOLWASCatId and Name = '*')
INSERT INTO PerformanceCounters.Instances (CategoryID, Name) Values(@ProcessClassID, 'OWSTIMER')
DECLARE @instance378Id INT
INSERT INTO PerformanceCounters.Instances (CategoryID, Name) Values(@WebServiceCatId, '*')
declare @webServiceStarInstanceID int
set @webServiceStarInstanceID = (select Min(InstanceID) from PerformanceCounters.Instances where CategoryID = @WebServiceCatId and Name = '*')
insert into PerformanceCounters.CounterThresholds (CounterID, InstanceID, TemplateID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) values(@PagesPersecId, @instance3Id, @diskTemplateId, 50, 20, 15, 0, 0, 12, 1, -1)
insert into PerformanceCounters.CounterThresholds (CounterID, InstanceID, TemplateID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) values(@DiskReadsPersecId, @instance2Id, @diskTemplateId, 0, 0, 15, 0, 0, 12, 1, 1)
insert into PerformanceCounters.CounterThresholds (CounterID, InstanceID, TemplateID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) values(@AvgDiskReadQueueLengthId, @instance2Id, @diskTemplateId, 0, 0, 15, 0, 0, 12, 1, 1)
insert into PerformanceCounters.CounterThresholds (CounterID, InstanceID, TemplateID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) values(@AvgDiskWriteQueueLengthId, @instance2Id, @diskTemplateId, 0, 0, 15, 0, 0, 12, 1, 1)
insert into PerformanceCounters.CounterThresholds (CounterID, InstanceID, TemplateID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) values(@PercentIdleTimeId, @instance2Id, @diskTemplateId, 10, 20, 15, 0, 0, 12, 2, -1)
insert into PerformanceCounters.CounterThresholds (CounterID, InstanceID, TemplateID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) values(@PercentFreeSpaceId, @instance2Id, @diskTemplateId, 20, 30, 1, 0, 0, 12, 2, -1)
insert into PerformanceCounters.CounterThresholds (CounterID, InstanceID, TemplateID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) values(@CacheFaultsPersecId, @instance3Id, @diskTemplateId, 1, 5/10, 15, 0, 0, 12, 1, 1)
insert into PerformanceCounters.CounterThresholds (CounterID, InstanceID, TemplateID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) values(@PercentUsageId, @instance21Id, @diskTemplateId, 0, 0, 15, 0, 0, 12, 1, -1)
insert into PerformanceCounters.CounterThresholds (CounterID, InstanceID, TemplateID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) values(@PercentUsagePeakId, @instance21Id, @diskTemplateId, 0, 0, 15, 0, 0, 12, 1, -1)
insert into PerformanceCounters.CounterThresholds (CounterID, InstanceID, TemplateID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) values(@TotalBytesPersecId, @instance22Id, @diskTemplateId, 0, 0, 15, 0, 0, 12, 1, 5)
insert into PerformanceCounters.CounterThresholds (CounterID, InstanceID, TemplateID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) values(@NumberGen0CollectionsId, @instance24Id, @dotNetTemplateId, 0, 0, 15, 0, 0, 12, 1, 1)
insert into PerformanceCounters.CounterThresholds (CounterID, InstanceID, TemplateID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) values(@NumberGen1CollectionsId, @instance24Id, @dotNetTemplateId, 0, 0, 15, 0, 0, 12, 1, 1)
insert into PerformanceCounters.CounterThresholds (CounterID, InstanceID, TemplateID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) values(@NumberGen2CollectionsId, @instance24Id, @dotNetTemplateId, 0, 0, 15, 0, 0, 12, 1, 1)
insert into PerformanceCounters.CounterThresholds (CounterID, InstanceID, TemplateID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) values(@PercentTimeinGCId, @instance24Id, @dotNetTemplateId, 0, 0, 15, 0, 0, 12, 1, 1)
insert into PerformanceCounters.CounterThresholds (CounterID, InstanceID, TemplateID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) values(@PageReadsPersecId, @instance3Id, @diskTemplateId, 0, 0, 15, 0, 0, 12, 1, -1)
insert into PerformanceCounters.CounterThresholds (CounterID, InstanceID, TemplateID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) values(@CurrentConnectionsId, @webServiceStarInstanceID, @iisTemplateId, 0, 0, 15, 0, 0, 12, 1, -1)
insert into PerformanceCounters.CounterThresholds (CounterID, InstanceID, TemplateID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) values(@GetRequestsPersecId, @webServiceStarInstanceID, @iisTemplateId, 0, 0, 15, 0, 0, 12, 1, 5)
insert into PerformanceCounters.CounterThresholds (CounterID, InstanceID, TemplateID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) values(@PostRequestsPersecId, @webServiceStarInstanceID, @iisTemplateId, 0, 0, 15, 0, 0, 12, 1, 5)
insert into PerformanceCounters.CounterThresholds (CounterID, InstanceID, TemplateID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) values(@BytesTotalPersecId, @webServiceStarInstanceID, @iisTemplateId, 0, 0, 15, 0, 0, 12, 1, 5)
insert into PerformanceCounters.CounterThresholds (CounterID, InstanceID, TemplateID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) values(@TotalApplicationPoolRecyclesId, @instance356Id, @iisTemplateId, 0, 0, 15, 0, 0, 12, 1, 1)
INSERT INTO PerformanceCounters.CounterThresholds(TemplateID, CounterID, InstanceID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) SELECT @diskTemplateId, @FreeMegabytesID, PerformanceCounters.Instances.InstanceID, 500, 1000, 1, 1, 0, 12, 2, -1 FROM PerformanceCounters.Instances WHERE PerformanceCounters.Instances.CategoryID = @LogicalDiskClassID
INSERT INTO PerformanceCounters.CounterThresholds(TemplateID, CounterID, InstanceID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) SELECT @diskTemplateId, @AvgDiskQueueID, PerformanceCounters.Instances.InstanceID, 4, 2, 15, 1, 0, 12, 1, -1 FROM PerformanceCounters.Instances WHERE PerformanceCounters.Instances.CategoryID = @LogicalDiskClassID
INSERT INTO PerformanceCounters.CounterThresholds(TemplateID, CounterID, InstanceID, CriticalThreshold, WarningThreshold, ThresholdPeriod, SendAlert, SendWarningAlert, IgnorePeriod, ThresholdTypeID, Tolerance) SELECT @diskTemplateId, @DiskTransfersID, PerformanceCounters.Instances.InstanceID, 400, 200, 15, 1, 0, 12, 1, 5 FROM PerformanceCounters.Instances WHERE PerformanceCounters.Instances.CategoryID = @LogicalDiskClassID

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

