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'Delete duplicate rows from [dbo].[ScopeAssignments]'
DELETE sc
FROM [dbo].[ScopeAssignments] sc
		LEFT OUTER JOIN (
		   SELECT MIN(ID) as RowId, ScopeID, SPObjectID, LoadingInfoID 
		   FROM [dbo].[ScopeAssignments] 
		   GROUP BY LoadingInfoID, ScopeID, SPObjectID
		) as KeepRows 
		ON sc.Id = KeepRows.RowId
WHERE KeepRows.RowId IS NULL
GO
PRINT N'Delete duplicate rows from [dbo].[SharePointObjectMemberships]'
DELETE sm
FROM [dbo].[SharePointObjectMemberships] sm
		LEFT OUTER JOIN (
		   SELECT MIN(ID) as RowId, ParentID, ChildID, LoadingInfoID 
		   FROM [dbo].[SharePointObjectMemberships] 
		   GROUP BY LoadingInfoID, ParentID, ChildID
		) as KeepRows 
		ON sm.Id = KeepRows.RowId
WHERE KeepRows.RowId IS NULL
GO
PRINT N'Delete duplicate rows from [dbo].[PrincipalMemberships]'
DELETE pm
FROM [dbo].[PrincipalMemberships] pm
		LEFT OUTER JOIN (
		   SELECT MIN(Id) as RowId, GroupID, UserID, LoadingInfoID 
		   FROM [dbo].[PrincipalMemberships] 
		   GROUP BY LoadingInfoID, GroupID, UserID
		) as KeepRows 
		ON pm.Id = KeepRows.RowId
WHERE KeepRows.RowId IS NULL
GO
PRINT N'Delete duplicate rows from [dbo].[LoadedPrincipals]'
DELETE sc
FROM [dbo].[LoadedPrincipals] sc
                               LEFT OUTER JOIN (
                                  SELECT MIN(ID) as RowId, LoadingInfoID, PrincipalID
                                  FROM [dbo].[LoadedPrincipals] 
                                  GROUP BY LoadingInfoID, PrincipalID
                               ) as KeepRows 
                               ON sc.Id = KeepRows.RowId
WHERE KeepRows.RowId IS NULL
GO
PRINT N'Delete duplicate rows from [dbo].[LoadedSiteCollections]'
DELETE sc
FROM [dbo].[LoadedSiteCollections] sc
                               LEFT OUTER JOIN (
                                  SELECT MIN(ID) as RowId, LoadingInfoID, WebApplicationID, SiteCollectionID
                                  FROM [dbo].[LoadedSiteCollections]
                                  GROUP BY LoadingInfoID, WebApplicationID, SiteCollectionID
                               ) as KeepRows 
                               ON sc.Id = KeepRows.RowId
WHERE KeepRows.RowId IS NULL
GO
PRINT N'Delete duplicate rows from [dbo].[RoleAssignments]'
DELETE ra
FROM [dbo].[RoleAssignments] ra
		LEFT OUTER JOIN (
		   SELECT MIN(Id) as RowId, LoadingInfoID, ScopeID, PrincipalID, RoleID 
		   FROM [dbo].RoleAssignments 
		   GROUP BY LoadingInfoID, ScopeID, PrincipalID, RoleID
		) as KeepRows 
		ON ra.Id = KeepRows.RowId
