This Stored Proc will check all satellite tables (prefix s_)  and see if they have any zero hub keys (prefix h_).

This should not happen if the order of the jobs is correct when building the data vault.

The code makes use of custom Red parameters, in this case we named it email_to_addr_on_updates. Enter the emails you want the procedure to contact after it runs.

 

CREATE PROCEDURE c_dm_row_check_email
  @p_sequence         integer
, @p_job_name         varchar(256)
, @p_task_name        varchar(256)
, @p_job_id           integer
, @p_task_id          integer
, @p_return_msg       varchar(256) OUTPUT
, @p_status           integer      OUTPUT
AS
  SET XACT_ABORT OFF  -- Turn off auto abort on errors

  SET NOCOUNT ON      -- Turn off row count messages

  --===============================================================
  -- Control variables used in most programs
  --===============================================================
  DECLARE
    @v_msgtext         varchar(256) -- Text for audit_trail
  , @v_step            integer      -- step number
  , @v_return_status   integer      -- Query result status
  , @v_db_code         varchar(10)  -- Database error code
  , @v_db_msg          varchar(100) -- Database error message

  DECLARE
    @v_name_of_job_to_release VARCHAR(256)
  , @v_Run_next_job varchar(10)
  , @v_return_code VARCHAR(1)
  , @v_return_msg VARCHAR(256)
  , @v_job_name VARCHAR(256)
  , @v_job_name_temp VARCHAR(256)
  , @v_result INT
  , @email_to     VARCHAR(512)

  --===============================================================
  -- Main
  --===============================================================
  BEGIN TRY

    SELECT  @email_to = dss_parameter_value
    FROM dss_parameter WHERE dss_parameter_name = 'email_to_addr_on_updates'

    SET @v_job_name = substring(@p_job_name,9,3)


    --SET @v_job_name_temp = 'edw_300_fssacr_dim'
    --SET @v_job_name = substring(@v_job_name_temp,9,3)

	--select @v_job_name



	DECLARE @bodyMsg nvarchar(max)
	DECLARE @subject nvarchar(max)
	DECLARE @tableHTML nvarchar(max)
	DECLARE @Table NVARCHAR(MAX) = N''

	SET @subject = 'Data Mart Check for ' + upper(@v_job_name)

	SELECT @Table = @Table +'<tr style ="font-size: 12px;font-weight:normal;">' +
	case when [rowcounts]=0 then '<td bgcolor="yellow">' else '<td>' end + [tablename]+ '</td>' +
	case when [rowcounts]=0 then '<td bgcolor="yellow">' else '<td>' end + cast([rowcounts] as nvarchar(30))+ '</td>' +
	'</tr>'
	FROM(SELECT
		t.NAME as tablename,
		p.rows as rowcounts,
		getdate() as countdate
	FROM
		sys.tables t
	INNER JOIN
		sys.schemas s ON t.schema_id = s.schema_id
	INNER JOIN
		sys.indexes i ON t.OBJECT_ID = i.object_id
	INNER JOIN
		sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
	WHERE t.is_ms_shipped = 0
	AND (left(t.NAME,6) = 'ft_'+ @v_job_name or left(t.NAME,7) = 'dim_'[email protected]_job_name)
	AND substring(t.NAME,len(t.NAME)-3,4) <> '_old'
	GROUP BY
		p.Rows, t.NAME, s.Name)dataset

	SET @tableHTML =
	N'<H3><font color="Black">Row Counts</H3>' +
	N'<table border="1" align="center" cellpadding="2" cellspacing="0" style="color:black;font-family:arial,helvetica,sans-serif;text-align:left;" >' +
	N'<tr style ="font-size: 12px;font-weight: normal;background: #61c6c4;">
	<th>Table</th>
	<th>Rowcount</th>
	</tr>' + @Table + N'</table>'

	--select @tableHTML

	EXEC msdb.dbo.sp_send_dbmail @recipients = @email_to,
		@subject = @subject,
		@body = @tableHTML,
		@body_format = 'HTML' ;

	--EXEC msdb.dbo.sp_send_dbmail @recipients='[email protected]',


      SET @v_step = 300;
      SET @p_return_msg = 'c_dm_row_check_email completed.';
      SET @p_status = 1;

    END TRY
  BEGIN CATCH
    SET @v_DB_code = CAST(/**/ERROR_NUMBER() AS VARCHAR);
    SET @v_DB_msg = /**/ERROR_MESSAGE();
    SET @p_status =/**/-3;
    SET @p_return_msg = LEFT(
                          'c_dm_row_check_email FAILED' +
                        '. Step num.: ' + CAST(@v_step AS VARCHAR) +
                        '. Err. num.: ' + @v_DB_code +
                        '. Err. msg.: ''' + @v_DB_msg + '''',
                          256
                        );
    EXEC @v_return_status = WSWrkError
                              'E',
                              @p_job_name,
                              @p_task_name,
                              @p_sequence,
                              @p_return_msg,
                              @v_DB_code,
                              @v_DB_msg,
                              @p_task_ID,
                              @p_job_ID,
                              NULL;
  END CATCH;

  RETURN 0

 

Share This Via: