Veeam.Virt.Extensions.VMware.Report.DWDataSet (DataWarehouseDataSet)

Element properties:

Source Code:

<DataWarehouseDataSet ID="Veeam.Virt.Extensions.VMware.Report.DWDataSet" Accessibility="Public">

  <Dependencies>

    <DataWarehouseScript>MSDL!Microsoft.SystemCenter.DataWarehouse.Script.DatasetDebuggingSupport</DataWarehouseScript>

    <DataWarehouseScript>MSDL!Microsoft.SystemCenter.DataWarehouse.Script.StandardDatasetSupport</DataWarehouseScript>

  </Dependencies>

  <Configuration>

    <IncludeSchemaTypes>

      <SchemaType>MSDL!Microsoft.SystemCenter.DataWarehouse.StandardDatasetSchema</SchemaType>

    </IncludeSchemaTypes>

    <xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="Aggregations" type="AggregationsType"/>

    <xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="RawInsertTableCount" type="xsd:positiveInteger"/>

    <xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="DefaultAggregationIntervalCount" type="xsd:positiveInteger"/>

    <xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="BlockingMaintenanceDailyStartTime" type="TimeOfDayType"/>

    <xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="BlockingMaintenanceDurationMinutes" type="xsd:positiveInteger"/>

  </Configuration>

  <Install>--------------------------------------------------------------------------------------

-- Create the StandardDataset RestoreSession

--------------------------------------------------------------------------------------



SET NOCOUNT ON

GO



IF NOT EXISTS (SELECT * FROM StandardDataset WHERE DatasetId = '$Config/DatasetId$')

BEGIN

	INSERT INTO StandardDataset (

		DatasetId

		,SchemaName

		,DefaultAggregationIntervalCount

		,RawInsertTableCount

		,StagingProcessorStoredProcedureName

		,BlockingMaintenanceDailyStartTime

		,BlockingMaintenanceDurationMinutes

	)

	VALUES

	(

		'$Config/DatasetId$'

		,'VeeamMP'

		,$Config/DefaultAggregationIntervalCount$

		,$Config/RawInsertTableCount$

		,'VeeamMP.ObjectProcessStaging'

		,'$Config/BlockingMaintenanceDailyStartTime$' 

		,$Config/BlockingMaintenanceDurationMinutes$

	)

END

GO





--------------------------------------------------------------------------------------

-- Create the StandardDatasetAggregation for the RestoreSession dataset.

--------------------------------------------------------------------------------------



SET NOCOUNT ON



DECLARE 

	@ErrorInd			bit

	,@ErrorMessageText	nvarchar(4000)

	,@ErrorNumber		int

	,@ErrorSeverity		int

	,@ErrorState		int

	,@ErrorLine			int

	,@ErrorProcedure	nvarchar(256)



SET @ErrorInd = 0



DECLARE

	@ExecResult int

	,@XmlDocHandle int

	,@XmlDocText nvarchar(max)



IF NOT EXISTS (SELECT * FROM StandardDatasetAggregation WHERE DatasetId = '$Config/DatasetId$')

BEGIN TRY

	SET @XmlDocText = '&lt;Aggregations&gt;$Config/Aggregations$&lt;/Aggregations&gt;'



	EXEC @ExecResult = sp_xml_preparedocument @XmlDocHandle OUTPUT, @XmlDocText

	IF @ExecResult &lt;&gt; 0 RAISERROR(777971000, 16, 1, 'VeeamMP_ObjectAggregation', @ExecResult)



	INSERT StandardDatasetAggregation

	(

		DatasetId

		,AggregationTypeId

		,AggregationIntervalDurationMinutes

		,AggregationStartDelayMinutes

		,BuildAggregationStoredProcedureName

		,DeleteAggregationStoredProcedureName

		,GroomStoredProcedureName

		,IndexOptimizationIntervalMinutes

		,MaxDataAgeDays

		,GroomingIntervalMinutes

		,MaxRowsToGroom

		,LastGroomingDateTime

		,DataFileGroupName

		,IndexFileGroupName

	)

	SELECT

		'$Config/DatasetId$'

		,CASE AggregationType

			WHEN 'Raw' THEN 0

			WHEN 'Hourly' THEN 20

			WHEN 'Daily' THEN 30

		END

		,CASE AggregationType

			WHEN 'Raw' THEN 0

			WHEN 'Hourly' THEN 60

			WHEN 'Daily' THEN 60*24

		END

		,CASE AggregationType

			WHEN 'Raw' THEN 1

			WHEN 'Hourly' THEN 60

			WHEN 'Daily' THEN 60*24

		END

		,CASE AggregationType

			WHEN 'Raw' THEN NULL

			ELSE 'VeeamMP.ObjectAggregate'

		END

		,

		CASE AggregationType

			WHEN 'Raw' THEN NULL

			ELSE 'VeeamMP.ObjectAggregationDelete'

		END

		,'VeeamMP.ObjectGroom'

		,IndexOptimizationIntervalMinutes

		,MaxDataAgeDays

		,GroomingIntervalMinutes

		,MaxRowsToGroom

		,GETUTCDATE()

		,DataFileGroupName

		,IndexFileGroupName

	FROM OPENXML(@XmlDocHandle, 'Aggregations/*', 2) WITH (

		AggregationType                    varchar(50)   '@mp:localname'

		,MaxTableRowCount                   int

		,MaxTableSizeKb                     int

		,AggregationStartDelayMinutes       int

		,MaxDataAgeDays                     int

		,GroomingIntervalMinutes            int

		,MaxRowsToGroom                     int

		,IndexOptimizationIntervalMinutes   int

		,DataFileGroupName                  nvarchar(256)

		,IndexFileGroupName                 nvarchar(256)

		,AggregationIntervalDurationMinutes int

	)

END TRY

BEGIN CATCH

	SELECT

		@ErrorNumber = ERROR_NUMBER()

		,@ErrorSeverity = ERROR_SEVERITY()

		,@ErrorState = ERROR_STATE()

		,@ErrorLine = ERROR_LINE()

		,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')

		,@ErrorMessageText = ERROR_MESSAGE()



	SET @ErrorInd = 1

END CATCH



IF (@XmlDocHandle IS NOT NULL)

	EXEC sp_xml_removedocument @XmlDocHandle



IF (@ErrorInd = 1)

BEGIN

	DECLARE @AdjustedErrorSeverity int



	SET @AdjustedErrorSeverity = CASE

									WHEN @ErrorSeverity &gt; 18 THEN 18

									ELSE @ErrorSeverity

								END



	RAISERROR (777971002, @AdjustedErrorSeverity, 1

		,@ErrorNumber

		,@ErrorSeverity

		,@ErrorState

		,@ErrorProcedure

		,@ErrorLine

		,@ErrorMessageText

	)

END

GO



--------------------------------------------------------------------------------------

-- Define the tables and views for the RestoreSession dataset.

--------------------------------------------------------------------------------------



IF NOT EXISTS (SELECT * FROM sys.schemas WHERE [name] = 'VeeamMP')

BEGIN

	EXECUTE('CREATE SCHEMA VeeamMP')

END

GO





-- change schema ownership to allow index rebuild and reorganize

ALTER AUTHORIZATION ON SCHEMA::VeeamMP TO OpsMgrWriter

GO



-- change schema alter to allow domain table index rebuild

GRANT ALTER ON SCHEMA::dbo TO OpsMgrWriter

GO

GRANT ALTER ON SCHEMA::VeeamMP TO OpsMgrWriter

GO



-- grant create table permissions

GRANT CREATE TABLE TO OpsMgrWriter

GO



SET NOCOUNT ON



DECLARE

	@ErrorInd			bit

	,@ErrorMessageText	nvarchar(4000)

	,@ErrorNumber		int

	,@ErrorSeverity		int

	,@ErrorState		int

	,@ErrorLine			int

	,@ErrorProcedure	nvarchar(256)



SET @ErrorInd = 0



DECLARE

	@ExecResult					int

	,@XmlDocHandle				int

	,@XmlDocText				nvarchar(max)



	

BEGIN TRY



-- Insert definition for VeeamMP.RestoreSessionRawStagingV14 table into StandardDatasetStagingArea.

-- The table VeeamMP.RestoreSessionRawStagingV14 table is a special table. 

-- Unlike others within RestoreSession dataset - it is processed by RestoreSessionProcessStaging sproc.

BEGIN TRAN

	IF EXISTS (SELECT * FROM StandardDatasetStagingArea WHERE DatasetId = '$Config/DatasetId$')

	BEGIN

		DELETE StandardDatasetStagingArea

		WHERE DatasetId = '$Config/DatasetId$';

	END



	INSERT INTO StandardDatasetStagingArea

	(

		DatasetId

		,StagingTableColumnDefinition

		,MaxRowsToProcessPerTransactionCount

		,BatchedProcessingSupportedInd

	)

	VALUES

	(

		'$Config/DatasetId$'

		,		

		-- The 'LastAccessed' column will be updated on each run of ProcessStaging sproc

		-- to let the sproc to process different recordset on each run - a FIFO queue mode.

		-- Without this, the sproc may each time process the same record set of previously skipped records 

		-- and thus never get to others until first ones a groomed.

		'ObjectRowId								int						NOT NULL  IDENTITY(1, 1)

		,ObjectTypeSystemName						nvarchar(max)

		,ObjectTypeDisplayName						nvarchar(max)

		,ObjectTypePropertySystemName				nvarchar(max)

		,ObjectTypePropertyDefaultName				nvarchar(max)	

		,ObjectSystemName							nvarchar(max)		

		,ObjectDisplayName							nvarchar(max)	

		,ObjectCreatedTime							datetime			

		,ObjectDeletedTime							datetime

		,[DateTime]									datetime

		,ObjectPropertyValue						nvarchar(max)

		,ObjectPropertyValueFromDateTime			nvarchar(max)

		,ObjectPropertyValueToDateTime				nvarchar(max)

		,StageDiscovery								nvarchar(256)

		,ManagementGroupId							uniqueidentifier					

		'

		-- This defines how many records does RestoreSessionProcessStaging sproc process per each execution (every 1 minute).

		,10000 -- Value based on experience in Datacenter Production environment (see bug 297340 for details).

		,1

	)

COMMIT



-- Create the VeeamMP.RestoreSessionRawStagingV14 table.

BEGIN TRAN

	-- Drop table if it exists.

	IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[VeeamMP].[MPStaging]') AND type in (N'U'))

	BEGIN

		DROP TABLE [VeeamMP].MPStaging;

	END



	DECLARE @Statement nvarchar(max);

	-- Create table.

	SELECT @Statement = 'CREATE TABLE [VeeamMP].[MPStaging] (' + StagingTableColumnDefinition + ')'

	FROM StandardDatasetStagingArea

	WHERE DatasetId = '$Config/DatasetId$';



	EXECUTE (@Statement);



	GRANT SELECT, INSERT, UPDATE, DELETE ON VeeamMP.MPStaging TO OpsMgrWriter

COMMIT





-- Inserting definition for table that will hold raw (non-aggregated) data.



IF NOT EXISTS (SELECT * FROM StandardDatasetAggregationStorage WHERE DatasetId = '$Config/DatasetId$' AND TableTag IS NULL)

	BEGIN

		INSERT StandardDatasetAggregationStorage

		(

			DatasetId

			,AggregationTypeId

			,BaseTableName

			,DependentTableInd

			,TableTemplate

			,CoverViewSelectClause

			,MaxTableRowCount

			,MaxTableSizeKb 

		)

		VALUES

		(

			'$Config/DatasetId$'

			,0

			,'Object'

			,0

			,'

				CREATE TABLE [VeeamMP].[Object_$Guid$]

				(

					 ObjectId						int						NOT NULL  IDENTITY(1, 1)

					,ObjectGuid						uniqueidentifier		DEFAULT NEWID()

					,TypeId							int

					,SystemName						nvarchar(max)

					,DisplayName					nvarchar(max)

					,[DateTime]						datetime

					,[TimeCreated]					datetime		

					,[Deletedtime]					datetime

					,TableGuid						uniqueidentifier

					,ManagementGroupId				uniqueidentifier

					,CONSTRAINT [PK_Object_$Guid$] PRIMARY KEY CLUSTERED (ObjectId) ON $DataFileGroupName$

				) ON $DataFileGroupName$

			'

			,'

				SELECT

					 ObjectId				

					,ObjectGuid												

					,TypeId				

					,SystemName						

					,DisplayName					

					,[Datetime]

					,[TimeCreated]							

					,[Deletedtime]

					,TableGuid

					,ManagementGroupId											

			'

			-- Do not partition table.

			,10000000

			,NULL

		)

	END

ELSE

	BEGIN

		UPDATE StandardDatasetAggregationStorage SET 

			AggregationTypeId = 0

			,BaseTableName = 'Object'

			,DependentTableInd = 0

			,TableTemplate = 'CREATE TABLE [VeeamMP].[Object_$Guid$]

				(

					 ObjectId						int						NOT NULL  IDENTITY(1, 1)

					,ObjectGuid						uniqueidentifier		DEFAULT NEWID()

					,TypeId							int

					,SystemName						nvarchar(max)

					,DisplayName					nvarchar(max)

					,[DateTime]						datetime

					,[TimeCreated]					datetime		

					,[Deletedtime]					datetime

					,TableGuid						uniqueidentifier

					,ManagementGroupId				uniqueidentifier	

					,CONSTRAINT [PK_Object_$Guid$] PRIMARY KEY CLUSTERED (ObjectId) ON $DataFileGroupName$

				) ON $DataFileGroupName$

			'

			,CoverViewSelectClause = 'SELECT

					 ObjectId				

					,ObjectGuid												

					,TypeId				

					,SystemName						

					,DisplayName					

					,[DateTime]

					,[TimeCreated]							

					,[Deletedtime]

					,TableGuid

					,ManagementGroupId											

			'

			,MaxTableRowCount = 10000000

			,MaxTableSizeKb = NULL

		WHERE DatasetId = '$Config/DatasetId$' AND TableTag IS NULL

	END



IF NOT EXISTS (SELECT * FROM StandardDatasetAggregationStorage WHERE DatasetId = '$Config/DatasetId$' AND TableTag = 'OT')

BEGIN

	INSERT StandardDatasetAggregationStorage

	(

		DatasetId

		,AggregationTypeId

		,BaseTableName

		,TableTag

		,DependentTableInd

		,TableTemplate

		,CoverViewSelectClause

		,MaxTableRowCount

		,MaxTableSizeKb 

	)

	VALUES

	(

		'$Config/DatasetId$'

		,0

		,'ObjectType'

		,'OT'

		,0

		,'

			CREATE TABLE [VeeamMP].[ObjectType_$Guid$]

			(

				 ObjectTypeId					int						NOT NULL  IDENTITY(1, 1)

				,SystemName						nvarchar(max)

				,DisplayName					nvarchar(max)

				,[DateTime]						datetime

				,TableGuid						uniqueidentifier

				,ManagementGroupId				uniqueidentifier

				,CONSTRAINT [PK_ObjectType_$Guid$] PRIMARY KEY CLUSTERED (ObjectTypeId) ON $DataFileGroupName$

			) ON $DataFileGroupName$

		'

		,'

			SELECT

				ObjectTypeId					

				,SystemName								

				,DisplayName

				,[DateTime]	

				,TableGuid

				,ManagementGroupId														

		'

		-- Do not partition table.

		,10000000

		,NULL

	)

END

ELSE

BEGIN

	UPDATE StandardDatasetAggregationStorage

	SET 

		AggregationTypeId = 0

		,BaseTableName = 'ObjectType'

		,DependentTableInd = 0

		,TableTemplate = 

		'

			CREATE TABLE [VeeamMP].[ObjectType_$Guid$]

			(

				 ObjectTypeId					int						NOT NULL  IDENTITY(1, 1)

				,SystemName						nvarchar(max)

				,DisplayName					nvarchar(max)

				,[DateTime]						datetime

				,TableGuid						uniqueidentifier

				,ManagementGroupId				uniqueidentifier

				,CONSTRAINT [PK_ObjectType_$Guid$] PRIMARY KEY CLUSTERED (ObjectTypeId) ON $DataFileGroupName$

			) ON $DataFileGroupName$

		'

		,CoverViewSelectClause = 

		'

			SELECT

				ObjectTypeId					

				,[SystemName]								

				,[DisplayName]

				,[DateTime]

				,TableGuid

				,ManagementGroupId															

		'

		,MaxTableRowCount = 10000000

		,MaxTableSizeKb = NULL

	WHERE DatasetId = '$Config/DatasetId$' AND TableTag = 'OT'

END



IF NOT EXISTS (SELECT * FROM StandardDatasetAggregationStorage WHERE DatasetId = '$Config/DatasetId$' AND TableTag = 'OV')

BEGIN

	INSERT StandardDatasetAggregationStorage

	(

		DatasetId

		,AggregationTypeId

		,BaseTableName

		,TableTag

		,DependentTableInd

		,TableTemplate

		,CoverViewSelectClause

		,MaxTableRowCount

		,MaxTableSizeKb		

	)

	VALUES

	(

		'$Config/DatasetId$'

		,0

		,'ObjectPropertyValue'

		,'OV'

		,0

		,'

			CREATE TABLE [VeeamMP].[ObjectPropertyValue_$Guid$]

			(

				 ObjectPropertyValueId			int						NOT NULL  IDENTITY(1, 1)

				,ObjectPropertyValueGuid		uniqueidentifier		DEFAULT NEWID()

				,ObjectTypePropertyId			int						NOT NULL

				,[OldValue]						nvarchar(max)

				,[Value]						nvarchar(max)

				,[NewValue]						nvarchar(max)

				,ObjectGuid						uniqueidentifier

				,[DateTime]						datetime				NOT NULL

				,[TimeCreated]					datetime

				,ToDatetime					    datetime

				,TableGuid						uniqueidentifier

				,ManagementGroupId				uniqueidentifier

				,CONSTRAINT [PK_ObjectPropertyValue_$Guid$] PRIMARY KEY CLUSTERED (ObjectPropertyValueId) ON $DataFileGroupName$

			) ON $DataFileGroupName$

		'

		,'

			SELECT

				 ObjectPropertyValueId											

				,ObjectPropertyValueGuid	

				,ObjectTypePropertyId

				,[OldValue]											

				,[Value]

				,[NewValue]							

				,ObjectGuid						

				,[DateTime]

				,[TimeCreated]					

				,[ToDatetime]

				,TableGuid

				,ManagementGroupId																				

		'

		-- Do not partition table.

		,10000000

		,NULL

	)

