
/*======== REPLICATED TABLE: 5.Folders*/

/* UPDATES IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_Folders_Changes]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_5_Folders_Changes]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_Folders_Changes]
         @dbInstanceId uniqueidentifier,
         @timeZoneShift int,
         @importDoc XML
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @imported_changes table
        (
           [id] uniqueidentifier,
           [parent_id] uniqueidentifier,
           [name] nvarchar(255),
           [db_instance_id] uniqueidentifier
        )
        
        /*              IMPORT XML DOCUMENT       */
        DECLARE @idoc int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
        
        INSERT INTO @imported_changes([id],[parent_id],[name],[db_instance_id])
        SELECT *
        FROM OPENXML( @idoc, '/Root/row', 2)
        WITH
        (
           [id] uniqueidentifier 'id',
           [parent_id] uniqueidentifier 'parent_id',
           [name] nvarchar(255) 'name',
           [db_instance_id] uniqueidentifier 'db_instance_id'
        )
        EXEC sp_xml_removedocument @idoc
        
        /*             ADD DBID  FIELD     */
        UPDATE @imported_changes
        SET db_instance_id= @dbInstanceId
        
        /*             TIMEZONE UPDATES     */
        /*      UPDATE EXISTING ROWS        */
        UPDATE [5.Folders]
        SET
            [5.Folders].[parent_id] = src.[parent_id],
            [5.Folders].[name] = src.[name],
            [5.Folders].[db_instance_id] = src.[db_instance_id]
        FROM [5.Folders] INNER JOIN @imported_changes src
        ON [5.Folders].id = src.id
        
        /*      INSERT NEW ROWS       */
        INSERT INTO [5.Folders]([id],[parent_id],[name],[db_instance_id])
        SELECT
            src.[id],
            src.[parent_id],
            src.[name],
            src.[db_instance_id]
        FROM @imported_changes src LEFT OUTER JOIN [5.Folders] trg
        ON src.id = trg.id
        WHERE trg.id IS NULL
    END
GO

/* DELS IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_Folders_Dels]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_5_Folders_Dels]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_Folders_Dels]
        @deleted_objects_xml XML,
	@dbInstanceId uniqueidentifier
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @deleted_objects_table table
        (
           obj_id  uniqueidentifier
        )
        
        /*                importing deleted objects set   */
        DECLARE @idoc  int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
        INSERT INTO @deleted_objects_table( obj_id )
        SELECT *
        FROM OPENXML( @idoc, N'/root/object',1 )
        WITH
        (
           obj_id uniqueidentifier '@id'
        )
        EXEC sp_xml_removedocument @idoc
        
        DELETE FROM [dbo].[5.Folders]
        WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
    END
GO

/*======== REPLICATED TABLE: 5.Folder_Host*/

/* UPDATES IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_Folder_Host_Changes]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_5_Folder_Host_Changes]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_Folder_Host_Changes]
         @dbInstanceId uniqueidentifier,
         @timeZoneShift int,
         @importDoc XML
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @imported_changes table
        (
           [id] uniqueidentifier,
           [folder_id] uniqueidentifier,
           [host_id] uniqueidentifier,
           [db_instance_id] uniqueidentifier
        )
        
        /*              IMPORT XML DOCUMENT       */
        DECLARE @idoc int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
        
        INSERT INTO @imported_changes([id],[folder_id],[host_id],[db_instance_id])
        SELECT *
        FROM OPENXML( @idoc, '/Root/row', 2)
        WITH
        (
           [id] uniqueidentifier 'id',
           [folder_id] uniqueidentifier 'folder_id',
           [host_id] uniqueidentifier 'host_id',
           [db_instance_id] uniqueidentifier 'db_instance_id'
        )
        EXEC sp_xml_removedocument @idoc
        
        /*             ADD DBID  FIELD     */
        UPDATE @imported_changes
        SET db_instance_id= @dbInstanceId
        
        /*             TIMEZONE UPDATES     */
        /*      UPDATE EXISTING ROWS        */
        UPDATE [5.Folder_Host]
        SET
            [5.Folder_Host].[folder_id] = src.[folder_id],
            [5.Folder_Host].[host_id] = src.[host_id],
            [5.Folder_Host].[db_instance_id] = src.[db_instance_id]
        FROM [5.Folder_Host] INNER JOIN @imported_changes src
        ON [5.Folder_Host].id = src.id
        
        /*      INSERT NEW ROWS       */
        INSERT INTO [5.Folder_Host]([id],[folder_id],[host_id],[db_instance_id])
        SELECT
            src.[id],
            src.[folder_id],
            src.[host_id],
            src.[db_instance_id]
        FROM @imported_changes src LEFT OUTER JOIN [5.Folder_Host] trg
        ON src.id = trg.id
        WHERE trg.id IS NULL
    END
GO

/* DELS IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_Folder_Host_Dels]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_5_Folder_Host_Dels]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_Folder_Host_Dels]
        @deleted_objects_xml XML,
		@dbInstanceId uniqueidentifier
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @deleted_objects_table table
        (
           obj_id  uniqueidentifier
        )
        
        /*                importing deleted objects set   */
        DECLARE @idoc  int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
        INSERT INTO @deleted_objects_table( obj_id )
        SELECT *
        FROM OPENXML( @idoc, N'/root/object',1 )
        WITH
        (
           obj_id uniqueidentifier '@id'
        )
        EXEC sp_xml_removedocument @idoc
        
        DELETE FROM [dbo].[5.Folder_Host]
        WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
    END
GO

/*======== REPLICATED TABLE: 5.ObjectsInJobs*/

/* UPDATES IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_ObjectsInJobs_Changes]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_5_ObjectsInJobs_Changes]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_ObjectsInJobs_Changes]
         @dbInstanceId uniqueidentifier,
         @timeZoneShift int,
         @importDoc XML
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @imported_changes table
        (
           [id] uniqueidentifier,
           [job_id] uniqueidentifier,
           [object_id] uniqueidentifier,
           [folder_id] uniqueidentifier,
           [vss_options] xml,
           [approx_size] bigint,
           [location] nvarchar(1024),
           [type] int,
           [db_instance_id] uniqueidentifier
        )
        
        /*              IMPORT XML DOCUMENT       */
        DECLARE @idoc int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
        
        INSERT INTO @imported_changes([id],[job_id],[object_id],[folder_id],[vss_options],[approx_size],[location],[type],[db_instance_id])
        SELECT *
        FROM OPENXML( @idoc, '/Root/row', 2)
        WITH
        (
           [id] uniqueidentifier 'id',
           [job_id] uniqueidentifier 'job_id',
           [object_id] uniqueidentifier 'object_id',
           [folder_id] uniqueidentifier 'folder_id',
           [vss_options] xml 'vss_options',
           [approx_size] bigint 'approx_size',
           [location] nvarchar(1024) 'location',
           [type] int 'type',
           [db_instance_id] uniqueidentifier 'db_instance_id'
        )
        EXEC sp_xml_removedocument @idoc
        
        /*             ADD DBID  FIELD     */
        UPDATE @imported_changes
        SET db_instance_id= @dbInstanceId
        
        /*             TIMEZONE UPDATES     */
        /*      UPDATE EXISTING ROWS        */
        UPDATE [5.ObjectsInJobs]
        SET
            [5.ObjectsInJobs].[job_id] = src.[job_id],
            [5.ObjectsInJobs].[object_id] = src.[object_id],
            [5.ObjectsInJobs].[folder_id] = src.[folder_id],
            [5.ObjectsInJobs].[vss_options] = src.[vss_options],
            [5.ObjectsInJobs].[approx_size] = src.[approx_size],
            [5.ObjectsInJobs].[location] = src.[location],
            [5.ObjectsInJobs].[type] = src.[type],
            [5.ObjectsInJobs].[db_instance_id] = src.[db_instance_id]
        FROM [5.ObjectsInJobs] INNER JOIN @imported_changes src
        ON [5.ObjectsInJobs].id = src.id
        
        /*      INSERT NEW ROWS       */
        INSERT INTO [5.ObjectsInJobs]([id],[job_id],[object_id],[folder_id],[vss_options],[approx_size],[location],[type],[db_instance_id])
        SELECT
            src.[id],
            src.[job_id],
            src.[object_id],
            src.[folder_id],
            src.[vss_options],
            src.[approx_size],
            src.[location],
            src.[type],
            src.[db_instance_id]
        FROM @imported_changes src LEFT OUTER JOIN [5.ObjectsInJobs] trg
        ON src.id = trg.id
        WHERE trg.id IS NULL
    END
GO

/* DELS IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_ObjectsInJobs_Dels]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_5_ObjectsInJobs_Dels]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_ObjectsInJobs_Dels]
        @deleted_objects_xml XML,
		@dbInstanceId uniqueidentifier
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @deleted_objects_table table
        (
           obj_id  uniqueidentifier
        )
        
        /*                importing deleted objects set   */
        DECLARE @idoc  int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
        INSERT INTO @deleted_objects_table( obj_id )
        SELECT *
        FROM OPENXML( @idoc, N'/root/object',1 )
        WITH
        (
           obj_id uniqueidentifier '@id'
        )
        EXEC sp_xml_removedocument @idoc
        
        DELETE FROM [dbo].[5.ObjectsInJobs]
        WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
    END
GO

/*======== REPLICATED TABLE: 5.HostsByJobs*/

/* UPDATES IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_HostsByJobs_Changes]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_5_HostsByJobs_Changes]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_HostsByJobs_Changes]
         @dbInstanceId uniqueidentifier,
         @timeZoneShift int,
         @importDoc XML
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @imported_changes table
        (
           [id] uniqueidentifier,
           [host_id] uniqueidentifier,
           [job_id] uniqueidentifier,
           [uuid] nvarchar(255),
           [db_instance_id] uniqueidentifier
        )
        
        /*              IMPORT XML DOCUMENT       */
        DECLARE @idoc int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
        
        INSERT INTO @imported_changes([id],[host_id],[job_id],[uuid],[db_instance_id])
        SELECT *
        FROM OPENXML( @idoc, '/Root/row', 2)
        WITH
        (
           [id] uniqueidentifier 'id',
           [host_id] uniqueidentifier 'host_id',
           [job_id] uniqueidentifier 'job_id',
           [uuid] nvarchar(255) 'uuid',
           [db_instance_id] uniqueidentifier 'db_instance_id'
        )
        EXEC sp_xml_removedocument @idoc
        
        /*             ADD DBID  FIELD     */
        UPDATE @imported_changes
        SET db_instance_id= @dbInstanceId
        
        /*             TIMEZONE UPDATES     */
        /*      UPDATE EXISTING ROWS        */
        UPDATE [5.HostsByJobs]
        SET
            [5.HostsByJobs].[host_id] = src.[host_id],
            [5.HostsByJobs].[job_id] = src.[job_id],
            [5.HostsByJobs].[uuid] = src.[uuid],
            [5.HostsByJobs].[db_instance_id] = src.[db_instance_id]
        FROM [5.HostsByJobs] INNER JOIN @imported_changes src
        ON [5.HostsByJobs].id = src.id
        
        /*      INSERT NEW ROWS       */
        INSERT INTO [5.HostsByJobs]([id],[host_id],[job_id],[uuid],[db_instance_id])
        SELECT
            src.[id],
            src.[host_id],
            src.[job_id],
            src.[uuid],
            src.[db_instance_id]
        FROM @imported_changes src LEFT OUTER JOIN [5.HostsByJobs] trg
        ON src.id = trg.id
        WHERE trg.id IS NULL
    END
GO

/* DELS IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_HostsByJobs_Dels]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_5_HostsByJobs_Dels]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_HostsByJobs_Dels]
        @deleted_objects_xml XML,
		@dbInstanceId uniqueidentifier
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @deleted_objects_table table
        (
           obj_id  uniqueidentifier
        )
        
        /*                importing deleted objects set   */
        DECLARE @idoc  int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
        INSERT INTO @deleted_objects_table( obj_id )
        SELECT *
        FROM OPENXML( @idoc, N'/root/object',1 )
        WITH
        (
           obj_id uniqueidentifier '@id'
        )
        EXEC sp_xml_removedocument @idoc
        
        DELETE FROM [dbo].[5.HostsByJobs]
        WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
    END
GO

/*======== REPLICATED TABLE: 5.Soap_creds*/

