SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Removing schema binding from [dbo].[v_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'Dropping constraints from [dbo].[SharePointObjects]'
GO
ALTER TABLE [dbo].[SharePointObjects] DROP CONSTRAINT [DF_SharePointObjects_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
PRINT N'Dropping index [IX_SiteID_SPGuid] from [dbo].[SharePointObjects]'
GO
DROP INDEX [IX_SiteID_SPGuid] 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'Altering [dbo].[SharePointObjects]'
GO
ALTER TABLE [dbo].[SharePointObjects] DROP
COLUMN [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
PRINT N'Creating index [IX_SiteID_SPGuid] on [dbo].[SharePointObjects]'
GO
CREATE NONCLUSTERED INDEX [IX_SiteID_SPGuid] ON [dbo].[SharePointObjects] ([SiteID], [SPGuid])
GO
IF @@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].[InsertOrUpdateSharePointObject]'
GO
ALTER PROCEDURE [dbo].[InsertOrUpdateSharePointObject]
	@ID INT=NULL,
	@Name nvarchar(255),
	@ObjectTypeID INT,
	@SPSiteGuid uniqueidentifier,
	@SPGuid uniqueidentifier,
	@IsRootWeb BIT,
	@webAppID INT,
	@HasUniqueRoleAssignments BIT,
	@Url nvarchar(2048),
	@SiteID INT,
	@BaseTemplateTypeID INT=NULL,
	@LastUpdated datetime = NULL,
	@CreatedOn datetime = NULL,
	@CreatedBy INT=NULL,
	@ModifiedBy INT=NULL,
	@SiteSize BIGINT=NULL,
	@MaxQuota BIGINT=NULL,
	@FarmID INT,
	@LoadingInfoID INT,
	@ParentID INT=NULL,
	@CreateScopeAssignments bit,
	@ParentPermissionScopeID INT=NULL,
	@ForceCreateNewPermissionScope bit,
	@ExternalSharingCapability INT = NULL,
	@spObjectID INT OUTPUT,
	@actualPermissionScopeID INT=NULL OUTPUT
	AS
	BEGIN
		SET NOCOUNT ON
	  	
		IF @ID IS NULL
		BEGIN
			INSERT INTO [dbo].[SharePointObjects]  ([Name]
							,[SPObjectTypeID]
							,[SPSiteGuid]
							,[SPGuid]
							,[IsRootWeb]
							,[WebAppID]
							,[Url]
							,[SiteID]
							,[ContentTypeID]
							,[LastUpdated]
							,[CreatedOn]
							,[CreatedBy]
							,[ModifiedBy]
							,[SiteSize]
							,[MaxQuota]
							,[FarmID])
				VALUES(@Name,
				@ObjectTypeID
				,@SPSiteGuid
				,@SPGuid
				,@IsRootWeb
				,@webAppID
				,@Url
				,@SiteID
				,@BaseTemplateTypeID
				,@LastUpdated
				,@CreatedOn
				,@CreatedBy
				,@ModifiedBy
				,@SiteSize
				,@MaxQuota
				,@FarmID)
				SET @spObjectID = SCOPE_IDENTITY()
				IF @ObjectTypeID = 1
					UPDATE [dbo].[SharePointObjects] SET SiteID=@spObjectID WHERE ID = @spObjectID
		END
		ELSE
		BEGIN
			IF @ObjectTypeID = 1
				SET @SiteID = @ID
			
			UPDATE [dbo].[SharePointObjects]
			SET [Name] = @Name
			,[SPObjectTypeID] =@ObjectTypeID
			,[SPSiteGuid] = @SPSiteGuid
			,[SPGuid] = @SPGuid
			,[IsRootWeb] = @IsRootWeb
			,[WebAppID] = @webAppID
			,[Url] = @Url
			,[SiteID] =@SiteID
			,[ContentTypeID] = @BaseTemplateTypeID
			,[LastUpdated] = @LastUpdated
			,[CreatedOn] = @CreatedOn
			,[CreatedBy] = @CreatedBy
			,[ModifiedBy] =@ModifiedBy
			,[SiteSize] = @SiteSize
			,[MaxQuota] =@MaxQuota
			,[FarmID] = @FarmID
				WHERE ID=@ID
				SET @spObjectID = @ID
		END
			
		IF @ObjectTypeID = 1
			INSERT INTO [dbo].[LoadedSiteCollections] (LoadingInfoID, WebApplicationID, SiteCollectionID) VALUES(@LoadingInfoID, @WebAppID, @spObjectID)
		if @ParentID IS NOT NULL
			INSERT INTO [dbo].[SharePointObjectMemberships] (LoadingInfoID, ParentID, ChildID, ChildObjectTypeID) VALUES(@LoadingInfoID, @ParentID, @spObjectID, @ObjectTypeID)
		
		IF @CreateScopeAssignments = 1
		BEGIN
			IF @ForceCreateNewPermissionScope = 1 OR @ParentPermissionScopeID IS NULL
			BEGIN
				INSERT INTO [dbo].[PermissionScopes] DEFAULT VALUES
				SET @actualPermissionScopeID = SCOPE_IDENTITY()
			END
		ELSE
			SET @actualPermissionScopeID =  @ParentPermissionScopeID
			INSERT INTO [dbo].[ScopeAssignments] (LoadingInfoID, ScopeID, SPObjectID, HasUniqueRoleAssignments) VALUES (@LoadingInfoID, @actualPermissionScopeID, @spObjectID, @HasUniqueRoleAssignments)
		END

		if @ExternalSharingCapability IS NOT NULL
			INSERT INTO [dbo].[SiteCollectionProperties] ([LoadingInfoID], [SiteCollectionID], [ExternalSharingCapability]) VALUES (@LoadingInfoID, @spObjectID, @ExternalSharingCapability)
				
	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

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

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

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

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

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

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

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

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

		 OPEN @RowCursor

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

		SELECT n.SPGuid, n.ID, s.ScopeID
		FROM #newSpObjects n
		INNER JOIN [dbo].[ScopeAssignments] s ON s.LoadingInfoID = @LoadingInfoID AND s.SPObjectID = n.ID
	 END
	 ELSE
	 BEGIN
	 	SELECT n.SPGuid, n.ID, -1 as ScopeID
		FROM #newSpObjects n
	 END
END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[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]
		  ,[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'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],
			[scope].[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]
	JOIN [dbo].[ScopeAssignments] as [scope] ON [scope].SPObjectID = [SPObject].ID AND [scope].LoadingInfoID = @Loc_LoadingInfoID
	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].[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, scope.[HasUniqueRoleAssignments], o.[IsRootWeb], scope.ScopeID, o.[SiteID], o.[LastUpdated], o.[WebAppID], o.Url, o.[SPGuid], o.[CreatedOn], o.[ContentTypeID]
				    FROM dbo.v_SharePointObjects AS o  
					JOIN dbo.ScopeAssignments as scope ON scope.SPObjectID = o.ID AND scope.LoadingInfoID = @Loc_LoadingInfoID
					WHERE  o.ID = @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, scope.[HasUniqueRoleAssignments], o.[IsRootWeb], scope.ScopeID, o.[SiteID], o.[LastUpdated], o.[WebAppID], o.Url, o.[SPGuid], o.[CreatedOn], o.[ContentTypeID]
					    FROM SharePointObjectMemberships as m 
					    JOIN @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],
            sa.[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, scope.[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'Altering [dbo].[GetUnusedContent]'
GO
ALTER PROCEDURE [dbo].[GetUnusedContent]
	@RootObjectIDs FilterIDsType READONLY,
	@FarmID int,
	@LoadingInfoID int,
	@ContentTypeIDs FilterIDsType READONLY,
	@UnmodifiedSince datetime,
	@MaxDepth int = 32
AS
	SET NOCOUNT ON

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

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

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

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

	CREATE TABLE #OrganisationDescendantUnusedContent ( 
                                        [Name][nvarchar](max),
                                        [ID] INT, 
                                        [SharePointObjectTypeID] INT,
                                        [ParentID] INT NULL,
                                        [PathLevel] INT,
                                        [HasUniqueRoleAssignments] bit,
                                        [IsRootWeb] bit,  
										[PermissionScopeID] INT,
                                        [SiteID] INT,
                                        [LastUpdated] DateTime,
                                        [WebApplicationID] INT,
                                        [Url][nvarchar](max)  ,
                                        [SPGuid] [uniqueidentifier],
										[CreatedOn] DateTime,
										[ContentTypeId] INT
                                        )

    DECLARE @Depth          INT
    DECLARE @NumberOfNewRows   INT

    SET @Depth = 0
    SET @NumberOfNewRows = 1
    WHILE @Depth <= @MaxDepth and @NumberOfNewRows > 0 
	    BEGIN
		    IF @Depth = 0
			    BEGIN
				    INSERT INTO #OrganisationDescendantUnusedContent([Name], [ID], [SharePointObjectTypeID], [ParentID], [PathLevel], [HasUniqueRoleAssignments], [IsRootWeb], [PermissionScopeID], [SiteID], [LastUpdated], [WebApplicationID], [Url], [SPGuid], [CreatedOn], [ContentTypeId])
				    SELECT o.Name, o.ID, o.SPObjectTypeID, NULL, 0 AS PathLevel, scope.[HasUniqueRoleAssignments], o.[IsRootWeb], scope.ScopeID, o.[SiteID], o.[LastUpdated], o.[WebAppID], o.Url, o.[SPGuid], o.[CreatedOn], o.[ContentTypeID]
				    FROM dbo.v_SharePointObjects AS o  
					JOIN dbo.ScopeAssignments as scope ON scope.SPObjectID = o.ID AND scope.LoadingInfoID = @Loc_LoadingInfoID  
					WHERE o.ID in (SELECT r.ID FROM @Loc_RootObjectIDs r) AND o.FarmID = @Loc_FarmID                                       				
			    END
		    ELSE
			    BEGIN
				    INSERT INTO #OrganisationDescendantUnusedContent([Name], [ID], [SharePointObjectTypeID], [ParentID], [PathLevel], [HasUniqueRoleAssignments], [IsRootWeb], [PermissionScopeID], [SiteID], [LastUpdated], [WebApplicationID], [Url], [SPGuid], [CreatedOn], [ContentTypeId])
				        SELECT o.Name, o.ID, o.SPObjectTypeID, m.ParentID, @Depth, 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'Disable all constraints for database'
GO
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Delete duplicate SharePointObjects from [dbo].[SharePointObjects]'
IF OBJECT_ID('tempdb..#IdsToDelete') IS NOT NULL
    DROP TABLE #IdsToDelete

CREATE TABLE #IdsToDelete (
 DuplicateID int,
 OriginalID int
) 

INSERT INTO #IdsToDelete
SELECT p.ID, p.ID
FROM [dbo].[SharePointObjects] p
		LEFT OUTER JOIN (
		   SELECT MAX(ID) as RowId, SiteID, SPGuid 
		   FROM [dbo].[SharePointObjects] 
		   GROUP BY SiteID, SPGuid
		) as KeepRows 
		ON p.ID = KeepRows.RowId
WHERE KeepRows.RowId IS NULL


UPDATE ids
SET OriginalID = (SELECT MAX(ID) 
				  FROM [dbo].[SharePointObjects] 
				  WHERE SiteID = p.SiteID AND SPGuid = p.SPGuid 					
				  GROUP BY SiteID, SPGuid
				 )
FROM #IdsToDelete ids		
INNER JOIN [dbo].[SharePointObjects] p ON p.ID = ids.DuplicateID

PRINT N'Merge rows from [dbo].[ContentType]'
UPDATE pm
SET pm.ParentSiteID = d.OriginalID
FROM [dbo].[ContentType] pm		
INNER JOIN #IdsToDelete d ON pm.ParentSiteID = d.DuplicateID

PRINT N'Merge rows from [dbo].[FeaturesActivations]'
UPDATE pm
SET pm.SPObjectID = d.OriginalID
FROM [dbo].FeaturesActivations pm		
INNER JOIN #IdsToDelete d ON pm.SPObjectID = d.DuplicateID AND pm.LoadingInfoID > 0

PRINT N'Merge rows from [dbo].[LoadedSiteCollections]'
UPDATE pm
SET pm.SiteCollectionID = d.OriginalID
FROM [dbo].LoadedSiteCollections pm		
INNER JOIN #IdsToDelete d ON pm.SiteCollectionID = d.DuplicateID AND pm.LoadingInfoID > 0

PRINT N'Merge rows from [dbo].[SandboxedSolutions]'
UPDATE pm
SET pm.SiteCollectionID = d.OriginalID
FROM [dbo].SandboxedSolutions pm		
INNER JOIN #IdsToDelete d ON pm.SiteCollectionID = d.DuplicateID AND pm.LoadingInfoID > 0

PRINT N'Merge rows from [dbo].[ScopeAssignments]'
PRINT N'Merge rows from [dbo].[SharePointObjectMemberships] for Parent'
PRINT N'Merge rows from [dbo].[SharePointObjectMemberships] for Child'

DECLARE @IDtoMerge INT
DECLARE @RowCursor CURSOR

SET @RowCursor = CURSOR FOR
SELECT ids.DuplicateID
FROM #IdsToDelete ids

OPEN @RowCursor

FETCH NEXT FROM @RowCursor INTO @IDtoMerge
WHILE @@FETCH_STATUS = 0
BEGIN	

	UPDATE pm
	SET pm.SPObjectID = d.OriginalID
	FROM [dbo].ScopeAssignments pm		
	INNER JOIN #IdsToDelete d ON pm.SPObjectID = d.DuplicateID AND pm.LoadingInfoID > 0
	WHERE  d.DuplicateID = @IDtoMerge AND NOT EXISTS (SELECT 1 FROM [dbo].ScopeAssignments o WHERE o.LoadingInfoID = pm.LoadingInfoID AND o.SPObjectID = d.OriginalID)

	UPDATE pm
	SET pm.ParentID = d.OriginalID
	FROM [dbo].SharePointObjectMemberships pm		
	INNER JOIN #IdsToDelete d ON pm.ParentID = d.DuplicateID AND pm.LoadingInfoID > 0
	WHERE d.DuplicateID = @IDtoMerge AND NOT EXISTS (SELECT 1 FROM [dbo].SharePointObjectMemberships o WHERE o.LoadingInfoID = pm.LoadingInfoID AND o.ChildID = pm.ChildID AND o.ParentID = d.OriginalID)

	UPDATE pm
	SET pm.ChildID = d.OriginalID
	FROM [dbo].SharePointObjectMemberships pm		
	INNER JOIN #IdsToDelete d ON pm.ChildID = d.DuplicateID AND pm.LoadingInfoID > 0
	WHERE d.DuplicateID = @IDtoMerge AND NOT EXISTS (SELECT 1 FROM [dbo].SharePointObjectMemberships o WHERE o.LoadingInfoID = pm.LoadingInfoID AND o.ParentID = pm.ParentID AND o.ChildID = d.OriginalID)
	
	FETCH NEXT FROM @RowCursor INTO @IDtoMerge	
END
CLOSE @RowCursor
DEALLOCATE @RowCursor

PRINT N'Delete rows from [dbo].[ScopeAssignments]'
DELETE pm
FROM [dbo].ScopeAssignments pm		
WHERE EXISTS (SELECT * FROM #IdsToDelete ids where ids.DuplicateID = pm.SPObjectID AND pm.LoadingInfoID > 0)

PRINT N'Merge rows from [dbo].[SharePointObjectContentType]'
UPDATE pm
SET pm.SharePointObjectID = d.OriginalID
FROM [dbo].SharePointObjectContentType pm		
INNER JOIN #IdsToDelete d ON pm.SharePointObjectID = d.DuplicateID AND pm.LoadingInfoID > 0

PRINT N'Delete rows from [dbo].[SharePointObjectMemberships] for Parent'
DELETE pm
FROM [dbo].SharePointObjectMemberships pm		
WHERE EXISTS (SELECT * FROM #IdsToDelete ids where ids.DuplicateID = pm.ParentID AND pm.LoadingInfoID > 0)

PRINT N'Delete rows from [dbo].[SharePointObjectMemberships] for Child'
DELETE pm
FROM [dbo].SharePointObjectMemberships pm		
WHERE EXISTS (SELECT * FROM #IdsToDelete ids where ids.DuplicateID = pm.ChildID AND pm.LoadingInfoID > 0)

PRINT N'Merge rows from [dbo].[SiteCollectionAdministrators]'
UPDATE pm
SET pm.SharePointObjectID = d.OriginalID
FROM [dbo].SiteCollectionAdministrators pm		
INNER JOIN #IdsToDelete d ON pm.SharePointObjectID = d.DuplicateID AND pm.LoadingInfoID > 0

PRINT N'Merge rows from [dbo].[SiteCollectionProperties]'
UPDATE pm
SET pm.SiteCollectionID = d.OriginalID
FROM [dbo].SiteCollectionProperties pm		
INNER JOIN #IdsToDelete d ON pm.SiteCollectionID = d.DuplicateID AND pm.LoadingInfoID > 0

PRINT N'Merge rows from [dbo].[SiteCollectionsContentDatabase]'
UPDATE pm
SET pm.SiteCollectionID = d.OriginalID
FROM [dbo].SiteCollectionsContentDatabase pm		
INNER JOIN #IdsToDelete d ON pm.SiteCollectionID = d.DuplicateID

PRINT N'Merge rows from [dbo].[SPDocKitRoleSiteCollections]'
UPDATE pm
SET pm.SiteCollectionID = d.OriginalID
FROM [dbo].SPDocKitRoleSiteCollections pm		
INNER JOIN #IdsToDelete d ON pm.SiteCollectionID = d.DuplicateID

PRINT N'Merge rows from [dbo].[SPDocKitSiteCollectionAdministrators]'
UPDATE pm
SET pm.SiteID = d.OriginalID
FROM [dbo].SPDocKitSiteCollectionAdministrators pm		
INNER JOIN #IdsToDelete d ON pm.SiteID = d.DuplicateID

PRINT N'Merge rows from [dbo].[WorkflowAssociations] for Subsite'
UPDATE pm
SET pm.SubSiteID = d.OriginalID
FROM [dbo].WorkflowAssociations pm		
INNER JOIN #IdsToDelete d ON pm.SubSiteID = d.DuplicateID AND pm.LoadingInfoID > 0

PRINT N'Merge rows from [dbo].[WorkflowAssociations] for List'
UPDATE pm
SET pm.ListID = d.OriginalID
FROM [dbo].WorkflowAssociations pm		
INNER JOIN #IdsToDelete d ON pm.ListID = d.DuplicateID AND pm.LoadingInfoID > 0

PRINT N'Merge rows from [dbo].[WorkflowDefinitions]'
UPDATE pm
SET pm.SubSiteID = d.OriginalID
FROM [dbo].WorkflowDefinitions pm		
INNER JOIN #IdsToDelete d ON pm.SubSiteID = d.DuplicateID AND pm.LoadingInfoID > 0

PRINT N'Merge rows from [dbo].[WorkflowSubscriptions]'
UPDATE pm
SET pm.ListID = d.OriginalID
FROM [dbo].WorkflowSubscriptions pm		
INNER JOIN #IdsToDelete d ON pm.ListID = d.DuplicateID AND pm.LoadingInfoID > 0

PRINT N'Merge rows from [dbo].[WorkflowTemplates]'
UPDATE pm
SET pm.SiteCollectionID = d.OriginalID
FROM [dbo].WorkflowTemplates pm		
INNER JOIN #IdsToDelete d ON pm.SiteCollectionID = d.DuplicateID

PRINT N'Merge rows from [dbo].[WorkflowTemplatesOnSubSite]'
UPDATE pm
SET pm.SubSiteID = d.OriginalID
FROM [dbo].WorkflowTemplatesOnSubSite pm		
INNER JOIN #IdsToDelete d ON pm.SubSiteID = d.DuplicateID AND pm.LoadingInfoID > 0

PRINT N'Delete rows from [dbo].[SharePointObjects]'
DELETE p
FROM [dbo].SharePointObjects p
WHERE EXISTS (SELECT * FROM #IdsToDelete ids where ids.DuplicateID = p.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'Enable all constraints for database'
GO
exec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
UPDATE Farms SET LEE = NULL WHERE LEE  LIKE 'S%'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
UPDATE Options SET OptionValue = '6.4.3' 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

