/*****************************************************************************
 * usp_update_esg_views
 * This procedure drops and recreates the views for the catalog database
 * that union the partition tables.
 *
 * Context: Written for Enterprise Reporting 
 *
 * Usage: exec usp_update_esg_views
 *
 * Dependencies: dbo.wse_partitions and optional dbo.usp_log
 *
 * Unit Test: create and execute in the context of the catalog database.
 *
 *****************************************************************************/
if exists (select * from sysobjects where id = object_id(N'dbo.usp_update_esg_views') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure dbo.usp_update_esg_views;
go

create procedure dbo.usp_update_esg_views (@debug tinyint = 0)
as
begin
    set NOCOUNT on;

    if @debug = 1 print N'==>Enter usp_update_esg_views()';

    declare @ErrNum int, @ErrMsg varchar(1000);

  ---------------
  -- Validation
  ---------------
    if not exists (select * from INFORMATION_SCHEMA.TABLES where Upper(TABLE_NAME) = N'wse_partitions' and Upper(TABLE_SCHEMA) = N'DBO')
    begin
        select @ErrNum = 1, @ErrMsg = N'usp_update_esg_views: missing dependency = dbo.wse_partitions';
        goto Error_Handler;
    end
  

    -- need to validate all databases exists
    begin transaction;

   ALTER TABLE dbo.wse_partitions DISABLE TRIGGER wsPartitions_Trigger;
    update dbo.wse_partitions set DELETED = 1, LAST_UPDATED = getDate() 
    where upper([DB_NAME])  COLLATE database_default not in (
      select upper([NAME]) from MASTER..SYSDATABASES where upper([name]) COLLATE database_default in (
         select upper([DB_NAME]) from dbo.wse_partitions with (nolock) where offline = 0 and DELETED = 0
      )
    ) and offline = 0 and deleted = 0;
   

   ALTER TABLE dbo.WSE_PARTITIONS ENABLE TRIGGER wsPartitions_Trigger ;
    select @ErrNum = @@Error;
    if (@@Error <>0 and @ErrNum <> 0)
    begin
		set @ErrMsg = N'usp_update_esg_views: enable trigger wsPartitions_Trigger';

        rollback transaction;
        goto Error_Handler;
    end
   
    commit transaction;
  
    declare @esg_detail_incoming_connection_sql					nvarchar(1000),
			@esg_detail_message_sql								nvarchar(1000),
			@esg_detail_saas_sql								nvarchar(1000),
			@esg_detail_message_queue_sql						nvarchar(1000),
			@esg_detail_delivery_connection_sql					nvarchar(1000),
			@esg_detail_delivery_recipient_sql					nvarchar(1000),
			@esg_detail_policy_process_results_sql				nvarchar(1000),
			--@esg_detail_dlp_process_results_sql					nvarchar(1000),
			@esg_detail_dlp_policy_sql							nvarchar(1000),  
			@esg_summary_logview_message_sql					nvarchar(1000),
        	@esg_summary_incoming_connection_sql				nvarchar(1000),
			@esg_summary_incoming_connection_hour_sql			nvarchar(1000),
			--@esg_summary_saas_hour_sql							nvarchar(1000),
			--@esg_summary_saas_sql								nvarchar(1000),
			--@esg_summary_message_queue_hour_sql					nvarchar(1000),
			--@esg_summary_message_queue_sql						nvarchar(1000),
			@esg_summary_delivery_connection_sql				nvarchar(1000),
			@esg_summary_delivery_recipient_sql					nvarchar(1000),
			@esg_summary_dlp_process_results_incident_sql		nvarchar(1000),
			@esg_summary_dlp_process_results_policy_sql			nvarchar(1000),
			@esg_summary_process_results_virus_sql								nvarchar(1000),
			@esg_summary_process_results_message_total_sql						nvarchar(1000),
			@esg_summary_process_results_message_direction_sql				    nvarchar(1000),
			@esg_summary_process_results_message_direction_type_sql				nvarchar(1000),
			@esg_summary_process_results_message_action_type_sql				nvarchar(1000),
			@esg_summary_process_results_message_address_sql					nvarchar(1000),
			@esg_summary_process_results_message_address_direction_sql			nvarchar(1000),
			@esg_summary_process_results_message_address_direction_type_sql		nvarchar(1000),
			@esg_summary_process_results_message_address_direction_action_sql	nvarchar(1000),
			@esg_summary_process_results_message_domain_direction_type_sql		nvarchar(1000),
			@esg_summary_process_results_message_domain_direction_sql			nvarchar(1000),
			@v_esg_summary_connection_total_sql nvarchar(1000),
			@dbname               sysname, 
            @PartitionName        sysname,
            @count                tinyint,
            @found                tinyint,
            @created_date         datetime;

    ----------------------------------
    -- Check current database name
    ----------------------------------
    set @found = 0;

    select top 1 @created_date = created_date, @PartitionName = [db_name]
      from dbo.wse_partitions with (nolock) where offline = 0 and deleted= 0
     order by created_date desc;
   

    ----------------------------------------------------------------------------
    -- Iterate through partitions, drop views and build CREATE VIEW statements
    ----------------------------------------------------------------------------
    while @found < 1 and @created_date is not null
    begin
		if @debug = 1 print @partitionName;
        -- esg views only if the tables are available, skip if esg tables are not there
        exec dbo.usp_db_verify_esg_content @PartitionName, @count OUTPUT, @debug;

		if @debug = 1 print @count;

        if @count > 0 -- esg detailed log tables exist in current partition database 
        begin
			-- esg_detail_incoming_connection view			
			-------------------------------------- 
            if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_detail_incoming_connection')
            begin
                drop view dbo.esg_detail_incoming_connection;
                set @ErrNum = @@ERROR;
                if @ErrNum <> 0
                begin
                    set @ErrMsg = N'usp_update_esg_views() error N' + Cast(@ErrNum as varchar(10)) + N' while dropping view esg_detail_incoming_connection.';
                    goto Error_Handler;
                end
            end -- if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_detail_incoming_connection')
         	set @esg_detail_incoming_connection_sql = 
            N'create view dbo.esg_detail_incoming_connection (
				[record_number],[date_time],[esg_id],[esg_connection_category_id],
				[esg_transport_type_id],[esg_connection_id], [source_ip_address_id], 
				[destination_ip_address_id],[recorded_date_time] 
              ) as select * from ' + @PartitionName + N'.dbo.esg_detail_incoming_connection';
			
			-- esg_detail_message view
			-----------------------------------
			if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_detail_message')
            begin
                drop view dbo.esg_detail_message;
                set @ErrNum = @@ERROR;
                if @ErrNum <> 0
                begin
                    set @ErrMsg = N'usp_update_esg_views() error N' + Cast(@ErrNum as varchar(10)) + N' while dropping view esg_detail_message.';
                    goto Error_Handler;
                end
            end -- if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_detail_message')
			set @esg_detail_message_sql = 
            N'create view dbo.esg_detail_message (
				[record_number],[date_time],[esg_id],[esg_message_id], [esg_connection_id], 
				[sender_email_address_id], [message_size],
				[number_of_attachment], [subject], [recipients], [attachment_names],[reference_message_guid], 
				[recorded_date_time], [scan_time]				
              ) as select * from ' + @PartitionName + N'.dbo.esg_detail_message';
			
			-- esg_detail_saas view
			-----------------------------------
			if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_detail_saas')
            begin
                drop view dbo.esg_detail_saas;
                set @ErrNum = @@ERROR;
                if @ErrNum <> 0
                begin
                    set @ErrMsg = N'usp_update_esg_views() error N' + Cast(@ErrNum as varchar(10)) + N' while dropping view esg_detail_saas.';
                    goto Error_Handler;
                end
            end -- if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_detail_saas')

			set @esg_detail_saas_sql = 
            N'create view dbo.esg_detail_saas (
				[record_number], [date_time],[esg_id], 
				[esg_action_type_id], [hits],[size]   	
              ) as select * from ' + @PartitionName + N'.dbo.esg_detail_saas';
			
			-- esg_detail_message_queue view
			-----------------------------------
			if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_detail_message_queue')
            begin
                drop view dbo.esg_detail_message_queue;
                set @ErrNum = @@ERROR;
                if @ErrNum <> 0
                begin
                    set @ErrMsg = N'usp_update_esg_views() error N' + Cast(@ErrNum as varchar(10)) + N' while dropping view esg_detail_message_queue.';
                    goto Error_Handler;
                end
            end -- if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_detail_message_queue')

			set @esg_detail_message_queue_sql = 
            N'create view dbo.esg_detail_message_queue (
				[record_number], [date_time], [esg_id], 
				[incoming_queue_num], [delivering_queue_num], [defered_queue_num]   	
              ) as select * from ' + @PartitionName + N'.dbo.esg_detail_message_queue';
			
			
			-- esg_detail_delivery_connection view
			----------------------------------
			if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_detail_delivery_connection')
            begin
                drop view dbo.esg_detail_delivery_connection;
                set @ErrNum = @@ERROR;
                if @ErrNum <> 0
                begin
                    set @ErrMsg = N'usp_update_esg_views() error N' + Cast(@ErrNum as varchar(10)) + N' while dropping view esg_detail_delivery_connection.';
                    goto Error_Handler;
                end
            end      
			set @esg_detail_delivery_connection_sql = 
				N'create view dbo.esg_detail_delivery_connection (
				  [record_number],[date_time],[esg_id],[esg_transport_type_id],[esg_encrypted_delivery_id],
					[esg_delivery_connection_id],[destination_ip_address_id],
				  [source_ip_address_id],[esg_connection_status_id],[recorded_date_time]					
				) as select * from ' + @PartitionName + N'.dbo.esg_detail_delivery_connection';
	
			-- esg_detail_delivery_recipient view
			----------------------------------
			if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_detail_delivery_recipient')
            begin
                drop view dbo.esg_detail_delivery_recipient;
                set @ErrNum = @@ERROR;
                if @ErrNum <> 0
                begin
                    set @ErrMsg = N'usp_update_esg_views() error N' + Cast(@ErrNum as varchar(10)) + N' while dropping view esg_detail_delivery_recipient.';
                    goto Error_Handler;
                end
            end   
			set @esg_detail_delivery_recipient_sql = 
				N'  create view dbo.esg_detail_delivery_recipient(
					[record_number],[date_time],[esg_delivery_connection_id],[esg_message_id],
					[recipient_email_address_id],[esg_delivery_code_id],[esg_delivery_status_id],
					[esg_id],[need_delivered_recipient_num],[recorded_date_time],[delivery_code_info]				
				 ) as select * from ' + @PartitionName + N'.dbo.esg_detail_delivery_recipient';
 
			-- esg_detail_policy_process_results view
			----------------------------------
			if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_detail_policy_process_results')
            begin
                drop view dbo.esg_detail_policy_process_results;
                set @ErrNum = @@ERROR;
                if @ErrNum <> 0
                begin
                    set @ErrMsg = N'usp_update_esg_views() error N' + Cast(@ErrNum as varchar(10)) + N' while dropping view esg_detail_policy_process_results.';
                    goto Error_Handler;
                end
            end  
			set @esg_detail_policy_process_results_sql = 
			N' create view dbo.esg_detail_policy_process_results(
				[record_number], [date_time], [esg_message_id],	[esg_direction_id],	
				[esg_message_type_id], [sender_email_address_id], [recipient_email_address_id],
				[esg_action_type_id], [esg_virus_id], [esg_policy_name_id],
				[esg_rule_id], [esg_id],[message_size],[recorded_date_time]
				 ) as select * from ' + @PartitionName + N'.dbo.esg_detail_policy_process_results';
			
			---- esg_detail_dlp_process_results view
			------------------------------------
			--if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_detail_dlp_process_results')
   --         begin
   --             drop view dbo.esg_detail_dlp_process_results;
   --             set @ErrNum = @@ERROR;
   --             if @ErrNum <> 0
   --             begin
   --                 set @ErrMsg = N'usp_update_esg_views() error N' + Cast(@ErrNum as varchar(10)) + N' while dropping view esg_detail_dlp_process_results.';
   --                 goto Error_Handler;
   --             end
   --         end      
			--set @esg_detail_dlp_process_results_sql	= 
			--N' create view dbo.esg_detail_dlp_process_results(
			--[record_number], [date_time], [dlp_transaction_id],	[esg_message_id],
			--[esg_direction_id],	[esg_action_type_id], [esg_dlp_severity_level_id],	
			--[sender_email_address_id], [recipient_email_address_id], [esg_policy_name_id],
			--[esg_id],[message_size],[recorded_date_time]	
			--) as select * from ' + @PartitionName + N'.dbo.esg_detail_dlp_process_results';
 
			-- esg_summary_incoming_connection view
			----------------------------------
			if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_summary_incoming_connection')
            begin
                drop view dbo.esg_summary_incoming_connection;
                set @ErrNum = @@ERROR;
                if @ErrNum <> 0
                begin
                    set @ErrMsg = N'usp_update_esg_views() error N' + Cast(@ErrNum as varchar(10)) + N' while dropping view esg_summary_incoming_connection.';
                    goto Error_Handler;
                end
            end             
			set @esg_summary_incoming_connection_sql = 
				N'create view dbo.esg_summary_incoming_connection (
				  [record_number],[date_time],[esg_id],[esg_connection_category_id],
					[esg_transport_type_id],[hits]				 					
				) as select * from ' + @PartitionName + N'.dbo.esg_summary_incoming_connection';
			
			-- esg_summary_incoming_connection_hour view
			---------------------------------------------
			if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_summary_incoming_connection_hour')
            begin
                drop view dbo.esg_summary_incoming_connection_hour;
                set @ErrNum = @@ERROR;
                if @ErrNum <> 0
                begin
                    set @ErrMsg = N'usp_update_esg_views() error N' + Cast(@ErrNum as varchar(10)) + N' while dropping view esg_summary_incoming_connection_hour.';
                    goto Error_Handler;
                end
            end      
			set @esg_summary_incoming_connection_hour_sql =
				N' create view dbo.esg_summary_incoming_connection_hour(
				  [record_number],[date_time],[esg_id],[esg_connection_category_id],[hits],[hour]
				 ) as select * from ' + @PartitionName + N'.dbo.esg_summary_incoming_connection_hour';
 
			---- esg_summary_saas_hour view
			------------------------------------
			--if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_summary_saas_hour')
   --         begin
   --             drop view dbo.esg_summary_saas_hour;
   --             set @ErrNum = @@ERROR;
   --             if @ErrNum <> 0
   --             begin
   --                 set @ErrMsg = N'usp_update_esg_views() error N' + Cast(@ErrNum as varchar(10)) + N' while dropping view esg_summary_saas_hour.';
   --                 goto Error_Handler;
   --             end
   --         end      
			--set @esg_summary_saas_hour_sql = 
			--	N' create view dbo.esg_summary_saas_hour(
			--	[record_number],[date_time], [esg_id],[esg_action_type_id],[hits],[size],[hour]
			--	 ) as select * from ' + @PartitionName + N'.dbo.esg_summary_saas_hour';

			---- esg_summary_saas view
			------------------------------------
			--if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_summary_saas')
   --         begin
   --             drop view dbo.esg_summary_saas;
   --             set @ErrNum = @@ERROR;
   --             if @ErrNum <> 0
   --             begin
   --                 set @ErrMsg = N'usp_update_esg_views() error N' + Cast(@ErrNum as varchar(10)) + N' while dropping view esg_summary_saas.';
   --                 goto Error_Handler;
   --             end
   --         end     
			--set @esg_summary_saas_sql = 
			--	N' create view dbo.esg_summary_saas(
			--	[record_number], [date_time],[esg_id], [esg_action_type_id],
			--	[hits],[size]
			--	 ) as select * from ' + @PartitionName + N'.dbo.esg_summary_saas';

			---- esg_summary_message_queue_hour view
			------------------------------------
			--if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_summary_message_queue_hour')
   --         begin
   --             drop view dbo.esg_summary_message_queue_hour;
   --             set @ErrNum = @@ERROR;
   --             if @ErrNum <> 0
   --             begin
   --                 set @ErrMsg = N'usp_update_esg_views() error N' + Cast(@ErrNum as varchar(10)) + N' while dropping view esg_summary_message_queue_hour.';
   --                 goto Error_Handler;
   --             end
   --         end      
			--set @esg_summary_message_queue_hour_sql = 
			--	N' create view dbo.esg_summary_message_queue_hour(
			--	[record_number],[date_time], [esg_id],[incoming_queue_num], 
			--	[delivering_queue_num], [defered_queue_num], [hour]
			--	 ) as select * from ' + @PartitionName + N'.dbo.esg_summary_message_queue_hour';

			---- esg_summary_message_queue view
			------------------------------------
			--if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_summary_message_queue')
   --         begin
   --             drop view dbo.esg_summary_message_queue;
   --             set @ErrNum = @@ERROR;
   --             if @ErrNum <> 0
   --             begin
   --                 set @ErrMsg = N'usp_update_esg_views() error N' + Cast(@ErrNum as varchar(10)) + N' while dropping view esg_summary_message_queue.';
   --                 goto Error_Handler;
   --             end
   --         end     
			--set @esg_summary_message_queue_sql = 
			--	N' create view dbo.esg_summary_message_queue(
			--	[record_number], [date_time],[esg_id], [incoming_queue_num], 
			--	[delivering_queue_num], [defered_queue_num]
			--	 ) as select * from ' + @PartitionName + N'.dbo.esg_summary_message_queue';

			-- esg_summary_delivery_connection view
			------------------------------------------
			if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_summary_delivery_connection')
            begin
                drop view dbo.esg_summary_delivery_connection;
                set @ErrNum = @@ERROR;
                if @ErrNum <> 0
                begin
                    set @ErrMsg = N'usp_update_esg_views() error N' + Cast(@ErrNum as varchar(10)) + N' while dropping view esg_summary_delivery_connection.';
                    goto Error_Handler;
                end
            end -- if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_summary_delivery_connection')
          	set @esg_summary_delivery_connection_sql = 
            N'create view dbo.esg_summary_delivery_connection(
				[record_number], [date_time],[esg_id], [esg_transport_type_id],[esg_encrypted_delivery_id],
				[hits]			  	
              ) as select * from ' + @PartitionName + N'.dbo.esg_summary_delivery_connection';

			-- esg_summary_delivery_recipient view
			------------------------------------------
			if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_summary_delivery_recipient')
            begin
                drop view dbo.esg_summary_delivery_recipient;
                set @ErrNum = @@ERROR;
                if @ErrNum <> 0
                begin
                    set @ErrMsg = N'usp_update_esg_views() error N' + Cast(@ErrNum as varchar(10)) + N' while dropping view esg_summary_delivery_recipient.';
                    goto Error_Handler;
                end
            end -- if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_summary_delivery_recipient')
            set @esg_summary_delivery_recipient_sql = 
            N'create view dbo.esg_summary_delivery_recipient(
				[record_number], [date_time],[esg_id],  [esg_delivery_status_id],[hits]					  	
              ) as select * from ' + @PartitionName + N'.dbo.esg_summary_delivery_recipient';

			-- esg_summary_process_results_virus view
			------------------------------------------
			if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_summary_process_results_virus')
            begin
                drop view dbo.esg_summary_process_results_virus;
                set @ErrNum = @@ERROR;
                if @ErrNum <> 0
                begin
                    set @ErrMsg = N'usp_update_esg_views() error N' + Cast(@ErrNum as varchar(10)) + N' while dropping view esg_summary_process_results_virus.';
                    goto Error_Handler;
                end
            end -- if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_summary_process_results_virus')
            set @esg_summary_process_results_virus_sql = 
            N'create view dbo.esg_summary_process_results_virus(
			[record_number], [date_time], [esg_id], [esg_virus_id], [hits]					  	
              ) as select * from ' + @PartitionName + N'.dbo.esg_summary_process_results_virus';

			-- esg_summary_dlp_process_results_incident view
			--------------------------------------------------
			if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_summary_dlp_process_results_incident')
            begin
                drop view dbo.esg_summary_dlp_process_results_incident;
                set @ErrNum = @@ERROR;
                if @ErrNum <> 0
                begin
                    set @ErrMsg = N'usp_update_esg_views() error N' + Cast(@ErrNum as varchar(10)) + N' while dropping view esg_summary_dlp_process_results_incident.';
                    goto Error_Handler;
                end
            end -- if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_summary_dlp_process_results_incident')
            set @esg_summary_dlp_process_results_incident_sql = 
            N'create view dbo.esg_summary_dlp_process_results_incident(
			[record_number], [date_time],[esg_id],  [esg_dlp_severity_level_id],[hits]							  	
              ) as select * from ' + @PartitionName + N'.dbo.esg_summary_dlp_process_results_incident';
					
			-- esg_summary_dlp_process_results_policy view
			--------------------------------------------------
			if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_summary_dlp_process_results_policy')
            begin
                drop view dbo.esg_summary_dlp_process_results_policy;
                set @ErrNum = @@ERROR;
                if @ErrNum <> 0
                begin
                    set @ErrMsg = N'usp_update_esg_views() error N' + Cast(@ErrNum as varchar(10)) + N' while dropping view esg_summary_dlp_process_results_policy.';
                    goto Error_Handler;
                end
            end -- if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_summary_dlp_process_results_policy')
            set @esg_summary_dlp_process_results_policy_sql = 
            N'create view dbo.esg_summary_dlp_process_results_policy(
			[record_number], [date_time],[esg_id],  [esg_direction_id],
			[esg_policy_name_id],[hits]							  	
              ) as select * from ' + @PartitionName + N'.dbo.esg_summary_dlp_process_results_policy';
	
			-- esg_summary_process_results_message_total view
			--------------------------------------------------
			if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_summary_process_results_message_total')
            begin
                drop view dbo.esg_summary_process_results_message_total;
                set @ErrNum = @@ERROR;
                if @ErrNum <> 0
                begin
                    set @ErrMsg = N'usp_update_esg_views() error N' + Cast(@ErrNum as varchar(10)) + N' while dropping view esg_summary_process_results_message_total.';
                    goto Error_Handler;
                end
            end -- if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_summary_process_results_message_total')
            set @esg_summary_process_results_message_total_sql = 
            N'create view dbo.esg_summary_process_results_message_total(
			[record_number], [date_time],[esg_id], [hits], [size] 					  	
              ) as select * from ' + @PartitionName + N'.dbo.esg_summary_process_results_message_total';
		
 			-- esg_summary_process_results_message_direction view
			-----------------------------------------------------------
			if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_summary_process_results_message_direction')
            begin
                drop view dbo.esg_summary_process_results_message_direction;
                set @ErrNum = @@ERROR;
                if @ErrNum <> 0
                begin
                    set @ErrMsg = N'usp_update_esg_views() error N' + Cast(@ErrNum as varchar(10)) + N' while dropping view esg_summary_process_results_message_direction.';
                    goto Error_Handler;
                end
            end -- if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_summary_process_results_message_direction')
            set @esg_summary_process_results_message_direction_sql = 
            N'create view dbo.esg_summary_process_results_message_direction(
			[record_number], [date_time],[esg_id],  [esg_direction_id],[hits], [size] 					  	
              ) as select * from ' + @PartitionName + N'.dbo.esg_summary_process_results_message_direction';
		
  			-- esg_summary_process_results_message_direction_type view
			-----------------------------------------------------------
			if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_summary_process_results_message_direction_type')
            begin
                drop view dbo.esg_summary_process_results_message_direction_type;
                set @ErrNum = @@ERROR;
                if @ErrNum <> 0
                begin
                    set @ErrMsg = N'usp_update_esg_views() error N' + Cast(@ErrNum as varchar(10)) + N' while dropping view esg_summary_process_results_message_direction_type.';
                    goto Error_Handler;
                end
            end -- if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_summary_process_results_message_direction_type')
            set @esg_summary_process_results_message_direction_type_sql = 
            N'create view dbo.esg_summary_process_results_message_direction_type(
			[record_number], [date_time], [esg_id], [esg_direction_id], [esg_message_type_id],
			[hits], [size] 					  	
              ) as select * from ' + @PartitionName + N'.dbo.esg_summary_process_results_message_direction_type';
		
 			-- esg_summary_process_results_message_action_type view
			----------------------------------------------------------
			if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_summary_process_results_message_action_type')
            begin
                drop view dbo.esg_summary_process_results_message_action_type;
                set @ErrNum = @@ERROR;
                if @ErrNum <> 0
                begin
                    set @ErrMsg = N'usp_update_esg_views() error N' + Cast(@ErrNum as varchar(10)) + N' while dropping view esg_summary_process_results_message_action_type.';
                    goto Error_Handler;
                end
            end -- if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_summary_process_results_message_action_type')
            set @esg_summary_process_results_message_action_type_sql = 
            N'create view dbo.esg_summary_process_results_message_action_type(
			[record_number], [date_time], [esg_id], [esg_action_type_id],[esg_message_type_id],
			[hits],[size]			  	
              ) as select * from ' + @PartitionName + N'.dbo.esg_summary_process_results_message_action_type';

			-- esg_summary_process_results_message_address view
			----------------------------------------------------------
			if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_summary_process_results_message_address')
            begin
                drop view dbo.esg_summary_process_results_message_address;
                set @ErrNum = @@ERROR;
                if @ErrNum <> 0
                begin
                    set @ErrMsg = N'usp_update_esg_views() error N' + Cast(@ErrNum as varchar(10)) + N' while dropping view esg_summary_process_results_message_address.';
                    goto Error_Handler;
                end
            end -- if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_summary_process_results_message_address')
            set @esg_summary_process_results_message_address_sql = 
            N'create view dbo.esg_summary_process_results_message_address(
				[record_number], [date_time],[esg_id],  [esg_email_address_id], 
				[address_type_sender],[hits],[size]			  	
              ) as select * from ' + @PartitionName + N'.dbo.esg_summary_process_results_message_address';

			-- esg_summary_process_results_message_address_direction view
			----------------------------------------------------------
			if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_summary_process_results_message_address_direction')
            begin
                drop view dbo.esg_summary_process_results_message_address_direction;
                set @ErrNum = @@ERROR;
                if @ErrNum <> 0
                begin
                    set @ErrMsg = N'usp_update_esg_views() error N' + Cast(@ErrNum as varchar(10)) + N' while dropping view esg_summary_process_results_message_address_direction.';
                    goto Error_Handler;
                end
            end -- if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_summary_process_results_message_address_direction')
            set @esg_summary_process_results_message_address_direction_sql = 
            N'create view dbo.esg_summary_process_results_message_address_direction(
				[record_number], [date_time], [esg_id], [esg_email_address_id], 
				[address_type_sender],[esg_direction_id],[hits],[size]			  	
              ) as select * from ' + @PartitionName + N'.dbo.esg_summary_process_results_message_address_direction';
				
			-- esg_summary_process_results_message_address_direction_action view
			----------------------------------------------------------------------
			if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_summary_process_results_message_address_direction_action')
            begin
                drop view dbo.esg_summary_process_results_message_address_direction_action;
                set @ErrNum = @@ERROR;
                if @ErrNum <> 0
                begin
                    set @ErrMsg = N'usp_update_esg_views() error N' + Cast(@ErrNum as varchar(10)) + N' while dropping view esg_summary_process_results_message_address_direction_action.';
                    goto Error_Handler;
                end
            end -- if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_summary_process_results_message_address_direction_action')
            set @esg_summary_process_results_message_address_direction_action_sql = 
            N'create view dbo.esg_summary_process_results_message_address_direction_action(
				[record_number], [date_time], [esg_id], [esg_email_address_id],[address_type_sender],
				[esg_direction_id],[esg_action_type_id],[hits],[size]			  	
              ) as select * from ' + @PartitionName + N'.dbo.esg_summary_process_results_message_address_direction_action';
			
			-- esg_summary_process_results_message_address_direction_type view
			-------------------------------------------------------------------
			if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_summary_process_results_message_address_direction_type')
            begin
                drop view dbo.esg_summary_process_results_message_address_direction_type;
                set @ErrNum = @@ERROR;
                if @ErrNum <> 0
                begin
                    set @ErrMsg = N'usp_update_esg_views() error N' + Cast(@ErrNum as varchar(10)) + N' while dropping view esg_summary_process_results_message_address_direction_type.';
                    goto Error_Handler;
                end
            end -- if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_summary_process_results_message_address_direction_type')
            set @esg_summary_process_results_message_address_direction_type_sql = 
            N'create view dbo.esg_summary_process_results_message_address_direction_type(
			[record_number], [date_time], [esg_id], [esg_email_address_id],[address_type_sender],
			[esg_direction_id], [esg_message_type_id],[hits], [size] 					  	
              ) as select * from ' + @PartitionName + N'.dbo.esg_summary_process_results_message_address_direction_type';
						
			---- esg_summary_process_results_message_domain_direction_type view
			------------------------------------------------------------------------
			if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_summary_process_results_message_domain_direction_type')
            begin
                drop view dbo.esg_summary_process_results_message_domain_direction_type;
                set @ErrNum = @@ERROR;
                if @ErrNum <> 0
                begin
                    set @ErrMsg = N'usp_update_esg_views() error N' + Cast(@ErrNum as varchar(10)) + N' while dropping view esg_summary_process_results_message_domain_direction_type.';
                    goto Error_Handler;
                end
            end -- if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_summary_process_results_message_domain_direction_type')
            set @esg_summary_process_results_message_domain_direction_type_sql = 
            N'create view dbo.esg_summary_process_results_message_domain_direction_type(
				[record_number], [date_time], [esg_id], [esg_domain_id],[address_type_sender],
				[esg_direction_id],[esg_message_type_id],[hits],[size]			  	
              ) as select * from ' + @PartitionName + N'.dbo.esg_summary_process_results_message_domain_direction_type';
					
			---- esg_summary_process_results_message_domain_direction view
			------------------------------------------------------------------------
			if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_summary_process_results_message_domain_direction')
            begin
                drop view dbo.esg_summary_process_results_message_domain_direction;
                set @ErrNum = @@ERROR;
                if @ErrNum <> 0
                begin
                    set @ErrMsg = N'usp_update_esg_views() error N' + Cast(@ErrNum as varchar(10)) + N' while dropping view esg_summary_process_results_message_domain_direction.';
                    goto Error_Handler;
                end
            end -- if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_summary_process_results_message_domain_direction')
            set @esg_summary_process_results_message_domain_direction_sql = 
            N'create view dbo.esg_summary_process_results_message_domain_direction(
				[record_number], [date_time],[esg_id],  [esg_domain_id],[address_type_sender],
				[esg_direction_id],[hits],[size]			  	
              ) as select * from ' + @PartitionName + N'.dbo.esg_summary_process_results_message_domain_direction';
			
			-- esg_detail_dlp_policy view
			----------------------------------------------------------------------
			if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_detail_dlp_policy')
            begin
                drop view dbo.esg_detail_dlp_policy;
                set @ErrNum = @@ERROR;
                if @ErrNum <> 0
                begin
                    set @ErrMsg = N'usp_update_esg_views() error N' + Cast(@ErrNum as varchar(10)) + N' while dropping view esg_detail_dlp_policy.';
                    goto Error_Handler;
                end
            end -- if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_detail_dlp_policy')
            set @esg_detail_dlp_policy_sql = 
            N'create view dbo.esg_detail_dlp_policy(
				[record_number], [esg_message_id], [esg_id], [sender_email_address_id], [date_time],
				[esg_policy_name_id],  [dlp_transaction_id],[esg_dlp_severity_level_id],[message_size]
		     ) as select * from ' + @PartitionName + N'.dbo.esg_detail_dlp_policy';
	
			-- esg_summary_logview_message view
			----------------------------------------------------------------------
			if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_summary_logview_message')
            begin
                drop view dbo.esg_summary_logview_message;
                set @ErrNum = @@ERROR;
                if @ErrNum <> 0
                begin
                    set @ErrMsg = N'usp_update_esg_views() error N' + Cast(@ErrNum as varchar(10)) + N' while dropping view esg_summary_logview_message.';
                    goto Error_Handler;
                end
            end -- if Exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'esg_summary_logview_message')
            set @esg_summary_logview_message_sql = 
            N'create view dbo.esg_summary_logview_message(
				[record_number], [date_time],[esg_message_id], [esg_sender_email_address_id],
				[esg_message_type_set_id], [esg_delivery_status_set_id],[dlp_transaction_id],
				[esg_id],[need_delivered_recipient_num],[delivered_recipient_num]			  	
              ) as select * from ' + @PartitionName + N'.dbo.esg_summary_logview_message';
  	
			-- we have what we needed
            set @found = @found + 1;
        end -- if @count > 0

        select  top 1 @created_date=created_date, @PartitionName = [db_name]
          from dbo.wse_partitions with (nolock) 
         where deleted = 0 and offline = 0 and created_date < @created_date
         order by created_date desc;
        if @@ROWCOUNT < 1
            set @created_date = null;
    end -- while

    declare @i                 int,
			@esg_detail_incoming_connection_sql_1 nvarchar(4000), @esg_detail_message_sql_1 nvarchar(4000),@esg_detail_saas_sql_1 nvarchar(4000),
			@esg_detail_incoming_connection_sql_2 nvarchar(4000), @esg_detail_message_sql_2 nvarchar(4000),@esg_detail_saas_sql_2 nvarchar(4000),
			@esg_detail_incoming_connection_sql_3 nvarchar(4000), @esg_detail_message_sql_3 nvarchar(4000),@esg_detail_saas_sql_3 nvarchar(4000),
			@esg_detail_incoming_connection_sql_4 nvarchar(4000), @esg_detail_message_sql_4 nvarchar(4000),@esg_detail_saas_sql_4 nvarchar(4000),
			@esg_detail_incoming_connection_sql_5 nvarchar(4000), @esg_detail_message_sql_5 nvarchar(4000),@esg_detail_saas_sql_5 nvarchar(4000),
			@esg_detail_incoming_connection_sql_6 nvarchar(4000), @esg_detail_message_sql_6 nvarchar(4000),@esg_detail_saas_sql_6 nvarchar(4000),
			--@esg_detail_dlp_process_results_sql_1 nvarchar(4000), 
			--@esg_detail_dlp_process_results_sql_2 nvarchar(4000),	
			--@esg_detail_dlp_process_results_sql_3 nvarchar(4000), 
			--@esg_detail_dlp_process_results_sql_4 nvarchar(4000), 
			--@esg_detail_dlp_process_results_sql_5 nvarchar(4000), 
			--@esg_detail_dlp_process_results_sql_6 nvarchar(4000), 
			@esg_detail_delivery_connection_sql_1 nvarchar(4000), @esg_detail_delivery_recipient_sql_1 nvarchar(4000), 
			@esg_detail_delivery_connection_sql_2 nvarchar(4000), @esg_detail_delivery_recipient_sql_2 nvarchar(4000), 
			@esg_detail_delivery_connection_sql_3 nvarchar(4000), @esg_detail_delivery_recipient_sql_3 nvarchar(4000), 
			@esg_detail_delivery_connection_sql_4 nvarchar(4000), @esg_detail_delivery_recipient_sql_4 nvarchar(4000), 
			@esg_detail_delivery_connection_sql_5 nvarchar(4000), @esg_detail_delivery_recipient_sql_5 nvarchar(4000), 
			@esg_detail_delivery_connection_sql_6 nvarchar(4000), @esg_detail_delivery_recipient_sql_6 nvarchar(4000), 
			@esg_detail_policy_process_results_sql_1 nvarchar(4000), 
			@esg_detail_policy_process_results_sql_2 nvarchar(4000), 
			@esg_detail_policy_process_results_sql_3 nvarchar(4000), 
			@esg_detail_policy_process_results_sql_4 nvarchar(4000), 
			@esg_detail_policy_process_results_sql_5 nvarchar(4000),
			@esg_detail_policy_process_results_sql_6 nvarchar(4000), 
			@esg_summary_incoming_connection_sql_1 nvarchar(4000),@esg_summary_incoming_connection_hour_sql_1 nvarchar(4000),
			@esg_summary_incoming_connection_sql_2 nvarchar(4000),@esg_summary_incoming_connection_hour_sql_2 nvarchar(4000), 
			@esg_summary_incoming_connection_sql_3 nvarchar(4000),@esg_summary_incoming_connection_hour_sql_3 nvarchar(4000),
			@esg_summary_incoming_connection_sql_4 nvarchar(4000),@esg_summary_incoming_connection_hour_sql_4 nvarchar(4000), 
			@esg_summary_incoming_connection_sql_5 nvarchar(4000),@esg_summary_incoming_connection_hour_sql_5 nvarchar(4000),
			@esg_summary_incoming_connection_sql_6 nvarchar(4000),@esg_summary_incoming_connection_hour_sql_6 nvarchar(4000),
			--@esg_summary_saas_hour_sql_1 nvarchar(4000), @esg_summary_saas_sql_1 nvarchar(4000),
			--@esg_summary_saas_hour_sql_2 nvarchar(4000), @esg_summary_saas_sql_2 nvarchar(4000),
			--@esg_summary_saas_hour_sql_3 nvarchar(4000), @esg_summary_saas_sql_3 nvarchar(4000),
			--@esg_summary_saas_hour_sql_4 nvarchar(4000), @esg_summary_saas_sql_4 nvarchar(4000),
			--@esg_summary_saas_hour_sql_5 nvarchar(4000), @esg_summary_saas_sql_5 nvarchar(4000),
			--@esg_summary_saas_hour_sql_6 nvarchar(4000), @esg_summary_saas_sql_6 nvarchar(4000),
			@esg_summary_delivery_connection_sql_1 nvarchar(4000), @esg_summary_delivery_recipient_sql_1 nvarchar(4000),
			@esg_summary_delivery_connection_sql_2 nvarchar(4000), @esg_summary_delivery_recipient_sql_2 nvarchar(4000),
			@esg_summary_delivery_connection_sql_3 nvarchar(4000), @esg_summary_delivery_recipient_sql_3 nvarchar(4000),
			@esg_summary_delivery_connection_sql_4 nvarchar(4000), @esg_summary_delivery_recipient_sql_4 nvarchar(4000),
			@esg_summary_delivery_connection_sql_5 nvarchar(4000), @esg_summary_delivery_recipient_sql_5 nvarchar(4000),
			@esg_summary_delivery_connection_sql_6 nvarchar(4000), @esg_summary_delivery_recipient_sql_6 nvarchar(4000),       
			@esg_summary_process_results_virus_sql_1 nvarchar(4000), @esg_summary_dlp_process_results_incident_sql_1 nvarchar(4000), @esg_summary_dlp_process_results_policy_sql_1 nvarchar(4000),
			@esg_summary_process_results_virus_sql_2 nvarchar(4000), @esg_summary_dlp_process_results_incident_sql_2 nvarchar(4000), @esg_summary_dlp_process_results_policy_sql_2 nvarchar(4000),
			@esg_summary_process_results_virus_sql_3 nvarchar(4000), @esg_summary_dlp_process_results_incident_sql_3 nvarchar(4000), @esg_summary_dlp_process_results_policy_sql_3 nvarchar(4000),
			@esg_summary_process_results_virus_sql_4 nvarchar(4000), @esg_summary_dlp_process_results_incident_sql_4 nvarchar(4000), @esg_summary_dlp_process_results_policy_sql_4 nvarchar(4000),
			@esg_summary_process_results_virus_sql_5 nvarchar(4000), @esg_summary_dlp_process_results_incident_sql_5 nvarchar(4000), @esg_summary_dlp_process_results_policy_sql_5 nvarchar(4000),
			@esg_summary_process_results_virus_sql_6 nvarchar(4000), @esg_summary_dlp_process_results_incident_sql_6 nvarchar(4000), @esg_summary_dlp_process_results_policy_sql_6 nvarchar(4000),
			@esg_summary_process_results_message_total_sql_1 nvarchar(4000), @esg_summary_process_results_message_direction_sql_1 nvarchar(4000), @esg_summary_process_results_message_direction_type_sql_1 nvarchar(4000), @esg_summary_process_results_message_action_type_sql_1 nvarchar(4000),
			@esg_summary_process_results_message_total_sql_2 nvarchar(4000), @esg_summary_process_results_message_direction_sql_2 nvarchar(4000), @esg_summary_process_results_message_direction_type_sql_2 nvarchar(4000), @esg_summary_process_results_message_action_type_sql_2 nvarchar(4000),
			@esg_summary_process_results_message_total_sql_3 nvarchar(4000), @esg_summary_process_results_message_direction_sql_3 nvarchar(4000), @esg_summary_process_results_message_direction_type_sql_3 nvarchar(4000), @esg_summary_process_results_message_action_type_sql_3 nvarchar(4000),
			@esg_summary_process_results_message_total_sql_4 nvarchar(4000), @esg_summary_process_results_message_direction_sql_4 nvarchar(4000), @esg_summary_process_results_message_direction_type_sql_4 nvarchar(4000), @esg_summary_process_results_message_action_type_sql_4 nvarchar(4000), 
			@esg_summary_process_results_message_total_sql_5 nvarchar(4000), @esg_summary_process_results_message_direction_sql_5 nvarchar(4000), @esg_summary_process_results_message_direction_type_sql_5 nvarchar(4000), @esg_summary_process_results_message_action_type_sql_5 nvarchar(4000),
			@esg_summary_process_results_message_total_sql_6 nvarchar(4000), @esg_summary_process_results_message_direction_sql_6 nvarchar(4000), @esg_summary_process_results_message_direction_type_sql_6 nvarchar(4000), @esg_summary_process_results_message_action_type_sql_6 nvarchar(4000),
			@esg_summary_process_results_message_address_sql_1 nvarchar(4000), @esg_summary_process_results_message_address_direction_type_sql_1 nvarchar(4000),@esg_summary_process_results_message_address_direction_sql_1 nvarchar(4000),@esg_summary_process_results_message_address_direction_action_sql_1 nvarchar(4000),
			@esg_summary_process_results_message_address_sql_2 nvarchar(4000), @esg_summary_process_results_message_address_direction_type_sql_2 nvarchar(4000),@esg_summary_process_results_message_address_direction_sql_2 nvarchar(4000),@esg_summary_process_results_message_address_direction_action_sql_2 nvarchar(4000),
			@esg_summary_process_results_message_address_sql_3 nvarchar(4000), @esg_summary_process_results_message_address_direction_type_sql_3 nvarchar(4000),@esg_summary_process_results_message_address_direction_sql_3 nvarchar(4000),@esg_summary_process_results_message_address_direction_action_sql_3 nvarchar(4000),
			@esg_summary_process_results_message_address_sql_4 nvarchar(4000), @esg_summary_process_results_message_address_direction_type_sql_4 nvarchar(4000),@esg_summary_process_results_message_address_direction_sql_4 nvarchar(4000),@esg_summary_process_results_message_address_direction_action_sql_4 nvarchar(4000),
			@esg_summary_process_results_message_address_sql_5 nvarchar(4000), @esg_summary_process_results_message_address_direction_type_sql_5 nvarchar(4000),@esg_summary_process_results_message_address_direction_sql_5 nvarchar(4000),@esg_summary_process_results_message_address_direction_action_sql_5 nvarchar(4000),
			@esg_summary_process_results_message_address_sql_6 nvarchar(4000), @esg_summary_process_results_message_address_direction_type_sql_6 nvarchar(4000),@esg_summary_process_results_message_address_direction_sql_6 nvarchar(4000),@esg_summary_process_results_message_address_direction_action_sql_6 nvarchar(4000),
			@esg_summary_process_results_message_domain_direction_type_sql_1 nvarchar(4000),@esg_summary_process_results_message_domain_direction_sql_1 nvarchar (4000),
			@esg_summary_process_results_message_domain_direction_type_sql_2 nvarchar(4000),@esg_summary_process_results_message_domain_direction_sql_2 nvarchar (4000),
			@esg_summary_process_results_message_domain_direction_type_sql_3 nvarchar(4000),@esg_summary_process_results_message_domain_direction_sql_3 nvarchar (4000),
			@esg_summary_process_results_message_domain_direction_type_sql_4 nvarchar(4000),@esg_summary_process_results_message_domain_direction_sql_4 nvarchar (4000),
			@esg_summary_process_results_message_domain_direction_type_sql_5 nvarchar(4000),@esg_summary_process_results_message_domain_direction_sql_5 nvarchar (4000),
			@esg_summary_process_results_message_domain_direction_type_sql_6 nvarchar(4000),@esg_summary_process_results_message_domain_direction_sql_6 nvarchar (4000),
			@esg_detail_message_queue_sql_1 nvarchar(4000), 
			@esg_detail_message_queue_sql_2 nvarchar(4000), 
			@esg_detail_message_queue_sql_3 nvarchar(4000), 
			@esg_detail_message_queue_sql_4 nvarchar(4000),
			@esg_detail_message_queue_sql_5 nvarchar(4000), 
			@esg_detail_message_queue_sql_6 nvarchar(4000), 
			--@esg_summary_message_queue_hour_sql_1 nvarchar(4000), @esg_summary_message_queue_sql_1 nvarchar(4000),
			--@esg_summary_message_queue_hour_sql_2 nvarchar(4000), @esg_summary_message_queue_sql_2 nvarchar(4000),
			--@esg_summary_message_queue_hour_sql_3 nvarchar(4000), @esg_summary_message_queue_sql_3 nvarchar(4000),
			--@esg_summary_message_queue_hour_sql_4 nvarchar(4000), @esg_summary_message_queue_sql_4 nvarchar(4000),
			--@esg_summary_message_queue_hour_sql_5 nvarchar(4000), @esg_summary_message_queue_sql_5 nvarchar(4000),
			--@esg_summary_message_queue_hour_sql_6 nvarchar(4000), @esg_summary_message_queue_sql_6 nvarchar(4000),
			@esg_detail_dlp_policy_sql_1 nvarchar(4000),@esg_summary_logview_message_sql_1 nvarchar(4000),
			@esg_detail_dlp_policy_sql_2 nvarchar(4000),@esg_summary_logview_message_sql_2 nvarchar(4000),
			@esg_detail_dlp_policy_sql_3 nvarchar(4000),@esg_summary_logview_message_sql_3 nvarchar(4000),
			@esg_detail_dlp_policy_sql_4 nvarchar(4000),@esg_summary_logview_message_sql_4 nvarchar(4000),
			@esg_detail_dlp_policy_sql_5 nvarchar(4000),@esg_summary_logview_message_sql_5 nvarchar(4000),
			@esg_detail_dlp_policy_sql_6 nvarchar(4000),@esg_summary_logview_message_sql_6 nvarchar(4000)
	;
	
    set @i = 1;
    --select @esg_detail_dlp_process_results_sql_1 = N'',  @esg_summary_saas_hour_sql_1 = N'', @esg_summary_saas_sql_1 = N'';
    --select @esg_detail_dlp_process_results_sql_2 = N'',  @esg_summary_saas_hour_sql_2 = N'', @esg_summary_saas_sql_2 = N'';
    --select @esg_detail_dlp_process_results_sql_3 = N'',  @esg_summary_saas_hour_sql_3 = N'', @esg_summary_saas_sql_3 = N'';
    --select @esg_detail_dlp_process_results_sql_4 = N'',  @esg_summary_saas_hour_sql_4 = N'', @esg_summary_saas_sql_4 = N'';
    --select @esg_detail_dlp_process_results_sql_5 = N'',  @esg_summary_saas_hour_sql_5 = N'', @esg_summary_saas_sql_5 = N'';
    --select @esg_detail_dlp_process_results_sql_6 = N'',  @esg_summary_saas_hour_sql_6 = N'', @esg_summary_saas_sql_6 = N'';
    select @esg_detail_incoming_connection_sql_1 = N'', @esg_detail_message_sql_1 = N'', @esg_detail_saas_sql_1 = N'';
	select @esg_detail_incoming_connection_sql_2 = N'', @esg_detail_message_sql_2 = N'', @esg_detail_saas_sql_2 = N'';
	select @esg_detail_incoming_connection_sql_3 = N'', @esg_detail_message_sql_3 = N'', @esg_detail_saas_sql_3 = N'';
	select @esg_detail_incoming_connection_sql_4 = N'', @esg_detail_message_sql_4 = N'', @esg_detail_saas_sql_4 = N'';
	select @esg_detail_incoming_connection_sql_5 = N'', @esg_detail_message_sql_5 = N'', @esg_detail_saas_sql_5 = N'';
	select @esg_detail_incoming_connection_sql_6 = N'', @esg_detail_message_sql_6 = N'', @esg_detail_saas_sql_6 = N'';
	select @esg_detail_delivery_connection_sql_1 = N'', @esg_detail_delivery_recipient_sql_1 = N'', @esg_detail_policy_process_results_sql_1 = N'';
	select @esg_detail_delivery_connection_sql_2 = N'', @esg_detail_delivery_recipient_sql_2 = N'', @esg_detail_policy_process_results_sql_2 = N''; 
	select @esg_detail_delivery_connection_sql_3 = N'', @esg_detail_delivery_recipient_sql_3 = N'', @esg_detail_policy_process_results_sql_3 = N''; 
	select @esg_detail_delivery_connection_sql_4 = N'', @esg_detail_delivery_recipient_sql_4 = N'', @esg_detail_policy_process_results_sql_4 = N''; 
	select @esg_detail_delivery_connection_sql_5 = N'', @esg_detail_delivery_recipient_sql_5 = N'', @esg_detail_policy_process_results_sql_5 = N''; 
	select @esg_detail_delivery_connection_sql_6 = N'', @esg_detail_delivery_recipient_sql_6 = N'', @esg_detail_policy_process_results_sql_6 = N''; 
	select @esg_summary_incoming_connection_sql_1 = N'', @esg_summary_incoming_connection_hour_sql_1 = N'';
	select @esg_summary_incoming_connection_sql_2 = N'', @esg_summary_incoming_connection_hour_sql_2 = N'';
	select @esg_summary_incoming_connection_sql_3 = N'', @esg_summary_incoming_connection_hour_sql_3 = N'';
	select @esg_summary_incoming_connection_sql_4 = N'', @esg_summary_incoming_connection_hour_sql_4 = N'';
	select @esg_summary_incoming_connection_sql_5 = N'', @esg_summary_incoming_connection_hour_sql_5 = N'';
	select @esg_summary_incoming_connection_sql_6 = N'', @esg_summary_incoming_connection_hour_sql_6 = N'';
	select @esg_summary_delivery_connection_sql_1 = N'', @esg_summary_delivery_recipient_sql_1 = N'';
	select @esg_summary_delivery_connection_sql_2 = N'', @esg_summary_delivery_recipient_sql_2 = N'';
	select @esg_summary_delivery_connection_sql_3 = N'', @esg_summary_delivery_recipient_sql_3 = N'';
	select @esg_summary_delivery_connection_sql_4 = N'', @esg_summary_delivery_recipient_sql_4 = N'';
	select @esg_summary_delivery_connection_sql_5 = N'', @esg_summary_delivery_recipient_sql_5 = N'';
	select @esg_summary_delivery_connection_sql_6 = N'', @esg_summary_delivery_recipient_sql_6 = N'';         
	select @esg_summary_process_results_virus_sql_1 = N'', @esg_summary_dlp_process_results_incident_sql_1 = N'', @esg_summary_dlp_process_results_policy_sql_1 = N'';
	select @esg_summary_process_results_virus_sql_2 = N'', @esg_summary_dlp_process_results_incident_sql_2 = N'', @esg_summary_dlp_process_results_policy_sql_2 = N'';
	select @esg_summary_process_results_virus_sql_3 = N'', @esg_summary_dlp_process_results_incident_sql_3 = N'', @esg_summary_dlp_process_results_policy_sql_3 = N'';
	select @esg_summary_process_results_virus_sql_4 = N'', @esg_summary_dlp_process_results_incident_sql_4 = N'', @esg_summary_dlp_process_results_policy_sql_4 = N'';
	select @esg_summary_process_results_virus_sql_5 = N'', @esg_summary_dlp_process_results_incident_sql_5 = N'', @esg_summary_dlp_process_results_policy_sql_5 = N'';
	select @esg_summary_process_results_virus_sql_6 = N'', @esg_summary_dlp_process_results_incident_sql_6 = N'', @esg_summary_dlp_process_results_policy_sql_6 = N'';
	select @esg_summary_process_results_message_total_sql_1 = N'', @esg_summary_process_results_message_action_type_sql_1 = N'',@esg_summary_process_results_message_direction_sql_1 = N'',@esg_summary_process_results_message_direction_type_sql_1 = N'';
	select @esg_summary_process_results_message_total_sql_2 = N'', @esg_summary_process_results_message_action_type_sql_2 = N'',@esg_summary_process_results_message_direction_sql_2 = N'',@esg_summary_process_results_message_direction_type_sql_2 = N''; 
	select @esg_summary_process_results_message_total_sql_3 = N'', @esg_summary_process_results_message_action_type_sql_3 = N'',@esg_summary_process_results_message_direction_sql_3 = N'',@esg_summary_process_results_message_direction_type_sql_3 = N'';
	select @esg_summary_process_results_message_total_sql_4 = N'', @esg_summary_process_results_message_action_type_sql_4 = N'',@esg_summary_process_results_message_direction_sql_4 = N'',@esg_summary_process_results_message_direction_type_sql_4 = N'';
	select @esg_summary_process_results_message_total_sql_5 = N'', @esg_summary_process_results_message_action_type_sql_5 = N'',@esg_summary_process_results_message_direction_sql_5 = N'',@esg_summary_process_results_message_direction_type_sql_5 = N'';
	select @esg_summary_process_results_message_total_sql_6 = N'', @esg_summary_process_results_message_action_type_sql_6 = N'',@esg_summary_process_results_message_direction_sql_6 = N'',@esg_summary_process_results_message_direction_type_sql_6 = N'';
	select @esg_summary_process_results_message_address_sql_1 = N'', @esg_summary_process_results_message_address_direction_sql_1 = N'',@esg_summary_process_results_message_address_direction_action_sql_1 = N'', @esg_summary_process_results_message_address_direction_type_sql_1 = N'';
	select @esg_summary_process_results_message_address_sql_2 = N'', @esg_summary_process_results_message_address_direction_sql_2 = N'',@esg_summary_process_results_message_address_direction_action_sql_2 = N'', @esg_summary_process_results_message_address_direction_type_sql_2 = N'';
	select @esg_summary_process_results_message_address_sql_3 = N'', @esg_summary_process_results_message_address_direction_sql_3 = N'',@esg_summary_process_results_message_address_direction_action_sql_3 = N'', @esg_summary_process_results_message_address_direction_type_sql_3 = N'';
	select @esg_summary_process_results_message_address_sql_4 = N'', @esg_summary_process_results_message_address_direction_sql_4 = N'',@esg_summary_process_results_message_address_direction_action_sql_4 = N'', @esg_summary_process_results_message_address_direction_type_sql_4 = N'';
	select @esg_summary_process_results_message_address_sql_5 = N'', @esg_summary_process_results_message_address_direction_sql_5 = N'',@esg_summary_process_results_message_address_direction_action_sql_5 = N'', @esg_summary_process_results_message_address_direction_type_sql_5 = N'';
	select @esg_summary_process_results_message_address_sql_6 = N'', @esg_summary_process_results_message_address_direction_sql_6 = N'',@esg_summary_process_results_message_address_direction_action_sql_6 = N'', @esg_summary_process_results_message_address_direction_type_sql_6 = N'';
	select @esg_summary_process_results_message_domain_direction_type_sql_1 = N'',@esg_summary_process_results_message_domain_direction_sql_1 = N'';
	select @esg_summary_process_results_message_domain_direction_type_sql_2 = N'',@esg_summary_process_results_message_domain_direction_sql_2 = N'';
	select @esg_summary_process_results_message_domain_direction_type_sql_3 = N'',@esg_summary_process_results_message_domain_direction_sql_3 = N'';
	select @esg_summary_process_results_message_domain_direction_type_sql_4 = N'',@esg_summary_process_results_message_domain_direction_sql_4 = N'';
	select @esg_summary_process_results_message_domain_direction_type_sql_5 = N'',@esg_summary_process_results_message_domain_direction_sql_5 = N'';
	select @esg_summary_process_results_message_domain_direction_type_sql_6 = N'',@esg_summary_process_results_message_domain_direction_sql_6 = N'';
	--select @esg_summary_message_queue_hour_sql_1 = N'', @esg_summary_message_queue_sql_1 = N'';
	--select @esg_summary_message_queue_hour_sql_2 = N'', @esg_summary_message_queue_sql_2 = N'';
	--select @esg_summary_message_queue_hour_sql_3 = N'', @esg_summary_message_queue_sql_3 = N'';
	--select @esg_summary_message_queue_hour_sql_4 = N'', @esg_summary_message_queue_sql_4 = N'';
	--select @esg_summary_message_queue_hour_sql_5 = N'', @esg_summary_message_queue_sql_5 = N'';
	--select @esg_summary_message_queue_hour_sql_6 = N'', @esg_summary_message_queue_sql_6 = N'';
	select @esg_detail_message_queue_sql_1 = N'';
	select @esg_detail_message_queue_sql_2 = N'';
	select @esg_detail_message_queue_sql_3 = N'';
	select @esg_detail_message_queue_sql_4 = N'';
	select @esg_detail_message_queue_sql_5 = N'';
	select @esg_detail_message_queue_sql_6 = N'';
	select @esg_detail_dlp_policy_sql_1 = N'', @esg_summary_logview_message_sql_1 = N'';
	select @esg_detail_dlp_policy_sql_2 = N'', @esg_summary_logview_message_sql_2 = N'';
	select @esg_detail_dlp_policy_sql_3 = N'', @esg_summary_logview_message_sql_3 = N'';
	select @esg_detail_dlp_policy_sql_4 = N'', @esg_summary_logview_message_sql_4 = N'';
	select @esg_detail_dlp_policy_sql_5 = N'', @esg_summary_logview_message_sql_5 = N'';
	select @esg_detail_dlp_policy_sql_6 = N'', @esg_summary_logview_message_sql_6 = N'';

	while (@found > 0 and @created_date is not null)
    begin
        -- esg views only if the tables are available, skip if esg tables are not there
		set @count = 0;
        exec dbo.usp_db_verify_esg_content @PartitionName, @count OUTPUT, @debug;
        if (@count > 0)
        begin
			if ( @i = 1 ) 
			begin
				set @esg_detail_incoming_connection_sql_1 = @esg_detail_incoming_connection_sql_1 + N' union all select * from ' + @PartitionName + N'..esg_detail_incoming_connection ';
				set @esg_detail_message_sql_1 = @esg_detail_message_sql_1 + ' union all select * from ' + @PartitionName + N'..esg_detail_message ';
				set @esg_detail_saas_sql_1 = @esg_detail_saas_sql_1 + N' union all select * from ' + @PartitionName + N'..esg_detail_saas ';
				set @esg_detail_delivery_connection_sql_1 = @esg_detail_delivery_connection_sql_1+ N' union all select * from ' + @PartitionName + N'..esg_detail_delivery_connection ';
				set @esg_detail_delivery_recipient_sql_1 = @esg_detail_delivery_recipient_sql_1+ N' union all select * from ' + @PartitionName + N'..esg_detail_delivery_recipient ';
				set @esg_detail_policy_process_results_sql_1 = @esg_detail_policy_process_results_sql_1+ N' union all select * from ' + @PartitionName + N'..esg_detail_policy_process_results ';
				set @esg_summary_incoming_connection_sql_1 = @esg_summary_incoming_connection_sql_1 + N' union all select * from ' + @PartitionName + N'..esg_summary_incoming_connection ';
				set @esg_summary_incoming_connection_hour_sql_1 = @esg_summary_incoming_connection_hour_sql_1+ N' union all select * from ' + @PartitionName + N'..esg_summary_incoming_connection_hour ';
				set @esg_summary_delivery_connection_sql_1 = @esg_summary_delivery_connection_sql_1 + N' union all select * from ' + @PartitionName + N'..esg_summary_delivery_connection';
				set @esg_summary_delivery_recipient_sql_1 = @esg_summary_delivery_recipient_sql_1 + N' union all select * from ' + @PartitionName + N'..esg_summary_delivery_recipient';
				set @esg_summary_dlp_process_results_incident_sql_1	= @esg_summary_dlp_process_results_incident_sql_1 +  N' union all select * from ' + @PartitionName + N'..esg_summary_dlp_process_results_incident';
				set @esg_summary_dlp_process_results_policy_sql_1 = @esg_summary_dlp_process_results_policy_sql_1 +  N' union all select * from ' + @PartitionName + N'..esg_summary_dlp_process_results_policy';
				set @esg_summary_process_results_virus_sql_1 = @esg_summary_process_results_virus_sql_1 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_virus';
				set @esg_summary_process_results_message_total_sql_1 = @esg_summary_process_results_message_total_sql_1 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_total';
				set @esg_summary_process_results_message_direction_sql_1 = @esg_summary_process_results_message_direction_sql_1	 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_direction';
				set @esg_summary_process_results_message_direction_type_sql_1 =@esg_summary_process_results_message_direction_type_sql_1 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_direction_type';
				set @esg_summary_process_results_message_action_type_sql_1 =@esg_summary_process_results_message_action_type_sql_1 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_action_type';
				set @esg_summary_process_results_message_address_sql_1 =@esg_summary_process_results_message_address_sql_1 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_address';
				set @esg_summary_process_results_message_address_direction_sql_1 =@esg_summary_process_results_message_address_direction_sql_1 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_address_direction';
				set @esg_summary_process_results_message_address_direction_type_sql_1 = @esg_summary_process_results_message_address_direction_type_sql_1	 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_address_direction_type';
				set @esg_summary_process_results_message_address_direction_action_sql_1 =@esg_summary_process_results_message_address_direction_action_sql_1 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_address_direction_action';
				set @esg_detail_message_queue_sql_1 = @esg_detail_message_queue_sql_1 +  N' union all select * from ' + @PartitionName + N'..esg_detail_message_queue';
				set @esg_detail_dlp_policy_sql_1 = @esg_detail_dlp_policy_sql_1 + N' union all select * from ' + @PartitionName + N'..esg_detail_dlp_policy';
				set @esg_summary_logview_message_sql_1 = @esg_summary_logview_message_sql_1 + N' union all select * from ' + @PartitionName + N'..esg_summary_logview_message';
				--set @esg_detail_dlp_process_results_sql_1 = @esg_detail_dlp_process_results_sql_1 + N' union all select * from ' + @PartitionName + N'..esg_detail_dlp_process_results ';
				--set @esg_summary_saas_hour_sql_1 = @esg_summary_saas_hour_sql_1 + N' union all select * from ' + @PartitionName + N'..esg_summary_saas_hour ';
				--set @esg_summary_saas_sql_1 = @esg_summary_saas_sql_1 + N' union all select * from ' + @PartitionName + N'..esg_summary_saas ';				
				set @esg_summary_process_results_message_domain_direction_sql_1 =@esg_summary_process_results_message_domain_direction_sql_1 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_domain_direction';
				set @esg_summary_process_results_message_domain_direction_type_sql_1 =@esg_summary_process_results_message_domain_direction_type_sql_1 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_domain_direction_type';
				--set @esg_summary_message_queue_hour_sql_1 = @esg_summary_message_queue_hour_sql_1 +  N' union all select * from ' + @PartitionName + N'..esg_summary_message_queue_hour';
				--set @esg_summary_message_queue_sql_1 = @esg_summary_message_queue_sql_1+  N' union all select * from ' + @PartitionName + N'..esg_summary_message_queue';
			end
			else if (@i = 2 )
			begin
				set @esg_detail_incoming_connection_sql_2 = @esg_detail_incoming_connection_sql_2 + N' union all select * from ' + @PartitionName + N'..esg_detail_incoming_connection ';
				set @esg_detail_message_sql_2 = @esg_detail_message_sql_2 + ' union all select * from ' + @PartitionName + N'..esg_detail_message ';
				set @esg_detail_saas_sql_2 = @esg_detail_saas_sql_2 + N' union all select * from ' + @PartitionName + N'..esg_detail_saas ';
				set @esg_summary_incoming_connection_sql_2 = @esg_summary_incoming_connection_sql_2 + N' union all select * from ' + @PartitionName + N'..esg_summary_incoming_connection ';
				set @esg_detail_delivery_connection_sql_2 = @esg_detail_delivery_connection_sql_2+ N' union all select * from ' + @PartitionName + N'..esg_detail_delivery_connection ';
				set @esg_detail_delivery_recipient_sql_2 = @esg_detail_delivery_recipient_sql_2+ N' union all select * from ' + @PartitionName + N'..esg_detail_delivery_recipient ';
				set @esg_detail_policy_process_results_sql_2 = @esg_detail_policy_process_results_sql_2+ N' union all select * from ' + @PartitionName + N'..esg_detail_policy_process_results ';
				set @esg_summary_incoming_connection_hour_sql_2 = @esg_summary_incoming_connection_hour_sql_2+ N' union all select * from ' + @PartitionName + N'..esg_summary_incoming_connection_hour ';
				set @esg_summary_delivery_connection_sql_2 = @esg_summary_delivery_connection_sql_2 + N' union all select * from ' + @PartitionName + N'..esg_summary_delivery_connection ';
				set @esg_summary_delivery_recipient_sql_2 = @esg_summary_delivery_recipient_sql_2 + N' union all select * from ' + @PartitionName + N'..esg_summary_delivery_recipient ';
				set @esg_summary_dlp_process_results_incident_sql_2	= @esg_summary_dlp_process_results_incident_sql_2 +  N' union all select * from ' + @PartitionName + N'..esg_summary_dlp_process_results_incident';
				set @esg_summary_dlp_process_results_policy_sql_2 = @esg_summary_dlp_process_results_policy_sql_2 +  N' union all select * from ' + @PartitionName + N'..esg_summary_dlp_process_results_policy';
				set @esg_summary_process_results_virus_sql_2 = @esg_summary_process_results_virus_sql_2 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_virus';
				set @esg_summary_process_results_message_total_sql_2 = @esg_summary_process_results_message_total_sql_2 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_total';
				set @esg_summary_process_results_message_direction_sql_2 = @esg_summary_process_results_message_direction_sql_2	 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_direction';
				set @esg_summary_process_results_message_direction_type_sql_2 =@esg_summary_process_results_message_direction_type_sql_2 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_direction_type';
				set @esg_summary_process_results_message_action_type_sql_2 =@esg_summary_process_results_message_action_type_sql_2 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_action_type';
				set @esg_summary_process_results_message_address_sql_2 =@esg_summary_process_results_message_address_sql_2 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_address';
				set @esg_summary_process_results_message_address_direction_sql_2 =@esg_summary_process_results_message_address_direction_sql_2 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_address_direction';
				set @esg_summary_process_results_message_address_direction_type_sql_2 = @esg_summary_process_results_message_address_direction_type_sql_2	 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_address_direction_type';
				set @esg_summary_process_results_message_address_direction_action_sql_2 =@esg_summary_process_results_message_address_direction_action_sql_2 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_address_direction_action';
				set @esg_detail_message_queue_sql_2 = @esg_detail_message_queue_sql_2 +  N' union all select * from ' + @PartitionName + N'..esg_detail_message_queue';
				set @esg_detail_dlp_policy_sql_2 = @esg_detail_dlp_policy_sql_2 + N' union all select * from ' + @PartitionName + N'..esg_detail_dlp_policy';
				set @esg_summary_logview_message_sql_2 = @esg_summary_logview_message_sql_2 + N' union all select * from ' + @PartitionName + N'..esg_summary_logview_message';
				--set @esg_detail_dlp_process_results_sql_2 = @esg_detail_dlp_process_results_sql_2 + N' union all select * from ' + @PartitionName + N'..esg_detail_dlp_process_results  ';
				--set @esg_summary_saas_hour_sql_2 = @esg_summary_saas_hour_sql_2 + N' union all select * from ' + @PartitionName + N'..esg_summary_saas_hour ';
				--set @esg_summary_saas_sql_2 = @esg_summary_saas_sql_2 + N' union all select * from ' + @PartitionName + N'..esg_summary_saas ';				
				set @esg_summary_process_results_message_domain_direction_sql_2 =@esg_summary_process_results_message_domain_direction_sql_2 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_domain_direction';
				set @esg_summary_process_results_message_domain_direction_type_sql_2 =@esg_summary_process_results_message_domain_direction_type_sql_2 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_domain_direction_type';
				--set @esg_summary_message_queue_hour_sql_2 = @esg_summary_message_queue_hour_sql_2 +  N' union all select * from ' + @PartitionName + N'..esg_summary_message_queue_hour';
				--set @esg_summary_message_queue_sql_2 = @esg_summary_message_queue_sql_2+  N' union all select * from ' + @PartitionName + N'..esg_summary_message_queue';

			end
			else if (@i = 3 )
			begin
				set @esg_detail_incoming_connection_sql_3 = @esg_detail_incoming_connection_sql_3 + N' union all select * from ' + @PartitionName + N'..esg_detail_incoming_connection ';
				set @esg_detail_message_sql_3 = @esg_detail_message_sql_3 + ' union all select * from ' + @PartitionName + N'..esg_detail_message ';
				set @esg_detail_saas_sql_3 = @esg_detail_saas_sql_3 + N' union all select * from ' + @PartitionName + N'..esg_detail_saas ';
				set @esg_summary_incoming_connection_sql_3 = @esg_summary_incoming_connection_sql_3 + N' union all select * from ' + @PartitionName + N'..esg_summary_incoming_connection ';
				set @esg_detail_delivery_connection_sql_3 = @esg_detail_delivery_connection_sql_3+ N' union all select * from ' + @PartitionName + N'..esg_detail_delivery_connection ';
				set @esg_detail_delivery_recipient_sql_3 = @esg_detail_delivery_recipient_sql_3+ N' union all select * from ' + @PartitionName + N'..esg_detail_delivery_recipient ';
				set @esg_detail_policy_process_results_sql_3 = @esg_detail_policy_process_results_sql_3+ N' union all select * from ' + @PartitionName + N'..esg_detail_policy_process_results ';
				set @esg_summary_incoming_connection_hour_sql_3 = @esg_summary_incoming_connection_hour_sql_3+ N' union all select * from ' + @PartitionName + N'..esg_summary_incoming_connection_hour ';
				set @esg_summary_delivery_connection_sql_3 = @esg_summary_delivery_connection_sql_3 + N' union all select * from ' + @PartitionName + N'..esg_summary_delivery_connection ';
				set @esg_summary_delivery_recipient_sql_3 = @esg_summary_delivery_recipient_sql_3 + N' union all select * from ' + @PartitionName + N'..esg_summary_delivery_recipient ';
				set @esg_summary_dlp_process_results_incident_sql_3	= @esg_summary_dlp_process_results_incident_sql_3 +  N' union all select * from ' + @PartitionName + N'..esg_summary_dlp_process_results_incident ';
				set @esg_summary_dlp_process_results_policy_sql_3 = @esg_summary_dlp_process_results_policy_sql_3 +  N' union all select * from ' + @PartitionName + N'..esg_summary_dlp_process_results_policy ';
				set @esg_summary_process_results_virus_sql_3 = @esg_summary_process_results_virus_sql_3 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_virus ';
				set @esg_summary_process_results_message_total_sql_3 = @esg_summary_process_results_message_total_sql_3 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_total ';
				set @esg_summary_process_results_message_direction_sql_3 = @esg_summary_process_results_message_direction_sql_3	 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_direction ';
				set @esg_summary_process_results_message_direction_type_sql_3 =@esg_summary_process_results_message_direction_type_sql_3 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_direction_type ';
				set @esg_summary_process_results_message_action_type_sql_3 =@esg_summary_process_results_message_action_type_sql_3 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_action_type ';
				set @esg_summary_process_results_message_address_sql_3 =@esg_summary_process_results_message_address_sql_3 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_address ';
				set @esg_summary_process_results_message_address_direction_sql_3 =@esg_summary_process_results_message_address_direction_sql_3 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_address_direction ';
				set @esg_summary_process_results_message_address_direction_type_sql_3 = @esg_summary_process_results_message_address_direction_type_sql_3	 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_address_direction_type ';
				set @esg_summary_process_results_message_address_direction_action_sql_3 =@esg_summary_process_results_message_address_direction_action_sql_3 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_address_direction_action ';
				set @esg_detail_message_queue_sql_3 = @esg_detail_message_queue_sql_3 +  N' union all select * from ' + @PartitionName + N'..esg_detail_message_queue';
				set @esg_detail_dlp_policy_sql_3 = @esg_detail_dlp_policy_sql_3 + N' union all select * from ' + @PartitionName + N'..esg_detail_dlp_policy';
				set @esg_summary_logview_message_sql_3 = @esg_summary_logview_message_sql_3 + N' union all select * from ' + @PartitionName + N'..esg_summary_logview_message';
				--set @esg_detail_dlp_process_results_sql_3 = @esg_detail_dlp_process_results_sql_3 + N' union all select * from ' + @PartitionName + N'..esg_detail_dlp_process_results ';
				--set @esg_summary_saas_hour_sql_3 = @esg_summary_saas_hour_sql_3 + N' union all select * from ' + @PartitionName + N'..esg_summary_saas_hour ';
				--set @esg_summary_saas_sql_3 = @esg_summary_saas_sql_3 + N' union all select * from ' + @PartitionName + N'..esg_summary_saas ';				
				set @esg_summary_process_results_message_domain_direction_sql_3 =@esg_summary_process_results_message_domain_direction_sql_3 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_domain_direction ';
				set @esg_summary_process_results_message_domain_direction_type_sql_3 =@esg_summary_process_results_message_domain_direction_type_sql_3 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_domain_direction_type ';
				--set @esg_summary_message_queue_hour_sql_3 = @esg_summary_message_queue_hour_sql_3 +  N' union all select * from ' + @PartitionName + N'..esg_summary_message_queue_hour';
				--set @esg_summary_message_queue_sql_3 = @esg_summary_message_queue_sql_3+  N' union all select * from ' + @PartitionName + N'..esg_summary_message_queue';
			end
			else if (@i = 4 )
			begin
				set @esg_detail_incoming_connection_sql_4 = @esg_detail_incoming_connection_sql_4 + N' union all select * from ' + @PartitionName + N'..esg_detail_incoming_connection ';
				set @esg_detail_message_sql_4 = @esg_detail_message_sql_4 + ' union all select * from ' + @PartitionName + N'..esg_detail_message ';
				set @esg_detail_saas_sql_4 = @esg_detail_saas_sql_4 + N' union all select * from ' + @PartitionName + N'..esg_detail_saas ';
				set @esg_summary_incoming_connection_sql_4 = @esg_summary_incoming_connection_sql_4 + N' union all select * from ' + @PartitionName + N'..esg_summary_incoming_connection ';
				set @esg_detail_delivery_connection_sql_4 = @esg_detail_delivery_connection_sql_4+ N' union all select * from ' + @PartitionName + N'..esg_detail_delivery_connection ';
				set @esg_detail_delivery_recipient_sql_4 = @esg_detail_delivery_recipient_sql_4+ N' union all select * from ' + @PartitionName + N'..esg_detail_delivery_recipient ';
				set @esg_detail_policy_process_results_sql_4 = @esg_detail_policy_process_results_sql_4+ N' union all select * from ' + @PartitionName + N'..esg_detail_policy_process_results ';
				set @esg_summary_incoming_connection_hour_sql_4 = @esg_summary_incoming_connection_hour_sql_4+ N' union all select * from ' + @PartitionName + N'..esg_summary_incoming_connection_hour ';
				set @esg_summary_delivery_connection_sql_4 = @esg_summary_delivery_connection_sql_4 + N' union all select * from ' + @PartitionName + N'..esg_summary_delivery_connection ';
				set @esg_summary_delivery_recipient_sql_4 = @esg_summary_delivery_recipient_sql_4 + N' union all select * from ' + @PartitionName + N'..esg_summary_delivery_recipient ';
				set @esg_summary_dlp_process_results_incident_sql_4	= @esg_summary_dlp_process_results_incident_sql_4 +  N' union all select * from ' + @PartitionName + N'..esg_summary_dlp_process_results_incident';
				set @esg_summary_dlp_process_results_policy_sql_4 = @esg_summary_dlp_process_results_policy_sql_4 +  N' union all select * from ' + @PartitionName + N'..esg_summary_dlp_process_results_policy';
				set @esg_summary_process_results_virus_sql_4 = @esg_summary_process_results_virus_sql_4 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_virus';
				set @esg_summary_process_results_message_total_sql_4 = @esg_summary_process_results_message_total_sql_4 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_total';
				set @esg_summary_process_results_message_direction_sql_4 = @esg_summary_process_results_message_direction_sql_4	 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_direction';
				set @esg_summary_process_results_message_direction_type_sql_4 =@esg_summary_process_results_message_direction_type_sql_4 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_direction_type';
				set @esg_summary_process_results_message_action_type_sql_4 =@esg_summary_process_results_message_action_type_sql_4 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_action_type';
				set @esg_summary_process_results_message_address_sql_4 =@esg_summary_process_results_message_address_sql_4 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_address';
				set @esg_summary_process_results_message_address_direction_sql_4 =@esg_summary_process_results_message_address_direction_sql_4 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_address_direction';
				set @esg_summary_process_results_message_address_direction_type_sql_4 = @esg_summary_process_results_message_address_direction_type_sql_4	 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_address_direction_type';
				set @esg_summary_process_results_message_address_direction_action_sql_4 =@esg_summary_process_results_message_address_direction_action_sql_4 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_address_direction_action';
				set @esg_detail_message_queue_sql_4 = @esg_detail_message_queue_sql_4 +  N' union all select * from ' + @PartitionName + N'..esg_detail_message_queue';
				set @esg_detail_dlp_policy_sql_4 = @esg_detail_dlp_policy_sql_4 + N' union all select * from ' + @PartitionName + N'..esg_detail_dlp_policy';
				set @esg_summary_logview_message_sql_4 = @esg_summary_logview_message_sql_4 + N' union all select * from ' + @PartitionName + N'..esg_summary_logview_message';
				--set @esg_detail_dlp_process_results_sql_4 = @esg_detail_dlp_process_results_sql_4 + N' union all select * from ' + @PartitionName + N'..esg_detail_dlp_process_results ';
				--set @esg_summary_saas_hour_sql_4 = @esg_summary_saas_hour_sql_4 + N' union all select * from ' + @PartitionName + N'..esg_summary_saas_hour ';
				--set @esg_summary_saas_sql_4 = @esg_summary_saas_sql_4 + N' union all select * from ' + @PartitionName + N'..esg_summary_saas ';				
				set @esg_summary_process_results_message_domain_direction_sql_4 =@esg_summary_process_results_message_domain_direction_sql_4 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_domain_direction';
				set @esg_summary_process_results_message_domain_direction_type_sql_4 =@esg_summary_process_results_message_domain_direction_type_sql_4 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_domain_direction_type';
				--set @esg_summary_message_queue_hour_sql_4 = @esg_summary_message_queue_hour_sql_4 +  N' union all select * from ' + @PartitionName + N'..esg_summary_message_queue_hour';
				--set @esg_summary_message_queue_sql_4 = @esg_summary_message_queue_sql_4+  N' union all select * from ' + @PartitionName + N'..esg_summary_message_queue';
			end
			else if (@i = 5 )
			begin
				set @esg_detail_incoming_connection_sql_5 = @esg_detail_incoming_connection_sql_5 + N' union all select * from ' + @PartitionName + N'..esg_detail_incoming_connection ';
				set @esg_detail_message_sql_5 = @esg_detail_message_sql_5 + ' union all select * from ' + @PartitionName + N'..esg_detail_message ';
				set @esg_detail_saas_sql_5 = @esg_detail_saas_sql_5 + N' union all select * from ' + @PartitionName + N'..esg_detail_saas ';
				set @esg_summary_incoming_connection_sql_5 = @esg_summary_incoming_connection_sql_5 + N' union all select * from ' + @PartitionName + N'..esg_summary_incoming_connection ';
				set @esg_detail_delivery_connection_sql_5 = @esg_detail_delivery_connection_sql_5+ N' union all select * from ' + @PartitionName + N'..esg_detail_delivery_connection ';
				set @esg_detail_delivery_recipient_sql_5 = @esg_detail_delivery_recipient_sql_5+ N' union all select * from ' + @PartitionName + N'..esg_detail_delivery_recipient ';
				set @esg_detail_policy_process_results_sql_5 = @esg_detail_policy_process_results_sql_5+ N' union all select * from ' + @PartitionName + N'..esg_detail_policy_process_results ';
				set @esg_summary_incoming_connection_hour_sql_5 = @esg_summary_incoming_connection_hour_sql_5+ N' union all select * from ' + @PartitionName + N'..esg_summary_incoming_connection_hour ';
				set @esg_summary_delivery_connection_sql_5 = @esg_summary_delivery_connection_sql_5 + N' union all select * from ' + @PartitionName + N'..esg_summary_delivery_connection ';
				set @esg_summary_delivery_recipient_sql_5 = @esg_summary_delivery_recipient_sql_5 + N' union all select * from ' + @PartitionName + N'..esg_summary_delivery_recipient ';
				set @esg_summary_dlp_process_results_incident_sql_5	= @esg_summary_dlp_process_results_incident_sql_5 +  N' union all select * from ' + @PartitionName + N'..esg_summary_dlp_process_results_incident';
				set @esg_summary_dlp_process_results_policy_sql_5 = @esg_summary_dlp_process_results_policy_sql_5 +  N' union all select * from ' + @PartitionName + N'..esg_summary_dlp_process_results_policy';
				set @esg_summary_process_results_virus_sql_5 = @esg_summary_process_results_virus_sql_5 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_virus';
				set @esg_summary_process_results_message_total_sql_5 = @esg_summary_process_results_message_total_sql_5 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_total';
				set @esg_summary_process_results_message_direction_sql_5 = @esg_summary_process_results_message_direction_sql_5	 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_direction';
				set @esg_summary_process_results_message_direction_type_sql_5 =@esg_summary_process_results_message_direction_type_sql_5 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_direction_type';
				set @esg_summary_process_results_message_action_type_sql_5 =@esg_summary_process_results_message_action_type_sql_5 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_action_type';
				set @esg_summary_process_results_message_address_sql_5 =@esg_summary_process_results_message_address_sql_5 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_address';
				set @esg_summary_process_results_message_address_direction_sql_5 =@esg_summary_process_results_message_address_direction_sql_5 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_address_direction';
				set @esg_summary_process_results_message_address_direction_type_sql_5 = @esg_summary_process_results_message_address_direction_type_sql_5	 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_address_direction_type';
				set @esg_summary_process_results_message_address_direction_action_sql_5 =@esg_summary_process_results_message_address_direction_action_sql_5 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_address_direction_action';
				set @esg_detail_message_queue_sql_5 = @esg_detail_message_queue_sql_5 +  N' union all select * from ' + @PartitionName + N'..esg_detail_message_queue';
				set @esg_detail_dlp_policy_sql_5 = @esg_detail_dlp_policy_sql_5 + N' union all select * from ' + @PartitionName + N'..esg_detail_dlp_policy';
				set @esg_summary_logview_message_sql_5 = @esg_summary_logview_message_sql_5 + N' union all select * from ' + @PartitionName + N'..esg_summary_logview_message';
				--set @esg_detail_dlp_process_results_sql_5 = @esg_detail_dlp_process_results_sql_5 + N' union all select * from ' + @PartitionName + N'..esg_detail_dlp_process_results ';
				--set @esg_summary_saas_hour_sql_5 = @esg_summary_saas_hour_sql_5 + N' union all select * from ' + @PartitionName + N'..esg_summary_saas_hour ';
				--set @esg_summary_saas_sql_5 = @esg_summary_saas_sql_5 + N' union all select * from ' + @PartitionName + N'..esg_summary_saas ';				
				set @esg_summary_process_results_message_domain_direction_sql_5 =@esg_summary_process_results_message_domain_direction_sql_5 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_domain_direction';
				set @esg_summary_process_results_message_domain_direction_type_sql_5 =@esg_summary_process_results_message_domain_direction_type_sql_5 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_domain_direction_type';
				--set @esg_summary_message_queue_hour_sql_5 = @esg_summary_message_queue_hour_sql_5 +  N' union all select * from ' + @PartitionName + N'..esg_summary_message_queue_hour';
				--set @esg_summary_message_queue_sql_5 = @esg_summary_message_queue_sql_5+  N' union all select * from ' + @PartitionName + N'..esg_summary_message_queue';
			end
			else if (@i = 6 )
			begin
				set @esg_detail_incoming_connection_sql_6 = @esg_detail_incoming_connection_sql_6 + N' union all select * from ' + @PartitionName + N'..esg_detail_incoming_connection ';
				set @esg_detail_message_sql_6 = @esg_detail_message_sql_6 + ' union all select * from ' + @PartitionName + N'..esg_detail_message ';
				set @esg_detail_saas_sql_6 = @esg_detail_saas_sql_6 + N' union all select * from ' + @PartitionName + N'..esg_detail_saas ';
				set @esg_summary_incoming_connection_sql_6 = @esg_summary_incoming_connection_sql_6 + N' union all select * from ' + @PartitionName + N'..esg_summary_incoming_connection ';
				set @esg_detail_delivery_connection_sql_6 = @esg_detail_delivery_connection_sql_6+ N' union all select * from ' + @PartitionName + N'..esg_detail_delivery_connection ';
				set @esg_detail_delivery_recipient_sql_6 = @esg_detail_delivery_recipient_sql_6+ N' union all select * from ' + @PartitionName + N'..esg_detail_delivery_recipient ';
				set @esg_detail_policy_process_results_sql_6 = @esg_detail_policy_process_results_sql_6+ N' union all select * from ' + @PartitionName + N'..esg_detail_policy_process_results ';
				set @esg_summary_incoming_connection_hour_sql_6 = @esg_summary_incoming_connection_hour_sql_6+ N' union all select * from ' + @PartitionName + N'..esg_summary_incoming_connection_hour ';
				set @esg_summary_delivery_connection_sql_6 = @esg_summary_delivery_connection_sql_6 + N' union all select * from ' + @PartitionName + N'..esg_summary_delivery_connection ';
				set @esg_summary_delivery_recipient_sql_6 = @esg_summary_delivery_recipient_sql_6 + N' union all select * from ' + @PartitionName + N'..esg_summary_delivery_recipient ';
				set @esg_summary_dlp_process_results_incident_sql_6	= @esg_summary_dlp_process_results_incident_sql_6 +  N' union all select * from ' + @PartitionName + N'..esg_summary_dlp_process_results_incident';
				set @esg_summary_dlp_process_results_policy_sql_6 = @esg_summary_dlp_process_results_policy_sql_6 +  N' union all select * from ' + @PartitionName + N'..esg_summary_dlp_process_results_policy';
				set @esg_summary_process_results_virus_sql_6 = @esg_summary_process_results_virus_sql_6 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_virus';
				set @esg_summary_process_results_message_total_sql_6 = @esg_summary_process_results_message_total_sql_6 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_total';
				set @esg_summary_process_results_message_direction_sql_6 = @esg_summary_process_results_message_direction_sql_6	 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_direction';
				set @esg_summary_process_results_message_direction_type_sql_6 =@esg_summary_process_results_message_direction_type_sql_6 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_direction_type';
				set @esg_summary_process_results_message_action_type_sql_6 =@esg_summary_process_results_message_action_type_sql_6 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_action_type';
				set @esg_summary_process_results_message_address_sql_6 =@esg_summary_process_results_message_address_sql_6 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_address';
				set @esg_summary_process_results_message_address_direction_sql_6 =@esg_summary_process_results_message_address_direction_sql_6 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_address_direction';
				set @esg_summary_process_results_message_address_direction_type_sql_6 = @esg_summary_process_results_message_address_direction_type_sql_6	 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_address_direction_type';
				set @esg_summary_process_results_message_address_direction_action_sql_6 =@esg_summary_process_results_message_address_direction_action_sql_6 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_address_direction_action';
				set @esg_detail_message_queue_sql_6 = @esg_detail_message_queue_sql_6 +  N' union all select * from ' + @PartitionName + N'..esg_detail_message_queue';
				set @esg_detail_dlp_policy_sql_6 = @esg_detail_dlp_policy_sql_6 + N' union all select * from ' + @PartitionName + N'..esg_detail_dlp_policy';
				set @esg_summary_logview_message_sql_6 = @esg_summary_logview_message_sql_6 + N' union all select * from ' + @PartitionName + N'..esg_summary_logview_message';
				--set @esg_detail_dlp_process_results_sql_6 = @esg_detail_dlp_process_results_sql_6 + N' union all select * from ' + @PartitionName + N'..esg_detail_dlp_process_results ';
				--set @esg_summary_saas_hour_sql_6 = @esg_summary_saas_hour_sql_6 + N' union all select * from ' + @PartitionName + N'..esg_summary_saas_hour ';
				--set @esg_summary_saas_sql_6 = @esg_summary_saas_sql_6 + N' union all select * from ' + @PartitionName + N'..esg_summary_saas ';				
				set @esg_summary_process_results_message_domain_direction_sql_6 =@esg_summary_process_results_message_domain_direction_sql_6 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_domain_direction';
				set @esg_summary_process_results_message_domain_direction_type_sql_6 =@esg_summary_process_results_message_domain_direction_type_sql_6 +  N' union all select * from ' + @PartitionName + N'..esg_summary_process_results_message_domain_direction_type';
				--set @esg_summary_message_queue_hour_sql_6 = @esg_summary_message_queue_hour_sql_6 +  N' union all select * from ' + @PartitionName + N'..esg_summary_message_queue_hour';
				--set @esg_summary_message_queue_sql_6 = @esg_summary_message_queue_sql_6+  N' union all select * from ' + @PartitionName + N'..esg_summary_message_queue';

			end

			if(@i = 1 and ( LEN(@esg_detail_incoming_connection_sql_1) > 3900 or LEN(@esg_detail_message_sql_1) > 3900 or LEN(@esg_detail_saas_sql_1) > 3900 
							or LEN(@esg_detail_delivery_connection_sql_1) > 3900 or LEN(@esg_detail_delivery_recipient_sql_1) > 3900 or LEN(@esg_detail_policy_process_results_sql_1) > 3900 
							or LEN(@esg_summary_incoming_connection_sql_1) >3900 or LEN(@esg_summary_incoming_connection_hour_sql_1) > 3900  
							or LEN(@esg_summary_delivery_connection_sql_1) > 3900 or LEN(@esg_summary_delivery_recipient_sql_1) > 3900 
							or LEN(@esg_summary_process_results_message_total_sql_1) >3900 or LEN(@esg_summary_process_results_message_direction_sql_1) > 3900 or LEN(@esg_summary_process_results_message_address_direction_type_sql_1) >3900 
							or LEN(@esg_summary_process_results_virus_sql_1) > 3900 or LEN(@esg_summary_dlp_process_results_incident_sql_1) > 3900 or LEN(@esg_summary_dlp_process_results_policy_sql_1) > 3900 
							or LEN(@esg_summary_process_results_message_address_sql_1) >3900 or LEN(@esg_summary_process_results_message_direction_type_sql_1) >3900 or LEN(@esg_summary_process_results_message_action_type_sql_1) >3900
							or LEN(@esg_summary_process_results_message_address_direction_sql_1) >3900 
							or LEN(@esg_summary_process_results_message_address_direction_action_sql_1) >3900 
							or LEN(@esg_detail_message_queue_sql_1) > 3900 
							or LEN(@esg_summary_process_results_message_domain_direction_sql_1) >3900 
							or LEN(@esg_summary_process_results_message_domain_direction_type_sql_1) >3900 
							--or  LEN(@esg_summary_saas_hour_sql_1) > 3900  or LEN(@esg_summary_saas_sql_1) > 3900 
							--or LEN(@esg_detail_dlp_process_results_sql_1) > 3900 
							--or LEN(@esg_summary_message_queue_hour_sql_1) >3900 or LEN(@esg_summary_message_queue_sql_1) > 3900 
							or LEN(@esg_detail_policy_process_results_sql_1) > 3900 or LEN(@esg_summary_logview_message_sql_1) >3900 ))
				set @i = 2;
			else if (@i = 2 and (LEN(@esg_detail_incoming_connection_sql_2) > 3900 or LEN(@esg_detail_message_sql_2) > 3900 or LEN(@esg_detail_saas_sql_2) > 3900 
								or LEN(@esg_detail_delivery_connection_sql_2) > 3900 or LEN(@esg_detail_delivery_recipient_sql_2) > 3900 or LEN(@esg_detail_policy_process_results_sql_2) > 3900 
								or LEN(@esg_summary_incoming_connection_sql_2) >3900 or LEN(@esg_summary_incoming_connection_hour_sql_2) > 3900  
								or LEN(@esg_summary_delivery_connection_sql_2) > 3900 or LEN(@esg_summary_delivery_recipient_sql_2) > 3900 
								or LEN(@esg_summary_process_results_message_total_sql_2) >3900 or LEN(@esg_summary_process_results_message_direction_sql_2) > 3900 or LEN(@esg_summary_process_results_message_address_direction_type_sql_2) >3900 
								or LEN(@esg_summary_process_results_virus_sql_2) > 3900 or LEN(@esg_summary_dlp_process_results_incident_sql_2) > 3900 or LEN(@esg_summary_dlp_process_results_policy_sql_2) > 3900
							or LEN(@esg_summary_process_results_message_address_sql_2) >3900 or LEN(@esg_summary_process_results_message_direction_type_sql_2) >3900 or LEN(@esg_summary_process_results_message_action_type_sql_2) >3900
							or LEN(@esg_summary_process_results_message_address_direction_sql_2) >3900 
							or LEN(@esg_summary_process_results_message_address_direction_action_sql_2) >3900  
							or LEN(@esg_detail_message_queue_sql_2) > 3900 
							or LEN(@esg_summary_process_results_message_domain_direction_type_sql_2) >3900 
							or LEN(@esg_summary_process_results_message_domain_direction_sql_2) >3900 
							--or LEN(@esg_summary_saas_hour_sql_2) > 3900  or LEN(@esg_summary_saas_sql_2) > 3900
							--or LEN(@esg_detail_dlp_process_results_sql_2) > 3900  
							--or LEN(@esg_summary_message_queue_hour_sql_2) >3900 or LEN(@esg_summary_message_queue_sql_2) > 3900 
							or LEN(@esg_detail_policy_process_results_sql_2) > 3900 or LEN(@esg_summary_logview_message_sql_2) >3900 ))
				set @i = 3;
			else if (@i = 3 and (LEN(@esg_detail_incoming_connection_sql_3) > 3900 or LEN(@esg_detail_message_sql_3) > 3900 or LEN(@esg_detail_saas_sql_3) > 3900 
								or LEN(@esg_detail_delivery_connection_sql_3) > 3900 or LEN(@esg_detail_delivery_recipient_sql_3) > 3900 or LEN(@esg_detail_policy_process_results_sql_3) > 3900  
								or  LEN(@esg_summary_incoming_connection_sql_3) >3900 or LEN(@esg_summary_incoming_connection_hour_sql_3) > 3900  
								or LEN(@esg_summary_delivery_connection_sql_3) > 3900 or LEN(@esg_summary_delivery_recipient_sql_3) > 3900 
								or LEN(@esg_summary_process_results_message_total_sql_3) >3900 or LEN(@esg_summary_process_results_message_direction_sql_3) > 3900 or LEN(@esg_summary_process_results_message_address_direction_type_sql_3) >3900 
								or LEN(@esg_summary_process_results_virus_sql_3) > 3900 or LEN(@esg_summary_dlp_process_results_incident_sql_3) > 3900 or LEN(@esg_summary_dlp_process_results_policy_sql_3) > 3900
							or LEN(@esg_summary_process_results_message_address_sql_3) >3900 or LEN(@esg_summary_process_results_message_direction_type_sql_3) >3900 or LEN(@esg_summary_process_results_message_action_type_sql_3) >3900
							or LEN(@esg_summary_process_results_message_address_direction_sql_3) >3900 
							or LEN(@esg_summary_process_results_message_address_direction_action_sql_3) >3900   
							or LEN(@esg_detail_message_queue_sql_3) > 3900 
							or LEN(@esg_summary_process_results_message_domain_direction_type_sql_3) >3900 
							--or LEN(@esg_detail_dlp_process_results_sql_3) > 3900  
							or LEN(@esg_summary_process_results_message_domain_direction_sql_3) >3900 
							--or LEN(@esg_summary_saas_hour_sql_3) > 3900  or LEN(@esg_summary_saas_sql_3) > 3900
							--or LEN(@esg_summary_message_queue_hour_sql_3) >3900 or LEN(@esg_summary_message_queue_sql_3) > 3900 
							or LEN(@esg_detail_policy_process_results_sql_3) > 3900 or LEN(@esg_summary_logview_message_sql_3) >3900 ))
				set @i = 4;
			else if (@i = 4 and (LEN(@esg_detail_incoming_connection_sql_4) > 3900 or LEN(@esg_detail_message_sql_4) > 3900 or LEN(@esg_detail_saas_sql_4) > 3900 
								or LEN(@esg_detail_delivery_connection_sql_4) > 3900 or LEN(@esg_detail_delivery_recipient_sql_4) > 3900 or LEN(@esg_detail_policy_process_results_sql_4) > 3900  
								or LEN(@esg_summary_incoming_connection_sql_4) >3900 or LEN(@esg_summary_incoming_connection_hour_sql_4) > 3900  
								or LEN(@esg_summary_delivery_connection_sql_4) > 3900 or LEN(@esg_summary_delivery_recipient_sql_4) > 3900
								or LEN(@esg_summary_process_results_message_total_sql_4) >3900 or LEN(@esg_summary_process_results_message_direction_sql_4) > 3900 or LEN(@esg_summary_process_results_message_address_direction_type_sql_4) >3900 
								or LEN(@esg_summary_process_results_virus_sql_4) > 3900 or LEN(@esg_summary_dlp_process_results_incident_sql_4) > 3900 or LEN(@esg_summary_dlp_process_results_policy_sql_4) > 3900
							or LEN(@esg_summary_process_results_message_address_sql_4) >3900 or LEN(@esg_summary_process_results_message_direction_type_sql_4) >3900 or LEN(@esg_summary_process_results_message_action_type_sql_4) >3900
							or LEN(@esg_summary_process_results_message_address_direction_sql_4) >3900 
							or LEN(@esg_summary_process_results_message_address_direction_action_sql_4) >3900   
							or LEN(@esg_detail_message_queue_sql_4) > 3900 
							--or LEN(@esg_detail_dlp_process_results_sql_4) > 3900  
							or LEN(@esg_summary_process_results_message_domain_direction_type_sql_4) >3900 
							--or LEN(@esg_summary_saas_hour_sql_4) > 3900  or LEN(@esg_summary_saas_sql_4) > 3900
							or LEN(@esg_summary_process_results_message_domain_direction_sql_4) >3900 
							--or LEN(@esg_summary_message_queue_hour_sql_4) >3900 or LEN(@esg_summary_message_queue_sql_4) > 3900 
							or LEN(@esg_detail_policy_process_results_sql_4) > 3900 or LEN(@esg_summary_logview_message_sql_4) >3900 ))
				set @i = 5;
			else if (@i = 5 and (len(@esg_detail_incoming_connection_sql_5) > 3900 or len(@esg_detail_message_sql_5) > 3900 or len(@esg_detail_saas_sql_5) > 3900 
								or LEN(@esg_detail_delivery_connection_sql_5) > 3900 or LEN(@esg_detail_delivery_recipient_sql_5) > 3900 or LEN(@esg_detail_policy_process_results_sql_5) > 3900  
								or LEN(@esg_summary_incoming_connection_sql_5) >3900 or LEN(@esg_summary_incoming_connection_hour_sql_5) > 3900 
								or len(@esg_summary_delivery_connection_sql_5) > 3900 or len(@esg_summary_delivery_recipient_sql_5) > 3900
								or LEN(@esg_summary_process_results_message_total_sql_5) >3900 or LEN(@esg_summary_process_results_message_direction_sql_5) > 3900 or LEN(@esg_summary_process_results_message_address_direction_type_sql_5) >3900 
								or LEN(@esg_summary_process_results_virus_sql_5) > 3900 or LEN(@esg_summary_dlp_process_results_incident_sql_5) > 3900 or LEN(@esg_summary_dlp_process_results_policy_sql_5) > 3900
							or LEN(@esg_summary_process_results_message_address_sql_5) >3900 or LEN(@esg_summary_process_results_message_direction_type_sql_5) >3900 or LEN(@esg_summary_process_results_message_action_type_sql_5) >3900
							or LEN(@esg_summary_process_results_message_address_direction_sql_5) >3900 
							or LEN(@esg_summary_process_results_message_address_direction_action_sql_5) >3900   
							or LEN(@esg_detail_message_queue_sql_5) > 3900 
							--or LEN(@esg_detail_dlp_process_results_sql_5) > 3900  
							--or LEN(@esg_summary_saas_hour_sql_5) > 3900  or LEN(@esg_summary_saas_sql_5) > 3900
							or LEN(@esg_summary_process_results_message_domain_direction_type_sql_5) >3900 
							or LEN(@esg_summary_process_results_message_domain_direction_sql_5) >3900 
							--or LEN(@esg_summary_message_queue_hour_sql_5) >3900 or LEN(@esg_summary_message_queue_sql_5) > 3900 
							or LEN(@esg_detail_policy_process_results_sql_5) > 3900 or LEN(@esg_summary_logview_message_sql_5) >3900 ))
				set @i = 6;
			else if (@i = 6 and (len(@esg_detail_incoming_connection_sql_6) > 3900 or len(@esg_detail_message_sql_6) > 3900 or len(@esg_detail_saas_sql_6) > 3900 
								or LEN(@esg_detail_delivery_connection_sql_6) > 3900 or LEN(@esg_detail_delivery_recipient_sql_6) > 3900 or LEN(@esg_detail_policy_process_results_sql_6) > 3900 
								or LEN(@esg_summary_incoming_connection_sql_6) >3900 or LEN(@esg_summary_incoming_connection_hour_sql_6) > 3900 
								or LEN(@esg_summary_delivery_connection_sql_6) > 3900 or LEN(@esg_summary_delivery_recipient_sql_6) > 3900
								or LEN(@esg_summary_process_results_message_total_sql_6) >3900 or LEN(@esg_summary_process_results_message_direction_sql_6) > 3900 or LEN(@esg_summary_process_results_message_address_direction_type_sql_6) >3900 
								or LEN(@esg_summary_process_results_virus_sql_6) > 3900 or LEN(@esg_summary_dlp_process_results_incident_sql_6) > 3900 or LEN(@esg_summary_dlp_process_results_policy_sql_6) > 3900
							or LEN(@esg_summary_process_results_message_address_sql_6) >3900 or LEN(@esg_summary_process_results_message_direction_type_sql_6) >3900 or LEN(@esg_summary_process_results_message_action_type_sql_6) >3900
							or LEN(@esg_summary_process_results_message_address_direction_sql_6) >3900 
							or LEN(@esg_summary_process_results_message_address_direction_action_sql_6) >3900   
							or LEN(@esg_detail_message_queue_sql_6) > 3900 
							--or LEN(@esg_detail_dlp_process_results_sql_6) > 3900  
							--or LEN(@esg_summary_saas_hour_sql_6) > 3900  or LEN(@esg_summary_saas_sql_6) > 3900
							or LEN(@esg_summary_process_results_message_domain_direction_type_sql_6) >3900 
							or LEN(@esg_summary_process_results_message_domain_direction_sql_6) >3900 
							--or LEN(@esg_summary_message_queue_hour_sql_6) >3900 or LEN(@esg_summary_message_queue_sql_6) > 3900 
							or LEN(@esg_detail_policy_process_results_sql_6) > 3900 or LEN(@esg_summary_logview_message_sql_6) >3900 ))
			begin
				set @errmsg = 'usp_update_views() error : max string limit reached after partition: ' + @partitionname
				        + ', ' + cast(@i as varchar) 
				        + ', ' + cast(len(@esg_detail_incoming_connection_sql_6) as varchar) 
				        + ', ' + cast(len(@esg_detail_message_sql_6) as varchar) 
				        + ', ' + cast(len(@esg_detail_saas_sql_6) as varchar) 
				        + ', ' + cast(len(@esg_summary_incoming_connection_sql_6) as varchar)  
						+ ', ' + cast(len(@esg_detail_delivery_connection_sql_6) as varchar) 
				        + ', ' + cast(len(@esg_detail_delivery_recipient_sql_6) as varchar) 
				        + ', ' + cast(len(@esg_detail_policy_process_results_sql_6) as varchar) 
				        + ', ' + cast(len(@esg_summary_incoming_connection_hour_sql_6) as varchar) 
				        --+ ', ' + cast(len(@esg_detail_dlp_process_results_sql_6) as varchar) 
				        --+ ', ' + cast(len(@esg_summary_saas_hour_sql_6) as varchar) 
				        --+ ', ' + cast(len(@esg_summary_saas_sql_6) as varchar) 
				        + ', ' + cast(len(@esg_summary_delivery_connection_sql_6) as varchar) 
						+ ', ' + cast(len(@esg_summary_delivery_recipient_sql_6) as varchar)
						+ ', ' + cast(len(@esg_summary_process_results_message_total_sql_6) as varchar)
						+ ', ' + cast(len(@esg_summary_process_results_message_direction_sql_6) as varchar) 
						+ ', ' + cast(len(@esg_summary_process_results_message_address_direction_type_sql_6) as varchar) 
						+ ', ' + cast(len(@esg_summary_process_results_virus_sql_6) as varchar)  
						+ ', ' + cast(len(@esg_summary_dlp_process_results_incident_sql_6) as varchar) 
						+ ', ' + cast(len(@esg_summary_dlp_process_results_policy_sql_6) as varchar) 
						+ ', ' + cast(len(@esg_summary_process_results_message_address_sql_6) as varchar) 
						+ ', ' + cast(len(@esg_summary_process_results_message_direction_type_sql_6) as varchar) 
						+ ', ' + cast(len(@esg_summary_process_results_message_action_type_sql_6) as varchar) 
						+ ', ' + cast(len(@esg_summary_process_results_message_domain_direction_type_sql_6) as varchar) 
						+ ', ' + cast(len(@esg_summary_process_results_message_address_direction_sql_6) as varchar) 
						+ ', ' + cast(len(@esg_summary_process_results_message_domain_direction_sql_6) as varchar) 
						+ ', ' + cast(len(@esg_summary_process_results_message_address_direction_action_sql_6) as varchar) 
				goto error_handler;		
				break;
			end
        end

        select top 1 @created_date=created_date, @PartitionName = [db_name]
          from dbo.wse_partitions with (nolock) 
         where deleted = 0 and offline = 0 and created_date < @created_date
         order by created_date desc;
        if @@ROWCOUNT < 1
            set @created_date = null;
    end -- while
		
    -- Create ESG views
    set @DBName = db_name();
    if (@found > 0 )
    begin   
		-- exec create view esg_detail_incoming_connection
        exec (@esg_detail_incoming_connection_sql+@esg_detail_incoming_connection_sql_1+@esg_detail_incoming_connection_sql_2+@esg_detail_incoming_connection_sql_3+@esg_detail_incoming_connection_sql_4+@esg_detail_incoming_connection_sql_5+@esg_detail_incoming_connection_sql_6);
        set @ErrNum = @@ERROR;
        if @ErrNum <> 0
        begin
            set @ErrMsg = N'usp_update_esg_views() error ' + Cast(@ErrNum as varchar(10)) + N' executing dynamic SQL = ' + Left(@esg_detail_incoming_connection_sql, 3800);
            goto Error_Handler;
        end
		-- exec create view esg_detail_message
        exec (@esg_detail_message_sql+@esg_detail_message_sql_1+@esg_detail_message_sql_2+@esg_detail_message_sql_3+@esg_detail_message_sql_4+@esg_detail_message_sql_5+@esg_detail_message_sql_6);
        set @ErrNum = @@ERROR;
        if @ErrNum <> 0
        begin
            set @ErrMsg = N'usp_update_esg_views() error ' + Cast(@ErrNum as varchar(10)) + N' executing dynamic SQL = ' + Left(@esg_detail_message_sql, 3800);
            goto Error_Handler;
        end

		-- exec create view esg_detail_saas
        exec (@esg_detail_saas_sql+@esg_detail_saas_sql_1+@esg_detail_saas_sql_2+@esg_detail_saas_sql_3+@esg_detail_saas_sql_4+@esg_detail_saas_sql_5+@esg_detail_saas_sql_6);
        set @ErrNum = @@ERROR;
        if @ErrNum <> 0
        begin
            set @ErrMsg = N'usp_update_esg_views() error ' + Cast(@ErrNum as varchar(10)) + N' executing dynamic SQL = ' + Left(@esg_detail_saas_sql, 3800);
            goto Error_Handler;
        end
		-- exec create view esg_detail_message_queue
        exec (@esg_detail_message_queue_sql +@esg_detail_message_queue_sql_1+@esg_detail_message_queue_sql_2+@esg_detail_message_queue_sql_3+@esg_detail_message_queue_sql_4+@esg_detail_message_queue_sql_5+@esg_detail_message_queue_sql_6);
        set @ErrNum = @@ERROR;
        if @ErrNum <> 0
        begin
            set @ErrMsg = N'usp_update_esg_views() error ' + Cast(@ErrNum as varchar(10)) + N' executing dynamic SQL = ' + Left(@esg_detail_message_queue_sql, 3800);
            goto Error_Handler;
        end
		-- exec create view esg_detail_delivery_connection
		exec (@esg_detail_delivery_connection_sql+@esg_detail_delivery_connection_sql_1+@esg_detail_delivery_connection_sql_2+@esg_detail_delivery_connection_sql_3+@esg_detail_delivery_connection_sql_4+@esg_detail_delivery_connection_sql_5+@esg_detail_delivery_connection_sql_6);
        set @ErrNum = @@ERROR;
        if @ErrNum <> 0
        begin
            set @ErrMsg = N'usp_update_esg_views() error ' + Cast(@ErrNum as varchar(10)) + N' executing dynamic SQL = ' + Left(@esg_detail_delivery_connection_sql, 3800);
            goto Error_Handler;
        end
		-- exec create view esg_detail_delivery_recipient
		exec (@esg_detail_delivery_recipient_sql+@esg_detail_delivery_recipient_sql_1+@esg_detail_delivery_recipient_sql_2+@esg_detail_delivery_recipient_sql_3+@esg_detail_delivery_recipient_sql_4+@esg_detail_delivery_recipient_sql_5+@esg_detail_delivery_recipient_sql_6);
        set @ErrNum = @@ERROR;
        if @ErrNum <> 0
        begin
            set @ErrMsg = N'usp_update_esg_views() error ' + Cast(@ErrNum as varchar(10)) + N' executing dynamic SQL = ' + Left(@esg_detail_delivery_recipient_sql, 3800);
            goto Error_Handler;
        end
		-- exec create view esg_detail_policy_process_results
		exec (@esg_detail_policy_process_results_sql+@esg_detail_policy_process_results_sql_1+@esg_detail_policy_process_results_sql_2+@esg_detail_policy_process_results_sql_3+@esg_detail_policy_process_results_sql_4+@esg_detail_policy_process_results_sql_5+@esg_detail_policy_process_results_sql_6);
        set @ErrNum = @@ERROR;
        if @ErrNum <> 0
        begin
            set @ErrMsg = N'usp_update_esg_views() error ' + Cast(@ErrNum as varchar(10)) + N' executing dynamic SQL = ' + Left(@esg_detail_policy_process_results_sql, 3800);
            goto Error_Handler;
        end
  --    	-- exec create view esg_detail_dlp_process_results
		--exec (@esg_detail_dlp_process_results_sql+@esg_detail_dlp_process_results_sql_1+@esg_detail_dlp_process_results_sql_2+@esg_detail_dlp_process_results_sql_3+@esg_detail_dlp_process_results_sql_4+@esg_detail_dlp_process_results_sql_5+@esg_detail_dlp_process_results_sql_6);
  --      set @ErrNum = @@ERROR;
  --      if @ErrNum <> 0
  --      begin
  --          set @ErrMsg = N'usp_update_esg_views() error ' + Cast(@ErrNum as varchar(10)) + N' executing dynamic SQL = ' + Left(@esg_detail_dlp_process_results_sql, 3800);
  --          goto Error_Handler;
  --      end
		-- exec create view esg_summary_incoming_connection
        exec (@esg_summary_incoming_connection_sql+@esg_summary_incoming_connection_sql_1+@esg_summary_incoming_connection_sql_2+@esg_summary_incoming_connection_sql_3+@esg_summary_incoming_connection_sql_4+@esg_summary_incoming_connection_sql_5+@esg_summary_incoming_connection_sql_6);
        set @ErrNum = @@ERROR;
        if @ErrNum <> 0
        begin
            set @ErrMsg = N'usp_update_esg_views() error ' + Cast(@ErrNum as varchar(10)) + N' executing dynamic SQL = ' + Left(@esg_summary_incoming_connection_sql, 3800);
            goto Error_Handler;
        end
		-- exec create view esg_summary_incoming_connection_hour
		exec (@esg_summary_incoming_connection_hour_sql+@esg_summary_incoming_connection_hour_sql_1+@esg_summary_incoming_connection_hour_sql_2+@esg_summary_incoming_connection_hour_sql_3+@esg_summary_incoming_connection_hour_sql_4+@esg_summary_incoming_connection_hour_sql_5+@esg_summary_incoming_connection_hour_sql_6);
        set @ErrNum = @@ERROR;
        if @ErrNum <> 0
        begin
            set @ErrMsg = N'usp_update_esg_views() error ' + Cast(@ErrNum as varchar(10)) + N' executing dynamic SQL = ' + Left(@esg_summary_incoming_connection_hour_sql, 3800);
            goto Error_Handler;
        end
		---- exec create view esg_summary_saas_hour
		--exec (@esg_summary_saas_hour_sql+@esg_summary_saas_hour_sql_1+@esg_summary_saas_hour_sql_2+@esg_summary_saas_hour_sql_3+@esg_summary_saas_hour_sql_4+@esg_summary_saas_hour_sql_5+@esg_summary_saas_hour_sql_6);
  --      set @ErrNum = @@ERROR;
  --      if @ErrNum <> 0
  --      begin
  --          set @ErrMsg = N'usp_update_esg_views() error ' + Cast(@ErrNum as varchar(10)) + N' executing dynamic SQL = ' + Left(@esg_summary_saas_hour_sql, 3800);
  --          goto Error_Handler;
  --      end
      		
		---- exec create view esg_summary_saas	
		--exec (@esg_summary_saas_sql+@esg_summary_saas_sql_1+@esg_summary_saas_sql_2+@esg_summary_saas_sql_3+@esg_summary_saas_sql_4+@esg_summary_saas_sql_5+@esg_summary_saas_sql_6);
  --      set @ErrNum = @@ERROR;
  --      if @ErrNum <> 0
  --      begin
  --          set @ErrMsg = N'usp_update_esg_views() error ' + Cast(@ErrNum as varchar(10)) + N' executing dynamic SQL = ' + Left(@esg_summary_saas_sql, 3800);
  --          goto Error_Handler;
  --      end
      			
		---- exec create view esg_summary_message_queue_hour
		--exec (@esg_summary_message_queue_hour_sql+@esg_summary_message_queue_hour_sql_1+@esg_summary_message_queue_hour_sql_2+@esg_summary_message_queue_hour_sql_3+@esg_summary_message_queue_hour_sql_4+@esg_summary_message_queue_hour_sql_5+@esg_summary_message_queue_hour_sql_6);
  --      set @ErrNum = @@ERROR;
  --      if @ErrNum <> 0
  --      begin
  --          set @ErrMsg = N'usp_update_esg_views() error ' + Cast(@ErrNum as varchar(10)) + N' executing dynamic SQL = ' + Left(@esg_summary_message_queue_hour_sql, 3800);
  --          goto Error_Handler;
  --      end
      		
		---- exec create view esg_summary_message_queue	
		--exec (@esg_summary_message_queue_sql+@esg_summary_message_queue_sql_1+@esg_summary_message_queue_sql_2+@esg_summary_message_queue_sql_3+@esg_summary_message_queue_sql_4+@esg_summary_message_queue_sql_5+@esg_summary_message_queue_sql_6);
  --      set @ErrNum = @@ERROR;
  --      if @ErrNum <> 0
  --      begin
  --          set @ErrMsg = N'usp_update_esg_views() error ' + Cast(@ErrNum as varchar(10)) + N' executing dynamic SQL = ' + Left(@esg_summary_message_queue_sql, 3800);
  --          goto Error_Handler;
  --      end
      				  
	    -- exec create view esg_summary_delivery_connection
		exec (@esg_summary_delivery_connection_sql+@esg_summary_delivery_connection_sql_1+@esg_summary_delivery_connection_sql_2+@esg_summary_delivery_connection_sql_3+@esg_summary_delivery_connection_sql_4+@esg_summary_delivery_connection_sql_5+@esg_summary_delivery_connection_sql_6);
        set @ErrNum = @@ERROR;
        if @ErrNum <> 0
        begin
            set @ErrMsg = N'usp_update_esg_views() error ' + Cast(@ErrNum as varchar(10)) + N' executing dynamic SQL = ' + Left(@esg_summary_delivery_connection_sql, 3800);
            goto Error_Handler;
        end

		-- v_esg_summary_connection_total view
		------------------------------------------
		if not exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where Upper(TABLE_NAME) = N'v_esg_summary_connection_total')
        begin
			set @v_esg_summary_connection_total_sql = 
            N'create view dbo.v_esg_summary_connection_total(
				[date_time],[esg_id], [esg_transport_type_id],[hits]			  	
              ) as 
			  select c.date_time, c.esg_id, c.esg_transport_type_id,sum(c.hits) as hits
			  from (  select a.date_time, a.esg_id, a.esg_transport_type_id,a.hits 
					from esg_summary_delivery_connection a with(nolock)
					union all select b.date_time, b.esg_id, b.esg_transport_type_id, b.hits	 
							from esg_summary_incoming_connection b with (nolock)
				) as c 
				group by c.date_time, c.esg_id, c.esg_transport_type_id ';
			exec ( @v_esg_summary_connection_total_sql );
			set @ErrNum = @@ERROR;
			if @ErrNum <> 0
			begin
				set @ErrMsg = N'usp_update_esg_views() error ' + Cast(@ErrNum as varchar(10)) + N' executing dynamic SQL = ' + Left(@v_esg_summary_connection_total_sql, 3800);
				goto Error_Handler;
			end
		end

		-- exec create view esg_summary_delivery_recipient
		exec (@esg_summary_delivery_recipient_sql+@esg_summary_delivery_recipient_sql_1+@esg_summary_delivery_recipient_sql_2+@esg_summary_delivery_recipient_sql_3+@esg_summary_delivery_recipient_sql_4+@esg_summary_delivery_recipient_sql_5+@esg_summary_delivery_recipient_sql_6);
        set @ErrNum = @@ERROR;
        if @ErrNum <> 0
        begin
            set @ErrMsg = N'usp_update_esg_views() error ' + Cast(@ErrNum as varchar(10)) + N' executing dynamic SQL = ' + Left(@esg_summary_delivery_recipient_sql, 3800);
            goto Error_Handler;
        end

		-- exec create view esg_summary_dlp_process_results_incident
		exec (@esg_summary_dlp_process_results_incident_sql+@esg_summary_dlp_process_results_incident_sql_1+@esg_summary_dlp_process_results_incident_sql_2+@esg_summary_dlp_process_results_incident_sql_3+@esg_summary_dlp_process_results_incident_sql_4+@esg_summary_dlp_process_results_incident_sql_5+@esg_summary_dlp_process_results_incident_sql_6);
        set @ErrNum = @@ERROR;
        if @ErrNum <> 0
        begin
            set @ErrMsg = N'usp_update_esg_views() error ' + Cast(@ErrNum as varchar(10)) + N' executing dynamic SQL = ' + Left(@esg_summary_dlp_process_results_incident_sql, 3800);
            goto Error_Handler;
        end
		
		-- exec create view esg_summary_dlp_process_results_policy
		exec (@esg_summary_dlp_process_results_policy_sql+@esg_summary_dlp_process_results_policy_sql_1+@esg_summary_dlp_process_results_policy_sql_2+@esg_summary_dlp_process_results_policy_sql_3+@esg_summary_dlp_process_results_policy_sql_4+@esg_summary_dlp_process_results_policy_sql_5+@esg_summary_dlp_process_results_policy_sql_6);
        set @ErrNum = @@ERROR;
        if @ErrNum <> 0
        begin
            set @ErrMsg = N'usp_update_esg_views() error ' + Cast(@ErrNum as varchar(10)) + N' executing dynamic SQL = ' + Left(@esg_summary_dlp_process_results_policy_sql, 3800);
            goto Error_Handler;
        end
		
		-- exec create view esg_summary_process_results_virus
		exec (@esg_summary_process_results_virus_sql+@esg_summary_process_results_virus_sql_1+@esg_summary_process_results_virus_sql_2+@esg_summary_process_results_virus_sql_3+@esg_summary_process_results_virus_sql_4+@esg_summary_process_results_virus_sql_5+@esg_summary_process_results_virus_sql_6);
        set @ErrNum = @@ERROR;
        if @ErrNum <> 0
        begin
            set @ErrMsg = N'usp_update_esg_views() error ' + Cast(@ErrNum as varchar(10)) + N' executing dynamic SQL = ' + Left(@esg_summary_process_results_virus_sql, 3800);
            goto Error_Handler;
        end
		
		-- exec create view esg_summary_process_results_message_total
		exec (@esg_summary_process_results_message_total_sql+@esg_summary_process_results_message_total_sql_1+@esg_summary_process_results_message_total_sql_2+@esg_summary_process_results_message_total_sql_3+@esg_summary_process_results_message_total_sql_4+@esg_summary_process_results_message_total_sql_5+@esg_summary_process_results_message_total_sql_6);
        set @ErrNum = @@ERROR;
        if @ErrNum <> 0
        begin
            set @ErrMsg = N'usp_update_esg_views() error ' + Cast(@ErrNum as varchar(10)) + N' executing dynamic SQL = ' + Left(@esg_summary_process_results_message_total_sql, 3800);
            goto Error_Handler;
        end


		-- exec create view esg_summary_process_results_message_direction
		exec (@esg_summary_process_results_message_direction_sql+@esg_summary_process_results_message_direction_sql_1+@esg_summary_process_results_message_direction_sql_2+@esg_summary_process_results_message_direction_sql_3+@esg_summary_process_results_message_direction_sql_4+@esg_summary_process_results_message_direction_sql_5+@esg_summary_process_results_message_direction_sql_6);
        set @ErrNum = @@ERROR;
        if @ErrNum <> 0
        begin
            set @ErrMsg = N'usp_update_esg_views() error ' + Cast(@ErrNum as varchar(10)) + N' executing dynamic SQL = ' + Left(@esg_summary_process_results_message_direction_sql, 3800);
            goto Error_Handler;
        end
		
		-- exec create view esg_summary_process_results_message_direction_type
		exec (@esg_summary_process_results_message_direction_type_sql+@esg_summary_process_results_message_direction_type_sql_1+@esg_summary_process_results_message_direction_type_sql_2+@esg_summary_process_results_message_direction_type_sql_3+@esg_summary_process_results_message_direction_type_sql_4+@esg_summary_process_results_message_direction_type_sql_5+@esg_summary_process_results_message_direction_type_sql_6);
        set @ErrNum = @@ERROR;
        if @ErrNum <> 0
        begin
            set @ErrMsg = N'usp_update_esg_views() error ' + Cast(@ErrNum as varchar(10)) + N' executing dynamic SQL = ' + Left(@esg_summary_process_results_message_direction_type_sql, 3800);
            goto Error_Handler;
        end

		-- exec create view esg_summary_process_results_message_action_type
		exec (@esg_summary_process_results_message_action_type_sql+@esg_summary_process_results_message_action_type_sql_1+@esg_summary_process_results_message_action_type_sql_2+@esg_summary_process_results_message_action_type_sql_3+@esg_summary_process_results_message_action_type_sql_4+@esg_summary_process_results_message_action_type_sql_5+@esg_summary_process_results_message_action_type_sql_6);
        set @ErrNum = @@ERROR;
        if @ErrNum <> 0
        begin
            set @ErrMsg = N'usp_update_esg_views() error ' + Cast(@ErrNum as varchar(10)) + N' executing dynamic SQL = ' + Left(@esg_summary_process_results_message_action_type_sql, 3800);
            goto Error_Handler;
        end

		-- exec create view esg_summary_process_results_message_address
		exec (@esg_summary_process_results_message_address_sql+@esg_summary_process_results_message_address_sql_1+@esg_summary_process_results_message_address_sql_2+@esg_summary_process_results_message_address_sql_3+@esg_summary_process_results_message_address_sql_4+@esg_summary_process_results_message_address_sql_5+@esg_summary_process_results_message_address_sql_6);
        set @ErrNum = @@ERROR;
        if @ErrNum <> 0
        begin
            set @ErrMsg = N'usp_update_esg_views() error ' + Cast(@ErrNum as varchar(10)) + N' executing dynamic SQL = ' + Left(@esg_summary_process_results_message_address_sql, 3800);
            goto Error_Handler;
        end

		-- exec create view esg_summary_process_results_message_address_direction
		exec (@esg_summary_process_results_message_address_direction_sql+@esg_summary_process_results_message_address_direction_sql_1+@esg_summary_process_results_message_address_direction_sql_2+@esg_summary_process_results_message_address_direction_sql_3+@esg_summary_process_results_message_address_direction_sql_4+@esg_summary_process_results_message_address_direction_sql_5+@esg_summary_process_results_message_address_direction_sql_6);
        set @ErrNum = @@ERROR;
        if @ErrNum <> 0
        begin
            set @ErrMsg = N'usp_update_esg_views() error ' + Cast(@ErrNum as varchar(10)) + N' executing dynamic SQL = ' + Left(@esg_summary_process_results_message_address_direction_sql, 3800);
            goto Error_Handler;
        end

		-- exec create view esg_summary_process_results_message_address_direction_type
		exec (@esg_summary_process_results_message_address_direction_type_sql+@esg_summary_process_results_message_address_direction_type_sql_1+@esg_summary_process_results_message_address_direction_type_sql_2+@esg_summary_process_results_message_address_direction_type_sql_3+@esg_summary_process_results_message_address_direction_type_sql_4+@esg_summary_process_results_message_address_direction_type_sql_5+@esg_summary_process_results_message_address_direction_type_sql_6);
        set @ErrNum = @@ERROR;
        if @ErrNum <> 0
        begin
            set @ErrMsg = N'usp_update_esg_views() error ' + Cast(@ErrNum as varchar(10)) + N' executing dynamic SQL = ' + Left(@esg_summary_process_results_message_address_direction_type_sql, 3800);
            goto Error_Handler;
        end

		-- exec create view esg_summary_process_results_message_address_direction_action
		exec (@esg_summary_process_results_message_address_direction_action_sql+@esg_summary_process_results_message_address_direction_action_sql_1+@esg_summary_process_results_message_address_direction_action_sql_2+@esg_summary_process_results_message_address_direction_action_sql_3+@esg_summary_process_results_message_address_direction_action_sql_4+@esg_summary_process_results_message_address_direction_action_sql_5+@esg_summary_process_results_message_address_direction_action_sql_6);
        set @ErrNum = @@ERROR;
        if @ErrNum <> 0
        begin
            set @ErrMsg = N'usp_update_esg_views() error ' + Cast(@ErrNum as varchar(10)) + N' executing dynamic SQL = ' + Left(@esg_summary_process_results_message_address_direction_action_sql, 3800);
            goto Error_Handler;
        end

		---- exec create view esg_summary_process_results_message_domain_direction_type
		exec (@esg_summary_process_results_message_domain_direction_type_sql+@esg_summary_process_results_message_domain_direction_type_sql_1+@esg_summary_process_results_message_domain_direction_type_sql_2+@esg_summary_process_results_message_domain_direction_type_sql_3+@esg_summary_process_results_message_domain_direction_type_sql_4+@esg_summary_process_results_message_domain_direction_type_sql_5+@esg_summary_process_results_message_domain_direction_type_sql_6);
        set @ErrNum = @@ERROR;
        if @ErrNum <> 0
        begin
            set @ErrMsg = N'usp_update_esg_views() error ' + Cast(@ErrNum as varchar(10)) + N' executing dynamic SQL = ' + Left(@esg_summary_process_results_message_domain_direction_type_sql, 3800);
            goto Error_Handler;
        end

		--		-- exec create view esg_summary_process_results_message_domain_direction
		exec (@esg_summary_process_results_message_domain_direction_sql+@esg_summary_process_results_message_domain_direction_sql_1+@esg_summary_process_results_message_domain_direction_sql_2+@esg_summary_process_results_message_domain_direction_sql_3+@esg_summary_process_results_message_domain_direction_sql_4+@esg_summary_process_results_message_domain_direction_sql_5+@esg_summary_process_results_message_domain_direction_sql_6);
        set @ErrNum = @@ERROR;
        if @ErrNum <> 0
        begin
            set @ErrMsg = N'usp_update_esg_views() error ' + Cast(@ErrNum as varchar(10)) + N' executing dynamic SQL = ' + Left(@esg_summary_process_results_message_domain_direction_sql, 3800);
            goto Error_Handler;
        end
		
		-- exec create view esg_detail_dlp_policy
		exec (@esg_detail_dlp_policy_sql + @esg_detail_dlp_policy_sql_1 + @esg_detail_dlp_policy_sql_2 + @esg_detail_dlp_policy_sql_3 + @esg_detail_dlp_policy_sql_4 + @esg_detail_dlp_policy_sql_5 + @esg_detail_dlp_policy_sql_6);
        set @ErrNum = @@ERROR;
        if @ErrNum <> 0
        begin
            set @ErrMsg = N'usp_update_esg_views() error ' + Cast(@ErrNum as varchar(10)) + N' executing dynamic SQL = ' + Left(@esg_detail_dlp_policy_sql, 3800);
            goto Error_Handler;
        end
	
		-- exec create view esg_summary_logview_message
		exec (@esg_summary_logview_message_sql + @esg_summary_logview_message_sql_1 + @esg_summary_logview_message_sql_2 + @esg_summary_logview_message_sql_3 + @esg_summary_logview_message_sql_4 + @esg_summary_logview_message_sql_5 + @esg_summary_logview_message_sql_6);
        set @ErrNum = @@ERROR;
        if @ErrNum <> 0
        begin
            set @ErrMsg = N'usp_update_esg_views() error ' + Cast(@ErrNum as varchar(10)) + N' executing dynamic SQL = ' + Left(@esg_summary_logview_message_sql, 3800);
            goto Error_Handler;
        end
	
	end --     if (@found > 0)

	exec dbo.usp_update_esg_quarantine_views @debug;

    if @debug = 1 print N'==>Exit usp_update_esg_views()';
    return 0;

    -----------------------------------
    -- Error Logging and Return Value
    -----------------------------------
Error_Handler:
    exec dbo.usp_log N'error', @ErrMsg;
    RAISERROR(@ErrMsg, 16, 1);

end  -- usp_update_esg_views
go