/* UPDATES IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_Soap_creds_Changes]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_5_Soap_creds_Changes]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_Soap_creds_Changes]
         @dbInstanceId uniqueidentifier,
         @timeZoneShift int,
         @importDoc XML
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @imported_changes table
        (
           [id] uniqueidentifier,
           [host_id] uniqueidentifier,
           [port] int,
           [user] nvarchar(255),
           [savepassword] bit,
           [useproxy] bit,
           [proxyip] nvarchar(50),
           [proxyport] int,
           [enabled] bit,
           [db_instance_id] uniqueidentifier
        )
        
        /*              IMPORT XML DOCUMENT       */
        DECLARE @idoc int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
        
        INSERT INTO @imported_changes([id],[host_id],[port],[user],[savepassword],[useproxy],[proxyip],[proxyport],[enabled],[db_instance_id])
        SELECT *
        FROM OPENXML( @idoc, '/Root/row', 2)
        WITH
        (
           [id] uniqueidentifier 'id',
           [host_id] uniqueidentifier 'host_id',
           [port] int 'port',
           [user] nvarchar(255) 'user',
           [savepassword] bit 'savepassword',
           [useproxy] bit 'useproxy',
           [proxyip] nvarchar(50) 'proxyip',
           [proxyport] int 'proxyport',
           [enabled] bit 'enabled',
           [db_instance_id] uniqueidentifier 'db_instance_id'
        )
        EXEC sp_xml_removedocument @idoc
        
        /*             ADD DBID  FIELD     */
        UPDATE @imported_changes
        SET db_instance_id= @dbInstanceId
        
        /*             TIMEZONE UPDATES     */
        /*      UPDATE EXISTING ROWS        */
        UPDATE [5.Soap_creds]
        SET
            [5.Soap_creds].[host_id] = src.[host_id],
            [5.Soap_creds].[port] = src.[port],
            [5.Soap_creds].[user] = src.[user],
            [5.Soap_creds].[savepassword] = src.[savepassword],
            [5.Soap_creds].[useproxy] = src.[useproxy],
            [5.Soap_creds].[proxyip] = src.[proxyip],
            [5.Soap_creds].[proxyport] = src.[proxyport],
            [5.Soap_creds].[enabled] = src.[enabled],
            [5.Soap_creds].[db_instance_id] = src.[db_instance_id]
        FROM [5.Soap_creds] INNER JOIN @imported_changes src
        ON [5.Soap_creds].id = src.id
        
        /*      INSERT NEW ROWS       */
        INSERT INTO [5.Soap_creds]([id],[host_id],[port],[user],[savepassword],[useproxy],[proxyip],[proxyport],[enabled],[db_instance_id])
        SELECT
            src.[id],
            src.[host_id],
            src.[port],
            src.[user],
            src.[savepassword],
            src.[useproxy],
            src.[proxyip],
            src.[proxyport],
            src.[enabled],
            src.[db_instance_id]
        FROM @imported_changes src LEFT OUTER JOIN [5.Soap_creds] trg
        ON src.id = trg.id
        WHERE trg.id IS NULL
    END
GO

/* DELS IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_Soap_creds_Dels]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_5_Soap_creds_Dels]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_Soap_creds_Dels]
        @deleted_objects_xml XML,
		@dbInstanceId uniqueidentifier
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @deleted_objects_table table
        (
           obj_id  uniqueidentifier
        )
        
        /*                importing deleted objects set   */
        DECLARE @idoc  int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
        INSERT INTO @deleted_objects_table( obj_id )
        SELECT *
        FROM OPENXML( @idoc, N'/root/object',1 )
        WITH
        (
           obj_id uniqueidentifier '@id'
        )
        EXEC sp_xml_removedocument @idoc
        
        DELETE FROM [dbo].[5.Soap_creds]
        WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
    END
GO

/*======== REPLICATED TABLE: 5.VirtualLabs*/

/* UPDATES IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_VirtualLabs_Changes]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_5_VirtualLabs_Changes]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_VirtualLabs_Changes]
         @dbInstanceId uniqueidentifier,
         @timeZoneShift int,
         @importDoc XML
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @imported_changes table
        (
           [id] uniqueidentifier,
           [name] nvarchar(255),
           [description] nvarchar(255),
           [host_id] uniqueidentifier,
           [vm_ref] nvarchar(1024),
           [db_instance_id] uniqueidentifier
        )
        
        /*              IMPORT XML DOCUMENT       */
        DECLARE @idoc int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
        
        INSERT INTO @imported_changes([id],[name],[description],[host_id],[vm_ref],[db_instance_id])
        SELECT *
        FROM OPENXML( @idoc, '/Root/row', 2)
        WITH
        (
           [id] uniqueidentifier 'id',
           [name] nvarchar(255) 'name',
           [description] nvarchar(255) 'description',
           [host_id] uniqueidentifier 'host_id',
           [vm_ref] nvarchar(1024) 'vm_ref',
           [db_instance_id] uniqueidentifier 'db_instance_id'
        )
        EXEC sp_xml_removedocument @idoc
        
        /*             ADD DBID  FIELD     */
        UPDATE @imported_changes
        SET db_instance_id= @dbInstanceId
        
        /*             TIMEZONE UPDATES     */
        /*      UPDATE EXISTING ROWS        */
        UPDATE [5.VirtualLabs]
        SET
            [5.VirtualLabs].[name] = src.[name],
            [5.VirtualLabs].[description] = src.[description],
            [5.VirtualLabs].[host_id] = src.[host_id],
            [5.VirtualLabs].[vm_ref] = src.[vm_ref],
            [5.VirtualLabs].[db_instance_id] = src.[db_instance_id]
        FROM [5.VirtualLabs] INNER JOIN @imported_changes src
        ON [5.VirtualLabs].id = src.id
        
        /*      INSERT NEW ROWS       */
        INSERT INTO [5.VirtualLabs]([id],[name],[description],[host_id],[vm_ref],[db_instance_id])
        SELECT
            src.[id],
            src.[name],
            src.[description],
            src.[host_id],
            src.[vm_ref],
            src.[db_instance_id]
        FROM @imported_changes src LEFT OUTER JOIN [5.VirtualLabs] trg
        ON src.id = trg.id
        WHERE trg.id IS NULL
    END
GO

/* DELS IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_VirtualLabs_Dels]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_5_VirtualLabs_Dels]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_VirtualLabs_Dels]
        @deleted_objects_xml XML,
		@dbInstanceId uniqueidentifier
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @deleted_objects_table table
        (
           obj_id  uniqueidentifier
        )
        
        /*                importing deleted objects set   */
        DECLARE @idoc  int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
        INSERT INTO @deleted_objects_table( obj_id )
        SELECT *
        FROM OPENXML( @idoc, N'/root/object',1 )
        WITH
        (
           obj_id uniqueidentifier '@id'
        )
        EXEC sp_xml_removedocument @idoc
        
        DELETE FROM [dbo].[5.VirtualLabs]
        WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
    END
GO

/*======== REPLICATED TABLE: 5.BJobs*/

/* UPDATES IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_BJobs_Changes]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp_Import_5_BJobs_Changes]
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_BJobs_Changes]
         @dbInstanceId uniqueidentifier,
         @timeZoneShift int,
         @importDoc XML
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @imported_changes table
        (
        db_instance_id uniqueidentifier,
           [id] uniqueidentifier,
           [type] int,
           [name] nvarchar(255),
           [target_host_id] uniqueidentifier,
           [target_dir] nvarchar(2000),
           [target_file] nvarchar(2000),
           [options] xml,
           [schedule] xml,
           [is_deleted] bit,
           [latest_result] int,
           [post_command_run_count] int,
           [vss_options] xml,
           [vcb_host_id] uniqueidentifier,
           [target_type] int,
           [description] nvarchar(1024),
           [next_run_time] datetime,
           [schedule_enabled] bit,
           [platform] int
        )
        
        /*              IMPORT XML DOCUMENT       */
        DECLARE @idoc int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
        
    INSERT INTO @imported_changes([id],[type],[name],[target_host_id],[target_dir],[target_file],[options],[schedule],[is_deleted],[latest_result],[post_command_run_count],[vss_options],[vcb_host_id],[target_type],[description], [next_run_time], [schedule_enabled],[platform])
        SELECT *
        FROM OPENXML( @idoc, '/Root/row', 2)
        WITH
        (
           [id] uniqueidentifier 'id',
           [type] int 'type',
           [name] nvarchar(255) 'name',
           [target_host_id] uniqueidentifier 'target_host_id',
           [target_dir] nvarchar(2000) 'target_dir',
           [target_file] nvarchar(2000) 'target_file',
       [options] xml 'options/*',
       [schedule] xml 'schedule/*',
           [is_deleted] bit 'is_deleted',
           [latest_result] int 'latest_result',
           [post_command_run_count] int 'post_command_run_count',
       [vss_options] xml 'vss_options/*',
           [vcb_host_id] uniqueidentifier 'vcb_host_id',
           [target_type] int 'target_type',
           [description] nvarchar(1024) 'description',
           [next_run_time] datetime 'next_run_time',
           [schedule_enabled] bit 'schedule_enabled',
           [platform] int 'platform'
        )
        EXEC sp_xml_removedocument @idoc
        
        /*             ADD DBID  FIELD     */
        UPDATE @imported_changes
        SET db_instance_id= @dbInstanceId
        
        /*             TIMEZONE UPDATES     */
        UPDATE @imported_changes
	SET [next_run_time] = DATEADD( hour, -@TimeZoneShift, [next_run_time])
    
        /*      UPDATE EXISTING ROWS        */
        UPDATE [5.BJobs]
        SET
            [5.BJobs].[type] = src.[type],
            [5.BJobs].[name] = src.[name],
            [5.BJobs].[target_host_id] = src.[target_host_id],
            [5.BJobs].[target_dir] = src.[target_dir],
            [5.BJobs].[target_file] = src.[target_file],
            [5.BJobs].[options] = src.[options],
            [5.BJobs].[schedule] = src.[schedule],
            [5.BJobs].[is_deleted] = src.[is_deleted],
            [5.BJobs].[latest_result] = src.[latest_result],
            [5.BJobs].[post_command_run_count] = src.[post_command_run_count],
            [5.BJobs].[vss_options] = src.[vss_options],
            [5.BJobs].[vcb_host_id] = src.[vcb_host_id],
            [5.BJobs].[target_type] = src.[target_type],
            [5.BJobs].[description] = src.[description],
            [5.BJobs].[next_run_time] = src.[next_run_time],
            [5.BJobs].[schedule_enabled] = src.[schedule_enabled],
            [5.BJobs].[platform] = src.[platform]
        FROM [5.BJobs] INNER JOIN @imported_changes src
        ON [5.BJobs].id = src.id
        
        /*      INSERT NEW ROWS       */
        INSERT INTO [5.BJobs](
        db_instance_id,[id],[type],[name],[target_host_id],[target_dir],[target_file],[options],[schedule],[is_deleted],[latest_result],[post_command_run_count],[vss_options],[vcb_host_id],[target_type],[description],[next_run_time],[schedule_enabled],[platform])
        SELECT
        src.db_instance_id,src.[id],src.[type],src.[name],src.[target_host_id],src.[target_dir],src.[target_file],src.[options],src.[schedule],src.[is_deleted],src.[latest_result],src.[post_command_run_count],src.[vss_options],src.[vcb_host_id],src.[target_type], src.[description],src.[next_run_time],src.[schedule_enabled],src.[platform]
        FROM @imported_changes src LEFT OUTER JOIN [5.BJobs] trg
        ON src.id = trg.id
        WHERE trg.id IS NULL
    END
GO

/* DELS IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_BJobs_Dels]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_5_BJobs_Dels]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_BJobs_Dels]
        @deleted_objects_xml XML,
		@dbInstanceId uniqueidentifier
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @deleted_objects_table table
        (
           obj_id  uniqueidentifier
        )
        
        /*                importing deleted objects set   */
        DECLARE @idoc  int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
        INSERT INTO @deleted_objects_table( obj_id )
        SELECT *
        FROM OPENXML( @idoc, N'/root/object',1 )
        WITH
        (
           obj_id uniqueidentifier '@id'
        )
        EXEC sp_xml_removedocument @idoc
        
        DELETE FROM [dbo].[5.BJobs]
        WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
    END
GO

/*======== REPLICATED TABLE: 5.Backup.Model.SbTaskSessions*/