END

ELSE

BEGIN

	UPDATE StandardDatasetAggregationStorage

	SET AggregationTypeId = 0

		,BaseTableName = 'ObjectPropertyValue'

		,DependentTableInd = 0

		,TableTemplate = '

			CREATE TABLE [VeeamMP].[ObjectPropertyValue_$Guid$]

			(

				 ObjectPropertyValueId			int						NOT NULL  IDENTITY(1, 1)

				,ObjectPropertyValueGuid		uniqueidentifier		DEFAULT NEWID()

				,ObjectTypePropertyId			int						NOT NULL

				,[OldValue]						nvarchar(max)

				,[Value]						nvarchar(max)

				,[NewValue]						nvarchar(max)

				,ObjectGuid						uniqueidentifier

				,[DateTime]						datetime				NOT NULL

				,[TimeCreated]					datetime

				,ToDatetime					    datetime

				,TableGuid						uniqueidentifier

				,ManagementGroupId				uniqueidentifier

				,CONSTRAINT [PK_ObjectPropertyValue_$Guid$] PRIMARY KEY CLUSTERED (ObjectPropertyValueId) ON $DataFileGroupName$

			) ON $DataFileGroupName$

		'

		,CoverViewSelectClause = '

			SELECT

				 ObjectPropertyValueId											

				,ObjectPropertyValueGuid	

				,ObjectTypePropertyId

				,OldValue											

				,[Value]

				,[NewValue]							

				,ObjectGuid						

				,[DateTime]

				,[TimeCreated]					

				,[ToDatetime]

				,TableGuid

				,ManagementGroupId										

		'

		,MaxTableRowCount = 10000000

		,MaxTableSizeKb	= NULL	

	WHERE DatasetId = '$Config/DatasetId$' AND TableTag = 'OV'

END



IF NOT EXISTS (SELECT * FROM StandardDatasetAggregationStorage WHERE DatasetId = '$Config/DatasetId$' AND TableTag = 'OTP')

BEGIN

	INSERT StandardDatasetAggregationStorage

	(

		DatasetId

		,AggregationTypeId

		,BaseTableName

		,TableTag

		,DependentTableInd

		,TableTemplate

		,CoverViewSelectClause

		,MaxTableRowCount

		,MaxTableSizeKb		

	)

	VALUES

	(

		'$Config/DatasetId$'

		,0

		,'ObjectTypeProperty'

		,'OTP'

		,0

		,'

			CREATE TABLE [VeeamMP].[ObjectTypeProperty_$Guid$]

			(

				 ObjectTypePropertyId			int						NOT NULL  IDENTITY(1, 1)

				,ObjectTypePropertyGuid			uniqueidentifier		DEFAULT NEWID()

				,ObjectTypeId					int

				,[SystemName]					nvarchar(max)			

				,[DefaultName]					nvarchar(max)

				,[DateTime]						datetime

				,TableGuid						uniqueidentifier

				,ManagementGroupId				uniqueidentifier

				,CONSTRAINT [PK_ObjectTypeProperty_$Guid$] PRIMARY KEY CLUSTERED (ObjectTypePropertyId) ON $DataFileGroupName$

			) ON $DataFileGroupName$

		'

		,'

			SELECT

				 ObjectTypePropertyId

				,ObjectTypePropertyGuid											

				,ObjectTypeId	

				,SystemName											

				,DefaultName

				,DateTime

				,TableGuid

				,ManagementGroupId																										

		'

		-- Do not partition table.

		,10000000

		,NULL

	)

END

ELSE

BEGIN

	UPDATE StandardDatasetAggregationStorage

	SET AggregationTypeId = 0

		,BaseTableName = 'ObjectTypeProperty'

		,DependentTableInd = 0

		,TableTemplate = '

			CREATE TABLE [VeeamMP].[ObjectTypeProperty_$Guid$]

			(

				 ObjectTypePropertyId			int						NOT NULL  IDENTITY(1, 1)

				,ObjectTypePropertyGuid			uniqueidentifier		DEFAULT NEWID()

				,ObjectTypeId					int

				,[SystemName]					nvarchar(max)			

				,[DefaultName]					nvarchar(max)

				,[DateTime]						datetime

				,TableGuid						uniqueidentifier

				,ManagementGroupId				uniqueidentifier

				,CONSTRAINT [PK_ObjectTypeProperty_$Guid$] PRIMARY KEY CLUSTERED (ObjectTypePropertyId) ON $DataFileGroupName$

			) ON $DataFileGroupName$

		'

		,CoverViewSelectClause = '

			SELECT

				 ObjectTypePropertyId

				,ObjectTypePropertyGuid											

				,ObjectTypeId	

				,SystemName											

				,DefaultName

				,DateTime

				,TableGuid

				,ManagementGroupId

		'

		,MaxTableRowCount = 10000000

		,MaxTableSizeKb	= NULL	

	WHERE DatasetId = '$Config/DatasetId$' AND TableTag = 'OTP'

END



END TRY

BEGIN CATCH

	SELECT 

		@ErrorNumber = ERROR_NUMBER()

		,@ErrorSeverity = ERROR_SEVERITY()

		,@ErrorState = ERROR_STATE()

		,@ErrorLine = ERROR_LINE()

		,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-'),@ErrorMessageText = ERROR_MESSAGE()



	SET @ErrorInd = 1

END CATCH



IF (@XmlDocHandle IS NOT NULL)

	EXEC sp_xml_removedocument @XmlDocHandle



IF (@ErrorInd = 1)

BEGIN

	DECLARE @AdjustedErrorSeverity int



	SET @AdjustedErrorSeverity = CASE

									WHEN @ErrorSeverity &gt; 18 THEN 18

									ELSE @ErrorSeverity

								END



	RAISERROR (777971002, @AdjustedErrorSeverity, 1

		,@ErrorNumber

		,@ErrorSeverity

		,@ErrorState

		,@ErrorProcedure

		,@ErrorLine

		,@ErrorMessageText

	)

END

GO



--------------------------------------------------------------------------------------

-- Allocate standard storage for the RestoreSession dataset.

--------------------------------------------------------------------------------------



SET NOCOUNT ON



DECLARE

	@ErrorInd			bit

	,@ErrorMessageText	nvarchar(4000)

	,@ErrorNumber		int

	,@ErrorSeverity		int

	,@ErrorState		int

	,@ErrorLine			int

	,@ErrorProcedure	nvarchar(256)



SET @ErrorInd = 0



DECLARE

	@ExecResult int

	,@XmlDocHandle int

	,@XmlDocText nvarchar(max)



BEGIN TRY

	SET @XmlDocText = '&lt;Aggregations&gt;$Config/Aggregations$&lt;/Aggregations&gt;'



	EXEC @ExecResult = sp_xml_preparedocument @XmlDocHandle OUTPUT, @XmlDocText

	IF @ExecResult &lt;&gt; 0 RAISERROR(777971000, 16, 1, 'VeeamMP_ObjectAggregation', @ExecResult)



	DECLARE

		@AggregationType varchar(50)

		,@Statement nvarchar(max)



	SET @AggregationType = ''



	WHILE EXISTS (SELECT *

				FROM OPENXML(@XmlDocHandle, 'Aggregations/*', 2) WITH (

					  AggregationType      varchar(50)   '@mp:localname')

				WHERE AggregationType &gt; @AggregationType

				)

	BEGIN

		SELECT TOP 1 @AggregationType = AggregationType

		FROM OPENXML(@XmlDocHandle, 'Aggregations/*', 2) WITH (

			  AggregationType      varchar(50)   '@mp:localname')

		WHERE AggregationType &gt; @AggregationType

		ORDER BY AggregationType



		SET @Statement = 'EXEC StandardDatasetAllocateStorage @DatasetId = ''$Config/DatasetId$'', @AggregationTypeId='

						+ CASE @AggregationType

							WHEN 'Raw' THEN '0'

							WHEN 'Hourly' THEN '20'

							WHEN 'Daily' THEN '30'

						END

		EXECUTE (@Statement)

	END

END TRY

BEGIN CATCH

	SELECT

		@ErrorNumber = ERROR_NUMBER()

		,@ErrorSeverity = ERROR_SEVERITY()

		,@ErrorState = ERROR_STATE()

		,@ErrorLine = ERROR_LINE()

		,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')

		,@ErrorMessageText = ERROR_MESSAGE()



	SET @ErrorInd = 1

END CATCH



IF (@XmlDocHandle IS NOT NULL)

	EXEC sp_xml_removedocument @XmlDocHandle



IF (@ErrorInd = 1)

BEGIN

	DECLARE @AdjustedErrorSeverity int



	SET @AdjustedErrorSeverity = CASE

									WHEN @ErrorSeverity &gt; 18 THEN 18

									ELSE @ErrorSeverity

								END



	RAISERROR (777971002, @AdjustedErrorSeverity, 1

		,@ErrorNumber

		,@ErrorSeverity

		,@ErrorState

		,@ErrorProcedure

		,@ErrorLine

		,@ErrorMessageText

	)

END

GO



-------

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[VeeamMP].[FullDiscovery]') AND type in (N'P'))

	BEGIN

		EXECUTE ('CREATE PROCEDURE [VeeamMP].[FullDiscovery] AS RETURN 1')

	END

GO



ALTER PROCEDURE VeeamMP.FullDiscovery

	@ManagementGroupId		uniqueidentifier

	,@DatasetId				uniqueidentifier

	,@DataXml				nvarchar(max)

AS

BEGIN

	SET NOCOUNT ON

	DECLARE @CheckExecResult int

			,@ManagementGroupRowId int



	-- get the management group

	SELECT @ManagementGroupRowId = ManagementGroupRowId

	FROM vManagementGroup 

	WHERE (ManagementGroupGuid = @ManagementGroupId) AND (WriterLoginName = SUSER_Name())



	-- access check

	EXEC @CheckExecResult = ManagementGroupAccessCheck @ManagementGroupId, @ManagementGroupRowId OUTPUT

	IF(@CheckExecResult &lt;&gt; 0) RETURN @CheckExecResult



	DECLARE

		@ErrorInd			bit

		,@ErrorNumber		int

		,@ErrorSeverity		int

		,@ErrorState		INT

		,@ErrorLine			int

		,@ErrorProcedure	nvarchar(256)

		,@ErrorMessageText	nvarchar(max)

		,@ResultingState	int



	SET @ErrorInd = 0

	SET @ResultingState = 1



	DECLARE

		@ExecResult			INT

		,@ExecResult1			int

		,@XmlDocHandle		INT

		,@XmlDocHandleDict		INT

		,@XmlDocHandleDictDN	INT



	BEGIN TRY

		-- Add it to VeeamBackupMP.SessionTaskStaging

		-- We may not find the alert in the Alert table because the alert is first saved

		-- in the SessionTaskStaging table and there is a separate process to move it to the 

		-- Alert table. Therefore, we have to move the parsing of DataXml in to the  

		-- scheduled aggregation function.

		DECLARE @PropertyDateTime DATETIME

			

		SET @PropertyDateTime=GETUTCDATE()

		

		EXEC @ExecResult = sp_xml_preparedocument @XmlDocHandle OUTPUT, @DataXml

		IF @ExecResult &lt;&gt; 0 RAISERROR(777971000, 16, 1, 'VeeamMP_FullDiscovery', @ExecResult)	 



		--create property dictionary

		DECLARE @prStringDictName NVARCHAR(MAX),

				@displayStringDictName NVARCHAR(MAX)

		

		

		SELECT 

			@prStringDictName=TSName

		FROM  OPENXML(@XmlDocHandle, '/Root/Item/Context/DataItem/Property',2) WITH (

				 			

				 			ID					NVARCHAR(MAX)        '@Name'

				 			,TSName				NVARCHAR(MAX)	'../Property[1]'

						

		)

		WHERE ID ='PnDictionary'



		SELECT 

			@displayStringDictName=TSName

		FROM  OPENXML(@XmlDocHandle, '/Root/Item/Context/DataItem/Property',2) WITH (

				 			

				 			ID					NVARCHAR(MAX)        '@Name'

				 			,TSName				NVARCHAR(MAX)	'../Property[1]'

						

		)

		WHERE ID ='DNDictionary'



		SET @prStringDictName='&lt;Root&gt;'+@prStringDictName+'&lt;/Root&gt;'

		SET @displayStringDictName='&lt;Root&gt;'+@displayStringDictName+'&lt;/Root&gt;'

		EXEC @ExecResult = sp_xml_preparedocument @XmlDocHandleDict OUTPUT,@prStringDictName



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

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

		CREATE TABLE #PropertyNameDictionary

		(

			ID INT NOT NULL  IDENTITY(1, 1),

			[KEY] NVARCHAR(MAX),

			[value] NVARCHAR(MAX)		

		)

		CREATE TABLE #PropertyDisplayNameDictionary

		(

			ID INT NOT NULL  IDENTITY(1, 1),

			[KEY] NVARCHAR(MAX),

			[value] NVARCHAR(MAX)		

		)



		INSERT INTO #PropertyNameDictionary

		(

			[KEY], 

			[value]	

		)

		SELECT 

			[KEY],

			[value]

		FROM  OPENXML(@XmlDocHandleDict, '/Root/pn',2) WITH (

				 			

				 			[KEY]					NVARCHAR(MAX)        '@key'

				 			,[value]					NVARCHAR(MAX)        '@value'



		)

		EXEC @ExecResult1 = sp_xml_preparedocument @XmlDocHandleDictDN OUTPUT,@displayStringDictName

		INSERT INTO #PropertyDisplayNameDictionary

		(

			[KEY], 

			[value]	

		)

		SELECT 

			[KEY],

			[value]

		FROM  OPENXML(@XmlDocHandleDictDN, '/Root/pn',2) WITH (

				 			

				 			[KEY]					NVARCHAR(MAX)        '@key'

				 			,[value]					NVARCHAR(MAX)        '@value'



		)

		

		INSERT INTO VeeamMP.Dictionary

		(

			PropertyName,

			PropertyDisplayName

		)

		SELECT

			pname.[value], 

			pdname.[value] 

		FROM 

		#PropertyNameDictionary AS pname

			INNER JOIN #PropertyDisplayNameDictionary AS pdname ON pdname.[KEY] = pname.[KEY]

		WHERE pname.[value] NOT IN (SELECT VeeamMP.Dictionary.PropertyName

		                              FROM VeeamMP.Dictionary) 

		

		BEGIN TRAN

		SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

			;WITH cte(ID, TSName,TDName,SName, DName, SD, [property]) 

			AS

			(

				SELECT 

					ID,

					TSName, 

					TDName, 

					SName, 

					DName, 

					SD,

					CONVERT (XML, '&lt;Root&gt;' + [property] + '&lt;/Root&gt;') 

				FROM  OPENXML(@XmlDocHandle, '/Root/Item/Context/DataItem/Property',2) WITH (

				 			

				 					ID					NVARCHAR(MAX)        '@Name'

				 					,TSName				NVARCHAR(MAX)	'../Property[1]'

									,TDName				NVARCHAR(MAX)	'../Property[2]'

									,SName				NVARCHAR(MAX)	'../Property[3]'

									,DName				NVARCHAR(MAX)	'../Property[4]'

									,SD					NVARCHAR(MAX)	'../Property[5]'

									,property			NVARCHAR(MAX)	'../Property[6]'

				)

				WHERE ID ='property'

			),

			propertyList(TSName,TDName,SName, DName, SD, propertyValue, propertyKey)

			AS

			(

				SELECT

					c.TSName,

					c.TDName,

					c.SName,

					c.DName,

					c.SD,

					COALESCE(f.b.value('(p)[1]','NVARCHAR(MAX)'),CAST([property].query('data(/Root/p)') AS NVARCHAR(MAX))),

					COALESCE(f.b.value('(p/@k)[1]','NVARCHAR(MAX)'),'collector' ) 

					--[property].query('data(/Root/pr/p)')

				FROM cte AS c

				--INNER JOIN #PropertyNameDictionary AS pdn ON 

				OUTER APPLY c.[property].nodes('/Root/pr') f(b)

			)

			INSERT INTO VeeamMP.MPStaging

			(

				 ObjectTypeSystemName						

				,ObjectTypeDisplayName					

				,ObjectTypePropertySystemName			

				,ObjectTypePropertyDefaultName																	

				,ObjectSystemName

				,ObjectDisplayName	

				,ObjectCreatedTime

				,ObjectPropertyValue					    																															

				,ObjectPropertyValueFromDateTime

				,StageDiscovery

				,ManagementGroupId		    			

			)

			SELECT 

			DISTINCT 

				pl.TSName, 

				pl.TDName,

				COALESCE(psd1.PropertyName,psd.[value],pl.propertyKey),

				COALESCE(psd1.PropertyDisplayName,psd.[value],pl.propertyKey),

				pl.SName,

				pl.DName,

				@PropertyDateTime,

				pl.propertyValue,

				@PropertyDateTime,

				pl.SD,

				@ManagementGroupId

			FROM propertyList AS pl

					LEFT JOIN #PropertyNameDictionary AS psd ON psd.[KEY]=pl.propertyKey

			LEFT JOIN VeeamMP.Dictionary AS psd1 ON psd1.PropertyName=psd.[value]

		COMMIT  TRAN

	END TRY

	BEGIN CATCH

		IF (@@TRANCOUNT &gt; 0)

			ROLLBACK TRAN



		SELECT

			@ErrorNumber = ERROR_NUMBER()

			,@ErrorSeverity = ERROR_SEVERITY()

			,@ErrorState = ERROR_STATE()

			,@ErrorLine = ERROR_LINE()

			,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')

			,@ErrorMessageText = ERROR_MESSAGE()



		SET @ErrorInd = 1

	END CATCH



	IF (@XmlDocHandle IS NOT NULL)

		EXEC sp_xml_removedocument @XmlDocHandle



	-- report error if any

	IF (@ErrorInd = 1)

	BEGIN

		DECLARE @AdjustedErrorSeverity int



		SET @AdjustedErrorSeverity = CASE

										WHEN @ErrorSeverity &gt; 18 THEN 18

										ELSE @ErrorSeverity

									END



		RAISERROR (777971002, @AdjustedErrorSeverity, @ResultingState

			,@ErrorNumber

			,@ErrorSeverity

			,@ErrorState

			,@ErrorProcedure

			,@ErrorLine

			,@ErrorMessageText

		)

	END