WHERE KeepRows.RowId IS NULL
GO
PRINT N'Dropping constraints from [dbo].[LoadedPrincipals]'
GO
ALTER TABLE [dbo].[LoadedPrincipals] DROP CONSTRAINT [PK_LoadedPrincipals]
GO
IF @@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].[LoadedSiteCollections]'
GO
ALTER TABLE [dbo].[LoadedSiteCollections] DROP CONSTRAINT [PK_LoadedSiteCollections]
GO
IF @@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].[LoadedWebApplications]'
GO
ALTER TABLE [dbo].[LoadedWebApplications] DROP CONSTRAINT [PK_LoadedWebApplications]
GO
IF @@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].[PrincipalMemberships]'
GO
ALTER TABLE [dbo].[PrincipalMemberships] DROP CONSTRAINT [PK_PrincipalMemberships]
GO
IF @@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].[RoleAssignments]'
GO
ALTER TABLE [dbo].[RoleAssignments] DROP CONSTRAINT [PK_RoleAssignments_1]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping constraints from [dbo].[ScopeAssignments]'
GO
ALTER TABLE [dbo].[ScopeAssignments] DROP CONSTRAINT [PK_ScopeAssignments]
GO
IF @@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].[SharePointObjectMemberships]'
GO
ALTER TABLE [dbo].[SharePointObjectMemberships] DROP CONSTRAINT [PK_SharePointObjectMemberships]
GO
IF @@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].[Principals]'
GO
ALTER TABLE [dbo].[Principals] DROP CONSTRAINT [DF_Principals_AccountEnabled]
GO
IF @@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 index [IX_LoadedPrincipals] from [dbo].[LoadedPrincipals]'
GO
DROP INDEX [IX_LoadedPrincipals] ON [dbo].[LoadedPrincipals]
GO
IF @@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 index [IX_LoadedSiteCollections] from [dbo].[LoadedSiteCollections]'
GO
DROP INDEX [IX_LoadedSiteCollections] ON [dbo].[LoadedSiteCollections]
GO
IF @@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 index [PrincipalMemberships_LoadingInfoID_GroupID] from [dbo].[PrincipalMemberships]'
GO
DROP INDEX [PrincipalMemberships_LoadingInfoID_GroupID] ON [dbo].[PrincipalMemberships]
GO
IF @@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 index [RoleAssignments_LoadingInfoID_ScopeID] from [dbo].[RoleAssignments]'
GO
DROP INDEX [RoleAssignments_LoadingInfoID_ScopeID] ON [dbo].[RoleAssignments]
GO
IF @@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 index [ScopeAssignments_LoadingInfoID_SPObjectID] from [dbo].[ScopeAssignments]'
GO
DROP INDEX [ScopeAssignments_LoadingInfoID_SPObjectID] ON [dbo].[ScopeAssignments]
GO
IF @@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 index [SharePointObjectMemberships_LoadingInfoID_ParentID] from [dbo].[SharePointObjectMemberships]'
GO
DROP INDEX [SharePointObjectMemberships_LoadingInfoID_ParentID] ON [dbo].[SharePointObjectMemberships]
GO
IF @@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 index [IX_SharePointObjects] from [dbo].[SharePointObjects]'
GO
DROP INDEX [IX_SharePointObjects] ON [dbo].[SharePointObjects]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating types'
GO
CREATE TYPE [dbo].[PrincipalMembershipTableType] AS TABLE
(
[GroupID] [int] NOT NULL,
[UserID] [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
CREATE TYPE [dbo].[RoleAssignmentTableType] AS TABLE
(
[ScopeID] [int] NOT NULL,
[PrincipalID] [int] NOT NULL,
[RoleID] [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
CREATE TYPE [dbo].[SharePointGroupType] AS TABLE
(
[Name] [nvarchar] (max) NOT NULL,
[DisplayName] [nvarchar] (max) NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
CREATE TYPE [dbo].[SharePointObjectType] AS TABLE
(
[Name] [nvarchar] (255) NOT NULL,
[SPObjectTypeID] [int] NOT NULL,
[SPGuid] [uniqueidentifier] NOT NULL,
[ParentID] [int] NOT NULL,
[ParentScopeID] [int] NOT NULL,
[IsRootWeb] [bit] NOT NULL,
[HasUniqueRoleAssignments] [bit] NOT NULL,
[Url] [nvarchar] (2048) NOT NULL,
[ContentTypeID] [int] NULL,
[LastUpdated] [datetime] NULL,
[CreatedOn] [datetime] NULL,
[CreatedBy] [int] NULL,
[ModifiedBy] [int] NULL,
[SharePointContentTypeID] [int] NULL
)
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].[UserType] AS TABLE
(
[Name] [nvarchar] (max) NOT NULL,
[DisplayName] [nvarchar] (max) NULL,
[Email] [nvarchar] (max) NULL,
[PrincipalTypeID] [int] NOT NULL,
[AccountEnabled] [bit] NOT NULL,
[ExternalUser] [bit] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[SharePointObjectMemberships]'
GO
ALTER TABLE [dbo].[SharePointObjectMemberships] DROP
COLUMN [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 primary key [PK_SharePointObjectMemberships] on [dbo].[SharePointObjectMemberships]'
GO
ALTER TABLE [dbo].[SharePointObjectMemberships] ADD CONSTRAINT [PK_SharePointObjectMemberships] PRIMARY KEY CLUSTERED  ([LoadingInfoID], [ParentID], [ChildID])
GO
IF @@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].[LoadedSiteCollections]'
GO
ALTER TABLE [dbo].[LoadedSiteCollections] DROP
COLUMN [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 primary key [PK_LoadedSiteCollections] on [dbo].[LoadedSiteCollections]'
GO
ALTER TABLE [dbo].[LoadedSiteCollections] ADD CONSTRAINT [PK_LoadedSiteCollections] PRIMARY KEY CLUSTERED  ([LoadingInfoID], [WebApplicationID], [SiteCollectionID])
GO
IF @@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].[LoadedWebApplications]'
GO
ALTER TABLE [dbo].[LoadedWebApplications] DROP
COLUMN [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 primary key [PK_LoadedWebApplications] on [dbo].[LoadedWebApplications]'
GO
ALTER TABLE [dbo].[LoadedWebApplications] ADD CONSTRAINT [PK_LoadedWebApplications] PRIMARY KEY CLUSTERED  ([LoadingInfoID], [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].[ScopeAssignments]'
GO
ALTER TABLE [dbo].[ScopeAssignments] DROP
COLUMN [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 primary key [PK_ScopeAssignments] on [dbo].[ScopeAssignments]'
GO
ALTER TABLE [dbo].[ScopeAssignments] ADD CONSTRAINT [PK_ScopeAssignments] PRIMARY KEY CLUSTERED  ([LoadingInfoID], [SPObjectID], [ScopeID])
GO
IF @@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].[PrincipalMemberships]'
GO
ALTER TABLE [dbo].[PrincipalMemberships] DROP
COLUMN [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 primary key [PK_PrincipalMemberships] on [dbo].[PrincipalMemberships]'
GO
ALTER TABLE [dbo].[PrincipalMemberships] ADD CONSTRAINT [PK_PrincipalMemberships] PRIMARY KEY CLUSTERED  ([LoadingInfoID], [GroupID], [UserID])
GO
IF @@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].[RoleAssignments]'
GO
ALTER TABLE [dbo].[RoleAssignments] DROP
COLUMN [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 primary key [PK_RoleAssignments] on [dbo].[RoleAssignments]'
GO
ALTER TABLE [dbo].[RoleAssignments] ADD CONSTRAINT [PK_RoleAssignments] PRIMARY KEY CLUSTERED  ([LoadingInfoID], [ScopeID], [PrincipalID], [RoleID])
GO
IF @@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].[GetChildrenForParent]'
GO
CREATE PROCEDURE [dbo].[GetChildrenForParent]
	@LoadingInfoID INT,
	@FarmID INT,
	@ParentID INT
AS
BEGIN 
	SET NOCOUNT ON
	DECLARE @SharePointObjectNodes TABLE
	( 
		[ID] [int],   
		[Url] [nvarchar](255),
		[Name] [nvarchar](255),
		[SPGuid] [uniqueidentifier],
		[HasChildren] [bit],
		[SharePointObjectType] [int],
		[HasUniquePermissions] [bit])

		INSERT INTO @SharePointObjectNodes([ID], [Url], [Name], [SPGuid], [HasChildren], [SharePointObjectType], [HasUniquePermissions])
			SELECT	[SPObject].[ID],
					[SPObject].[Url], 
					[SPObject].[Name],
					[SPObject].[SPGuid],
					(CASE WHEN EXISTS 
						(
						SELECT [dbo].[SharePointObjectMemberships].[ParentID] FROM [dbo].[SharePointObjectMemberships] WHERE [dbo].[SharePointObjectMemberships].[LoadingInfoID] = @LoadingInfoID
						AND [dbo].[SharePointObjectMemberships].[ParentID] = [SPObject].[ID]
						) THEN 1 ELSE 0 END),
					[SPObject].[SPObjectTypeID],
					[SPObject].[HasUniqueRoleAssignments]
			FROM [dbo].[SharePointObjects] AS [SPObject]
			JOIN [dbo].[SharePointObjectMemberships] AS [Membership] ON [Membership].[ParentID] = @ParentID AND [Membership].[LoadingInfoID] = @LoadingInfoID AND [Membership].[ChildID] = [SPObject].[ID]
		SELECT * FROM @SharePointObjectNodes ORDER BY [SharePointObjectType], [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'Creating [dbo].[GetSiteCollectionsForWebApplication]'
GO
CREATE PROCEDURE [dbo].[GetSiteCollectionsForWebApplication]
	@LoadingInfoID INT,
	@FarmID INT,
	@WebApplicationID INT
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @SharePointObjectNodes TABLE
	( 
		[ID] [int],   
		[Url] [nvarchar](255),
		[Name] [nvarchar](255),
		[SPGuid] [uniqueidentifier])

INSERT INTO @SharePointObjectNodes([ID], [Url], [Name], [SPGuid])
		SELECT	[SiteCollection].[ID],
				[SiteCollection].[Url], 
				[SiteCollection].[Name],
				[SiteCollection].[SPGuid]
		FROM [dbo].[LoadedSiteCollections]
		JOIN [dbo].[SharePointObjects] AS [SiteCollection] ON [dbo].[LoadedSiteCollections].[SiteCollectionID] = [SiteCollection].[ID] 
		WHERE [dbo].[LoadedSiteCollections].[LoadingInfoID] = @LoadingInfoID AND [dbo].[LoadedSiteCollections].[WebApplicationID] = @WebApplicationID
		SELECT * FROM @SharePointObjectNodes ORDER BY [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'Creating [dbo].[GetSPWebApplicationsForLoadingInfo]'
GO
CREATE PROCEDURE [dbo].[GetSPWebApplicationsForLoadingInfo]
	@LoadingInfoID INT,
	@FarmID INT
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @SharePointObjectNodes TABLE
	( 
		[ID] [int],   
		[Url] [nvarchar](255),
		[Name] [nvarchar](255),
		[SPGuid] [uniqueidentifier],
		[HasChildren] [bit])

	INSERT INTO @SharePointObjectNodes([ID], [Url], [Name], [SPGuid], [HasChildren])
		SELECT	[WebApplication].[ID],
				[WebApplication].[Url], 
				[WebApplication].[Name],	
				[WebApplication].[SPGuid],			
				(CASE WHEN EXISTS (SELECT [LoadedSiteCollection].[SiteCollectionID]
				FROM [dbo].[LoadedSiteCollections] AS [LoadedSiteCollection] WHERE [LoadedSiteCollection].[LoadingInfoID] = @LoadingInfoID AND [LoadedSiteCollection].[WebApplicationID] = [WebApplication].[ID])						
				THEN 1 ELSE 0 END)
		FROM [dbo].[WebApplications] AS [WebApplication]
		JOIN [dbo].[LoadedWebApplications] AS [LoadedWebApplication] ON [LoadedWebApplication].[WebApplicationID] = [WebApplication].[ID] 
		WHERE [WebApplication].[FarmID] = @FarmID AND [LoadedWebApplication].[LoadingInfoID] = @LoadingInfoID

	SELECT * FROM @SharePointObjectNodes ORDER BY [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'Creating [dbo].[GetSPWebApplicationsForLoadingInfoWithoutChildren]'
GO
CREATE PROCEDURE [dbo].[GetSPWebApplicationsForLoadingInfoWithoutChildren]
	@LoadingInfoID INT,
	@FarmID INT
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @SharePointObjectNodes TABLE
	( 
		[ID] [int],   
		[Url] [nvarchar](255),
		[Name] [nvarchar](255),
		[SPGuid] [uniqueidentifier])

	INSERT INTO @SharePointObjectNodes([ID], [Url], [Name], [SPGuid])
		SELECT	[WebApplication].[ID],
				[WebApplication].[Url], 
				[WebApplication].[Name],	
				[WebApplication].[SPGuid]							
		FROM [dbo].[WebApplications] AS [WebApplication]
		JOIN [dbo].[LoadedWebApplications] AS [LoadedWebApplication] ON [LoadedWebApplication].[WebApplicationID] = [WebApplication].[ID] 
		WHERE [WebApplication].[FarmID] = @FarmID AND [LoadedWebApplication].[LoadingInfoID] = @LoadingInfoID

	SELECT * FROM @SharePointObjectNodes ORDER BY [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'Creating [dbo].[GetSubsitesOfSite]'
GO
CREATE PROCEDURE [dbo].[GetSubsitesOfSite]
	@LoadingInfoID INT,
	@FarmID INT,
	@ParentID INT
AS
BEGIN 
	SET NOCOUNT ON
	DECLARE @SharePointObjectNodes TABLE
	( 
		[ID] [int],   
		[Url] [nvarchar](255),
		[Name] [nvarchar](255),
		[SPGuid] [uniqueidentifier])

		INSERT INTO @SharePointObjectNodes([ID], [Url], [Name], [SPGuid])
			SELECT	[SPObject].[ID],
					[SPObject].[Url], 
					[SPObject].[Name],
					[SPObject].[SPGuid]
			FROM [dbo].[SharePointObjects] AS [SPObject] 
			JOIN [dbo].[SharePointObjectMemberships] AS [Membership] ON [Membership].[ParentID] = @ParentID AND [Membership].[LoadingInfoID] = @LoadingInfoID AND [Membership].[ChildID] = [SPObject].[ID] AND [SPObject].[SPObjectTypeID] = 2
		SELECT * FROM @SharePointObjectNodes ORDER BY [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].[Principals]'
GO
ALTER TABLE [dbo].[Principals] DROP
COLUMN [AccountEnabled]
GO
IF @@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].[LoadedPrincipals]'
GO
ALTER TABLE [dbo].[LoadedPrincipals] DROP
COLUMN [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 primary key [PK_LoadedPrincipals] on [dbo].[LoadedPrincipals]'
GO
ALTER TABLE [dbo].[LoadedPrincipals] ADD CONSTRAINT [PK_LoadedPrincipals] 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'Creating [dbo].[InsertOrUpdateSharePointGroups]'
GO
CREATE PROCEDURE [dbo].[InsertOrUpdateSharePointGroups]	
	@LoadingInfoID int,
	@FarmID int,
	@TenantID 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.TenantID = @TenantID 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]
           ,[TenantID]
           ,[DisplayName]
           ,[Email]           
           ,[ExternalUser]
           ,[FarmID])
	SELECT p.[Name]
		  ,1
		  ,@SiteID
		  ,@TenantID
		  ,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.TenantID = @TenantID 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'Creating [dbo].[InsertOrUpdateSharePointObjects]'
GO
CREATE PROCEDURE [dbo].[InsertOrUpdateSharePointObjects]
	@LoadingInfoID int,
	@FarmID int,
	@WebAppID int,
	@SiteID int,
	@SPSiteGuid uniqueidentifier,
	@CreateScopeAssignments bit,
	@TableVariable SharePointObjectType READONLY
AS
BEGIN
	SET NOCOUNT ON	
	
	CREATE TABLE #newSpObjects (
		[ID] [int] NULL,
		[Name] nvarchar(255) NOT NULL,
		[SPObjectTypeID] [int] NOT NULL,
		[SPGuid] [uniqueidentifier] NOT NULL,
		[ParentID] [int] NOT NULL,
		[ParentScopeID] [int] NOT NULL,
		[IsRootWeb] [bit] NOT NULL,
		[HasUniqueRoleAssignments] [bit] NOT NULL,
		[Url] nvarchar(2048) NOT NULL,
		[ContentTypeID] [int] NULL,
		[LastUpdated] [datetime] NULL,
		[CreatedOn] [datetime] NULL,
		[CreatedBy] [int] NULL,
		[ModifiedBy] [int] NULL,
		[SharePointContentTypeID] [int] NULL
	) 

	INSERT INTO #newSpObjects ([ID], [Name], [SPObjectTypeID], [SPGuid], [ParentID], [ParentScopeID], [IsRootWeb], [HasUniqueRoleAssignments], [Url], [ContentTypeID], [LastUpdated], [CreatedOn], [CreatedBy], [ModifiedBy], [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.[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])
	 SELECT @LoadingInfoID, n.ParentID, n.ID
	 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

	 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'Creating [dbo].[InsertOrUpdateTenantUsers]'
GO
CREATE PROCEDURE [dbo].[InsertOrUpdateTenantUsers]	
	@LoadingInfoID int,
	@FarmID int,
	@TenantID 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.TenantID = @TenantID AND Principals.FarmID = @FarmID
	  LEFT OUTER JOIN LoadedPrincipals lp ON Principals.PrincipalID = lp.PrincipalID AND lp.LoadingInfoID = @LoadingInfoID


	INSERT INTO Principals ([Name]
           ,[PrincipalTypeID]
           ,[SiteID]
           ,[TenantID]
           ,[DisplayName]
           ,[Email]           
           ,[ExternalUser]
           ,[FarmID])
	SELECT p.[Name]
		  ,p.[PrincipalTypeID]
		  ,NULL
		  ,@TenantID
		  ,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.TenantID = @TenantID AND Principals.FarmID = @FarmID
	WHERE np.[PrincipalID] IS NULL

	INSERT INTO LoadedPrincipals
		([LoadingInfoID]
		,[PrincipalID]
		,[AccountEnabled])	
	SELECT @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'Creating [dbo].[InsertPrincipalMemberships]'
GO
CREATE PROCEDURE [dbo].[InsertPrincipalMemberships]	
	@LoadingInfoID int,
	@TableVariable PrincipalMembershipTableType READONLY
AS
BEGIN
	SET NOCOUNT ON	
	INSERT INTO [dbo].[PrincipalMemberships]
           ([LoadingInfoID]
           ,[GroupID]
           ,[UserID])
	 SELECT @LoadingInfoID, newPm.GroupID, newPm.UserID
	 FROM @TableVariable newPm
	 LEFT OUTER JOIN [dbo].[PrincipalMemberships] pm2 ON pm2.LoadingInfoID = @LoadingInfoID AND pm2.GroupID = newPm.GroupID AND pm2.UserID = newPm.UserID
	 WHERE pm2.LoadingInfoID IS 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'Creating [dbo].[InsertRoleAssignments]'
GO
CREATE PROCEDURE [dbo].[InsertRoleAssignments]	
	@LoadingInfoID int,
	@SiteID int,
	@TableVariable RoleAssignmentTableType READONLY
AS
BEGIN
	SET NOCOUNT ON	
	INSERT INTO [dbo].[RoleAssignments]
           ([LoadingInfoID]
           ,[ScopeID]
           ,[PrincipalID]
		   ,[RoleID]
		   ,[SiteID])
	 SELECT @LoadingInfoID, [ScopeID], [PrincipalID], [RoleID], @SiteID
	 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'Altering [dbo].[InsertOrUpdatePrincipal]'
GO
ALTER PROCEDURE [dbo].[InsertOrUpdatePrincipal]
	@PrincipalID int = NULL OUTPUT ,
	@Name nvarchar(MAX),
	@PrincipalTypeID int,
	@SiteID int = NULL,
	@TenantID 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
		INSERT INTO [dbo].[Principals]
           ([Name]
           ,[PrincipalTypeID]
           ,[SiteID]
           ,[TenantID]
           ,[DisplayName]
           ,[Email]           
           ,[ExternalUser]
           ,[FarmID])
		VALUES(@Name
			,@PrincipalTypeID
			,@SiteID
			,@TenantID
			,@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) VALUES (@PrincipalID, @LoadingInfoID, @AccountEnabled)
END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating index [IX_SiteID_SPGuid] on [dbo].[SharePointObjects]'
GO
CREATE NONCLUSTERED INDEX [IX_SiteID_SPGuid] ON [dbo].[SharePointObjects] ([SiteID], [SPGuid]) INCLUDE ([HasUniqueRoleAssignments])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
Declare @QueriesID INT
Select @QueriesID=ID FROM Rules Where Name LIKE 'Queries'
Declare @SampleQueriesID INT
Select @SampleQueriesID=ID FROM Rules Where Name LIKE 'Samples' And ParentID = @QueriesID
INSERT INTO Rules VALUES(100, 'Lists Above Default List View Threshold', 0, 1, 
'<RuleConfiguration><RuleSchedule xmlns:p2="http://www.w3.org/2001/XMLSchema-instance" p2:type="OneTimeSchedule"><StartTime>2015-10-27T00:00:00</StartTime></RuleSchedule><EmailRecipients /><QueryDescription>Finds all lists with more than 5000 items. You cannot have more than 5,000 items (by default) returned in your list view.</QueryDescription><QueryConfiguration><DataSource>Lists</DataSource><FieldNames><string>Title</string><string>Url</string><string>ItemCount</string></FieldNames><FieldSorting><string>N</string><string>N</string><string>DESC</string></FieldSorting></QueryConfiguration><RuleDataSource>None</RuleDataSource><GroupCondition><Enabled>True</Enabled><LogicalOperators>0</LogicalOperators><Conditions><GroupCondition><Enabled>True</Enabled><LogicalOperators>0</LogicalOperators><Conditions><SimpleCondition><Enabled>True</Enabled><FieldInfo><FieldName>ItemCount</FieldName><FieldType>System.Int32</FieldType><Alias /></FieldInfo><Parameter>5000</Parameter><ConditionApplies>0</ConditionApplies><ConditionTypeID>4</ConditionTypeID></SimpleCondition></Conditions></GroupCondition></Conditions></GroupCondition></RuleConfiguration>'
, '2000-01-01 00:00:00.000', @SampleQueriesID, 0, 0)
UPDATE Options SET OptionValue = '5.4.0' 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
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