/* UPDATES IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_Backup_Model_SbTaskSessions_Changes]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_5_Backup_Model_SbTaskSessions_Changes]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_Backup_Model_SbTaskSessions_Changes]
         @dbInstanceId uniqueidentifier,
         @timeZoneShift int,
         @importDoc XML
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @imported_changes table
        (
           [id] uniqueidentifier,
           [drsession_id] uniqueidentifier,
           [type] int,
           [object_name] nvarchar(max),
           [object_id] uniqueidentifier,
           [oib_id] uniqueidentifier,
           [overall_status] int,
           [description] nvarchar(max),
           [start_time] datetime,
           [finish_time] datetime,
           [total_steps] int,
           [processed_steps] int,
           [powerOn_status] int,
           [heartbeat_status] int,
           [ping_status] int,
           [test_script_status] int,
           [powerOff_status] int,
           [test_script_error_code] int,
           [vm_ref] nvarchar(max),
           [percent] int,
           [appliance_ip] nvarchar(80),
           [subnet_ip] nvarchar(80),
           [subnet_mask] nvarchar(80),
           [vm_external_ip] nvarchar(80),
           [state] int,
           [order_num] int,
           [control] int,
           [leave_powered_on] bit,
           [oiag_id] uniqueidentifier,
           [restore_counter] int,
           [test_scripts_results] xml,
           [db_instance_id] uniqueidentifier
        )
        
        /*              IMPORT XML DOCUMENT       */
        DECLARE @idoc int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
        
        INSERT INTO @imported_changes([id],[drsession_id],[type],[object_name],[object_id],[oib_id],[overall_status],[description],[start_time],[finish_time],[total_steps],[processed_steps],[powerOn_status],[heartbeat_status],[ping_status],[test_script_status],[powerOff_status],[test_script_error_code],[vm_ref],[percent],[appliance_ip],[subnet_ip],[subnet_mask],[vm_external_ip],[state],[order_num],[control],[leave_powered_on],[oiag_id],[restore_counter],[test_scripts_results],[db_instance_id])
        SELECT *
        FROM OPENXML( @idoc, '/Root/row', 2)
        WITH
        (
           [id] uniqueidentifier 'id',
           [drsession_id] uniqueidentifier 'drsession_id',
           [type] int 'type',
           [object_name] nvarchar(max) 'object_name',
           [object_id] uniqueidentifier 'object_id',
           [oib_id] uniqueidentifier 'oib_id',
           [overall_status] int 'overall_status',
           [description] nvarchar(max) 'description',
           [start_time] datetime 'start_time',
           [finish_time] datetime 'finish_time',
           [total_steps] int 'total_steps',
           [processed_steps] int 'processed_steps',
           [powerOn_status] int 'powerOn_status',
           [heartbeat_status] int 'heartbeat_status',
           [ping_status] int 'ping_status',
           [test_script_status] int 'test_script_status',
           [powerOff_status] int 'powerOff_status',
           [test_script_error_code] int 'test_script_error_code',
           [vm_ref] nvarchar(max) 'vm_ref',
           [percent] int 'percent',
           [appliance_ip] nvarchar(80) 'appliance_ip',
           [subnet_ip] nvarchar(80) 'subnet_ip',
           [subnet_mask] nvarchar(80) 'subnet_mask',
           [vm_external_ip] nvarchar(80) 'vm_external_ip',
           [state] int 'state',
           [order_num] int 'order_num',
           [control] int 'control',
           [leave_powered_on] bit 'leave_powered_on',
           [oiag_id] uniqueidentifier 'oiag_id',
           [restore_counter] int 'restore_counter',
           [test_scripts_results] xml 'test_scripts_results',
           [db_instance_id] uniqueidentifier 'db_instance_id'
        )
        EXEC sp_xml_removedocument @idoc
        
        /*             ADD DBID  FIELD     */
        UPDATE @imported_changes
        SET db_instance_id= @dbInstanceId
        
        /*             TIMEZONE UPDATES     */
        UPDATE @imported_changes
        SET
        [start_time] = DATEADD( hour, -@TimeZoneShift, [start_time]),
        [finish_time] = DATEADD( hour, -@TimeZoneShift, [finish_time])
        /*      UPDATE EXISTING ROWS        */
        UPDATE [5.Backup.Model.SbTaskSessions]
        SET
            [5.Backup.Model.SbTaskSessions].[drsession_id] = src.[drsession_id],
            [5.Backup.Model.SbTaskSessions].[type] = src.[type],
            [5.Backup.Model.SbTaskSessions].[object_name] = src.[object_name],
            [5.Backup.Model.SbTaskSessions].[object_id] = src.[object_id],
            [5.Backup.Model.SbTaskSessions].[oib_id] = src.[oib_id],
            [5.Backup.Model.SbTaskSessions].[overall_status] = src.[overall_status],
            [5.Backup.Model.SbTaskSessions].[description] = src.[description],
            [5.Backup.Model.SbTaskSessions].[start_time] = src.[start_time],
            [5.Backup.Model.SbTaskSessions].[finish_time] = src.[finish_time],
            [5.Backup.Model.SbTaskSessions].[total_steps] = src.[total_steps],
            [5.Backup.Model.SbTaskSessions].[processed_steps] = src.[processed_steps],
            [5.Backup.Model.SbTaskSessions].[powerOn_status] = src.[powerOn_status],
            [5.Backup.Model.SbTaskSessions].[heartbeat_status] = src.[heartbeat_status],
            [5.Backup.Model.SbTaskSessions].[ping_status] = src.[ping_status],
            [5.Backup.Model.SbTaskSessions].[test_script_status] = src.[test_script_status],
            [5.Backup.Model.SbTaskSessions].[powerOff_status] = src.[powerOff_status],
            [5.Backup.Model.SbTaskSessions].[test_script_error_code] = src.[test_script_error_code],
            [5.Backup.Model.SbTaskSessions].[vm_ref] = src.[vm_ref],
            [5.Backup.Model.SbTaskSessions].[percent] = src.[percent],
            [5.Backup.Model.SbTaskSessions].[appliance_ip] = src.[appliance_ip],
            [5.Backup.Model.SbTaskSessions].[subnet_ip] = src.[subnet_ip],
            [5.Backup.Model.SbTaskSessions].[subnet_mask] = src.[subnet_mask],
            [5.Backup.Model.SbTaskSessions].[vm_external_ip] = src.[vm_external_ip],
            [5.Backup.Model.SbTaskSessions].[state] = src.[state],
            [5.Backup.Model.SbTaskSessions].[order_num] = src.[order_num],
            [5.Backup.Model.SbTaskSessions].[control] = src.[control],
            [5.Backup.Model.SbTaskSessions].[leave_powered_on] = src.[leave_powered_on],
            [5.Backup.Model.SbTaskSessions].[oiag_id] = src.[oiag_id],
            [5.Backup.Model.SbTaskSessions].[restore_counter] = src.[restore_counter],
            [5.Backup.Model.SbTaskSessions].[test_scripts_results] = src.[test_scripts_results],
            [5.Backup.Model.SbTaskSessions].[db_instance_id] = src.[db_instance_id]
        FROM [5.Backup.Model.SbTaskSessions] INNER JOIN @imported_changes src
        ON [5.Backup.Model.SbTaskSessions].id = src.id
        
        /*      INSERT NEW ROWS       */
        INSERT INTO [5.Backup.Model.SbTaskSessions]([id],[drsession_id],[type],[object_name],[object_id],[oib_id],[overall_status],[description],[start_time],[finish_time],[total_steps],[processed_steps],[powerOn_status],[heartbeat_status],[ping_status],[test_script_status],[powerOff_status],[test_script_error_code],[vm_ref],[percent],[appliance_ip],[subnet_ip],[subnet_mask],[vm_external_ip],[state],[order_num],[control],[leave_powered_on],[oiag_id],[restore_counter],[test_scripts_results],[db_instance_id])
        SELECT
            src.[id],
            src.[drsession_id],
            src.[type],
            src.[object_name],
            src.[object_id],
            src.[oib_id],
            src.[overall_status],
            src.[description],
            src.[start_time],
            src.[finish_time],
            src.[total_steps],
            src.[processed_steps],
            src.[powerOn_status],
            src.[heartbeat_status],
            src.[ping_status],
            src.[test_script_status],
            src.[powerOff_status],
            src.[test_script_error_code],
            src.[vm_ref],
            src.[percent],
            src.[appliance_ip],
            src.[subnet_ip],
            src.[subnet_mask],
            src.[vm_external_ip],
            src.[state],
            src.[order_num],
            src.[control],
            src.[leave_powered_on],
            src.[oiag_id],
            src.[restore_counter],
            src.[test_scripts_results],
            src.[db_instance_id]
        FROM @imported_changes src LEFT OUTER JOIN [5.Backup.Model.SbTaskSessions] trg
        ON src.id = trg.id
        WHERE trg.id IS NULL
    END
GO

/* DELS IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_Backup_Model_SbTaskSessions_Dels]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_5_Backup_Model_SbTaskSessions_Dels]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_Backup_Model_SbTaskSessions_Dels]
        @deleted_objects_xml XML,
		@dbInstanceId uniqueidentifier
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @deleted_objects_table table
        (
           obj_id  uniqueidentifier
        )
        
        /*                importing deleted objects set   */
        DECLARE @idoc  int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
        INSERT INTO @deleted_objects_table( obj_id )
        SELECT *
        FROM OPENXML( @idoc, N'/root/object',1 )
        WITH
        (
           obj_id uniqueidentifier '@id'
        )
        EXEC sp_xml_removedocument @idoc
        
        DELETE FROM [dbo].[5.Backup.Model.SbTaskSessions]
        WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
    END
GO

/*======== REPLICATED TABLE: 5.BObjects*/

/* UPDATES IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_BObjects_Changes]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_5_BObjects_Changes]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_BObjects_Changes]
         @dbInstanceId uniqueidentifier,
         @timeZoneShift int,
         @importDoc XML
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @imported_changes table
        (
           [id] uniqueidentifier,
           [type] int,
           [host_id] uniqueidentifier,
           [object_name] nvarchar(2000),
           [object_id] nvarchar(434),
           [viobject_type] nvarchar(50),
           [guest_os] xml,
           [db_instance_id] uniqueidentifier,
           [mb_size] bigint
        )
        
        /*              IMPORT XML DOCUMENT       */
        DECLARE @idoc int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
        
        INSERT INTO @imported_changes([id],[type],[host_id],[object_name],[object_id],[viobject_type],[guest_os],[db_instance_id],[mb_size])
        SELECT *, 0 mb_size
        FROM OPENXML( @idoc, '/Root/row', 2)
        WITH
        (
           [id] uniqueidentifier 'id',
           [type] int 'type',
           [host_id] uniqueidentifier 'host_id',
           [object_name] nvarchar(2000) 'object_name',
           [object_id] nvarchar(434) 'object_id',
           [viobject_type] nvarchar(50) 'viobject_type',
           [guest_os] xml 'guest_os',
           [db_instance_id] uniqueidentifier 'db_instance_id'
        )
        EXEC sp_xml_removedocument @idoc
        
        /*             ADD DBID  FIELD     */
        UPDATE @imported_changes
        SET db_instance_id= @dbInstanceId
        
        /*             TIMEZONE UPDATES     */
        /*      UPDATE EXISTING ROWS        */
        UPDATE [5.BObjects]
        SET
            [5.BObjects].[type] = src.[type],
            [5.BObjects].[host_id] = src.[host_id],
            [5.BObjects].[object_name] = src.[object_name],
            [5.BObjects].[object_id] = src.[object_id],
            [5.BObjects].[viobject_type] = src.[viobject_type],
            [5.BObjects].[guest_os] = src.[guest_os],
            [5.BObjects].[db_instance_id] = src.[db_instance_id],
            [5.BObjects].[mb_size] = src.[mb_size]
        FROM [5.BObjects] INNER JOIN @imported_changes src
        ON [5.BObjects].id = src.id
        
        /*      INSERT NEW ROWS       */
        INSERT INTO [5.BObjects]([id],[type],[host_id],[object_name],[object_id],[viobject_type],[guest_os],[db_instance_id],[mb_size])
        SELECT
            src.[id],
            src.[type],
            src.[host_id],
            src.[object_name],
            src.[object_id],
            src.[viobject_type],
            src.[guest_os],
            src.[db_instance_id],
            src.[mb_size]
        FROM @imported_changes src LEFT OUTER JOIN [5.BObjects] trg
        ON src.id = trg.id
        WHERE trg.id IS NULL
    END
GO

/* DELS IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_BObjects_Dels]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_5_BObjects_Dels]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_BObjects_Dels]
        @deleted_objects_xml XML,
		@dbInstanceId uniqueidentifier
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @deleted_objects_table table
        (
           obj_id  uniqueidentifier
        )
        
        /*                importing deleted objects set   */
        DECLARE @idoc  int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
        INSERT INTO @deleted_objects_table( obj_id )
        SELECT *
        FROM OPENXML( @idoc, N'/root/object',1 )
        WITH
        (
           obj_id uniqueidentifier '@id'
        )
        EXEC sp_xml_removedocument @idoc
        
        DELETE FROM [dbo].[5.BObjects]
        WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
    END