END

GO



GRANT EXECUTE ON VeeamMP.FullDiscovery TO OpsMgrReader

GO

---------------------------------------------------------------------------------------------

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[VeeamMP].[ObjectProcessStaging]') AND type in (N'P'))

	BEGIN

		EXECUTE ('CREATE PROCEDURE [VeeamMP].[ObjectProcessStaging] AS RETURN 1')

	END

GO



ALTER PROCEDURE [VeeamMP].[ObjectProcessStaging]

	@DatasetId								uniqueidentifier

AS

BEGIN

	SET NOCOUNT ON

	DECLARE

		@ErrorInd							bit

		,@ErrorNumber						int

		,@ErrorSeverity						int

		,@ErrorState						int

		,@ErrorLine							int

		,@ErrorProcedure					nvarchar(256)

		,@ErrorMessageText					nvarchar(4000)

	

	SET @ErrorInd = 0

	DECLARE	

		-- Configuration variables.

		-- Maximum age of data to keep in table SessionTaskStaging.

		@MaxDataAgeDays	int

		-- Maximum number of rows to process per single run of this stored procedure.

		-- Diagnostic variables.

		,@DebugLevel						int

		,@DebugMessage						nvarchar(4000)

		,@OperationStartedDateTime			datetime

		,@OperationDurationMs				bigint

		,@RowsDeleted						int

		,@Statement							nvarchar(max)

		,@SchemaName						nvarchar(max)

		,@TableGuid							varchar(50)	

		,@FullTableGuid						varchar(50)		

		,@StoredTableGuid					varchar(50)	

		,@StoredInsertTableName				nvarchar(90)

		,@RowId								INT

		,@ManagementGroupId					UNIQUEIDENTIFIER

		--,@DatasetId		UNIQUEIDENTIFIER

		

		--SET @DatasetId='A5A8F603-B62D-4AC3-9E36-A87F58703B0F'

	BEGIN TRY

	        BEGIN TRAN

		DECLARE @PropertyDateTime DATETIME

			

		SET @PropertyDateTime=GETUTCDATE()

		

		SELECT @MaxDataAgeDays = MaxDataAgeDays

		FROM dbo.StandardDatasetAggregation WHERE DatasetId = @DatasetId AND AggregationTypeId = 0;

		-- Delete the outdated records using the same grooming settings as for Staging table.

		DELETE FROM VeeamMP.MPStaging WHERE [DateTime]  &lt; DATEADD(day, - @MaxDataAgeDays, @PropertyDateTime);

		SET @RowsDeleted = @@ROWCOUNT;



		IF((@DebugLevel &gt; 2) AND (@RowsDeleted &gt; 0))

		BEGIN

			SET @DebugMessage = 'Groomed table VeeamMP.MPStaging by ' + CAST(@RowsDeleted as varchar(16)) + ' records.';



			EXEC DebugMessageInsert

				@DatasetId = @DatasetId

				,@MessageLevel = 3

				,@MessageText = @DebugMessage

		END				

		IF (@DebugLevel &gt; 2)

		BEGIN

			SET @OperationStartedDateTime = GETDATE();

			SET @OperationDurationMs = 0;

			

			SET @DebugMessage = 'Started stage processing of VeeamMP.MPStaging table.';



			EXEC DebugMessageInsert

				@DatasetId = @DatasetId

				,@MessageLevel = 3

				,@MessageText = @DebugMessage

				,@OperationDurationMs = @OperationDurationMs;

		END

		if object_id('tempdb..#oldobj') is not null drop table #oldobj

		if object_id('tempdb..#newobj') is not null drop table #newobj

		if object_id('tempdb..#hosts') is not null drop table #hosts

		if object_id('tempdb..#objectTableGuid') is not null drop table #objectTableGuid

		if object_id('tempdb..#ObjectStagingBatch') is not null drop table #ObjectStagingBatch

		if object_id('tempdb..#DiscoveryTimes') is not null drop table #DiscoveryTimes

		

		CREATE TABLE #DiscoveryTimes

		(

			ID		   INT			NOT NULL  IDENTITY(1, 1),

			[Datetime] DATETIME,

			ObjectTypeSystemName NVARCHAR(512)	

		)

		

			INSERT INTO #DiscoveryTimes

			(

				[Datetime],

				ObjectTypeSystemName

			)

			SELECT 

				DISTINCT hsm.ObjectCreatedTime,

						 hsm.ObjectTypeSystemName 

			FROM VeeamMP.MPStaging AS hsm

			ORDER BY hsm.ObjectCreatedTime 

			

			DECLARE @DtRowId INT,

					@CurrentDatetime DATETIME,

					@CurrentObjectType NVARCHAR(512)

			SET @DtRowId = -1	

			WHILE EXISTS(

						SELECT TOP(1)

								dt.ID

							FROM #DiscoveryTimes AS dt

							WHERE dt.ID &gt; @DtRowId

						)

						BEGIN

							SELECT TOP(1)

								@DtRowId=dt.ID,

								@CurrentDatetime = dt.[Datetime],

								@CurrentObjectType = dt.ObjectTypeSystemName

							FROM #DiscoveryTimes dt

								WHERE dt.ID &gt; @DtRowId

									if object_id('tempdb..#VMSnapshots') IS NOT NULL drop table #VMSnapshots

									if object_id('tempdb..#NewVMSnapshotObj') IS NOT NULL drop table #NewVMSnapshotObj

									if object_id('tempdb..#oldobj') is not null drop table #oldobj

									if object_id('tempdb..#newobj') is not null drop table #newobj

									if object_id('tempdb..#hosts') is not null drop table #hosts

									if object_id('tempdb..#objectTableGuid') is not null drop table #objectTableGuid

									if object_id('tempdb..#ObjectStagingBatch') is not null drop table #ObjectStagingBatch

											

									CREATE TABLE #ObjectStagingBatch

									(		 

											 ObjectRowId								int

											,ObjectTypeSystemName						nvarchar(max)

											,ObjectTypeDisplayName						nvarchar(max)

											,ObjectTypePropertySystemName				nvarchar(max)

											,ObjectTypePropertyDefaultName				nvarchar(max)	

											,ObjectSystemName							nvarchar(max)		

											,ObjectDisplayName							nvarchar(max)	

											,ObjectCreatedTime							datetime			

											,ObjectDeletedTime							datetime

											,[DateTime]									datetime

											,ObjectPropertyValue						nvarchar(max)

											,ObjectPropertyValueFromDateTime			nvarchar(max)

											,ObjectPropertyValueToDateTime				nvarchar(max)

											,StageDiscovery								nvarchar(256)

											,ManagementGroupId							uniqueidentifier	

									)

									

										INSERT INTO #ObjectStagingBatch

										(

											ObjectRowId,

											ObjectTypeSystemName, 

											ObjectTypeDisplayName, 

											ObjectTypePropertySystemName,

											ObjectTypePropertyDefaultName, 

											ObjectSystemName, 

											ObjectDisplayName,

											ObjectCreatedTime, 

											ObjectDeletedTime, 

											[DateTime], 

											ObjectPropertyValue,

											ObjectPropertyValueFromDateTime, 

											ObjectPropertyValueToDateTime, 

											StageDiscovery,

											ManagementGroupId

										)

										SELECT

											hsm.ObjectRowId, 

											hsm.ObjectTypeSystemName, 

											hsm.ObjectTypeDisplayName,

											hsm.ObjectTypePropertySystemName, 

											hsm.ObjectTypePropertyDefaultName,

											hsm.ObjectSystemName, 

											hsm.ObjectDisplayName, 

											hsm.ObjectCreatedTime,

											hsm.ObjectDeletedTime, 

											hsm.[DateTime], 

											hsm.ObjectPropertyValue,

											hsm.ObjectPropertyValueFromDateTime, 

											hsm.ObjectPropertyValueToDateTime,

											hsm.StageDiscovery,

											hsm.ManagementGroupId 

										FROM VeeamMP.MPStaging AS hsm

															WHERE hsm.ObjectCreatedTime = @CurrentDatetime

															AND	hsm.ObjectTypeSystemName=@CurrentObjectType

									IF EXISTS(SELECT * FROM #ObjectStagingBatch WHERE StageDiscovery='DeleteSnapshotsByVM')

									BEGIN

										;WITH host(hostName, ManagementGroupId, ObjectTypeSystemName)

											AS

											(

												SELECT 

													osb.ObjectPropertyValue, 

													osb.ManagementGroupId,

													osb.ObjectTypeSystemName 

												FROM #ObjectStagingBatch  AS osb

												WHERE osb.ObjectTypePropertySystemName='collector' AND  StageDiscovery='DeleteSnapshotsByVM'	 	

											)

										UPDATE VeeamMP.vObject	

											SET

												Deletedtime = @PropertyDateTime

											WHERE SystemName IN 

											(

												SELECT 

													vo.SystemName

												FROM VeeamMP.vObject AS vo

												INNER JOIN VeeamMP.vObjectPropertyValue AS vopv ON vopv.ObjectGuid=vo.ObjectGuid

												INNER JOIN VeeamMP.vObjectTypeProperty AS votp ON votp.ObjectTypePropertyId=vopv.ObjectTypePropertyId

												INNER JOIN host AS h ON h.hostName=vopv.[Value] 

												INNER JOIN VeeamMP.vObjectType AS vot ON vot.SystemName=h.ObjectTypeSystemName 

												WHERE votp.SystemName='collector' AND vo.Deletedtime IS NULL AND vo.ManagementGroupId=h.ManagementGroupId

																										AND vopv.ToDatetime IS NULL

											) 

				 		

				 							DELETE FROM #ObjectStagingBatch 

				 							WHERE StageDiscovery='DeleteSnapshotsByVM' AND ManagementGroupId IN (SELECT ManagementGroupId FROM #ObjectStagingBatch WHERE StageDiscovery='DeleteSnapshotsByVM')

				 							DELETE FROM VeeamMP.MPStaging 

				 							WHERE StageDiscovery='DeleteSnapshotsByVM' AND	ManagementGroupId IN (SELECT ManagementGroupId FROM VeeamMP.MPStaging WHERE StageDiscovery='DeleteSnapshotsByVM') 

									END

									IF EXISTS(SELECT * FROM #ObjectStagingBatch WHERE StageDiscovery='DeleteByMG')

									BEGIN

										UPDATE vo

										SET

											Deletedtime = @PropertyDateTime

										FROM VeeamMP.vObject AS vo

											INNER JOIN VeeamMP.vObjectType AS vot ON vot.ObjectTypeId=vo.TypeId

											INNER JOIN #ObjectStagingBatch b ON b.ObjectTypeSystemName = vot.SystemName	 

																		AND vo.ManagementGroupId=b.ManagementGroupId

										WHERE vo.Deletedtime IS NULL AND b.StageDiscovery='DeleteByMG' 

												AND b.ObjectTypeSystemName=@CurrentObjectType

				

										DELETE FROM VeeamMP.MPStaging

										FROM VeeamMP.MPStaging AS hsm -- Second FROM clause is used for filtering the records to be deleted.

											INNER JOIN #ObjectStagingBatch AS batch ON hsm.ObjectRowId = batch.ObjectRowId AND batch.StageDiscovery='DeleteByMG' 

										DELETE FROM #ObjectStagingBatch

										WHERE StageDiscovery='DeleteByMG' 

									END	

									ELSE

										IF EXISTS(SELECT * FROM VeeamMP.MPStaging WHERE StageDiscovery='DeleteByHost')

										BEGIN

											;WITH host(hostName, ManagementGroupId, ObjectTypeSystemName)

											AS

											(

												SELECT 

													osb.ObjectPropertyValue, 

													osb.ManagementGroupId,

													osb.ObjectTypeSystemName 

												FROM #ObjectStagingBatch  AS osb

												WHERE osb.ObjectTypePropertySystemName='host' AND  StageDiscovery='DeleteByHost'	 	

											)

											UPDATE VeeamMP.vObject	

											SET

												Deletedtime = @PropertyDateTime

											WHERE SystemName IN 

											(

												SELECT 

													vo.SystemName

												FROM VeeamMP.vObject AS vo

												INNER JOIN VeeamMP.vObjectPropertyValue AS vopv ON vopv.ObjectGuid=vo.ObjectGuid

												INNER JOIN VeeamMP.vObjectTypeProperty AS votp ON votp.ObjectTypePropertyId=vopv.ObjectTypePropertyId

												INNER JOIN host AS h ON h.hostName=vopv.[Value] 

												INNER JOIN VeeamMP.vObjectType AS vot ON vot.SystemName=h.ObjectTypeSystemName 

												WHERE votp.SystemName='host' AND vo.Deletedtime IS NULL AND vo.ManagementGroupId=h.ManagementGroupId

																										AND vopv.ToDatetime IS NULL

											) 

				 		

				 							DELETE FROM #ObjectStagingBatch 

				 							WHERE StageDiscovery='DeleteByHost' AND ManagementGroupId IN (SELECT ManagementGroupId FROM #ObjectStagingBatch WHERE StageDiscovery='DeleteByHost')

				 							DELETE FROM VeeamMP.MPStaging 

				 							WHERE StageDiscovery='DeleteByHost' AND	ManagementGroupId IN (SELECT ManagementGroupId FROM VeeamMP.MPStaging WHERE StageDiscovery='DeleteByHost')	

										END	

				 		

									-- Get the debug level for RestoreSession dataset.

									SELECT @DebugLevel = DebugLevel ,

											@SchemaName = SchemaName

									FROM dbo.StandardDataset WHERE DatasetId = @DatasetId;



									EXEC dbo.StandardDatasetGetInsertTableGuid @DatasetId, 0, @FullTableGuid OUTPUT

									SET @TableGuid = REPLACE(CAST(@FullTableGuid AS varchar(50)), '-', '')



									--adding new types in DS

									SET @Statement = 'INSERT INTO VeeamMP.ObjectType_' + CAST(@TableGuid AS varchar(300)) +

									'(

										SystemName, 

										DisplayName,

										TableGuid,

										ManagementGroupId

									)

									SELECT 

										DISTINCT hsm.ObjectTypeSystemName,

												 hsm.ObjectTypeDisplayName, '

										+''''+ CAST(@FullTableGuid AS varchar(300)) +''''+','+

										'hsm.ManagementGroupId 

									 FROM #ObjectStagingBatch AS hsm

									LEFT OUTER JOIN VeeamMP.vObjectType AS vot ON hsm.ObjectTypeSystemName=vot.SystemName

									WHERE vot.SystemName IS NULL'



									EXECUTE (@Statement)



									--adding new propert in DS

									SET @Statement = 'INSERT INTO VeeamMP.ObjectTypeProperty_'+ CAST(@TableGuid AS varchar(300)) +

									'(

										  ObjectTypeId,

										  SystemName,

										  DefaultName,

										  TableGuid,

										  ManagementGroupId

									)

									SELECT 

										DISTINCT

												 vot.ObjectTypeId,

												 hsm.ObjectTypePropertySystemName,

												 hsm.ObjectTypePropertyDefaultName, '

												 +''''+  CAST(@FullTableGuid AS varchar(300))  +''''+','+

										'hsm.ManagementGroupId  

									 FROM #ObjectStagingBatch AS hsm

									INNER JOIN VeeamMP.vObjectType AS vot ON vot.SystemName = hsm.ObjectTypeSystemName

									LEFT OUTER JOIN VeeamMP.vObjectTypeProperty AS otp ON hsm.ObjectTypePropertySystemName=otp.SystemName AND otp.ObjectTypeId=vot.ObjectTypeId 

									WHERE otp.SystemName IS NULL '

 

									EXECUTE (@Statement)



									CREATE TABLE #VMSnapshots

									(

										ObjectGuid UNIQUEIDENTIFIER	

									)

			

									CREATE TABLE #NewVMSnapshotObj

									(

										ObjectTypeId INT,

										ObjectSystemName NVARCHAR(256),

										ObjectDisplayName NVARCHAR(256),

										ManagementGroupId uniqueidentifier

									)

	

									INSERT INTO #VMSnapshots

									(

										ObjectGuid

									)

									SELECT DISTINCT

											vopv.ObjectGuid 

									FROM VeeamMP.vObjectPropertyValue AS vopv

										INNER JOIN #ObjectStagingBatch AS osb ON vopv.[Value]=osb.ObjectPropertyValue

										INNER JOIN VeeamMP.vObjectTypeProperty AS votp ON votp.ObjectTypePropertyId = vopv.ObjectTypePropertyId

									WHERE votp.SystemName='collector'

	

									DELETE FROM VeeamMP.vObject

									WHERE ObjectGuid IN (SELECT ObjectGuid FROM #VMSnapshots)

									DELETE FROM VeeamMP.vObjectPropertyValue

									WHERE ObjectGuid IN (SELECT ObjectGuid FROM #VMSnapshots) 

	

									--adding new object in DS

									INSERT INTO #NewVMSnapshotObj

									(

										ObjectTypeId, 

										ObjectSystemName, 

										ObjectDisplayName,

										ManagementGroupId

									)

									SELECT DISTINCT

										vot.ObjectTypeId,

										hsm.ObjectSystemName,

										hsm.ObjectDisplayName,

										hsm.ManagementGroupId

									FROM #ObjectStagingBatch AS hsm

									INNER JOIN VeeamMP.vObjectType AS vot ON vot.SystemName = hsm.ObjectTypeSystemName

									WHERE hsm.StageDiscovery = 'U'

	

			

									SET @Statement = 'INSERT INTO VeeamMP.Object_'+ CAST(@TableGuid AS varchar(300)) +

									'(

										ObjectGuid,

										TypeId,

										SystemName,

										DisplayName,

										[TimeCreated],

										[DateTime],

										TableGuid,

										ManagementGroupId

									)

									SELECT

										NEWID(),

										ObjectTypeId, 

										ObjectSystemName, 

										ObjectDisplayName,

										'+''''+CAST(@PropertyDateTime AS varchar(300))+''''+', '

											+''''+  CAST(@PropertyDateTime AS varchar(300))  +''''+','+ 

											+''''+  CAST(@FullTableGuid AS varchar(300))  +''''+','+  

										'ManagementGroupId 

										FROM #NewVMSnapshotObj'



									EXECUTE (@Statement)

		

									SET @Statement = 'INSERT INTO VeeamMP.ObjectPropertyValue_' + CAST(@TableGuid AS varchar(300)) +

									'(

										ObjectPropertyValueGuid, 

										ObjectTypePropertyId, 

										[Value], 

										ObjectGuid, 

										[DateTime],

										[TimeCreated],

										ToDatetime,

										TableGuid,

										ManagementGroupId

									)

									SELECT

										NEWID(),

										votp.ObjectTypePropertyId,

										hsm.ObjectPropertyValue,

										vo.ObjectGuid,

										'+''''+ CAST(@PropertyDateTime AS varchar(300))+''''+','+

										+''''+  CAST(@PropertyDateTime AS varchar(300))  +''''+',   

										NULL, '

											+''''+  CAST(@FullTableGuid AS varchar(300))  +''''+','+  

										'hsm.ManagementGroupId   

										FROM #NewVMSnapshotObj AS n

									INNER JOIN VeeamMP.vObject AS vo ON vo.SystemName=n.ObjectSystemName

									INNER JOIN #ObjectStagingBatch AS hsm ON hsm.ObjectSystemName=vo.SystemName

									INNER JOIN VeeamMP.vObjectTypeProperty AS votp ON hsm.ObjectTypePropertySystemName=votp.SystemName	AND votp.ObjectTypeId=vo.TypeId

									WHERE vo.ManagementGroupId=hsm.ManagementGroupId '



									EXECUTE (@Statement)

		

									DELETE FROM VeeamMP.MPStaging

									FROM VeeamMP.MPStaging AS hsm -- Second FROM clause is used for filtering the records to be deleted.

										INNER JOIN #ObjectStagingBatch AS batch ON hsm.ObjectRowId = batch.ObjectRowId 

									WHERE batch.StageDiscovery='U'

		

									DELETE FROM #ObjectStagingBatch WHERE StageDiscovery='U'



									CREATE TABLE #hosts

									(

										HostName NVARCHAR(MAX),

										ManagementGroupId uniqueidentifier	

									)



									CREATE TABLE #oldobj

									(

										ObjectTypeId INT,

										ObjectSystemName NVARCHAR(256),

										ObjectDisplayName NVARCHAR(256),

										ManagementGroupId uniqueidentifier

									)



									CREATE TABLE #newobj

									(

										ObjectTypeId INT,

										ObjectSystemName NVARCHAR(256),

										ObjectDisplayName NVARCHAR(256),

										ManagementGroupId uniqueidentifier

									)



									INSERT INTO #hosts

									(

										HostName,

										ManagementGroupId

									)

									SELECT 

										DISTINCT hsm.ObjectPropertyValue,

												 hsm.ManagementGroupId 

									FROM #ObjectStagingBatch AS hsm

									WHERE hsm.ObjectTypePropertySystemName='host' AND hsm.StageDiscovery='host'



									CREATE TABLE #objectTableGuid

									(

	

										ID				INT 					NOT NULL  IDENTITY(1, 1),

										ObjectId		INT,

										SystemName		NVARCHAR(MAX),

										TableGuid		UNIQUEIDENTIFIER,

										ManagementGroupId uniqueidentifier

									)

									DECLARE @ObjectId INT

									--deleting vm by host stage

									;WITH existVmByHost(ID ,SystemName, TableGuid, ManagementGroupId)

									AS

									(

										SELECT

											vo.ObjectId,

											vo.SystemName,

											vo.TableGuid,

											vo.ManagementGroupId

										FROM VeeamMP.vObject AS vo

										INNER JOIN VeeamMP.vObjectType AS vot ON vo.TypeId=vot.ObjectTypeId

										INNER JOIN VeeamMP.vObjectPropertyValue AS vopv ON vo.ObjectGuid=vopv.ObjectGuid

										INNER JOIN VeeamMP.vObjectTypeProperty AS votp ON votp.ObjectTypePropertyId = vopv.ObjectTypePropertyId

										INNER JOIN #hosts AS h ON h.HostName=vopv.[Value]

										WHERE votp.SystemName='host' AND vo.ManagementGroupId = h.ManagementGroupId	AND vot.SystemName=@CurrentObjectType

									),

									hostStageVm(ObjectSystemName, ManagementGroupId)

									AS

									(

										SELECT 

											DISTINCT hsm.ObjectSystemName,

													 hsm.ManagementGroupId

										FROM #ObjectStagingBatch AS hsm

										WHERE  hsm.StageDiscovery='host'

									)

									INSERT INTO #objectTableGuid

									(

										ObjectId, 

										SystemName, 

										TableGuid,

										ManagementGroupId

									)

									SELECT

										exvm.ID,

										exvm.SystemName,

										exvm.TableGuid,

										exvm.ManagementGroupId

									FROM existVmByHost AS exvm

										LEFT OUTER JOIN hostStageVm AS o ON	exvm.SystemName=o.ObjectSystemName

									WHERE o.ObjectSystemName IS NULL



									SET @RowId = -1	

									WHILE EXISTS(

												SELECT TOP(1)

													tgb.ID

													FROM #objectTableGuid tgb

													WHERE tgb.ID &gt; @RowId

												)

												BEGIN

													SELECT TOP(1)

														@StoredTableGuid = tgb.TableGuid

														,@RowId = tgb.ID

														,@ObjectId=tgb.ObjectId

														,@ManagementGroupId=tgb.ManagementGroupId

														FROM #objectTableGuid tgb

														WHERE tgb.ID &gt; @RowId

													SET @StoredInsertTableName = QUOTENAME(@SchemaName) + '.' +  QUOTENAME('Object' + '_' + REPLACE(CAST(@StoredTableGuid AS varchar(50)), '-', ''))

													SET @Statement = 'UPDATE ' + CAST(@StoredInsertTableName AS varchar(max)) +  

													' SET Deletedtime = '+''''+CAST(@PropertyDateTime AS VARCHAR(300))+''''+

													 ' WHERE	ObjectId = ' + CAST(@ObjectId AS NVARCHAR(256)) +' AND ManagementGroupId=' +''''+ CAST(@ManagementGroupId AS NVARCHAR(512))+''''



													EXECUTE (@Statement)

												END



									----------------------------------

									--object removed from deleted 

									DELETE FROM #objectTableGuid



									INSERT INTO #objectTableGuid

									(

										ObjectId, 

										SystemName, 

										TableGuid,

										ManagementGroupId

									)

									SELECT

									DISTINCT

										vo.ObjectId, 

										vo.SystemName,

										vo.TableGuid,

										vo.ManagementGroupId 

									FROM VeeamMP.vObject AS vo

									INNER JOIN VeeamMP.vObjectType AS vot ON vo.TypeId=vot.ObjectTypeId

									INNER JOIN #ObjectStagingBatch AS hsm ON hsm.ObjectSystemName = vo.SystemName

									WHERE vo.Deletedtime IS NOT NULL AND vo.ManagementGroupId=hsm.ManagementGroupId

																	AND vot.SystemName=@CurrentObjectType



									SET @RowId = -1	

									WHILE EXISTS(

												SELECT TOP(1)

													tgb.ID

													FROM #objectTableGuid tgb

													WHERE tgb.ID &gt; @RowId

												)

												BEGIN

													SELECT TOP(1)

														@StoredTableGuid = tgb.TableGuid

														,@RowId = tgb.ID

														,@ObjectId= tgb.ObjectId

														,@ManagementGroupId=tgb.ManagementGroupId

														FROM #objectTableGuid tgb

														WHERE tgb.ID &gt; @RowId

													SET @StoredInsertTableName = QUOTENAME(@SchemaName) + '.' +  QUOTENAME('Object' + '_' + REPLACE(CAST(@StoredTableGuid AS varchar(50)), '-', ''))

													SET @Statement = 'UPDATE ' + CAST(@StoredInsertTableName AS varchar(max)) +  

													' SET Deletedtime = NULL

													 WHERE ObjectId='+CAST(@ObjectId AS NVARCHAR(256)) +' AND ManagementGroupId=' +''''+ CAST(@ManagementGroupId AS NVARCHAR(512))+''''



													EXECUTE (@Statement)

												END

									-----------------------------

									INSERT INTO #oldobj

									(

										ObjectTypeId,

										ObjectSystemName,

										ObjectDisplayName,

										ManagementGroupId

									)

									SELECT DISTINCT

										vot.ObjectTypeId,

										hsm.ObjectSystemName,

										hsm.ObjectDisplayName,

										hsm.ManagementGroupId

									FROM #ObjectStagingBatch AS hsm

									LEFT OUTER JOIN VeeamMP.vObject AS vo ON hsm.ObjectSystemName=vo.SystemName

									INNER JOIN VeeamMP.vObjectType AS vot ON vot.SystemName = hsm.ObjectTypeSystemName

									WHERE vo.SystemName IS NOT NULL AND vo.ManagementGroupId=hsm.ManagementGroupId 

																	AND vot.SystemName=@CurrentObjectType



									--set deleted time if necessary

									DELETE FROM #objectTableGuid

									IF EXISTS(SELECT TOP(1) * FROM #ObjectStagingBatch AS hsm	WHERE hsm.StageDiscovery='stage2' )

									BEGIN

										;WITH MPStagingStageTwoOnly(ObjectRowId, ObjectTypeSystemName, ObjectTypeDisplayName, ObjectTypePropertySystemName, ObjectTypePropertyDefaultName, ObjectSystemName		

																		,ObjectDisplayName, ObjectCreatedTime, ObjectDeletedTime, [DateTime], ObjectPropertyValue, ObjectPropertyValueFromDateTime, 

																		ObjectPropertyValueToDateTime, StageDiscovery,ManagementGroupId)

										AS

										(

											SELECT

											 *

											FROM #ObjectStagingBatch AS hsm

											WHERE hsm.StageDiscovery='stage2'

										)

										INSERT INTO #objectTableGuid

										(

											ObjectId, 

											SystemName, 

											TableGuid,

											ManagementGroupId

										)

										SELECT

											DISTINCT vo.ObjectId, 

													vo.SystemName,

													vo.TableGuid,

													vo.ManagementGroupId

										FROM VeeamMP.vObject AS vo

										INNER JOIN VeeamMP.vObjectType AS vot ON vo.TypeId=vot.ObjectTypeId

										LEFT OUTER JOIN MPStagingStageTwoOnly AS hsm ON hsm.ObjectSystemName = vo.SystemName

										WHERE hsm.ObjectSystemName IS NULL 

										  AND vot.SystemName=@CurrentObjectType 



										SET @RowId = -1	

										WHILE EXISTS(

													SELECT TOP(1)

														tgb.ID

														FROM #objectTableGuid tgb

														WHERE tgb.ID &gt; @RowId

													)

													BEGIN

														SELECT TOP(1)

															@StoredTableGuid = tgb.TableGuid

															,@RowId = tgb.ID

															,@ObjectId = tgb.ObjectId

															,@ManagementGroupId=tgb.ManagementGroupId

															FROM #objectTableGuid tgb

															WHERE tgb.ID &gt; @RowId

														SET @StoredInsertTableName = QUOTENAME(@SchemaName) + '.' +  QUOTENAME('Object' + '_' + REPLACE(CAST(@StoredTableGuid AS varchar(50)), '-', ''))



														SET @Statement = 'UPDATE ' + CAST(@StoredInsertTableName AS varchar(max)) +

														' SET Deletedtime ='+''''+ CAST(@PropertyDateTime AS VARCHAR(MAX))+''''+		

														' WHERE ObjectId='+CAST(@ObjectId AS NVARCHAR(MAX)) +' AND ManagementGroupId=' +''''+ CAST(@ManagementGroupId AS NVARCHAR(512))+''''

							

													EXECUTE (@Statement)  

													END

										-----------------------------

									END

									--adding new object in DS

									INSERT INTO #newobj

									(

										ObjectTypeId, 

										ObjectSystemName, 

										ObjectDisplayName,

										ManagementGroupId

									)

									SELECT DISTINCT

										vot.ObjectTypeId,

										hsm.ObjectSystemName,

										hsm.ObjectDisplayName,

										hsm.ManagementGroupId

									FROM #ObjectStagingBatch AS hsm

									LEFT OUTER JOIN VeeamMP.vObject AS vo ON hsm.ObjectSystemName=vo.SystemName

									INNER JOIN VeeamMP.vObjectType AS vot ON vot.SystemName = hsm.ObjectTypeSystemName

									WHERE vo.SystemName IS NULL	AND vo.Deletedtime IS NULL

			

									SET @Statement = 'INSERT INTO VeeamMP.Object_'+ CAST(@TableGuid AS varchar(300)) +

									'(

										ObjectGuid,

										TypeId,

										SystemName,

										DisplayName,

										[TimeCreated],

										[DateTime],

										TableGuid,

										ManagementGroupId

									)

									SELECT

										NEWID(),

										ObjectTypeId, 

										ObjectSystemName, 

										ObjectDisplayName,

										'+''''+CAST(@PropertyDateTime AS varchar(300))+''''+', '

										 +''''+  CAST(@PropertyDateTime AS varchar(300))  +''''+','+ 

										 +''''+  CAST(@FullTableGuid AS varchar(300))  +''''+','+  

										'ManagementGroupId 

									  FROM #newobj'



									EXECUTE (@Statement)



									SET @Statement = 'INSERT INTO VeeamMP.ObjectPropertyValue_' + CAST(@TableGuid AS varchar(300)) +

									'(

										ObjectPropertyValueGuid, 

										ObjectTypePropertyId, 

										[Value], 

										ObjectGuid, 

										[DateTime],

										[TimeCreated],

										ToDatetime,

										TableGuid,

										ManagementGroupId

									)

									SELECT

										NEWID(),

										votp.ObjectTypePropertyId,

										hsm.ObjectPropertyValue,

										vo.ObjectGuid,

										'+''''+ CAST(@PropertyDateTime AS varchar(300))+''''+','+

										+''''+  CAST(@PropertyDateTime AS varchar(300))  +''''+',   

										NULL, '

										 +''''+  CAST(@FullTableGuid AS varchar(300))  +''''+','+  

										'hsm.ManagementGroupId   

									  FROM #newobj AS n

									INNER JOIN VeeamMP.vObject AS vo ON vo.SystemName=n.ObjectSystemName

									INNER JOIN #ObjectStagingBatch AS hsm ON hsm.ObjectSystemName=vo.SystemName

									INNER JOIN VeeamMP.vObjectTypeProperty AS votp ON hsm.ObjectTypePropertySystemName=votp.SystemName	AND votp.ObjectTypeId=vo.TypeId

									WHERE vo.ManagementGroupId=hsm.ManagementGroupId '



									EXECUTE (@Statement)

									---------------------------



									DELETE FROM #objectTableGuid



									INSERT INTO #objectTableGuid

									(

										ObjectId, 

										SystemName, 

										TableGuid,

										ManagementGroupId

									)

									SELECT 

										vopv.ObjectPropertyValueId,

										hsm.ObjectPropertyValue,

										vopv.TableGuid,

										vopv.ManagementGroupId

									FROM VeeamMP.vObject AS vo

									INNER JOIN #oldobj AS o ON o.ObjectSystemName=vo.SystemName

									INNER JOIN #ObjectStagingBatch AS hsm ON hsm.ObjectSystemName=o.ObjectSystemName

									INNER JOIN VeeamMP.vObjectTypeProperty AS votp ON votp.SystemName=hsm.ObjectTypePropertySystemName

									INNER JOIN VeeamMP.vObjectPropertyValue AS vopv ON vopv.ObjectTypePropertyId=votp.ObjectTypePropertyId AND vopv.ObjectGuid=vo.ObjectGuid

									WHERE  vopv.[Value]!=hsm.ObjectPropertyValue AND  vopv.ToDatetime IS NULL AND hsm.ManagementGroupId=vopv.ManagementGroupId

			

									SET @Statement = 'INSERT INTO VeeamMP.ObjectPropertyValue_' + CAST(@TableGuid AS varchar(300)) +

									'(

										ObjectPropertyValueGuid, 

										ObjectTypePropertyId,

										[OldValue],

										[Value], 

										ObjectGuid, 

										[DateTime],

										[TimeCreated],

										ToDatetime,

										TableGuid,

										ManagementGroupId

									)

									SELECT

										DISTINCT NEWID(), 

										vopv.ObjectTypePropertyId,

										vopv.[Value],

										hsm.ObjectPropertyValue,

										vo.ObjectGuid,

										'+''''+ CAST(@PropertyDateTime AS varchar(300))+''''+','+

										+''''+  CAST(@PropertyDateTime AS varchar(300))  +''''+', 

										NULL,' 

										 +''''+  CAST(@FullTableGuid AS varchar(300))  +''''+','+  

										'hsm.ManagementGroupId  

									 FROM VeeamMP.vObject AS vo

									INNER JOIN #oldobj AS o ON o.ObjectSystemName=vo.SystemName

									INNER JOIN #ObjectStagingBatch AS hsm ON hsm.ObjectSystemName=o.ObjectSystemName

									INNER JOIN VeeamMP.vObjectTypeProperty AS votp ON votp.SystemName=hsm.ObjectTypePropertySystemName

									INNER JOIN VeeamMP.vObjectPropertyValue AS vopv ON vopv.ObjectTypePropertyId=votp.ObjectTypePropertyId AND vopv.ObjectGuid=vo.ObjectGuid

									WHERE  vopv.[Value]!=hsm.ObjectPropertyValue AND  vopv.ToDatetime IS NULL AND hsm.ManagementGroupId=vo.ManagementGroupId'



									UPDATE vopv	

									SET vopv.[DateTime] = @PropertyDateTime	

									FROM VeeamMP.vObjectPropertyValue AS vopv

										INNER JOIN VeeamMP.vObject AS vo ON vo.ObjectGuid = vopv.ObjectGuid

										INNER JOIN #oldobj AS o ON vo.SystemName = o.ObjectSystemName

									WHERE vopv.ToDatetime IS NULL

									

									UPDATE vo	

									SET vo.[DateTime] = @PropertyDateTime	

									FROM  VeeamMP.vObject AS vo  

										INNER JOIN #oldobj AS o ON vo.SystemName = o.ObjectSystemName

									WHERE vo.Deletedtime IS NULL

																					

									EXECUTE (@Statement)

									DECLARE @NewValue NVARCHAR(MAX),

										@ObjectPropertyValueId INT

									SET @RowId = -1	

									WHILE EXISTS(

												SELECT TOP(1)

													tgb.ID

													FROM #objectTableGuid tgb

													WHERE tgb.ID &gt; @RowId

												)

												BEGIN

													SELECT TOP(1)

														@StoredTableGuid = tgb.TableGuid

														,@RowId = tgb.ID

														,@ManagementGroupId=tgb.ManagementGroupId

														,@NewValue=tgb.SystemName

														,@ObjectPropertyValueId=tgb.ObjectId

														FROM #objectTableGuid tgb

														WHERE tgb.ID &gt; @RowId

													SET @StoredInsertTableName = QUOTENAME(@SchemaName) + '.' +  QUOTENAME('ObjectPropertyValue' + '_' + REPLACE(CAST(@StoredTableGuid AS varchar(50)), '-', ''))

													SET @Statement = 'UPDATE ' + CAST(@StoredInsertTableName AS varchar(max)) + 

													' SET ToDatetime = '+''''+ CAST(@PropertyDateTime AS VARCHAR(300))+''''+',

															NewValue ='+''''+ CAST(@NewValue AS VARCHAR(300))+''''+

												'	FROM #objectTableGuid as tgb		

													WHERE ObjectPropertyValueId = '+CAST(@ObjectPropertyValueId AS varchar(max))+' AND '+CAST(@StoredInsertTableName AS varchar(max))+'.ManagementGroupId=' +''''+ CAST(@ManagementGroupId AS NVARCHAR(512))+''''

													EXECUTE (@Statement)

												END



									DELETE FROM VeeamMP.MPStaging

										FROM VeeamMP.MPStaging AS hsm -- Second FROM clause is used for filtering the records to be deleted.

											INNER JOIN #ObjectStagingBatch AS batch ON hsm.ObjectRowId = batch.ObjectRowId

									if object_id('tempdb..#VMSnapshots') IS NOT NULL drop table #VMSnapshots

									if object_id('tempdb..#NewVMSnapshotObj') IS NOT NULL drop table #NewVMSnapshotObj

									if object_id('tempdb..#oldobj') is not null drop table #oldobj

									if object_id('tempdb..#newobj') is not null drop table #newobj

									if object_id('tempdb..#hosts') is not null drop table #hosts

									if object_id('tempdb..#objectTableGuid') is not null drop table #objectTableGuid

									if object_id('tempdb..#ObjectStagingBatch') is not null drop table #ObjectStagingBatch



						END



		COMMIT TRAN				

		if object_id('tempdb..#DiscoveryTimes') is not null drop table #DiscoveryTimes				

		END TRY

		BEGIN CATCH

		if object_id('tempdb..#VMSnapshots') IS NOT NULL drop table #VMSnapshots

		if object_id('tempdb..#NewVMSnapshotObj') IS NOT NULL drop table #NewVMSnapshotObj

		if object_id('tempdb..#oldobj') is not null drop table #oldobj

		if object_id('tempdb..#newobj') is not null drop table #newobj

		if object_id('tempdb..#hosts') is not null drop table #hosts

		if object_id('tempdb..#objectTableGuid') is not null drop table #objectTableGuid

		if object_id('tempdb..#ObjectStagingBatch') is not null drop table #ObjectStagingBatch

		if object_id('tempdb..#DiscoveryTimes') is not null drop table #DiscoveryTimes	

		IF (@@TRANCOUNT &gt; 0)

			ROLLBACK TRAN



		SELECT

			@ErrorNumber = ERROR_NUMBER()

			,@ErrorSeverity = ERROR_SEVERITY()

			,@ErrorState = ERROR_STATE()

			,@ErrorLine = ERROR_LINE()

			,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')

			,@ErrorMessageText = ERROR_MESSAGE()



		SET @ErrorInd = 1

		END CATCH

		IF (@ErrorInd = 1)

	BEGIN

		IF (@DebugLevel &gt; 0)

		BEGIN

			DECLARE @DebugMessageText nvarchar(max)



			SET @DebugMessageText = N'Failed to process staging. Error ' + CAST(@ErrorNumber AS varchar(15))

										+ ', Procedure ' + @ErrorProcedure

										+ ', Line ' + CAST(@ErrorLine AS varchar(15))

										+ ', Message: '+ @ErrorMessageText



			EXEC DebugMessageInsert

				@DatasetId = @DatasetId

				,@MessageLevel = 1

				,@MessageText = @DebugMessageText

		END

		DECLARE @AdjustedErrorSeverity int



		SET @AdjustedErrorSeverity = CASE

										WHEN @ErrorSeverity &gt; 18 THEN 18

										ELSE @ErrorSeverity

									END



		RAISERROR (777971002, @AdjustedErrorSeverity, 1

			,@ErrorNumber

			,@ErrorSeverity

			,@ErrorState

			,@ErrorProcedure

			,@ErrorLine

			,@ErrorMessageText

		)

	END

		

	