GO

/*======== REPLICATED TABLE: 5.DRRoles*/

/* UPDATES IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_DRRoles_Changes]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_5_DRRoles_Changes]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_DRRoles_Changes]
         @dbInstanceId uniqueidentifier,
         @timeZoneShift int,
         @importDoc XML
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @imported_changes table
        (
           [id] uniqueidentifier,
           [name] nvarchar(max),
           [effective_memory] bigint,
           [boot_delay] int,
           [test_script_file_full_name] nvarchar(max),
           [db_instance_id] uniqueidentifier
        )
        
        /*              IMPORT XML DOCUMENT       */
        DECLARE @idoc int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
        
        INSERT INTO @imported_changes([id],[name],[effective_memory],[boot_delay],[test_script_file_full_name],[db_instance_id])
        SELECT *
        FROM OPENXML( @idoc, '/Root/row', 2)
        WITH
        (
           [id] uniqueidentifier 'id',
           [name] nvarchar(max) 'name',
           [effective_memory] bigint 'effective_memory',
           [boot_delay] int 'boot_delay',
           [test_script_file_full_name] nvarchar(max) 'test_script_file_full_name',
           [db_instance_id] uniqueidentifier 'db_instance_id'
        )
        EXEC sp_xml_removedocument @idoc
        
        /*             ADD DBID  FIELD     */
        UPDATE @imported_changes
        SET db_instance_id= @dbInstanceId
        
        /*             TIMEZONE UPDATES     */
        /*      UPDATE EXISTING ROWS        */
        UPDATE [5.DRRoles]
        SET
            [5.DRRoles].[name] = src.[name],
            [5.DRRoles].[effective_memory] = src.[effective_memory],
            [5.DRRoles].[boot_delay] = src.[boot_delay],
            [5.DRRoles].[test_script_file_full_name] = src.[test_script_file_full_name],
            [5.DRRoles].[db_instance_id] = src.[db_instance_id]
        FROM [5.DRRoles] INNER JOIN @imported_changes src
        ON [5.DRRoles].id = src.id
        
        /*      INSERT NEW ROWS       */
        INSERT INTO [5.DRRoles]([id],[name],[effective_memory],[boot_delay],[test_script_file_full_name],[db_instance_id])
        SELECT
            src.[id],
            src.[name],
            src.[effective_memory],
            src.[boot_delay],
            src.[test_script_file_full_name],
            src.[db_instance_id]
        FROM @imported_changes src LEFT OUTER JOIN [5.DRRoles] trg
        ON src.id = trg.id
        WHERE trg.id IS NULL
    END
GO

/* DELS IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_DRRoles_Dels]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_5_DRRoles_Dels]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_DRRoles_Dels]
        @deleted_objects_xml XML,
		@dbInstanceId uniqueidentifier
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @deleted_objects_table table
        (
           obj_id  uniqueidentifier
        )
        
        /*                importing deleted objects set   */
        DECLARE @idoc  int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
        INSERT INTO @deleted_objects_table( obj_id )
        SELECT *
        FROM OPENXML( @idoc, N'/root/object',1 )
        WITH
        (
           obj_id uniqueidentifier '@id'
        )
        EXEC sp_xml_removedocument @idoc
        
        DELETE FROM [dbo].[5.DRRoles]
        WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
    END
GO

/*======== REPLICATED TABLE: 5.Ssh_creds*/

/* UPDATES IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_Ssh_creds_Changes]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_5_Ssh_creds_Changes]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_Ssh_creds_Changes]
         @dbInstanceId uniqueidentifier,
         @timeZoneShift int,
         @importDoc XML
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @imported_changes table
        (
           [id] uniqueidentifier,
           [host_id] uniqueidentifier,
           [user] nvarchar(255),
           [savepassword] bit,
           [elevatetoroot] bit,
           [port] int,
           [startftport] int,
           [endftport] int,
           [buffersize] int,
           [isftserver] bit,
           [timeout] int,
           [adjust_firewall] bit,
           [auto_sudo] bit,
           [enabled] bit,
           [db_instance_id] uniqueidentifier
        )
        
        /*              IMPORT XML DOCUMENT       */
        DECLARE @idoc int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
        
        INSERT INTO @imported_changes([id],[host_id],[user],[savepassword],[elevatetoroot],[port],[startftport],[endftport],[buffersize],[isftserver],[timeout],[adjust_firewall],[auto_sudo],[enabled],[db_instance_id])
        SELECT *
        FROM OPENXML( @idoc, '/Root/row', 2)
        WITH
        (
           [id] uniqueidentifier 'id',
           [host_id] uniqueidentifier 'host_id',
           [user] nvarchar(255) 'user',
           [savepassword] bit 'savepassword',
           [elevatetoroot] bit 'elevatetoroot',
           [port] int 'port',
           [startftport] int 'startftport',
           [endftport] int 'endftport',
           [buffersize] int 'buffersize',
           [isftserver] bit 'isftserver',
           [timeout] int 'timeout',
           [adjust_firewall] bit 'adjust_firewall',
           [auto_sudo] bit 'auto_sudo',
           [enabled] bit 'enabled',
           [db_instance_id] uniqueidentifier 'db_instance_id'
        )
        EXEC sp_xml_removedocument @idoc
        
        /*             ADD DBID  FIELD     */
        UPDATE @imported_changes
        SET db_instance_id= @dbInstanceId
        
        /*             TIMEZONE UPDATES     */
        /*      UPDATE EXISTING ROWS        */
        UPDATE [5.Ssh_creds]
        SET
            [5.Ssh_creds].[host_id] = src.[host_id],
            [5.Ssh_creds].[user] = src.[user],
            [5.Ssh_creds].[savepassword] = src.[savepassword],
            [5.Ssh_creds].[elevatetoroot] = src.[elevatetoroot],
            [5.Ssh_creds].[port] = src.[port],
            [5.Ssh_creds].[startftport] = src.[startftport],
            [5.Ssh_creds].[endftport] = src.[endftport],
            [5.Ssh_creds].[buffersize] = src.[buffersize],
            [5.Ssh_creds].[isftserver] = src.[isftserver],
            [5.Ssh_creds].[timeout] = src.[timeout],
            [5.Ssh_creds].[adjust_firewall] = src.[adjust_firewall],
            [5.Ssh_creds].[auto_sudo] = src.[auto_sudo],
            [5.Ssh_creds].[enabled] = src.[enabled],
            [5.Ssh_creds].[db_instance_id] = src.[db_instance_id]
        FROM [5.Ssh_creds] INNER JOIN @imported_changes src
        ON [5.Ssh_creds].id = src.id
        
        /*      INSERT NEW ROWS       */
        INSERT INTO [5.Ssh_creds]([id],[host_id],[user],[savepassword],[elevatetoroot],[port],[startftport],[endftport],[buffersize],[isftserver],[timeout],[adjust_firewall],[auto_sudo],[enabled],[db_instance_id])
        SELECT
            src.[id],
            src.[host_id],
            src.[user],
            src.[savepassword],
            src.[elevatetoroot],
            src.[port],
            src.[startftport],
            src.[endftport],
            src.[buffersize],
            src.[isftserver],
            src.[timeout],
            src.[adjust_firewall],
            src.[auto_sudo],
            src.[enabled],
            src.[db_instance_id]
        FROM @imported_changes src LEFT OUTER JOIN [5.Ssh_creds] trg
        ON src.id = trg.id
        WHERE trg.id IS NULL
    END
GO

/* DELS IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_Ssh_creds_Dels]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_5_Ssh_creds_Dels]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_Ssh_creds_Dels]
        @deleted_objects_xml XML,
		@dbInstanceId uniqueidentifier
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @deleted_objects_table table
        (
           obj_id  uniqueidentifier
        )
        
        /*                importing deleted objects set   */
        DECLARE @idoc  int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
        INSERT INTO @deleted_objects_table( obj_id )
        SELECT *
        FROM OPENXML( @idoc, N'/root/object',1 )
        WITH
        (
           obj_id uniqueidentifier '@id'
        )
        EXEC sp_xml_removedocument @idoc
        
        DELETE FROM [dbo].[5.Ssh_creds]
        WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
    END
GO

/*======== REPLICATED TABLE: 5.Backup.Model.JobSessions*/

/* UPDATES IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_Backup_Model_JobSessions_Changes]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_5_Backup_Model_JobSessions_Changes]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_Backup_Model_JobSessions_Changes]
         @dbInstanceId uniqueidentifier,
         @timeZoneShift int,
         @importDoc XML
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @imported_changes table
        (
           [id] uniqueidentifier,
           [job_id] uniqueidentifier,
           [job_name] nvarchar(255),
           [job_type] int,
           [creation_time] datetime,
           [end_time] datetime,
           [state] int,
           [result] int,
           [control] int,
           [description] text,
           [operation] nvarchar(400),
           [progress] int,
           [log_xml] xml,
           [db_instance_id] uniqueidentifier,
           [log_text] ntext
        )
        
        /*              IMPORT XML DOCUMENT       */
        DECLARE @idoc int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
        
        INSERT INTO @imported_changes([id],[job_id],[job_name],[job_type],[creation_time],[end_time],[state],[result],[control],[description],[operation],[progress],[log_xml],[db_instance_id],[log_text])
        SELECT *
        FROM OPENXML( @idoc, '/Root/row', 2)
        WITH
        (
           [id] uniqueidentifier 'id',
           [job_id] uniqueidentifier 'job_id',
           [job_name] nvarchar(255) 'job_name',
           [job_type] int 'job_type',
           [creation_time] datetime 'creation_time',
           [end_time] datetime 'end_time',
           [state] int 'state',
           [result] int 'result',
           [control] int 'control',
           [description] text 'description',
           [operation] nvarchar(400) 'operation',
           [progress] int 'progress',
           [log_xml] xml 'log_xml',
           [db_instance_id] uniqueidentifier 'db_instance_id',
           [log_text] ntext 'log_text'
        )
        EXEC sp_xml_removedocument @idoc
        
        /*             ADD DBID  FIELD     */
        UPDATE @imported_changes
        SET db_instance_id= @dbInstanceId
        
        /*             TIMEZONE UPDATES     */
        UPDATE @imported_changes
        SET
        [creation_time] = DATEADD( hour, -@TimeZoneShift, [creation_time]),
        [end_time] = DATEADD( hour, -@TimeZoneShift, [end_time])
        /*      UPDATE EXISTING ROWS        */
        UPDATE [5.Backup.Model.JobSessions]
        SET
            [5.Backup.Model.JobSessions].[job_id] = src.[job_id],
            [5.Backup.Model.JobSessions].[job_name] = src.[job_name],
            [5.Backup.Model.JobSessions].[job_type] = src.[job_type],
            [5.Backup.Model.JobSessions].[creation_time] = src.[creation_time],
            [5.Backup.Model.JobSessions].[end_time] = src.[end_time],
            [5.Backup.Model.JobSessions].[state] = src.[state],
            [5.Backup.Model.JobSessions].[result] = src.[result],
            [5.Backup.Model.JobSessions].[control] = src.[control],
            [5.Backup.Model.JobSessions].[description] = src.[description],
            [5.Backup.Model.JobSessions].[operation] = src.[operation],
            [5.Backup.Model.JobSessions].[progress] = src.[progress],
            [5.Backup.Model.JobSessions].[log_xml] = src.[log_xml],
            [5.Backup.Model.JobSessions].[db_instance_id] = src.[db_instance_id],
            [5.Backup.Model.JobSessions].[log_text] = src.[log_text]
        FROM [5.Backup.Model.JobSessions] INNER JOIN @imported_changes src
        ON [5.Backup.Model.JobSessions].id = src.id
        
        /*      INSERT NEW ROWS       */
        INSERT INTO [5.Backup.Model.JobSessions]([id],[job_id],[job_name],[job_type],[creation_time],[end_time],[state],[result],[control],[description],[operation],[progress],[log_xml],[db_instance_id],[log_text])
        SELECT
            src.[id],
            src.[job_id],
            src.[job_name],
            src.[job_type],
            src.[creation_time],
            src.[end_time],
            src.[state],
            src.[result],
            src.[control],
            src.[description],
            src.[operation],
            src.[progress],
            src.[log_xml],
            src.[db_instance_id],
            src.[log_text]
        FROM @imported_changes src LEFT OUTER JOIN [5.Backup.Model.JobSessions] trg
        ON src.id = trg.id
        WHERE trg.id IS NULL
    END
GO

/* DELS IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_Backup_Model_JobSessions_Dels]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_5_Backup_Model_JobSessions_Dels]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_Backup_Model_JobSessions_Dels]
        @deleted_objects_xml XML,
		@dbInstanceId uniqueidentifier
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @deleted_objects_table table
        (
           obj_id  uniqueidentifier
        )
        
        /*                importing deleted objects set   */
        DECLARE @idoc  int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
        INSERT INTO @deleted_objects_table( obj_id )
        SELECT *
        FROM OPENXML( @idoc, N'/root/object',1 )
        WITH
        (
           obj_id uniqueidentifier '@id'
        )
        EXEC sp_xml_removedocument @idoc
        
        DELETE FROM [dbo].[5.Backup.Model.JobSessions]
        WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
    END
GO

/*======== REPLICATED TABLE: 5.Backup.Model.SbSessions*/

/* UPDATES IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_Backup_Model_SbSessions_Changes]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_5_Backup_Model_SbSessions_Changes]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_Backup_Model_SbSessions_Changes]
         @dbInstanceId uniqueidentifier,
         @timeZoneShift int,
         @importDoc XML
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @imported_changes table
        (
           [id] uniqueidentifier,
           [preferred_date] datetime,
           [db_instance_id] uniqueidentifier
        )
        
        /*              IMPORT XML DOCUMENT       */
        DECLARE @idoc int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
        
        INSERT INTO @imported_changes([id],[preferred_date],[db_instance_id])
        SELECT *
        FROM OPENXML( @idoc, '/Root/row', 2)
        WITH
        (
           [id] uniqueidentifier 'id',
           [preferred_date] datetime 'preferred_date',
           [db_instance_id] uniqueidentifier 'db_instance_id'
        )
        EXEC sp_xml_removedocument @idoc
        
        /*             ADD DBID  FIELD     */
        UPDATE @imported_changes
        SET db_instance_id= @dbInstanceId
        
        /*             TIMEZONE UPDATES     */
        UPDATE @imported_changes
        SET
        [preferred_date] = DATEADD( hour, -@TimeZoneShift, [preferred_date])
        /*      UPDATE EXISTING ROWS        */
        UPDATE [5.Backup.Model.SbSessions]
        SET
            [5.Backup.Model.SbSessions].[preferred_date] = src.[preferred_date],
            [5.Backup.Model.SbSessions].[db_instance_id] = src.[db_instance_id]
        FROM [5.Backup.Model.SbSessions] INNER JOIN @imported_changes src
        ON [5.Backup.Model.SbSessions].id = src.id
        
        /*      INSERT NEW ROWS       */
        INSERT INTO [5.Backup.Model.SbSessions]([id],[preferred_date],[db_instance_id])
        SELECT
            src.[id],
            src.[preferred_date],
            src.[db_instance_id]
        FROM @imported_changes src LEFT OUTER JOIN [5.Backup.Model.SbSessions] trg
        ON src.id = trg.id
        WHERE trg.id IS NULL
    END
GO

/* DELS IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_Backup_Model_SbSessions_Dels]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_5_Backup_Model_SbSessions_Dels]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_Backup_Model_SbSessions_Dels]
        @deleted_objects_xml XML,
		@dbInstanceId uniqueidentifier
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @deleted_objects_table table
        (
           obj_id  uniqueidentifier
        )
        
        /*                importing deleted objects set   */
        DECLARE @idoc  int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
        INSERT INTO @deleted_objects_table( obj_id )
        SELECT *
        FROM OPENXML( @idoc, N'/root/object',1 )
        WITH
        (
           obj_id uniqueidentifier '@id'
        )
        EXEC sp_xml_removedocument @idoc
        
        DELETE FROM [dbo].[5.Backup.Model.SbSessions]
        WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
    END
GO

/*======== REPLICATED TABLE: 5.Backup.Model.BackupJobSessions*/

/* UPDATES IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_Backup_Model_BackupJobSessions_Changes]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_5_Backup_Model_BackupJobSessions_Changes]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_Backup_Model_BackupJobSessions_Changes]
         @dbInstanceId uniqueidentifier,
         @timeZoneShift int,
         @importDoc XML
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @imported_changes table
        (
           [id] uniqueidentifier,
           [is_retry] bit,
           [total_objects] int,
           [processed_objects] int,
           [total_size] bigint,
           [processed_size] bigint,
           [job_source_type] int,
           [avg_speed] bigint,
           [is_startfull] bit,
           [db_instance_id] uniqueidentifier
        )
        
        /*              IMPORT XML DOCUMENT       */
        DECLARE @idoc int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
        
        INSERT INTO @imported_changes([id],[is_retry],[total_objects],[processed_objects],[total_size],[processed_size],[job_source_type],[avg_speed],[is_startfull],[db_instance_id])
        SELECT *
        FROM OPENXML( @idoc, '/Root/row', 2)
        WITH
        (
           [id] uniqueidentifier 'id',
           [is_retry] bit 'is_retry',
           [total_objects] int 'total_objects',
           [processed_objects] int 'processed_objects',
           [total_size] bigint 'total_size',
           [processed_size] bigint 'processed_size',
           [job_source_type] int 'job_source_type',
           [avg_speed] bigint 'avg_speed',
           [is_startfull] bit 'is_startfull',
           [db_instance_id] uniqueidentifier 'db_instance_id'
        )
        EXEC sp_xml_removedocument @idoc
        
        /*             ADD DBID  FIELD     */
        UPDATE @imported_changes
        SET db_instance_id= @dbInstanceId
        
        /*             TIMEZONE UPDATES     */
        /*      UPDATE EXISTING ROWS        */
        UPDATE [5.Backup.Model.BackupJobSessions]
        SET
            [5.Backup.Model.BackupJobSessions].[is_retry] = src.[is_retry],
            [5.Backup.Model.BackupJobSessions].[total_objects] = src.[total_objects],
            [5.Backup.Model.BackupJobSessions].[processed_objects] = src.[processed_objects],
            [5.Backup.Model.BackupJobSessions].[total_size] = src.[total_size],
            [5.Backup.Model.BackupJobSessions].[processed_size] = src.[processed_size],
            [5.Backup.Model.BackupJobSessions].[job_source_type] = src.[job_source_type],
            [5.Backup.Model.BackupJobSessions].[avg_speed] = src.[avg_speed],
            [5.Backup.Model.BackupJobSessions].[is_startfull] = src.[is_startfull],
            [5.Backup.Model.BackupJobSessions].[db_instance_id] = src.[db_instance_id]
        FROM [5.Backup.Model.BackupJobSessions] INNER JOIN @imported_changes src
        ON [5.Backup.Model.BackupJobSessions].id = src.id
        
        /*      INSERT NEW ROWS       */
        INSERT INTO [5.Backup.Model.BackupJobSessions]([id],[is_retry],[total_objects],[processed_objects],[total_size],[processed_size],[job_source_type],[avg_speed],[is_startfull],[db_instance_id])
        SELECT
            src.[id],
            src.[is_retry],
            src.[total_objects],
            src.[processed_objects],
            src.[total_size],
            src.[processed_size],
            src.[job_source_type],
            src.[avg_speed],
            src.[is_startfull],
            src.[db_instance_id]
        FROM @imported_changes src LEFT OUTER JOIN [5.Backup.Model.BackupJobSessions] trg
        ON src.id = trg.id
        WHERE trg.id IS NULL
    END
GO

/* DELS IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_Backup_Model_BackupJobSessions_Dels]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_5_Backup_Model_BackupJobSessions_Dels]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_Backup_Model_BackupJobSessions_Dels]
        @deleted_objects_xml XML,
		@dbInstanceId uniqueidentifier
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @deleted_objects_table table
        (
           obj_id  uniqueidentifier
        )
        
        /*                importing deleted objects set   */
        DECLARE @idoc  int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
        INSERT INTO @deleted_objects_table( obj_id )
        SELECT *
        FROM OPENXML( @idoc, N'/root/object',1 )
        WITH
        (
           obj_id uniqueidentifier '@id'
        )
        EXEC sp_xml_removedocument @idoc
        
        DELETE FROM [dbo].[5.Backup.Model.BackupJobSessions]
        WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
    END
GO

/*======== REPLICATED TABLE: 5.LicensedHosts*/

/* UPDATES IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_LicensedHosts_Changes]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_5_LicensedHosts_Changes]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_LicensedHosts_Changes]
         @dbInstanceId uniqueidentifier,
         @timeZoneShift int,
         @importDoc XML
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @imported_changes table
        (
           [id] uniqueidentifier,
           [uuid] nvarchar(255),
           [name] nvarchar(255),
           [cpu] int,
           [type] int,
           [db_instance_id] uniqueidentifier,
           [lic_edition] int,
           [is_licensed] bit
        )
        
        /*              IMPORT XML DOCUMENT       */
        DECLARE @idoc int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
        
        INSERT INTO @imported_changes([id],[uuid],[name],[cpu],[type],[db_instance_id],[lic_edition],[is_licensed])
        SELECT *
        FROM OPENXML( @idoc, '/Root/row', 2)
        WITH
        (
           [id] uniqueidentifier 'id',
           [uuid] nvarchar(255) 'uuid',
           [name] nvarchar(255) 'name',
           [cpu] int 'cpu',
           [type] int 'type',
           [db_instance_id] uniqueidentifier 'db_instance_id',
           [lic_edition] int 'lic_edition',
           [is_licensed] bit 'is_licensed'
        )
        EXEC sp_xml_removedocument @idoc
        
        /*             ADD DBID  FIELD     */
        UPDATE @imported_changes
        SET db_instance_id= @dbInstanceId
        
        /*             TIMEZONE UPDATES     */
        /*      UPDATE EXISTING ROWS        */
        UPDATE [5.LicensedHosts]
        SET
            [5.LicensedHosts].[uuid] = src.[uuid],
            [5.LicensedHosts].[name] = src.[name],
            [5.LicensedHosts].[cpu] = src.[cpu],
            [5.LicensedHosts].[type] = src.[type],
            [5.LicensedHosts].[db_instance_id] = src.[db_instance_id],
            [5.LicensedHosts].[lic_edition] = src.[lic_edition],
            [5.LicensedHosts].[is_licensed] = src.[is_licensed]
        FROM [5.LicensedHosts] INNER JOIN @imported_changes src
        ON [5.LicensedHosts].id = src.id
        
        /*      INSERT NEW ROWS       */
        INSERT INTO [5.LicensedHosts]([id],[uuid],[name],[cpu],[type],[db_instance_id],[lic_edition],[is_licensed])
        SELECT
            src.[id],
            src.[uuid],
            src.[name],
            src.[cpu],
            src.[type],
            src.[db_instance_id],
            src.[lic_edition],
            src.[is_licensed]
        FROM @imported_changes src LEFT OUTER JOIN [5.LicensedHosts] trg
        ON src.id = trg.id
        WHERE trg.id IS NULL
    END
GO

/* DELS IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_LicensedHosts_Dels]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_5_LicensedHosts_Dels]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_LicensedHosts_Dels]
        @deleted_objects_xml XML,
		@dbInstanceId uniqueidentifier
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @deleted_objects_table table
        (
           obj_id  uniqueidentifier
        )
        
        /*                importing deleted objects set   */
        DECLARE @idoc  int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
        INSERT INTO @deleted_objects_table( obj_id )
        SELECT *
        FROM OPENXML( @idoc, N'/root/object',1 )
        WITH
        (
           obj_id uniqueidentifier '@id'
        )
        EXEC sp_xml_removedocument @idoc
        
        DELETE FROM [dbo].[5.LicensedHosts]
        WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
    END
GO

/*======== REPLICATED TABLE: 5.Backup.Model.BackupTaskSessions*/