END

GO



GRANT EXECUTE ON [VeeamMP].[ObjectProcessStaging] TO OpsMgrReader

GO



IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[VeeamMP].[ObjectAggregate]') AND type in (N'P'))

	BEGIN

		EXECUTE ('CREATE PROCEDURE [VeeamMP].[ObjectAggregate] AS RETURN 1')

	END

GO



ALTER PROCEDURE VeeamMP.ObjectAggregate

	@DatasetId								uniqueidentifier

	,@AggregationTypeId						int					-- aggregration type: e.g. 20 = hourly

	,@InsertTableGuid						varchar(256)		-- GUID of the aggregation table

	,@IntervalStartDateTime					datetime

	,@IntervalEndDateTime					datetime

AS

BEGIN

	SET NOCOUNT ON

	return 1

	GRANT EXECUTE ON VeeamMP.[ObjectAggregate] TO OpsMgrReader

END

GO



GRANT EXECUTE ON VeeamMP.[ObjectAggregate] TO OpsMgrReader

GO



IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[VeeamMP].[ObjectAggregationDelete]') AND type in (N'P'))

	BEGIN

		EXECUTE ('CREATE PROCEDURE [VeeamMP].[ObjectAggregationDelete] AS RETURN 1')

	END

GO



ALTER PROCEDURE VeeamMP.ObjectAggregationDelete

	@DatasetId						uniqueidentifier

	,@AggregationTypeId				tinyint

	,@TableGuid						uniqueidentifier

	,@AggregationDateTime			datetime

	,@RowsDeleted					int OUTPUT