/* UPDATES IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_Backup_Model_BackupTaskSessions_Changes]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_5_Backup_Model_BackupTaskSessions_Changes]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_Backup_Model_BackupTaskSessions_Changes]
         @dbInstanceId uniqueidentifier,
         @timeZoneShift int,
         @importDoc XML
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @imported_changes table
        (
           [id] uniqueidentifier,
           [session_id] uniqueidentifier,
           [creation_time] datetime,
           [object_name] nvarchar(2000),
           [status] int,
           [reason] ntext,
           [object_id] uniqueidentifier,
           [end_time] datetime,
           [operation] nvarchar(400),
           [total_objects] int,
           [processed_objects] int,
           [total_size] bigint,
           [processed_size] bigint,
           [mode] int,
           [avg_speed] bigint,
           [change_tracking] bit,
           [db_instance_id] uniqueidentifier
        )
        
        /*              IMPORT XML DOCUMENT       */
        DECLARE @idoc int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
        
        INSERT INTO @imported_changes([id],[session_id],[creation_time],[object_name],[status],[reason],[object_id],[end_time],[operation],[total_objects],[processed_objects],[total_size],[processed_size],[mode],[avg_speed],[change_tracking],[db_instance_id])
        SELECT *
        FROM OPENXML( @idoc, '/Root/row', 2)
        WITH
        (
           [id] uniqueidentifier 'id',
           [session_id] uniqueidentifier 'session_id',
           [creation_time] datetime 'creation_time',
           [object_name] nvarchar(2000) 'object_name',
           [status] int 'status',
           [reason] ntext 'reason',
           [object_id] uniqueidentifier 'object_id',
           [end_time] datetime 'end_time',
           [operation] nvarchar(400) 'operation',
           [total_objects] int 'total_objects',
           [processed_objects] int 'processed_objects',
           [total_size] bigint 'total_size',
           [processed_size] bigint 'processed_size',
           [mode] int 'mode',
           [avg_speed] bigint 'avg_speed',
           [change_tracking] bit 'change_tracking',
           [db_instance_id] uniqueidentifier 'db_instance_id'
        )
        EXEC sp_xml_removedocument @idoc
        
        /*             ADD DBID  FIELD     */
        UPDATE @imported_changes
        SET db_instance_id= @dbInstanceId
        
        /*             TIMEZONE UPDATES     */
        UPDATE @imported_changes
        SET
        [creation_time] = DATEADD( hour, -@TimeZoneShift, [creation_time]),
        [end_time] = DATEADD( hour, -@TimeZoneShift, [end_time])
        /*      UPDATE EXISTING ROWS        */
        UPDATE [5.Backup.Model.BackupTaskSessions]
        SET
            [5.Backup.Model.BackupTaskSessions].[session_id] = src.[session_id],
            [5.Backup.Model.BackupTaskSessions].[creation_time] = src.[creation_time],
            [5.Backup.Model.BackupTaskSessions].[object_name] = src.[object_name],
            [5.Backup.Model.BackupTaskSessions].[status] = src.[status],
            [5.Backup.Model.BackupTaskSessions].[reason] = src.[reason],
            [5.Backup.Model.BackupTaskSessions].[object_id] = src.[object_id],
            [5.Backup.Model.BackupTaskSessions].[end_time] = src.[end_time],
            [5.Backup.Model.BackupTaskSessions].[operation] = src.[operation],
            [5.Backup.Model.BackupTaskSessions].[total_objects] = src.[total_objects],
            [5.Backup.Model.BackupTaskSessions].[processed_objects] = src.[processed_objects],
            [5.Backup.Model.BackupTaskSessions].[total_size] = src.[total_size],
            [5.Backup.Model.BackupTaskSessions].[processed_size] = src.[processed_size],
            [5.Backup.Model.BackupTaskSessions].[mode] = src.[mode],
            [5.Backup.Model.BackupTaskSessions].[avg_speed] = src.[avg_speed],
            [5.Backup.Model.BackupTaskSessions].[change_tracking] = src.[change_tracking],
            [5.Backup.Model.BackupTaskSessions].[db_instance_id] = src.[db_instance_id]
        FROM [5.Backup.Model.BackupTaskSessions] INNER JOIN @imported_changes src
        ON [5.Backup.Model.BackupTaskSessions].id = src.id
        
        /*      INSERT NEW ROWS       */
        INSERT INTO [5.Backup.Model.BackupTaskSessions]([id],[session_id],[creation_time],[object_name],[status],[reason],[object_id],[end_time],[operation],[total_objects],[processed_objects],[total_size],[processed_size],[mode],[avg_speed],[change_tracking],[db_instance_id])
        SELECT
            src.[id],
            src.[session_id],
            src.[creation_time],
            src.[object_name],
            src.[status],
            src.[reason],
            src.[object_id],
            src.[end_time],
            src.[operation],
            src.[total_objects],
            src.[processed_objects],
            src.[total_size],
            src.[processed_size],
            src.[mode],
            src.[avg_speed],
            src.[change_tracking],
            src.[db_instance_id]
        FROM @imported_changes src LEFT OUTER JOIN [5.Backup.Model.BackupTaskSessions] trg
        ON src.id = trg.id
        WHERE trg.id IS NULL
    END
GO

/* DELS IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_Backup_Model_BackupTaskSessions_Dels]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_5_Backup_Model_BackupTaskSessions_Dels]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_Backup_Model_BackupTaskSessions_Dels]
        @deleted_objects_xml XML,
		@dbInstanceId uniqueidentifier
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @deleted_objects_table table
        (
           obj_id  uniqueidentifier
        )
        
        /*                importing deleted objects set   */
        DECLARE @idoc  int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
        INSERT INTO @deleted_objects_table( obj_id )
        SELECT *
        FROM OPENXML( @idoc, N'/root/object',1 )
        WITH
        (
           obj_id uniqueidentifier '@id'
        )
        EXEC sp_xml_removedocument @idoc
        
        DELETE FROM [dbo].[5.Backup.Model.BackupTaskSessions]
        WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
    END
GO

/*======== REPLICATED TABLE: 5.ObjectsInApplicationGroups*/

/* UPDATES IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_ObjectsInApplicationGroups_Changes]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_5_ObjectsInApplicationGroups_Changes]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_ObjectsInApplicationGroups_Changes]
         @dbInstanceId uniqueidentifier,
         @timeZoneShift int,
         @importDoc XML
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @imported_changes table
        (
           [id] uniqueidentifier,
           [folder_id] uniqueidentifier,
           [object_id] uniqueidentifier,
           [order] int,
           [guest_os_info] xml,
           [effective_memory] bigint,
           [options] xml,
           [db_instance_id] uniqueidentifier
        )
        
        /*              IMPORT XML DOCUMENT       */
        DECLARE @idoc int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
        
        INSERT INTO @imported_changes([id],[folder_id],[object_id],[order],[guest_os_info],[effective_memory],[options],[db_instance_id])
        SELECT *
        FROM OPENXML( @idoc, '/Root/row', 2)
        WITH
        (
           [id] uniqueidentifier 'id',
           [folder_id] uniqueidentifier 'folder_id',
           [object_id] uniqueidentifier 'object_id',
           [order] int 'order',
           [guest_os_info] xml 'guest_os_info',
           [effective_memory] bigint 'effective_memory',
           [options] xml 'options',
           [db_instance_id] uniqueidentifier 'db_instance_id'
        )
        EXEC sp_xml_removedocument @idoc
        
        /*             ADD DBID  FIELD     */
        UPDATE @imported_changes
        SET db_instance_id= @dbInstanceId
        
        /*             TIMEZONE UPDATES     */
        /*      UPDATE EXISTING ROWS        */
        UPDATE [5.ObjectsInApplicationGroups]
        SET
            [5.ObjectsInApplicationGroups].[folder_id] = src.[folder_id],
            [5.ObjectsInApplicationGroups].[object_id] = src.[object_id],
            [5.ObjectsInApplicationGroups].[order] = src.[order],
            [5.ObjectsInApplicationGroups].[guest_os_info] = src.[guest_os_info],
            [5.ObjectsInApplicationGroups].[effective_memory] = src.[effective_memory],
            [5.ObjectsInApplicationGroups].[options] = src.[options],
            [5.ObjectsInApplicationGroups].[db_instance_id] = src.[db_instance_id]
        FROM [5.ObjectsInApplicationGroups] INNER JOIN @imported_changes src
        ON [5.ObjectsInApplicationGroups].id = src.id
        
        /*      INSERT NEW ROWS       */
        INSERT INTO [5.ObjectsInApplicationGroups]([id],[folder_id],[object_id],[order],[guest_os_info],[effective_memory],[options],[db_instance_id])
        SELECT
            src.[id],
            src.[folder_id],
            src.[object_id],
            src.[order],
            src.[guest_os_info],
            src.[effective_memory],
            src.[options],
            src.[db_instance_id]
        FROM @imported_changes src LEFT OUTER JOIN [5.ObjectsInApplicationGroups] trg
        ON src.id = trg.id
        WHERE trg.id IS NULL
    END
GO

/* DELS IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_ObjectsInApplicationGroups_Dels]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_5_ObjectsInApplicationGroups_Dels]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_ObjectsInApplicationGroups_Dels]
        @deleted_objects_xml XML,
		@dbInstanceId uniqueidentifier
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @deleted_objects_table table
        (
           obj_id  uniqueidentifier
        )
        
        /*                importing deleted objects set   */
        DECLARE @idoc  int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
        INSERT INTO @deleted_objects_table( obj_id )
        SELECT *
        FROM OPENXML( @idoc, N'/root/object',1 )
        WITH
        (
           obj_id uniqueidentifier '@id'
        )
        EXEC sp_xml_removedocument @idoc
        
        DELETE FROM [dbo].[5.ObjectsInApplicationGroups]
        WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
    END
GO

/*======== REPLICATED TABLE: 5.SbVerificationRules*/

/* UPDATES IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_SbVerificationRules_Changes]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_5_SbVerificationRules_Changes]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_SbVerificationRules_Changes]
         @dbInstanceId uniqueidentifier,
         @timeZoneShift int,
         @importDoc XML
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @imported_changes table
        (
           [id] uniqueidentifier,
           [job_id] uniqueidentifier,
           [object_id] uniqueidentifier,
           [object_type] int,
           [options] xml,
           [db_instance_id] uniqueidentifier
        )
        
        /*              IMPORT XML DOCUMENT       */
        DECLARE @idoc int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
        
        INSERT INTO @imported_changes([id],[job_id],[object_id],[object_type],[options],[db_instance_id])
        SELECT *
        FROM OPENXML( @idoc, '/Root/row', 2)
        WITH
        (
           [id] uniqueidentifier 'id',
           [job_id] uniqueidentifier 'job_id',
           [object_id] uniqueidentifier 'object_id',
           [object_type] int 'object_type',
           [options] xml 'options',
           [db_instance_id] uniqueidentifier 'db_instance_id'
        )
        EXEC sp_xml_removedocument @idoc
        
        /*             ADD DBID  FIELD     */
        UPDATE @imported_changes
        SET db_instance_id= @dbInstanceId
        
        /*             TIMEZONE UPDATES     */
        /*      UPDATE EXISTING ROWS        */
        UPDATE [5.SbVerificationRules]
        SET
            [5.SbVerificationRules].[job_id] = src.[job_id],
            [5.SbVerificationRules].[object_id] = src.[object_id],
            [5.SbVerificationRules].[object_type] = src.[object_type],
            [5.SbVerificationRules].[options] = src.[options],
            [5.SbVerificationRules].[db_instance_id] = src.[db_instance_id]
        FROM [5.SbVerificationRules] INNER JOIN @imported_changes src
        ON [5.SbVerificationRules].id = src.id
        
        /*      INSERT NEW ROWS       */
        INSERT INTO [5.SbVerificationRules]([id],[job_id],[object_id],[object_type],[options],[db_instance_id])
        SELECT
            src.[id],
            src.[job_id],
            src.[object_id],
            src.[object_type],
            src.[options],
            src.[db_instance_id]
        FROM @imported_changes src LEFT OUTER JOIN [5.SbVerificationRules] trg
        ON src.id = trg.id
        WHERE trg.id IS NULL
    END
GO

/* DELS IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_SbVerificationRules_Dels]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_5_SbVerificationRules_Dels]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_SbVerificationRules_Dels]
        @deleted_objects_xml XML,
		@dbInstanceId uniqueidentifier
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @deleted_objects_table table
        (
           obj_id  uniqueidentifier
        )
        
        /*                importing deleted objects set   */
        DECLARE @idoc  int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
        INSERT INTO @deleted_objects_table( obj_id )
        SELECT *
        FROM OPENXML( @idoc, N'/root/object',1 )
        WITH
        (
           obj_id uniqueidentifier '@id'
        )
        EXEC sp_xml_removedocument @idoc
        
        DELETE FROM [dbo].[5.SbVerificationRules]
        WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
    END
GO

/*======== REPLICATED TABLE: 5.Backup.Model.Points*/

/* UPDATES IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_Backup_Model_Points_Changes]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_5_Backup_Model_Points_Changes]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_Backup_Model_Points_Changes]
         @dbInstanceId uniqueidentifier,
         @timeZoneShift int,
         @importDoc XML
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @imported_changes table
        (
           [id] uniqueidentifier,
           [link_id] uniqueidentifier,
           [creation_time] datetime,
           [type] int,
           [alg] int,
           [group_id] uniqueidentifier,
           [backup_id] uniqueidentifier,
           [db_instance_id] uniqueidentifier,
           [num] numeric
        )
        
        /*              IMPORT XML DOCUMENT       */
        DECLARE @idoc int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
        
        INSERT INTO @imported_changes([id],[link_id],[creation_time],[type],[alg],[group_id],[backup_id],[db_instance_id],[num])
        SELECT *
        FROM OPENXML( @idoc, '/Root/row', 2)
        WITH
        (
           [id] uniqueidentifier 'id',
           [link_id] uniqueidentifier 'link_id',
           [creation_time] datetime 'creation_time',
           [type] int 'type',
           [alg] int 'alg',
           [group_id] uniqueidentifier 'group_id',
           [backup_id] uniqueidentifier 'backup_id',
           [db_instance_id] uniqueidentifier 'db_instance_id',
           [num] numeric 'num'
        )
        EXEC sp_xml_removedocument @idoc
        
        /*             ADD DBID  FIELD     */
        UPDATE @imported_changes
        SET db_instance_id= @dbInstanceId
        
        /*             TIMEZONE UPDATES     */
        UPDATE @imported_changes
        SET
        [creation_time] = DATEADD( hour, -@TimeZoneShift, [creation_time])
        /*      UPDATE EXISTING ROWS        */
        UPDATE [5.Backup.Model.Points]
        SET
            [5.Backup.Model.Points].[link_id] = src.[link_id],
            [5.Backup.Model.Points].[creation_time] = src.[creation_time],
            [5.Backup.Model.Points].[type] = src.[type],
            [5.Backup.Model.Points].[alg] = src.[alg],
            [5.Backup.Model.Points].[group_id] = src.[group_id],
            [5.Backup.Model.Points].[backup_id] = src.[backup_id],
            [5.Backup.Model.Points].[db_instance_id] = src.[db_instance_id],
            [5.Backup.Model.Points].[num] = src.[num]
        FROM [5.Backup.Model.Points] INNER JOIN @imported_changes src
        ON [5.Backup.Model.Points].id = src.id
        
        /*      INSERT NEW ROWS       */
        INSERT INTO [5.Backup.Model.Points]([id],[link_id],[creation_time],[type],[alg],[group_id],[backup_id],[db_instance_id],[num])
        SELECT
            src.[id],
            src.[link_id],
            src.[creation_time],
            src.[type],
            src.[alg],
            src.[group_id],
            src.[backup_id],
            src.[db_instance_id],
            src.[num]
        FROM @imported_changes src LEFT OUTER JOIN [5.Backup.Model.Points] trg
        ON src.id = trg.id
        WHERE trg.id IS NULL
    END
GO

/* DELS IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_Backup_Model_Points_Dels]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_5_Backup_Model_Points_Dels]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_Backup_Model_Points_Dels]
        @deleted_objects_xml XML,
		@dbInstanceId uniqueidentifier
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @deleted_objects_table table
        (
           obj_id  uniqueidentifier
        )
        
        /*                importing deleted objects set   */
        DECLARE @idoc  int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
        INSERT INTO @deleted_objects_table( obj_id )
        SELECT *
        FROM OPENXML( @idoc, N'/root/object',1 )
        WITH
        (
           obj_id uniqueidentifier '@id'
        )
        EXEC sp_xml_removedocument @idoc
        
        DELETE FROM [dbo].[5.Backup.Model.Points]
        WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
    END
GO

/*======== REPLICATED TABLE: 5.Backup.Model.OIBs*/

/* UPDATES IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_Backup_Model_OIBs_Changes]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_5_Backup_Model_OIBs_Changes]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_Backup_Model_OIBs_Changes]
         @dbInstanceId uniqueidentifier,
         @timeZoneShift int,
         @importDoc XML
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @imported_changes table
        (
           [id] uniqueidentifier,
           [object_id] uniqueidentifier,
           [point_id] uniqueidentifier,
           [storage_id] uniqueidentifier,
           [link_id] uniqueidentifier,
           [is_corrupted] bit,
           [is_consistent] bit,
           [state] int,
           [type] int,
           [alg] int,
           [inside_dir] nvarchar(400),
           [creation_time] datetime,
           [vmname] nvarchar(1000),
           [guest_os_info] xml,
           [memory] bigint,
           [approx_size] bigint,
           [process_id] int,
           [has_index] bit,
           [aux_data] xml,
           [db_instance_id] uniqueidentifier
        )
        
        /*              IMPORT XML DOCUMENT       */
        DECLARE @idoc int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
        
        INSERT INTO @imported_changes([id],[object_id],[point_id],[storage_id],[link_id],[is_corrupted],[is_consistent],[state],[type],[alg],[inside_dir],[creation_time],[vmname],[guest_os_info],[memory],[approx_size],[process_id],[has_index],[aux_data],[db_instance_id])
        SELECT *
        FROM OPENXML( @idoc, '/Root/row', 2)
        WITH
        (
           [id] uniqueidentifier 'id',
           [object_id] uniqueidentifier 'object_id',
           [point_id] uniqueidentifier 'point_id',
           [storage_id] uniqueidentifier 'storage_id',
           [link_id] uniqueidentifier 'link_id',
           [is_corrupted] bit 'is_corrupted',
           [is_consistent] bit 'is_consistent',
           [state] int 'state',
           [type] int 'type',
           [alg] int 'alg',
           [inside_dir] nvarchar(400) 'inside_dir',
           [creation_time] datetime 'creation_time',
           [vmname] nvarchar(1000) 'vmname',
           [guest_os_info] xml 'guest_os_info',
           [memory] bigint 'memory',
           [approx_size] bigint 'approx_size',
           [process_id] int 'process_id',
           [has_index] bit 'has_index',
           [aux_data] xml 'aux_data',
           [db_instance_id] uniqueidentifier 'db_instance_id'
        )
        EXEC sp_xml_removedocument @idoc
        
        /*             ADD DBID  FIELD     */
        UPDATE @imported_changes
        SET db_instance_id= @dbInstanceId
        
        /*             TIMEZONE UPDATES     */
        UPDATE @imported_changes
        SET
        [creation_time] = DATEADD( hour, -@TimeZoneShift, [creation_time])
        /*      UPDATE EXISTING ROWS        */
        UPDATE [5.Backup.Model.OIBs]
        SET
            [5.Backup.Model.OIBs].[object_id] = src.[object_id],
            [5.Backup.Model.OIBs].[point_id] = src.[point_id],
            [5.Backup.Model.OIBs].[storage_id] = src.[storage_id],
            [5.Backup.Model.OIBs].[link_id] = src.[link_id],
            [5.Backup.Model.OIBs].[is_corrupted] = src.[is_corrupted],
            [5.Backup.Model.OIBs].[is_consistent] = src.[is_consistent],
            [5.Backup.Model.OIBs].[state] = src.[state],
            [5.Backup.Model.OIBs].[type] = src.[type],
            [5.Backup.Model.OIBs].[alg] = src.[alg],
            [5.Backup.Model.OIBs].[inside_dir] = src.[inside_dir],
            [5.Backup.Model.OIBs].[creation_time] = src.[creation_time],
            [5.Backup.Model.OIBs].[vmname] = src.[vmname],
            [5.Backup.Model.OIBs].[guest_os_info] = src.[guest_os_info],
            [5.Backup.Model.OIBs].[memory] = src.[memory],
            [5.Backup.Model.OIBs].[approx_size] = src.[approx_size],
            [5.Backup.Model.OIBs].[process_id] = src.[process_id],
            [5.Backup.Model.OIBs].[has_index] = src.[has_index],
            [5.Backup.Model.OIBs].[aux_data] = src.[aux_data],
            [5.Backup.Model.OIBs].[db_instance_id] = src.[db_instance_id]
        FROM [5.Backup.Model.OIBs] INNER JOIN @imported_changes src
        ON [5.Backup.Model.OIBs].id = src.id
        
        /*      INSERT NEW ROWS       */
        INSERT INTO [5.Backup.Model.OIBs]([id],[object_id],[point_id],[storage_id],[link_id],[is_corrupted],[is_consistent],[state],[type],[alg],[inside_dir],[creation_time],[vmname],[guest_os_info],[memory],[approx_size],[process_id],[has_index],[aux_data],[db_instance_id])
        SELECT
            src.[id],
            src.[object_id],
            src.[point_id],
            src.[storage_id],
            src.[link_id],
            src.[is_corrupted],
            src.[is_consistent],
            src.[state],
            src.[type],
            src.[alg],
            src.[inside_dir],
            src.[creation_time],
            src.[vmname],
            src.[guest_os_info],
            src.[memory],
            src.[approx_size],
            src.[process_id],
            src.[has_index],
            src.[aux_data],
            src.[db_instance_id]
        FROM @imported_changes src LEFT OUTER JOIN [5.Backup.Model.OIBs] trg
        ON src.id = trg.id
        WHERE trg.id IS NULL
    END
GO

/* DELS IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_Backup_Model_OIBs_Dels]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_5_Backup_Model_OIBs_Dels]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_Backup_Model_OIBs_Dels]
        @deleted_objects_xml XML,
		@dbInstanceId uniqueidentifier
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @deleted_objects_table table
        (
           obj_id  uniqueidentifier
        )
        
        /*                importing deleted objects set   */
        DECLARE @idoc  int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
        INSERT INTO @deleted_objects_table( obj_id )
        SELECT *
        FROM OPENXML( @idoc, N'/root/object',1 )
        WITH
        (
           obj_id uniqueidentifier '@id'
        )
        EXEC sp_xml_removedocument @idoc
        
        DELETE FROM [dbo].[5.Backup.Model.OIBs]
        WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
    END
GO

/*======== REPLICATED TABLE: 5.LinkedJobs*/

/* UPDATES IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_LinkedJobs_Changes]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_5_LinkedJobs_Changes]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_LinkedJobs_Changes]
         @dbInstanceId uniqueidentifier,
         @timeZoneShift int,
         @importDoc XML
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @imported_changes table
        (
           [id] uniqueidentifier,
           [job_id] uniqueidentifier,
           [linked_job_id] uniqueidentifier,
           [db_instance_id] uniqueidentifier
        )
        
        /*              IMPORT XML DOCUMENT       */
        DECLARE @idoc int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
        
        INSERT INTO @imported_changes([id],[job_id],[linked_job_id],[db_instance_id])
        SELECT *
        FROM OPENXML( @idoc, '/Root/row', 2)
        WITH
        (
           [id] uniqueidentifier 'id',
           [job_id] uniqueidentifier 'job_id',
           [linked_job_id] uniqueidentifier 'linked_job_id',
           [db_instance_id] uniqueidentifier 'db_instance_id'
        )
        EXEC sp_xml_removedocument @idoc
        
        /*             ADD DBID  FIELD     */
        UPDATE @imported_changes
        SET db_instance_id= @dbInstanceId
        
        /*             TIMEZONE UPDATES     */
        /*      UPDATE EXISTING ROWS        */
        UPDATE [5.LinkedJobs]
        SET
            [5.LinkedJobs].[job_id] = src.[job_id],
            [5.LinkedJobs].[linked_job_id] = src.[linked_job_id],
            [5.LinkedJobs].[db_instance_id] = src.[db_instance_id]
        FROM [5.LinkedJobs] INNER JOIN @imported_changes src
        ON [5.LinkedJobs].id = src.id
        
        /*      INSERT NEW ROWS       */
        INSERT INTO [5.LinkedJobs]([id],[job_id],[linked_job_id],[db_instance_id])
        SELECT
            src.[id],
            src.[job_id],
            src.[linked_job_id],
            src.[db_instance_id]
        FROM @imported_changes src LEFT OUTER JOIN [5.LinkedJobs] trg
        ON src.id = trg.id
        WHERE trg.id IS NULL
    END
GO

/* DELS IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_LinkedJobs_Dels]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_5_LinkedJobs_Dels]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_LinkedJobs_Dels]
        @deleted_objects_xml XML,
		@dbInstanceId uniqueidentifier
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @deleted_objects_table table
        (
           obj_id  uniqueidentifier
        )
        
        /*                importing deleted objects set   */
        DECLARE @idoc  int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
        INSERT INTO @deleted_objects_table( obj_id )
        SELECT *
        FROM OPENXML( @idoc, N'/root/object',1 )
        WITH
        (
           obj_id uniqueidentifier '@id'
        )
        EXEC sp_xml_removedocument @idoc
        
        DELETE FROM [dbo].[5.LinkedJobs]
        WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
    END
GO