AS

BEGIN

	SET NOCOUNT ON

	return 1

	RETURN @RowsDeleted

END

GO



GRANT EXECUTE ON [VeeamMP].[ObjectAggregationDelete] TO OpsMgrReader

GO



IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[VeeamMP].[ObjectGroom]') AND type in (N'P'))

	BEGIN

		EXECUTE ('CREATE PROCEDURE [VeeamMP].[ObjectGroom] AS RETURN 1')

	END

GO



ALTER PROCEDURE VeeamMP.ObjectGroom

	@DatasetId				uniqueidentifier

	,@AggregationTypeId		tinyint

	,@TableGuid				uniqueidentifier

	,@CutoffDateTime		datetime

	,@MaxRowsToGroom		int

	,@RowsDeleted			int OUTPUT

AS

BEGIN

	SET NOCOUNT ON



	DECLARE

		@ErrorInd			bit

		,@ErrorMessage		nvarchar(4000)

		,@ErrorNumber		int

		,@ErrorSeverity		int

		,@ErrorState		int

		,@ErrorLine			int

		,@ErrorProcedure	nvarchar(256)

		,@ErrorMessageText	nvarchar(4000)



	SET @ErrorInd = 0



	DECLARE

		@TableName			sysname

		,@Statement			nvarchar(max)

		,@DebugLevel		tinyint

		,@SchemaName		sysname



	BEGIN TRY

		SELECT

			@DebugLevel = DebugLevel

			,@SchemaName = SchemaName

		FROM StandardDataset

		WHERE DatasetId = @DatasetId



		-- there are no dependent tables in RestoreSession data set

		DECLARE TableName_Cursor CURSOR FOR  

		SELECT BaseTableName + '_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', '')

		FROM StandardDatasetAggregationStorage

		WHERE (DatasetId = @DatasetId)

			AND (AggregationTypeId = @AggregationTypeId)

			AND (DependentTableInd = 0)



			OPEN TableName_Cursor;  

	FETCH NEXT FROM TableName_Cursor INTO @TableName ;  

	WHILE @@FETCH_STATUS = 0  

	BEGIN

		SET @Statement = 'DELETE TOP (' + CAST(@MaxRowsToGroom AS varchar(15)) + ')'

							+ ' FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName)

							+ ' WHERE ([DateTime] &lt; CONVERT(datetime, ''' + CONVERT(varchar(50), @CutoffDateTime, 120) + ''', 120))'

		EXECUTE (@Statement)



		SET @RowsDeleted = @@ROWCOUNT

		

		declare @DebugMessage nvarchar(max)

		SET @DebugMessage = 'Starting grooming statement  ' + @Statement  + ' rows affected ' +convert (nvarchar(max),@RowsDeleted)

            

            EXEC DebugMessageInsert

               @DatasetId = @DatasetId

              ,@MessageLevel = 3

              ,@MessageText = @DebugMessage

		  

      FETCH NEXT FROM TableName_Cursor INTO @TableName;  

	END;  

	CLOSE TableName_Cursor;  

	DEALLOCATE TableName_Cursor; 

	GRANT EXECUTE ON [VeeamMP].[ObjectGroom] TO OpsMgrReader

	END TRY

	BEGIN CATCH

		IF (@@TRANCOUNT &gt; 0)

			ROLLBACK TRAN



		SELECT

			@ErrorNumber = ERROR_NUMBER()

			,@ErrorSeverity = ERROR_SEVERITY()

			,@ErrorState = ERROR_STATE()

			,@ErrorLine = ERROR_LINE()

			,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')

			,@ErrorMessageText = ERROR_MESSAGE()



		SET @ErrorInd = 1

	END CATCH



	-- report error if any

	IF (@ErrorInd = 1)

	BEGIN

		IF (@DebugLevel &gt; 0)

		BEGIN

			DECLARE @DebugMessageText nvarchar(max)



			SET @DebugMessageText = N'Failed to groom data for Object data set. Error ' + CAST(@ErrorNumber AS varchar(15))

										+ ', Procedure ' + @ErrorProcedure

										+ ', Line ' + CAST(@ErrorLine AS varchar(15))

										+ ', Message: '+ @ErrorMessageText



			EXEC DebugMessageInsert

				@DatasetId = @DatasetId

				,@MessageLevel = 1

				,@MessageText = @DebugMessageText

		END



		DECLARE @AdjustedErrorSeverity int



		SET @AdjustedErrorSeverity = CASE

										WHEN @ErrorSeverity &gt; 18 THEN 18

										ELSE @ErrorSeverity

									END



		RAISERROR (777971002, @AdjustedErrorSeverity, 1

			,@ErrorNumber

			,@ErrorSeverity

			,@ErrorState

			,@ErrorProcedure

			,@ErrorLine

			,@ErrorMessageText

			)

	END



	RETURN @RowsDeleted

END

GO



GRANT EXECUTE ON [VeeamMP].[ObjectGroom] TO OpsMgrReader

GO 

-----

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES AS t WHERE t.TABLE_NAME = 'Dictionary')



BEGIN



DROP TABLE VeeamMP.Dictionary



END



 

CREATE TABLE VeeamMP.Dictionary(PropertyName NVARCHAR(256),PropertyDisplayName NVARCHAR(256),)



GO

------------------------------------------------------------------------------------------

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[VeeamMP].[DecreaseMaxRowsToProcessPerTransactionCount]') AND type in (N'P'))

	BEGIN

		EXECUTE ('CREATE PROCEDURE [VeeamMP].[DecreaseMaxRowsToProcessPerTransactionCount] AS RETURN 1')

	END

GO



ALTER PROCEDURE [VeeamMP].[DecreaseMaxRowsToProcessPerTransactionCount]

	@ManagementGroupId		uniqueidentifier

	,@DatasetId				uniqueidentifier

	,@DataXml				nvarchar(max)

AS

BEGIN

	SET NOCOUNT ON		

	BEGIN	

		UPDATE StandardDatasetStagingArea

		SET			

			MaxRowsToProcessPerTransactionCount = CASE WHEN MaxRowsToProcessPerTransactionCount * 0.9 &lt;100 THEN 100 ELSE MaxRowsToProcessPerTransactionCount * 0.9 END

		WHERE DatasetId = (SELECT DatasetId FROM StandardDataset WHERE SchemaName='VeeamMP')					

	END

END

GO



GRANT EXECUTE ON [VeeamMP].[DecreaseMaxRowsToProcessPerTransactionCount] TO OpsMgrReader

GO



</Install>

  <Uninstall>IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[VeeamMP].[FullDiscovery]') AND type in (N'P'))

BEGIN

EXECUTE ('DROP PROCEDURE [VeeamMP].[FullDiscovery]')

END



IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[VeeamMP].[ObjectProcessStaging]') AND type in (N'P'))

BEGIN

EXECUTE ('DROP PROCEDURE [VeeamMP].[ObjectProcessStaging]')

END



IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[VeeamMP].[ObjectAggregate]') AND type in (N'P'))

BEGIN

EXECUTE ('DROP PROCEDURE [VeeamMP].[ObjectAggregate]')

END



IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[VeeamMP].[ObjectAggregationDelete]') AND type in (N'P'))

BEGIN

EXECUTE ('DROP PROCEDURE [VeeamMP].[ObjectAggregationDelete]')

END



IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[VeeamMP].[ObjectGroom]') AND type in (N'P'))

BEGIN

EXECUTE ('DROP PROCEDURE [VeeamMP].[ObjectGroom]')

END



-- delete std dataset related data

EXEC StandardDatasetDelete '$Config/DatasetId$'

GO</Uninstall>

  <Upgrade>IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[VeeamMP].[ObjectAggregate]') AND type in (N'P'))

	BEGIN

		EXECUTE ('CREATE PROCEDURE [VeeamMP].[ObjectAggregate] AS RETURN 1')

	END

GO



ALTER PROCEDURE VeeamMP.ObjectAggregate

	@DatasetId								uniqueidentifier

	,@AggregationTypeId						int					-- aggregration type: e.g. 20 = hourly

	,@InsertTableGuid						varchar(256)		-- GUID of the aggregation table

	,@IntervalStartDateTime					datetime

	,@IntervalEndDateTime					datetime

AS

BEGIN

	SET NOCOUNT ON

	return 1

	GRANT EXECUTE ON VeeamMP.[ObjectAggregate] TO OpsMgrReader

END

GO



GRANT EXECUTE ON VeeamMP.[ObjectAggregate] TO OpsMgrReader

GO



IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[VeeamMP].[ObjectAggregationDelete]') AND type in (N'P'))

	BEGIN

		EXECUTE ('CREATE PROCEDURE [VeeamMP].[ObjectAggregationDelete] AS RETURN 1')

	END

GO



ALTER PROCEDURE VeeamMP.ObjectAggregationDelete

	@DatasetId						uniqueidentifier

	,@AggregationTypeId				tinyint

	,@TableGuid						uniqueidentifier

	,@AggregationDateTime			datetime

	,@RowsDeleted					int OUTPUT

AS

BEGIN

	SET NOCOUNT ON

	return 1

	RETURN @RowsDeleted

END

GO



GRANT EXECUTE ON [VeeamMP].[ObjectAggregationDelete] TO OpsMgrReader

GO



IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[VeeamMP].[ObjectGroom]') AND type in (N'P'))

	BEGIN

		EXECUTE ('CREATE PROCEDURE [VeeamMP].[ObjectGroom] AS RETURN 1')

	END

GO



ALTER PROCEDURE VeeamMP.ObjectGroom

	@DatasetId				uniqueidentifier

	,@AggregationTypeId		tinyint

	,@TableGuid				uniqueidentifier

	,@CutoffDateTime		datetime

	,@MaxRowsToGroom		int

	,@RowsDeleted			int OUTPUT

AS

BEGIN

	SET NOCOUNT ON



	DECLARE

		@ErrorInd			bit

		,@ErrorMessage		nvarchar(4000)

		,@ErrorNumber		int

		,@ErrorSeverity		int

		,@ErrorState		int

		,@ErrorLine			int

		,@ErrorProcedure	nvarchar(256)

		,@ErrorMessageText	nvarchar(4000)



	SET @ErrorInd = 0



	DECLARE

		@TableName			sysname

		,@Statement			nvarchar(max)

		,@DebugLevel		tinyint

		,@SchemaName		sysname



	BEGIN TRY

		SELECT

			@DebugLevel = DebugLevel

			,@SchemaName = SchemaName

		FROM StandardDataset

		WHERE DatasetId = @DatasetId



		-- there are no dependent tables in RestoreSession data set

		DECLARE TableName_Cursor CURSOR FOR  

		SELECT BaseTableName + '_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', '')

		FROM StandardDatasetAggregationStorage

		WHERE (DatasetId = @DatasetId)

			AND (AggregationTypeId = @AggregationTypeId)

			AND (DependentTableInd = 0)



			OPEN TableName_Cursor;  

	FETCH NEXT FROM TableName_Cursor INTO @TableName ;  

	WHILE @@FETCH_STATUS = 0  

	BEGIN

		SET @Statement = 'DELETE TOP (' + CAST(@MaxRowsToGroom AS varchar(15)) + ')'

							+ ' FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName)

							+ ' WHERE ([DateTime] &lt; CONVERT(datetime, ''' + CONVERT(varchar(50), @CutoffDateTime, 120) + ''', 120))'

		EXECUTE (@Statement)



		SET @RowsDeleted = @@ROWCOUNT

		

		declare @DebugMessage nvarchar(max)

		SET @DebugMessage = 'Starting grooming statement  ' + @Statement  + ' rows affected ' +convert (nvarchar(max),@RowsDeleted)

            

            EXEC DebugMessageInsert

               @DatasetId = @DatasetId

              ,@MessageLevel = 3

              ,@MessageText = @DebugMessage

		  

      FETCH NEXT FROM TableName_Cursor INTO @TableName;  

	END;  

	CLOSE TableName_Cursor;  

	DEALLOCATE TableName_Cursor; 

	GRANT EXECUTE ON [VeeamMP].[ObjectGroom] TO OpsMgrReader

	END TRY

	BEGIN CATCH

		IF (@@TRANCOUNT &gt; 0)

			ROLLBACK TRAN



		SELECT

			@ErrorNumber = ERROR_NUMBER()

			,@ErrorSeverity = ERROR_SEVERITY()

			,@ErrorState = ERROR_STATE()

			,@ErrorLine = ERROR_LINE()

			,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')

			,@ErrorMessageText = ERROR_MESSAGE()



		SET @ErrorInd = 1

	END CATCH



	-- report error if any

	IF (@ErrorInd = 1)

	BEGIN

		IF (@DebugLevel &gt; 0)

		BEGIN

			DECLARE @DebugMessageText nvarchar(max)



			SET @DebugMessageText = N'Failed to groom data for Object data set. Error ' + CAST(@ErrorNumber AS varchar(15))

										+ ', Procedure ' + @ErrorProcedure

										+ ', Line ' + CAST(@ErrorLine AS varchar(15))

										+ ', Message: '+ @ErrorMessageText



			EXEC DebugMessageInsert

				@DatasetId = @DatasetId

				,@MessageLevel = 1

				,@MessageText = @DebugMessageText

		END



		DECLARE @AdjustedErrorSeverity int



		SET @AdjustedErrorSeverity = CASE

										WHEN @ErrorSeverity &gt; 18 THEN 18

										ELSE @ErrorSeverity

									END



		RAISERROR (777971002, @AdjustedErrorSeverity, 1

			,@ErrorNumber

			,@ErrorSeverity

			,@ErrorState

			,@ErrorProcedure

			,@ErrorLine

			,@ErrorMessageText

			)

	END



	RETURN @RowsDeleted

END

GO



GRANT EXECUTE ON [VeeamMP].[ObjectGroom] TO OpsMgrReader

GO-------

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[VeeamMP].[FullDiscovery]') AND type in (N'P'))

	BEGIN

		EXECUTE ('CREATE PROCEDURE [VeeamMP].[FullDiscovery] AS RETURN 1')

	END

GO



ALTER PROCEDURE VeeamMP.FullDiscovery

	@ManagementGroupId		uniqueidentifier

	,@DatasetId				uniqueidentifier

	,@DataXml				nvarchar(max)

AS

BEGIN

	SET NOCOUNT ON

	DECLARE @CheckExecResult int

			,@ManagementGroupRowId int



	-- get the management group

	SELECT @ManagementGroupRowId = ManagementGroupRowId

	FROM vManagementGroup 

	WHERE (ManagementGroupGuid = @ManagementGroupId) AND (WriterLoginName = SUSER_Name())



	-- access check

	EXEC @CheckExecResult = ManagementGroupAccessCheck @ManagementGroupId, @ManagementGroupRowId OUTPUT

	IF(@CheckExecResult &lt;&gt; 0) RETURN @CheckExecResult



	DECLARE

		@ErrorInd			bit

		,@ErrorNumber		int

		,@ErrorSeverity		int

		,@ErrorState		INT

		,@ErrorLine			int

		,@ErrorProcedure	nvarchar(256)

		,@ErrorMessageText	nvarchar(max)

		,@ResultingState	int



	SET @ErrorInd = 0

	SET @ResultingState = 1



	DECLARE

		@ExecResult			INT

		,@ExecResult1			int

		,@XmlDocHandle		INT

		,@XmlDocHandleDict		INT

		,@XmlDocHandleDictDN	INT



	BEGIN TRY

		-- Add it to VeeamBackupMP.SessionTaskStaging

		-- We may not find the alert in the Alert table because the alert is first saved

		-- in the SessionTaskStaging table and there is a separate process to move it to the 

		-- Alert table. Therefore, we have to move the parsing of DataXml in to the  

		-- scheduled aggregation function.

		DECLARE @PropertyDateTime DATETIME

			

		SET @PropertyDateTime=GETUTCDATE()

		

		EXEC @ExecResult = sp_xml_preparedocument @XmlDocHandle OUTPUT, @DataXml

		IF @ExecResult &lt;&gt; 0 RAISERROR(777971000, 16, 1, 'VeeamMP_FullDiscovery', @ExecResult)	 



		--create property dictionary

		DECLARE @prStringDictName NVARCHAR(MAX),

				@displayStringDictName NVARCHAR(MAX)

		

		

		SELECT 

			@prStringDictName=TSName

		FROM  OPENXML(@XmlDocHandle, '/Root/Item/Context/DataItem/Property',2) WITH (

				 			

				 			ID					NVARCHAR(MAX)        '@Name'

				 			,TSName				NVARCHAR(MAX)	'../Property[1]'

						

		)

		WHERE ID ='PnDictionary'



		SELECT 

			@displayStringDictName=TSName

		FROM  OPENXML(@XmlDocHandle, '/Root/Item/Context/DataItem/Property',2) WITH (

				 			

				 			ID					NVARCHAR(MAX)        '@Name'

				 			,TSName				NVARCHAR(MAX)	'../Property[1]'

						

		)

		WHERE ID ='DNDictionary'



		SET @prStringDictName='&lt;Root&gt;'+@prStringDictName+'&lt;/Root&gt;'

		SET @displayStringDictName='&lt;Root&gt;'+@displayStringDictName+'&lt;/Root&gt;'

		EXEC @ExecResult = sp_xml_preparedocument @XmlDocHandleDict OUTPUT,@prStringDictName



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

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

		CREATE TABLE #PropertyNameDictionary

		(

			ID INT NOT NULL  IDENTITY(1, 1),

			[KEY] NVARCHAR(MAX),

			[value] NVARCHAR(MAX)		

		)

		CREATE TABLE #PropertyDisplayNameDictionary

		(

			ID INT NOT NULL  IDENTITY(1, 1),

			[KEY] NVARCHAR(MAX),

			[value] NVARCHAR(MAX)		

		)



		INSERT INTO #PropertyNameDictionary

		(

			[KEY], 

			[value]	

		)

		SELECT 

			[KEY],

			[value]

		FROM  OPENXML(@XmlDocHandleDict, '/Root/pn',2) WITH (

				 			

				 			[KEY]					NVARCHAR(MAX)        '@key'

				 			,[value]					NVARCHAR(MAX)        '@value'



		)

		EXEC @ExecResult1 = sp_xml_preparedocument @XmlDocHandleDictDN OUTPUT,@displayStringDictName

		INSERT INTO #PropertyDisplayNameDictionary

		(

			[KEY], 

			[value]	

		)

		SELECT 

			[KEY],

			[value]

		FROM  OPENXML(@XmlDocHandleDictDN, '/Root/pn',2) WITH (

				 			

				 			[KEY]					NVARCHAR(MAX)        '@key'

				 			,[value]					NVARCHAR(MAX)        '@value'



		)

		

		INSERT INTO VeeamMP.Dictionary

		(

			PropertyName,

			PropertyDisplayName

		)

		SELECT

			pname.[value], 

			pdname.[value] 

		FROM 

		#PropertyNameDictionary AS pname

			INNER JOIN #PropertyDisplayNameDictionary AS pdname ON pdname.[KEY] = pname.[KEY]

		WHERE pname.[value] NOT IN (SELECT VeeamMP.Dictionary.PropertyName

		                              FROM VeeamMP.Dictionary) 

		

		BEGIN TRAN

		SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

			;WITH cte(ID, TSName,TDName,SName, DName, SD, [property]) 

			AS

			(

				SELECT 

					ID,

					TSName, 

					TDName, 

					SName, 

					DName, 

					SD,

					CONVERT (XML, '&lt;Root&gt;' + [property] + '&lt;/Root&gt;') 

				FROM  OPENXML(@XmlDocHandle, '/Root/Item/Context/DataItem/Property',2) WITH (

				 			

				 					ID					NVARCHAR(MAX)        '@Name'

				 					,TSName				NVARCHAR(MAX)	'../Property[1]'

									,TDName				NVARCHAR(MAX)	'../Property[2]'

									,SName				NVARCHAR(MAX)	'../Property[3]'

									,DName				NVARCHAR(MAX)	'../Property[4]'

									,SD					NVARCHAR(MAX)	'../Property[5]'

									,property			NVARCHAR(MAX)	'../Property[6]'

				)

				WHERE ID ='property'

			),

			propertyList(TSName,TDName,SName, DName, SD, propertyValue, propertyKey)

			AS

			(

				SELECT

					c.TSName,

					c.TDName,

					c.SName,

					c.DName,

					c.SD,

					COALESCE(f.b.value('(p)[1]','NVARCHAR(MAX)'),CAST([property].query('data(/Root/p)') AS NVARCHAR(MAX))),

					COALESCE(f.b.value('(p/@k)[1]','NVARCHAR(MAX)'),'collector' ) 

					--[property].query('data(/Root/pr/p)')

				FROM cte AS c

				--INNER JOIN #PropertyNameDictionary AS pdn ON 

				OUTER APPLY c.[property].nodes('/Root/pr') f(b)

			)

			INSERT INTO VeeamMP.MPStaging

			(

				 ObjectTypeSystemName						

				,ObjectTypeDisplayName					

				,ObjectTypePropertySystemName			

				,ObjectTypePropertyDefaultName																	

				,ObjectSystemName

				,ObjectDisplayName	

				,ObjectCreatedTime

				,ObjectPropertyValue					    																															

				,ObjectPropertyValueFromDateTime

				,StageDiscovery

				,ManagementGroupId		    			

			)

			SELECT 

			DISTINCT 

				pl.TSName, 

				pl.TDName,

				COALESCE(psd1.PropertyName,psd.[value],pl.propertyKey),

				COALESCE(psd1.PropertyDisplayName,psd.[value],pl.propertyKey),

				pl.SName,

				pl.DName,

				@PropertyDateTime,

				pl.propertyValue,

				@PropertyDateTime,

				pl.SD,

				@ManagementGroupId

			FROM propertyList AS pl

					LEFT JOIN #PropertyNameDictionary AS psd ON psd.[KEY]=pl.propertyKey

			LEFT JOIN VeeamMP.Dictionary AS psd1 ON psd1.PropertyName=psd.[value]

		COMMIT  TRAN

	END TRY

	BEGIN CATCH

		IF (@@TRANCOUNT &gt; 0)

			ROLLBACK TRAN



		SELECT

			@ErrorNumber = ERROR_NUMBER()

			,@ErrorSeverity = ERROR_SEVERITY()

			,@ErrorState = ERROR_STATE()

			,@ErrorLine = ERROR_LINE()

			,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')

			,@ErrorMessageText = ERROR_MESSAGE()



		SET @ErrorInd = 1

	END CATCH



	IF (@XmlDocHandle IS NOT NULL)

		EXEC sp_xml_removedocument @XmlDocHandle



	-- report error if any

	IF (@ErrorInd = 1)

	BEGIN

		DECLARE @AdjustedErrorSeverity int



		SET @AdjustedErrorSeverity = CASE

										WHEN @ErrorSeverity &gt; 18 THEN 18

										ELSE @ErrorSeverity

									END



		RAISERROR (777971002, @AdjustedErrorSeverity, @ResultingState

			,@ErrorNumber

			,@ErrorSeverity

			,@ErrorState

			,@ErrorProcedure

			,@ErrorLine

			,@ErrorMessageText

		)

	END

END

GO



GRANT EXECUTE ON VeeamMP.FullDiscovery TO OpsMgrReader

GO

---------------------------------------------------------------------------------------------

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[VeeamMP].[ObjectProcessStaging]') AND type in (N'P'))

	BEGIN

		EXECUTE ('CREATE PROCEDURE [VeeamMP].[ObjectProcessStaging] AS RETURN 1')

	END

GO



ALTER PROCEDURE [VeeamMP].[ObjectProcessStaging]

	@DatasetId								uniqueidentifier

AS

BEGIN

	SET NOCOUNT ON

	DECLARE

		@ErrorInd							bit

		,@ErrorNumber						int

		,@ErrorSeverity						int

		,@ErrorState						int

		,@ErrorLine							int

		,@ErrorProcedure					nvarchar(256)

		,@ErrorMessageText					nvarchar(4000)

	

	SET @ErrorInd = 0

	DECLARE	

		-- Configuration variables.

		-- Maximum age of data to keep in table SessionTaskStaging.

		@MaxDataAgeDays	int

		-- Maximum number of rows to process per single run of this stored procedure.

		-- Diagnostic variables.

		,@DebugLevel						int

		,@DebugMessage						nvarchar(4000)

		,@OperationStartedDateTime			datetime

		,@OperationDurationMs				bigint

		,@RowsDeleted						int

		,@Statement							nvarchar(max)

		,@SchemaName						nvarchar(max)

		,@TableGuid							varchar(50)	

		,@FullTableGuid						varchar(50)		

		,@StoredTableGuid					varchar(50)	

		,@StoredInsertTableName				nvarchar(90)

		,@RowId								INT

		,@ManagementGroupId					UNIQUEIDENTIFIER

		--,@DatasetId		UNIQUEIDENTIFIER

		

		--SET @DatasetId='A5A8F603-B62D-4AC3-9E36-A87F58703B0F'

	BEGIN TRY

	        BEGIN TRAN

		DECLARE @PropertyDateTime DATETIME

			

		SET @PropertyDateTime=GETUTCDATE()

		

		SELECT @MaxDataAgeDays = MaxDataAgeDays

		FROM dbo.StandardDatasetAggregation WHERE DatasetId = @DatasetId AND AggregationTypeId = 0;

		-- Delete the outdated records using the same grooming settings as for Staging table.

		DELETE FROM VeeamMP.MPStaging WHERE [DateTime]  &lt; DATEADD(day, - @MaxDataAgeDays, @PropertyDateTime);

		SET @RowsDeleted = @@ROWCOUNT;



		IF((@DebugLevel &gt; 2) AND (@RowsDeleted &gt; 0))

		BEGIN

			SET @DebugMessage = 'Groomed table VeeamMP.MPStaging by ' + CAST(@RowsDeleted as varchar(16)) + ' records.';



			EXEC DebugMessageInsert

				@DatasetId = @DatasetId

				,@MessageLevel = 3

				,@MessageText = @DebugMessage

		END				

		IF (@DebugLevel &gt; 2)

		BEGIN

			SET @OperationStartedDateTime = GETDATE();

			SET @OperationDurationMs = 0;

			

			SET @DebugMessage = 'Started stage processing of VeeamMP.MPStaging table.';



			EXEC DebugMessageInsert

				@DatasetId = @DatasetId

				,@MessageLevel = 3

				,@MessageText = @DebugMessage

				,@OperationDurationMs = @OperationDurationMs;

		END

		if object_id('tempdb..#oldobj') is not null drop table #oldobj

		if object_id('tempdb..#newobj') is not null drop table #newobj

		if object_id('tempdb..#hosts') is not null drop table #hosts

		if object_id('tempdb..#objectTableGuid') is not null drop table #objectTableGuid

		if object_id('tempdb..#ObjectStagingBatch') is not null drop table #ObjectStagingBatch

		if object_id('tempdb..#DiscoveryTimes') is not null drop table #DiscoveryTimes

		

		CREATE TABLE #DiscoveryTimes

		(

			ID		   INT			NOT NULL  IDENTITY(1, 1),

			[Datetime] DATETIME,

			ObjectTypeSystemName NVARCHAR(512)	

		)

		

			INSERT INTO #DiscoveryTimes

			(

				[Datetime],

				ObjectTypeSystemName

			)

			SELECT 

				DISTINCT hsm.ObjectCreatedTime,

						 hsm.ObjectTypeSystemName 

			FROM VeeamMP.MPStaging AS hsm

			ORDER BY hsm.ObjectCreatedTime 

			

			DECLARE @DtRowId INT,

					@CurrentDatetime DATETIME,

					@CurrentObjectType NVARCHAR(512)

			SET @DtRowId = -1	

			WHILE EXISTS(

						SELECT TOP(1)

								dt.ID

							FROM #DiscoveryTimes AS dt

							WHERE dt.ID &gt; @DtRowId

						)

						BEGIN

							SELECT TOP(1)

								@DtRowId=dt.ID,

								@CurrentDatetime = dt.[Datetime],

								@CurrentObjectType = dt.ObjectTypeSystemName

							FROM #DiscoveryTimes dt

								WHERE dt.ID &gt; @DtRowId

									if object_id('tempdb..#VMSnapshots') IS NOT NULL drop table #VMSnapshots

									if object_id('tempdb..#NewVMSnapshotObj') IS NOT NULL drop table #NewVMSnapshotObj

									if object_id('tempdb..#oldobj') is not null drop table #oldobj

									if object_id('tempdb..#newobj') is not null drop table #newobj

									if object_id('tempdb..#hosts') is not null drop table #hosts

									if object_id('tempdb..#objectTableGuid') is not null drop table #objectTableGuid

									if object_id('tempdb..#ObjectStagingBatch') is not null drop table #ObjectStagingBatch

											

									CREATE TABLE #ObjectStagingBatch

									(		 

											 ObjectRowId								int

											,ObjectTypeSystemName						nvarchar(max)

											,ObjectTypeDisplayName						nvarchar(max)

											,ObjectTypePropertySystemName				nvarchar(max)

											,ObjectTypePropertyDefaultName				nvarchar(max)	

											,ObjectSystemName							nvarchar(max)		

											,ObjectDisplayName							nvarchar(max)	

											,ObjectCreatedTime							datetime			

											,ObjectDeletedTime							datetime

											,[DateTime]									datetime

											,ObjectPropertyValue						nvarchar(max)

											,ObjectPropertyValueFromDateTime			nvarchar(max)

											,ObjectPropertyValueToDateTime				nvarchar(max)

											,StageDiscovery								nvarchar(256)

											,ManagementGroupId							uniqueidentifier	

									)

									

										INSERT INTO #ObjectStagingBatch

										(

											ObjectRowId,

											ObjectTypeSystemName, 

											ObjectTypeDisplayName, 

											ObjectTypePropertySystemName,

											ObjectTypePropertyDefaultName, 

											ObjectSystemName, 

											ObjectDisplayName,

											ObjectCreatedTime, 

											ObjectDeletedTime, 

											[DateTime], 

											ObjectPropertyValue,

											ObjectPropertyValueFromDateTime, 

											ObjectPropertyValueToDateTime, 

											StageDiscovery,

											ManagementGroupId

										)

										SELECT

											hsm.ObjectRowId, 

											hsm.ObjectTypeSystemName, 

											hsm.ObjectTypeDisplayName,

											hsm.ObjectTypePropertySystemName, 

											hsm.ObjectTypePropertyDefaultName,

											hsm.ObjectSystemName, 

											hsm.ObjectDisplayName, 

											hsm.ObjectCreatedTime,

											hsm.ObjectDeletedTime, 

											hsm.[DateTime], 

											hsm.ObjectPropertyValue,

											hsm.ObjectPropertyValueFromDateTime, 

											hsm.ObjectPropertyValueToDateTime,

											hsm.StageDiscovery,

											hsm.ManagementGroupId 

										FROM VeeamMP.MPStaging AS hsm

															WHERE hsm.ObjectCreatedTime = @CurrentDatetime

															AND	hsm.ObjectTypeSystemName=@CurrentObjectType

									IF EXISTS(SELECT * FROM #ObjectStagingBatch WHERE StageDiscovery='DeleteSnapshotsByVM')

									BEGIN

										;WITH host(hostName, ManagementGroupId, ObjectTypeSystemName)

											AS

											(

												SELECT 

													osb.ObjectPropertyValue, 

													osb.ManagementGroupId,

													osb.ObjectTypeSystemName 

												FROM #ObjectStagingBatch  AS osb

												WHERE osb.ObjectTypePropertySystemName='collector' AND  StageDiscovery='DeleteSnapshotsByVM'	 	

											)

										UPDATE VeeamMP.vObject	

											SET

												Deletedtime = @PropertyDateTime

											WHERE SystemName IN 

											(

												SELECT 

													vo.SystemName

												FROM VeeamMP.vObject AS vo

												INNER JOIN VeeamMP.vObjectPropertyValue AS vopv ON vopv.ObjectGuid=vo.ObjectGuid

												INNER JOIN VeeamMP.vObjectTypeProperty AS votp ON votp.ObjectTypePropertyId=vopv.ObjectTypePropertyId

												INNER JOIN host AS h ON h.hostName=vopv.[Value] 

												INNER JOIN VeeamMP.vObjectType AS vot ON vot.SystemName=h.ObjectTypeSystemName 

												WHERE votp.SystemName='collector' AND vo.Deletedtime IS NULL AND vo.ManagementGroupId=h.ManagementGroupId

																										AND vopv.ToDatetime IS NULL

											) 

				 		

				 							DELETE FROM #ObjectStagingBatch 

				 							WHERE StageDiscovery='DeleteSnapshotsByVM' AND ManagementGroupId IN (SELECT ManagementGroupId FROM #ObjectStagingBatch WHERE StageDiscovery='DeleteSnapshotsByVM')

				 							DELETE FROM VeeamMP.MPStaging 

				 							WHERE StageDiscovery='DeleteSnapshotsByVM' AND	ManagementGroupId IN (SELECT ManagementGroupId FROM VeeamMP.MPStaging WHERE StageDiscovery='DeleteSnapshotsByVM') 

									END

									IF EXISTS(SELECT * FROM #ObjectStagingBatch WHERE StageDiscovery='DeleteByMG')

									BEGIN

										UPDATE vo

										SET

											Deletedtime = @PropertyDateTime

										FROM VeeamMP.vObject AS vo

											INNER JOIN VeeamMP.vObjectType AS vot ON vot.ObjectTypeId=vo.TypeId

											INNER JOIN #ObjectStagingBatch b ON b.ObjectTypeSystemName = vot.SystemName	 

																		AND vo.ManagementGroupId=b.ManagementGroupId

										WHERE vo.Deletedtime IS NULL AND b.StageDiscovery='DeleteByMG' 

												AND b.ObjectTypeSystemName=@CurrentObjectType

				

										DELETE FROM VeeamMP.MPStaging

										FROM VeeamMP.MPStaging AS hsm -- Second FROM clause is used for filtering the records to be deleted.

											INNER JOIN #ObjectStagingBatch AS batch ON hsm.ObjectRowId = batch.ObjectRowId AND batch.StageDiscovery='DeleteByMG' 

										DELETE FROM #ObjectStagingBatch

										WHERE StageDiscovery='DeleteByMG' 

									END	

									ELSE

										IF EXISTS(SELECT * FROM VeeamMP.MPStaging WHERE StageDiscovery='DeleteByHost')

										BEGIN

											;WITH host(hostName, ManagementGroupId, ObjectTypeSystemName)

											AS

											(

												SELECT 

													osb.ObjectPropertyValue, 

													osb.ManagementGroupId,

													osb.ObjectTypeSystemName 

												FROM #ObjectStagingBatch  AS osb

												WHERE osb.ObjectTypePropertySystemName='host' AND  StageDiscovery='DeleteByHost'	 	

											)

											UPDATE VeeamMP.vObject	

											SET

												Deletedtime = @PropertyDateTime

											WHERE SystemName IN 

											(

												SELECT 

													vo.SystemName

												FROM VeeamMP.vObject AS vo

												INNER JOIN VeeamMP.vObjectPropertyValue AS vopv ON vopv.ObjectGuid=vo.ObjectGuid

												INNER JOIN VeeamMP.vObjectTypeProperty AS votp ON votp.ObjectTypePropertyId=vopv.ObjectTypePropertyId

												INNER JOIN host AS h ON h.hostName=vopv.[Value] 

												INNER JOIN VeeamMP.vObjectType AS vot ON vot.SystemName=h.ObjectTypeSystemName 

												WHERE votp.SystemName='host' AND vo.Deletedtime IS NULL AND vo.ManagementGroupId=h.ManagementGroupId

																										AND vopv.ToDatetime IS NULL

											) 

				 		

				 							DELETE FROM #ObjectStagingBatch 

				 							WHERE StageDiscovery='DeleteByHost' AND ManagementGroupId IN (SELECT ManagementGroupId FROM #ObjectStagingBatch WHERE StageDiscovery='DeleteByHost')

				 							DELETE FROM VeeamMP.MPStaging 

				 							WHERE StageDiscovery='DeleteByHost' AND	ManagementGroupId IN (SELECT ManagementGroupId FROM VeeamMP.MPStaging WHERE StageDiscovery='DeleteByHost')	

										END	

				 		

									-- Get the debug level for RestoreSession dataset.

									SELECT @DebugLevel = DebugLevel ,

											@SchemaName = SchemaName

									FROM dbo.StandardDataset WHERE DatasetId = @DatasetId;



									EXEC dbo.StandardDatasetGetInsertTableGuid @DatasetId, 0, @FullTableGuid OUTPUT

									SET @TableGuid = REPLACE(CAST(@FullTableGuid AS varchar(50)), '-', '')



									--adding new types in DS

									SET @Statement = 'INSERT INTO VeeamMP.ObjectType_' + CAST(@TableGuid AS varchar(300)) +

									'(

										SystemName, 

										DisplayName,

										TableGuid,

										ManagementGroupId

									)

									SELECT 

										DISTINCT hsm.ObjectTypeSystemName,

												 hsm.ObjectTypeDisplayName, '

										+''''+ CAST(@FullTableGuid AS varchar(300)) +''''+','+

										'hsm.ManagementGroupId 

									 FROM #ObjectStagingBatch AS hsm

									LEFT OUTER JOIN VeeamMP.vObjectType AS vot ON hsm.ObjectTypeSystemName=vot.SystemName

									WHERE vot.SystemName IS NULL'



									EXECUTE (@Statement)



									--adding new propert in DS

									SET @Statement = 'INSERT INTO VeeamMP.ObjectTypeProperty_'+ CAST(@TableGuid AS varchar(300)) +

									'(

										  ObjectTypeId,

										  SystemName,

										  DefaultName,

										  TableGuid,

										  ManagementGroupId

									)

									SELECT 

										DISTINCT

												 vot.ObjectTypeId,

												 hsm.ObjectTypePropertySystemName,

												 hsm.ObjectTypePropertyDefaultName, '

												 +''''+  CAST(@FullTableGuid AS varchar(300))  +''''+','+

										'hsm.ManagementGroupId  

									 FROM #ObjectStagingBatch AS hsm

									INNER JOIN VeeamMP.vObjectType AS vot ON vot.SystemName = hsm.ObjectTypeSystemName

									LEFT OUTER JOIN VeeamMP.vObjectTypeProperty AS otp ON hsm.ObjectTypePropertySystemName=otp.SystemName AND otp.ObjectTypeId=vot.ObjectTypeId 

									WHERE otp.SystemName IS NULL '

 

									EXECUTE (@Statement)



									CREATE TABLE #VMSnapshots

									(

										ObjectGuid UNIQUEIDENTIFIER	

									)

			

									CREATE TABLE #NewVMSnapshotObj

									(

										ObjectTypeId INT,

										ObjectSystemName NVARCHAR(256),

										ObjectDisplayName NVARCHAR(256),

										ManagementGroupId uniqueidentifier

									)

	

									INSERT INTO #VMSnapshots

									(

										ObjectGuid

									)

									SELECT DISTINCT

											vopv.ObjectGuid 

									FROM VeeamMP.vObjectPropertyValue AS vopv

										INNER JOIN #ObjectStagingBatch AS osb ON vopv.[Value]=osb.ObjectPropertyValue

										INNER JOIN VeeamMP.vObjectTypeProperty AS votp ON votp.ObjectTypePropertyId = vopv.ObjectTypePropertyId

									WHERE votp.SystemName='collector'

	

									DELETE FROM VeeamMP.vObject

									WHERE ObjectGuid IN (SELECT ObjectGuid FROM #VMSnapshots)

									DELETE FROM VeeamMP.vObjectPropertyValue

									WHERE ObjectGuid IN (SELECT ObjectGuid FROM #VMSnapshots) 

	

									--adding new object in DS

									INSERT INTO #NewVMSnapshotObj

									(

										ObjectTypeId, 

										ObjectSystemName, 

										ObjectDisplayName,

										ManagementGroupId

									)

									SELECT DISTINCT

										vot.ObjectTypeId,

										hsm.ObjectSystemName,

										hsm.ObjectDisplayName,

										hsm.ManagementGroupId

									FROM #ObjectStagingBatch AS hsm

									INNER JOIN VeeamMP.vObjectType AS vot ON vot.SystemName = hsm.ObjectTypeSystemName

									WHERE hsm.StageDiscovery = 'U'

	

			

									SET @Statement = 'INSERT INTO VeeamMP.Object_'+ CAST(@TableGuid AS varchar(300)) +

									'(

										ObjectGuid,

										TypeId,

										SystemName,

										DisplayName,

										[TimeCreated],

										[DateTime],

										TableGuid,

										ManagementGroupId

									)

									SELECT

										NEWID(),

										ObjectTypeId, 

										ObjectSystemName, 

										ObjectDisplayName,

										'+''''+CAST(@PropertyDateTime AS varchar(300))+''''+', '

											+''''+  CAST(@PropertyDateTime AS varchar(300))  +''''+','+ 

											+''''+  CAST(@FullTableGuid AS varchar(300))  +''''+','+  

										'ManagementGroupId 

										FROM #NewVMSnapshotObj'



									EXECUTE (@Statement)

		

									SET @Statement = 'INSERT INTO VeeamMP.ObjectPropertyValue_' + CAST(@TableGuid AS varchar(300)) +

									'(

										ObjectPropertyValueGuid, 

										ObjectTypePropertyId, 

										[Value], 

										ObjectGuid, 

										[DateTime],

										[TimeCreated],

										ToDatetime,

										TableGuid,

										ManagementGroupId

									)

									SELECT

										NEWID(),

										votp.ObjectTypePropertyId,

										hsm.ObjectPropertyValue,

										vo.ObjectGuid,

										'+''''+ CAST(@PropertyDateTime AS varchar(300))+''''+','+

										+''''+  CAST(@PropertyDateTime AS varchar(300))  +''''+',   

										NULL, '

											+''''+  CAST(@FullTableGuid AS varchar(300))  +''''+','+  

										'hsm.ManagementGroupId   

										FROM #NewVMSnapshotObj AS n

									INNER JOIN VeeamMP.vObject AS vo ON vo.SystemName=n.ObjectSystemName

									INNER JOIN #ObjectStagingBatch AS hsm ON hsm.ObjectSystemName=vo.SystemName

									INNER JOIN VeeamMP.vObjectTypeProperty AS votp ON hsm.ObjectTypePropertySystemName=votp.SystemName	AND votp.ObjectTypeId=vo.TypeId

									WHERE vo.ManagementGroupId=hsm.ManagementGroupId '



									EXECUTE (@Statement)

		

									DELETE FROM VeeamMP.MPStaging

									FROM VeeamMP.MPStaging AS hsm -- Second FROM clause is used for filtering the records to be deleted.

										INNER JOIN #ObjectStagingBatch AS batch ON hsm.ObjectRowId = batch.ObjectRowId 

									WHERE batch.StageDiscovery='U'

		

									DELETE FROM #ObjectStagingBatch WHERE StageDiscovery='U'



									CREATE TABLE #hosts

									(

										HostName NVARCHAR(MAX),

										ManagementGroupId uniqueidentifier	

									)



									CREATE TABLE #oldobj

									(

										ObjectTypeId INT,

										ObjectSystemName NVARCHAR(256),

										ObjectDisplayName NVARCHAR(256),

										ManagementGroupId uniqueidentifier

									)



									CREATE TABLE #newobj

									(

										ObjectTypeId INT,

										ObjectSystemName NVARCHAR(256),

										ObjectDisplayName NVARCHAR(256),

										ManagementGroupId uniqueidentifier

									)



									INSERT INTO #hosts

									(

										HostName,

										ManagementGroupId

									)

									SELECT 

										DISTINCT hsm.ObjectPropertyValue,

												 hsm.ManagementGroupId 

									FROM #ObjectStagingBatch AS hsm

									WHERE hsm.ObjectTypePropertySystemName='host' AND hsm.StageDiscovery='host'



									CREATE TABLE #objectTableGuid

									(

	

										ID				INT 					NOT NULL  IDENTITY(1, 1),

										ObjectId		INT,

										SystemName		NVARCHAR(MAX),

										TableGuid		UNIQUEIDENTIFIER,

										ManagementGroupId uniqueidentifier

									)

									DECLARE @ObjectId INT

									--deleting vm by host stage

									;WITH existVmByHost(ID ,SystemName, TableGuid, ManagementGroupId)

									AS

									(

										SELECT

											vo.ObjectId,

											vo.SystemName,

											vo.TableGuid,

											vo.ManagementGroupId

										FROM VeeamMP.vObject AS vo

										INNER JOIN VeeamMP.vObjectType AS vot ON vo.TypeId=vot.ObjectTypeId

										INNER JOIN VeeamMP.vObjectPropertyValue AS vopv ON vo.ObjectGuid=vopv.ObjectGuid

										INNER JOIN VeeamMP.vObjectTypeProperty AS votp ON votp.ObjectTypePropertyId = vopv.ObjectTypePropertyId

										INNER JOIN #hosts AS h ON h.HostName=vopv.[Value]

										WHERE votp.SystemName='host' AND vo.ManagementGroupId = h.ManagementGroupId	AND vot.SystemName=@CurrentObjectType

									),

									hostStageVm(ObjectSystemName, ManagementGroupId)

									AS

									(

										SELECT 

											DISTINCT hsm.ObjectSystemName,

													 hsm.ManagementGroupId

										FROM #ObjectStagingBatch AS hsm

										WHERE  hsm.StageDiscovery='host'

									)

									INSERT INTO #objectTableGuid

									(

										ObjectId, 

										SystemName, 

										TableGuid,

										ManagementGroupId

									)

									SELECT

										exvm.ID,

										exvm.SystemName,

										exvm.TableGuid,

										exvm.ManagementGroupId

									FROM existVmByHost AS exvm

										LEFT OUTER JOIN hostStageVm AS o ON	exvm.SystemName=o.ObjectSystemName

									WHERE o.ObjectSystemName IS NULL



									SET @RowId = -1	

									WHILE EXISTS(

												SELECT TOP(1)

													tgb.ID

													FROM #objectTableGuid tgb

													WHERE tgb.ID &gt; @RowId

												)

												BEGIN

													SELECT TOP(1)

														@StoredTableGuid = tgb.TableGuid

														,@RowId = tgb.ID

														,@ObjectId=tgb.ObjectId

														,@ManagementGroupId=tgb.ManagementGroupId

														FROM #objectTableGuid tgb

														WHERE tgb.ID &gt; @RowId

													SET @StoredInsertTableName = QUOTENAME(@SchemaName) + '.' +  QUOTENAME('Object' + '_' + REPLACE(CAST(@StoredTableGuid AS varchar(50)), '-', ''))

													SET @Statement = 'UPDATE ' + CAST(@StoredInsertTableName AS varchar(max)) +  

													' SET Deletedtime = '+''''+CAST(@PropertyDateTime AS VARCHAR(300))+''''+

													 ' WHERE	ObjectId = ' + CAST(@ObjectId AS NVARCHAR(256)) +' AND ManagementGroupId=' +''''+ CAST(@ManagementGroupId AS NVARCHAR(512))+''''



													EXECUTE (@Statement)

												END



									----------------------------------

									--object removed from deleted 

									DELETE FROM #objectTableGuid



									INSERT INTO #objectTableGuid

									(

										ObjectId, 

										SystemName, 

										TableGuid,

										ManagementGroupId

									)

									SELECT

									DISTINCT

										vo.ObjectId, 

										vo.SystemName,

										vo.TableGuid,

										vo.ManagementGroupId 

									FROM VeeamMP.vObject AS vo

									INNER JOIN VeeamMP.vObjectType AS vot ON vo.TypeId=vot.ObjectTypeId

									INNER JOIN #ObjectStagingBatch AS hsm ON hsm.ObjectSystemName = vo.SystemName

									WHERE vo.Deletedtime IS NOT NULL AND vo.ManagementGroupId=hsm.ManagementGroupId

																	AND vot.SystemName=@CurrentObjectType



									SET @RowId = -1	

									WHILE EXISTS(

												SELECT TOP(1)

													tgb.ID

													FROM #objectTableGuid tgb

													WHERE tgb.ID &gt; @RowId

												)

												BEGIN

													SELECT TOP(1)

														@StoredTableGuid = tgb.TableGuid

														,@RowId = tgb.ID

														,@ObjectId= tgb.ObjectId

														,@ManagementGroupId=tgb.ManagementGroupId

														FROM #objectTableGuid tgb

														WHERE tgb.ID &gt; @RowId

													SET @StoredInsertTableName = QUOTENAME(@SchemaName) + '.' +  QUOTENAME('Object' + '_' + REPLACE(CAST(@StoredTableGuid AS varchar(50)), '-', ''))

													SET @Statement = 'UPDATE ' + CAST(@StoredInsertTableName AS varchar(max)) +  

													' SET Deletedtime = NULL

													 WHERE ObjectId='+CAST(@ObjectId AS NVARCHAR(256)) +' AND ManagementGroupId=' +''''+ CAST(@ManagementGroupId AS NVARCHAR(512))+''''



													EXECUTE (@Statement)

												END

									-----------------------------

									INSERT INTO #oldobj

									(

										ObjectTypeId,

										ObjectSystemName,

										ObjectDisplayName,

										ManagementGroupId

									)

									SELECT DISTINCT

										vot.ObjectTypeId,

										hsm.ObjectSystemName,

										hsm.ObjectDisplayName,

										hsm.ManagementGroupId

									FROM #ObjectStagingBatch AS hsm

									LEFT OUTER JOIN VeeamMP.vObject AS vo ON hsm.ObjectSystemName=vo.SystemName

									INNER JOIN VeeamMP.vObjectType AS vot ON vot.SystemName = hsm.ObjectTypeSystemName

									WHERE vo.SystemName IS NOT NULL AND vo.ManagementGroupId=hsm.ManagementGroupId 

																	AND vot.SystemName=@CurrentObjectType



									--set deleted time if necessary

									DELETE FROM #objectTableGuid

									IF EXISTS(SELECT TOP(1) * FROM #ObjectStagingBatch AS hsm	WHERE hsm.StageDiscovery='stage2' )

									BEGIN

										;WITH MPStagingStageTwoOnly(ObjectRowId, ObjectTypeSystemName, ObjectTypeDisplayName, ObjectTypePropertySystemName, ObjectTypePropertyDefaultName, ObjectSystemName		

																		,ObjectDisplayName, ObjectCreatedTime, ObjectDeletedTime, [DateTime], ObjectPropertyValue, ObjectPropertyValueFromDateTime, 

																		ObjectPropertyValueToDateTime, StageDiscovery,ManagementGroupId)

										AS

										(

											SELECT

											 *

											FROM #ObjectStagingBatch AS hsm

											WHERE hsm.StageDiscovery='stage2'

										)

										INSERT INTO #objectTableGuid

										(

											ObjectId, 

											SystemName, 

											TableGuid,

											ManagementGroupId

										)

										SELECT

											DISTINCT vo.ObjectId, 

													vo.SystemName,

													vo.TableGuid,

													vo.ManagementGroupId

										FROM VeeamMP.vObject AS vo

										INNER JOIN VeeamMP.vObjectType AS vot ON vo.TypeId=vot.ObjectTypeId

										LEFT OUTER JOIN MPStagingStageTwoOnly AS hsm ON hsm.ObjectSystemName = vo.SystemName

										WHERE hsm.ObjectSystemName IS NULL 

										  AND vot.SystemName=@CurrentObjectType 



										SET @RowId = -1	

										WHILE EXISTS(

													SELECT TOP(1)

														tgb.ID

														FROM #objectTableGuid tgb

														WHERE tgb.ID &gt; @RowId

													)

													BEGIN

														SELECT TOP(1)

															@StoredTableGuid = tgb.TableGuid

															,@RowId = tgb.ID

															,@ObjectId = tgb.ObjectId

															,@ManagementGroupId=tgb.ManagementGroupId

															FROM #objectTableGuid tgb

															WHERE tgb.ID &gt; @RowId

														SET @StoredInsertTableName = QUOTENAME(@SchemaName) + '.' +  QUOTENAME('Object' + '_' + REPLACE(CAST(@StoredTableGuid AS varchar(50)), '-', ''))



														SET @Statement = 'UPDATE ' + CAST(@StoredInsertTableName AS varchar(max)) +

														' SET Deletedtime ='+''''+ CAST(@PropertyDateTime AS VARCHAR(MAX))+''''+		

														' WHERE ObjectId='+CAST(@ObjectId AS NVARCHAR(MAX)) +' AND ManagementGroupId=' +''''+ CAST(@ManagementGroupId AS NVARCHAR(512))+''''

							

													EXECUTE (@Statement)  

													END

										-----------------------------

									END

									--adding new object in DS

									INSERT INTO #newobj

									(

										ObjectTypeId, 

										ObjectSystemName, 

										ObjectDisplayName,

										ManagementGroupId

									)

									SELECT DISTINCT

										vot.ObjectTypeId,

										hsm.ObjectSystemName,

										hsm.ObjectDisplayName,

										hsm.ManagementGroupId

									FROM #ObjectStagingBatch AS hsm

									LEFT OUTER JOIN VeeamMP.vObject AS vo ON hsm.ObjectSystemName=vo.SystemName

									INNER JOIN VeeamMP.vObjectType AS vot ON vot.SystemName = hsm.ObjectTypeSystemName

									WHERE vo.SystemName IS NULL	AND vo.Deletedtime IS NULL

			

									SET @Statement = 'INSERT INTO VeeamMP.Object_'+ CAST(@TableGuid AS varchar(300)) +

									'(

										ObjectGuid,

										TypeId,

										SystemName,

										DisplayName,

										[TimeCreated],

										[DateTime],

										TableGuid,

										ManagementGroupId

									)

									SELECT

										NEWID(),

										ObjectTypeId, 

										ObjectSystemName, 

										ObjectDisplayName,

										'+''''+CAST(@PropertyDateTime AS varchar(300))+''''+', '

										 +''''+  CAST(@PropertyDateTime AS varchar(300))  +''''+','+ 

										 +''''+  CAST(@FullTableGuid AS varchar(300))  +''''+','+  

										'ManagementGroupId 

									  FROM #newobj'



									EXECUTE (@Statement)



									SET @Statement = 'INSERT INTO VeeamMP.ObjectPropertyValue_' + CAST(@TableGuid AS varchar(300)) +

									'(

										ObjectPropertyValueGuid, 

										ObjectTypePropertyId, 

										[Value], 

										ObjectGuid, 

										[DateTime],

										[TimeCreated],

										ToDatetime,

										TableGuid,

										ManagementGroupId

									)

									SELECT

										NEWID(),

										votp.ObjectTypePropertyId,

										hsm.ObjectPropertyValue,

										vo.ObjectGuid,

										'+''''+ CAST(@PropertyDateTime AS varchar(300))+''''+','+

										+''''+  CAST(@PropertyDateTime AS varchar(300))  +''''+',   

										NULL, '

										 +''''+  CAST(@FullTableGuid AS varchar(300))  +''''+','+  

										'hsm.ManagementGroupId   

									  FROM #newobj AS n

									INNER JOIN VeeamMP.vObject AS vo ON vo.SystemName=n.ObjectSystemName

									INNER JOIN #ObjectStagingBatch AS hsm ON hsm.ObjectSystemName=vo.SystemName

									INNER JOIN VeeamMP.vObjectTypeProperty AS votp ON hsm.ObjectTypePropertySystemName=votp.SystemName	AND votp.ObjectTypeId=vo.TypeId

									WHERE vo.ManagementGroupId=hsm.ManagementGroupId '



									EXECUTE (@Statement)

									---------------------------



									DELETE FROM #objectTableGuid



									INSERT INTO #objectTableGuid

									(

										ObjectId, 

										SystemName, 

										TableGuid,

										ManagementGroupId

									)

									SELECT 

										vopv.ObjectPropertyValueId,

										hsm.ObjectPropertyValue,

										vopv.TableGuid,

										vopv.ManagementGroupId

									FROM VeeamMP.vObject AS vo

									INNER JOIN #oldobj AS o ON o.ObjectSystemName=vo.SystemName

									INNER JOIN #ObjectStagingBatch AS hsm ON hsm.ObjectSystemName=o.ObjectSystemName

									INNER JOIN VeeamMP.vObjectTypeProperty AS votp ON votp.SystemName=hsm.ObjectTypePropertySystemName

									INNER JOIN VeeamMP.vObjectPropertyValue AS vopv ON vopv.ObjectTypePropertyId=votp.ObjectTypePropertyId AND vopv.ObjectGuid=vo.ObjectGuid

									WHERE  vopv.[Value]!=hsm.ObjectPropertyValue AND  vopv.ToDatetime IS NULL AND hsm.ManagementGroupId=vopv.ManagementGroupId

			

									SET @Statement = 'INSERT INTO VeeamMP.ObjectPropertyValue_' + CAST(@TableGuid AS varchar(300)) +

									'(

										ObjectPropertyValueGuid, 

										ObjectTypePropertyId,

										[OldValue],

										[Value], 

										ObjectGuid, 

										[DateTime],

										[TimeCreated],

										ToDatetime,

										TableGuid,

										ManagementGroupId

									)

									SELECT

										DISTINCT NEWID(), 

										vopv.ObjectTypePropertyId,

										vopv.[Value],

										hsm.ObjectPropertyValue,

										vo.ObjectGuid,

										'+''''+ CAST(@PropertyDateTime AS varchar(300))+''''+','+

										+''''+  CAST(@PropertyDateTime AS varchar(300))  +''''+', 

										NULL,' 

										 +''''+  CAST(@FullTableGuid AS varchar(300))  +''''+','+  

										'hsm.ManagementGroupId  

									 FROM VeeamMP.vObject AS vo

									INNER JOIN #oldobj AS o ON o.ObjectSystemName=vo.SystemName

									INNER JOIN #ObjectStagingBatch AS hsm ON hsm.ObjectSystemName=o.ObjectSystemName

									INNER JOIN VeeamMP.vObjectTypeProperty AS votp ON votp.SystemName=hsm.ObjectTypePropertySystemName

									INNER JOIN VeeamMP.vObjectPropertyValue AS vopv ON vopv.ObjectTypePropertyId=votp.ObjectTypePropertyId AND vopv.ObjectGuid=vo.ObjectGuid

									WHERE  vopv.[Value]!=hsm.ObjectPropertyValue AND  vopv.ToDatetime IS NULL AND hsm.ManagementGroupId=vo.ManagementGroupId'



									UPDATE vopv	

									SET vopv.[DateTime] = @PropertyDateTime	

									FROM VeeamMP.vObjectPropertyValue AS vopv

										INNER JOIN VeeamMP.vObject AS vo ON vo.ObjectGuid = vopv.ObjectGuid

										INNER JOIN #oldobj AS o ON vo.SystemName = o.ObjectSystemName

									WHERE vopv.ToDatetime IS NULL

									

									UPDATE vo	

									SET vo.[DateTime] = @PropertyDateTime	

									FROM  VeeamMP.vObject AS vo  

										INNER JOIN #oldobj AS o ON vo.SystemName = o.ObjectSystemName

									WHERE vo.Deletedtime IS NULL

																					

									EXECUTE (@Statement)

									DECLARE @NewValue NVARCHAR(MAX),

										@ObjectPropertyValueId INT

									SET @RowId = -1	

									WHILE EXISTS(

												SELECT TOP(1)

													tgb.ID

													FROM #objectTableGuid tgb

													WHERE tgb.ID &gt; @RowId

												)

												BEGIN

													SELECT TOP(1)

														@StoredTableGuid = tgb.TableGuid

														,@RowId = tgb.ID

														,@ManagementGroupId=tgb.ManagementGroupId

														,@NewValue=tgb.SystemName

														,@ObjectPropertyValueId=tgb.ObjectId

														FROM #objectTableGuid tgb

														WHERE tgb.ID &gt; @RowId

													SET @StoredInsertTableName = QUOTENAME(@SchemaName) + '.' +  QUOTENAME('ObjectPropertyValue' + '_' + REPLACE(CAST(@StoredTableGuid AS varchar(50)), '-', ''))

													SET @Statement = 'UPDATE ' + CAST(@StoredInsertTableName AS varchar(max)) + 

													' SET ToDatetime = '+''''+ CAST(@PropertyDateTime AS VARCHAR(300))+''''+',

															NewValue ='+''''+ CAST(@NewValue AS VARCHAR(300))+''''+

												'	FROM #objectTableGuid as tgb		

													WHERE ObjectPropertyValueId = '+CAST(@ObjectPropertyValueId AS varchar(max))+' AND '+CAST(@StoredInsertTableName AS varchar(max))+'.ManagementGroupId=' +''''+ CAST(@ManagementGroupId AS NVARCHAR(512))+''''

													EXECUTE (@Statement)

												END



									DELETE FROM VeeamMP.MPStaging

										FROM VeeamMP.MPStaging AS hsm -- Second FROM clause is used for filtering the records to be deleted.

											INNER JOIN #ObjectStagingBatch AS batch ON hsm.ObjectRowId = batch.ObjectRowId

									if object_id('tempdb..#VMSnapshots') IS NOT NULL drop table #VMSnapshots

									if object_id('tempdb..#NewVMSnapshotObj') IS NOT NULL drop table #NewVMSnapshotObj

									if object_id('tempdb..#oldobj') is not null drop table #oldobj

									if object_id('tempdb..#newobj') is not null drop table #newobj

									if object_id('tempdb..#hosts') is not null drop table #hosts

									if object_id('tempdb..#objectTableGuid') is not null drop table #objectTableGuid

									if object_id('tempdb..#ObjectStagingBatch') is not null drop table #ObjectStagingBatch



						END



		COMMIT TRAN				

		if object_id('tempdb..#DiscoveryTimes') is not null drop table #DiscoveryTimes				

		END TRY

		BEGIN CATCH

		if object_id('tempdb..#VMSnapshots') IS NOT NULL drop table #VMSnapshots

		if object_id('tempdb..#NewVMSnapshotObj') IS NOT NULL drop table #NewVMSnapshotObj

		if object_id('tempdb..#oldobj') is not null drop table #oldobj

		if object_id('tempdb..#newobj') is not null drop table #newobj

		if object_id('tempdb..#hosts') is not null drop table #hosts

		if object_id('tempdb..#objectTableGuid') is not null drop table #objectTableGuid

		if object_id('tempdb..#ObjectStagingBatch') is not null drop table #ObjectStagingBatch

		if object_id('tempdb..#DiscoveryTimes') is not null drop table #DiscoveryTimes	

		IF (@@TRANCOUNT &gt; 0)

			ROLLBACK TRAN



		SELECT

			@ErrorNumber = ERROR_NUMBER()

			,@ErrorSeverity = ERROR_SEVERITY()

			,@ErrorState = ERROR_STATE()

			,@ErrorLine = ERROR_LINE()

			,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')

			,@ErrorMessageText = ERROR_MESSAGE()



		SET @ErrorInd = 1

		END CATCH

		IF (@ErrorInd = 1)

	BEGIN

		IF (@DebugLevel &gt; 0)

		BEGIN

			DECLARE @DebugMessageText nvarchar(max)



			SET @DebugMessageText = N'Failed to process staging. Error ' + CAST(@ErrorNumber AS varchar(15))

										+ ', Procedure ' + @ErrorProcedure

										+ ', Line ' + CAST(@ErrorLine AS varchar(15))

										+ ', Message: '+ @ErrorMessageText



			EXEC DebugMessageInsert

				@DatasetId = @DatasetId

				,@MessageLevel = 1

				,@MessageText = @DebugMessageText

		END

		DECLARE @AdjustedErrorSeverity int



		SET @AdjustedErrorSeverity = CASE

										WHEN @ErrorSeverity &gt; 18 THEN 18

										ELSE @ErrorSeverity

									END



		RAISERROR (777971002, @AdjustedErrorSeverity, 1

			,@ErrorNumber

			,@ErrorSeverity

			,@ErrorState

			,@ErrorProcedure

			,@ErrorLine

			,@ErrorMessageText

		)

	END

		

	

END

GO



GRANT EXECUTE ON [VeeamMP].[ObjectProcessStaging] TO OpsMgrReader

GO



 

-----

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES AS t WHERE t.TABLE_NAME = 'Dictionary')



BEGIN



DROP TABLE VeeamMP.Dictionary



END



 

CREATE TABLE VeeamMP.Dictionary(PropertyName NVARCHAR(256),PropertyDisplayName NVARCHAR(256),)



GO

------------------------------------------------------------------------------------------

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[VeeamMP].[DecreaseMaxRowsToProcessPerTransactionCount]') AND type in (N'P'))

	BEGIN

		EXECUTE ('CREATE PROCEDURE [VeeamMP].[DecreaseMaxRowsToProcessPerTransactionCount] AS RETURN 1')

	END

GO



ALTER PROCEDURE [VeeamMP].[DecreaseMaxRowsToProcessPerTransactionCount]

	@ManagementGroupId		uniqueidentifier

	,@DatasetId				uniqueidentifier

	,@DataXml				nvarchar(max)

AS

BEGIN

	SET NOCOUNT ON		

	BEGIN	

		UPDATE StandardDatasetStagingArea

		SET			

			MaxRowsToProcessPerTransactionCount = CASE WHEN MaxRowsToProcessPerTransactionCount * 0.9 &lt;100 THEN 100 ELSE MaxRowsToProcessPerTransactionCount * 0.9 END

		WHERE DatasetId = (SELECT DatasetId FROM StandardDataset WHERE SchemaName='VeeamMP')					

	END

END

GO



GRANT EXECUTE ON [VeeamMP].[DecreaseMaxRowsToProcessPerTransactionCount] TO OpsMgrReader

GO



</Upgrade>

</DataWarehouseDataSet>