/*======== REPLICATED TABLE: 5.Backup.Model.Backups*/

/* UPDATES IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_Backup_Model_Backups_Changes]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_5_Backup_Model_Backups_Changes]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_Backup_Model_Backups_Changes]
         @dbInstanceId uniqueidentifier,
         @timeZoneShift int,
         @importDoc XML
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @imported_changes table
        (
           [id] uniqueidentifier,
           [job_id] uniqueidentifier,
           [job_name] nvarchar(255),
           [job_source_type] int,
           [job_target_type] int,
           [job_target_host_id] uniqueidentifier,
           [job_target_host_protocol] int,
           [db_instance_id] uniqueidentifier
        )
        
        /*              IMPORT XML DOCUMENT       */
        DECLARE @idoc int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
        
        INSERT INTO @imported_changes([id],[job_id],[job_name],[job_source_type],[job_target_type],[job_target_host_id],[job_target_host_protocol],[db_instance_id])
        SELECT *
        FROM OPENXML( @idoc, '/Root/row', 2)
        WITH
        (
           [id] uniqueidentifier 'id',
           [job_id] uniqueidentifier 'job_id',
           [job_name] nvarchar(255) 'job_name',
           [job_source_type] int 'job_source_type',
           [job_target_type] int 'job_target_type',
           [job_target_host_id] uniqueidentifier 'job_target_host_id',
           [job_target_host_protocol] int 'job_target_host_protocol',
           [db_instance_id] uniqueidentifier 'db_instance_id'
        )
        EXEC sp_xml_removedocument @idoc
        
        /*             ADD DBID  FIELD     */
        UPDATE @imported_changes
        SET db_instance_id= @dbInstanceId
        
        /*             TIMEZONE UPDATES     */
        /*      UPDATE EXISTING ROWS        */
        UPDATE [5.Backup.Model.Backups]
        SET
            [5.Backup.Model.Backups].[job_id] = src.[job_id],
            [5.Backup.Model.Backups].[job_name] = src.[job_name],
            [5.Backup.Model.Backups].[job_source_type] = src.[job_source_type],
            [5.Backup.Model.Backups].[job_target_type] = src.[job_target_type],
            [5.Backup.Model.Backups].[job_target_host_id] = src.[job_target_host_id],
            [5.Backup.Model.Backups].[job_target_host_protocol] = src.[job_target_host_protocol],
            [5.Backup.Model.Backups].[db_instance_id] = src.[db_instance_id]
        FROM [5.Backup.Model.Backups] INNER JOIN @imported_changes src
        ON [5.Backup.Model.Backups].id = src.id
        
        /*      INSERT NEW ROWS       */
        INSERT INTO [5.Backup.Model.Backups]([id],[job_id],[job_name],[job_source_type],[job_target_type],[job_target_host_id],[job_target_host_protocol],[db_instance_id])
        SELECT
            src.[id],
            src.[job_id],
            src.[job_name],
            src.[job_source_type],
            src.[job_target_type],
            src.[job_target_host_id],
            src.[job_target_host_protocol],
            src.[db_instance_id]
        FROM @imported_changes src LEFT OUTER JOIN [5.Backup.Model.Backups] trg
        ON src.id = trg.id
        WHERE trg.id IS NULL
    END
GO

/* DELS IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_Backup_Model_Backups_Dels]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_5_Backup_Model_Backups_Dels]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_Backup_Model_Backups_Dels]
        @deleted_objects_xml XML,
		@dbInstanceId uniqueidentifier
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @deleted_objects_table table
        (
           obj_id  uniqueidentifier
        )
        
        /*                importing deleted objects set   */
        DECLARE @idoc  int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
        INSERT INTO @deleted_objects_table( obj_id )
        SELECT *
        FROM OPENXML( @idoc, N'/root/object',1 )
        WITH
        (
           obj_id uniqueidentifier '@id'
        )
        EXEC sp_xml_removedocument @idoc
        
        DELETE FROM [dbo].[5.Backup.Model.Backups]
        WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
    END
GO

/*======== REPLICATED TABLE: 5.Backup.Model.Storages*/

/* UPDATES IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_Backup_Model_Storages_Changes]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_5_Backup_Model_Storages_Changes]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_Backup_Model_Storages_Changes]
         @dbInstanceId uniqueidentifier,
         @timeZoneShift int,
         @importDoc XML
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @imported_changes table
        (
           [id] uniqueidentifier,
           [file_path] nvarchar(1000),
           [backup_id] uniqueidentifier,
           [host_id] uniqueidentifier,
           [creation_time] datetime,
           [modification_time] datetime,
           [stats] xml,
           [version] int,
           [block_size] int,
           [db_instance_id] uniqueidentifier,
           [full_size] bigint
        )
        
        /*              IMPORT XML DOCUMENT       */
        DECLARE @idoc int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
        
        INSERT INTO @imported_changes([id],[file_path],[backup_id],[host_id],[creation_time],[modification_time],[stats],[version],[block_size],[db_instance_id],[full_size])
        SELECT *, 0 full_size
        FROM OPENXML( @idoc, '/Root/row', 2)
        WITH
        (
           [id] uniqueidentifier 'id',
           [file_path] nvarchar(1000) 'file_path',
           [backup_id] uniqueidentifier 'backup_id',
           [host_id] uniqueidentifier 'host_id',
           [creation_time] datetime 'creation_time',
           [modification_time] datetime 'modification_time',
           [stats] xml 'stats',
           [version] int 'version',
           [block_size] int 'block_size',
           [db_instance_id] uniqueidentifier 'db_instance_id'           
        )
        EXEC sp_xml_removedocument @idoc
        
        /*             ADD DBID  FIELD     */
        UPDATE @imported_changes
        SET db_instance_id= @dbInstanceId
        
        /*             TIMEZONE UPDATES     */
        UPDATE @imported_changes
        SET
        [creation_time] = DATEADD( hour, -@TimeZoneShift, [creation_time]),
        [modification_time] = DATEADD( hour, -@TimeZoneShift, [modification_time])
        /*      UPDATE EXISTING ROWS        */
        UPDATE [5.Backup.Model.Storages]
        SET
            [5.Backup.Model.Storages].[file_path] = src.[file_path],
            [5.Backup.Model.Storages].[backup_id] = src.[backup_id],
            [5.Backup.Model.Storages].[host_id] = src.[host_id],
            [5.Backup.Model.Storages].[creation_time] = src.[creation_time],
            [5.Backup.Model.Storages].[modification_time] = src.[modification_time],
            [5.Backup.Model.Storages].[stats] = src.[stats],
            [5.Backup.Model.Storages].[version] = src.[version],
            [5.Backup.Model.Storages].[block_size] = src.[block_size],
            [5.Backup.Model.Storages].[db_instance_id] = src.[db_instance_id],
            [5.Backup.Model.Storages].[full_size] = src.[full_size]
        FROM [5.Backup.Model.Storages] INNER JOIN @imported_changes src
        ON [5.Backup.Model.Storages].id = src.id
        
        /*      INSERT NEW ROWS       */
        INSERT INTO [5.Backup.Model.Storages]([id],[file_path],[backup_id],[host_id],[creation_time],[modification_time],[stats],[version],[block_size],[db_instance_id],[full_size])
        SELECT
            src.[id],
            src.[file_path],
            src.[backup_id],
            src.[host_id],
            src.[creation_time],
            src.[modification_time],
            src.[stats],
            src.[version],
            src.[block_size],
            src.[db_instance_id],
            src.[full_size]
        FROM @imported_changes src LEFT OUTER JOIN [5.Backup.Model.Storages] trg
        ON src.id = trg.id
        WHERE trg.id IS NULL
    END
GO

/* DELS IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_Backup_Model_Storages_Dels]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_5_Backup_Model_Storages_Dels]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_Backup_Model_Storages_Dels]
        @deleted_objects_xml XML,
		@dbInstanceId uniqueidentifier
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @deleted_objects_table table
        (
           obj_id  uniqueidentifier
        )
        
        /*                importing deleted objects set   */
        DECLARE @idoc  int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
        INSERT INTO @deleted_objects_table( obj_id )
        SELECT *
        FROM OPENXML( @idoc, N'/root/object',1 )
        WITH
        (
           obj_id uniqueidentifier '@id'
        )
        EXEC sp_xml_removedocument @idoc
        
        DELETE FROM [dbo].[5.Backup.Model.Storages]
        WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
    END
GO

/*======== REPLICATED TABLE: 5.Hosts*/

/* UPDATES IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_Hosts_Changes]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_5_Hosts_Changes]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_Hosts_Changes]
         @dbInstanceId uniqueidentifier,
         @timeZoneShift int,
         @importDoc XML
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @imported_changes table
        (
           [id] uniqueidentifier,
           [type] int,
           [name] nvarchar(255),
           [ip] nvarchar(50),
           [parent_id] uniqueidentifier,
           [info] nvarchar(255),
           [description] nvarchar(1024),
           [protocol] int,
           [reference] nvarchar(1024),
           [api_version] int,
           [db_instance_id] uniqueidentifier,
           [win_creds] xml
        )
        
        /*              IMPORT XML DOCUMENT       */
        DECLARE @idoc int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @importDoc
        
        INSERT INTO @imported_changes([id],[type],[name],[ip],[parent_id],[info],[description],[protocol],[reference],[api_version],[db_instance_id],[win_creds])
        SELECT *
        FROM OPENXML( @idoc, '/Root/row', 2)
        WITH
        (
           [id] uniqueidentifier 'id',
           [type] int 'type',
           [name] nvarchar(255) 'name',
           [ip] nvarchar(50) 'ip',
           [parent_id] uniqueidentifier 'parent_id',
           [info] nvarchar(255) 'info',
           [description] nvarchar(1024) 'description',
           [protocol] int 'protocol',
           [reference] nvarchar(1024) 'reference',
           [api_version] int 'api_version',
           [db_instance_id] uniqueidentifier 'db_instance_id',
           [win_creds] xml 'win_creds'
        )
        EXEC sp_xml_removedocument @idoc
        
        /*             ADD DBID  FIELD     */
        UPDATE @imported_changes
        SET db_instance_id= @dbInstanceId
        
        /*             TIMEZONE UPDATES     */
        /*      UPDATE EXISTING ROWS        */
        UPDATE [5.Hosts]
        SET
            [5.Hosts].[type] = src.[type],
            [5.Hosts].[name] = src.[name],
            [5.Hosts].[ip] = src.[ip],
            [5.Hosts].[parent_id] = src.[parent_id],
            [5.Hosts].[info] = src.[info],
            [5.Hosts].[description] = src.[description],
            [5.Hosts].[protocol] = src.[protocol],
            [5.Hosts].[reference] = src.[reference],
            [5.Hosts].[api_version] = src.[api_version],
            [5.Hosts].[db_instance_id] = src.[db_instance_id],
            [5.Hosts].[win_creds] = src.[win_creds]
        FROM [5.Hosts] INNER JOIN @imported_changes src
        ON [5.Hosts].id = src.id
        
        /*      INSERT NEW ROWS       */
        INSERT INTO [5.Hosts]([id],[type],[name],[ip],[parent_id],[info],[description],[protocol],[reference],[api_version],[db_instance_id],[win_creds])
        SELECT
            src.[id],
            src.[type],
            src.[name],
            src.[ip],
            src.[parent_id],
            src.[info],
            src.[description],
            src.[protocol],
            src.[reference],
            src.[api_version],
            src.[db_instance_id],
            src.[win_creds]
        FROM @imported_changes src LEFT OUTER JOIN [5.Hosts] trg
        ON src.id = trg.id
        WHERE trg.id IS NULL
    END
GO

/* DELS IMPORT*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Import_5_Hosts_Dels]') AND type in (N'P'))
   BEGIN
       DROP PROCEDURE [dbo].[usp_Import_5_Hosts_Dels]
   END
GO
    CREATE PROCEDURE [dbo].[usp_Import_5_Hosts_Dels]
        @deleted_objects_xml XML,
		@dbInstanceId uniqueidentifier
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @deleted_objects_table table
        (
           obj_id  uniqueidentifier
        )
        
        /*                importing deleted objects set   */
        DECLARE @idoc  int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @deleted_objects_xml
        INSERT INTO @deleted_objects_table( obj_id )
        SELECT *
        FROM OPENXML( @idoc, N'/root/object',1 )
        WITH
        (
           obj_id uniqueidentifier '@id'
        )
        EXEC sp_xml_removedocument @idoc
        
        DELETE FROM [dbo].[5.Hosts]
        WHERE id IN ( SELECT obj_id FROM @deleted_objects_table )
    END
GO
