Share via

SQL : Send email via DB mail based on the row count mismatch.

kkran 831 Reputation points
2023-09-20T15:08:40.13+00:00

Hi All - I have written a stored procedure and it sends out an email to users in the below format. Its basically the row count between three systems ( Oracle, STAGE, SDS). Currently, the email is sent every hour, but the user wants to get an email only if there is row count mismatch for each category.

Report Run: 2023-09-19 14:08:38

Description Oracle_Count STAGE_Count SDS_Count MOST_RECENT_LAST_UPDATED_DATE
S Count 254815 254815 254815 2023-09-01T13:16:37
L Count 5305 5305 5305 2023-08-18T07:40:59
FC Count 975 975 978 2023-09-15T11:00:00
Pro Count 127933 127937 127937 2023-08-31T12:05:04

For example , the 'FC Count' doesn't match between STAGE and SDS and 'Pro Count' doesn't match between Oracle and STAGE/SDS, so then only user should get an email.

Attached is my current script which generates the table/email above:

Could you please help on how to modify the script below so it sends an email only when row count is mismatched.

DECLARE @BODY NVARCHAR(MAX)
		, @SUBJECT VARCHAR(MAX) = 'Counts - ' + CONVERT(VARCHAR(20),GETDATE(),120) 
		, @RECIPIENTS VARCHAR(MAX) = 'Testemail@email.com'
		, @CNT INT

DECLARE @LIST TABLE (
Description varchar (20)not null,
Oracle_Count varchar (max) not null,
STAGE_Count varchar(50) not null,
SDS_Count varchar (50) not null,
MOST_RECENT_LAST_UPDATED_DATE DATETIME NOT NULL)
	
INSERT INTO @LIST 
Select 'TestSR Count' as Description,COUNT(DISTINCT Oracle.number) as Oracle_S,
COUNT(DISTINCT STAGE.number) as STAGE_S, 
COUNT(DISTINCT SDS.NUMBER) as SDS_S, MAX(SDS.LAST_UPDATED_DT)
from dw.SDS SDS  LEFT JOIN OPENQUERY ( ORACLE, 'Select number from CS')  as Oracle  on SDS.NUMBER = Oracle.number
LEFT JOIN [Stage].dw.CS  STAGE on STAGE.NUMBER = Oracle.number
WHERE SDS.ID IS NULL

UNION

Select distinct 'TestIns Count' as Description,   COUNT(DISTINCT Oracle.SERIAL) as Oracle_Number
,COUNT(DISTINCT STAGE.SERIAL) as STAGE_Number
,COUNT(DISTINCT SDS.SERIAL) as SDS_Serial, 
MAX(SDS.LAST_UPDATED_DT) as MOST_RECENT_LASER_LAST_UPDATED_DATE
 from DW.sdsr SDS
LEFT JOIN OPENQUERY ( ORACLE, 'Select serial_number from ITEM where  INSTANCE_TYPE_CODE = ''TestIns''') Oracle on Oracle.SERIAL_NUMBER = SDS.SERIAL_NUMBER
LEFT JOIN STAGE.dw.ITEM STAGE on STAGE.SERIAL = Oracle.Serial
WHERE SDS.ORACLE_ID IS NOT NULL

UNION

  Select 'TestFc Count' as Description, COUNT(DISTINCT Oracle.FCO_Number) as Oracle_FC, 
  COUNT(DISTINCT STAGE.FC) as STAGE_FC, 
  COUNT(DISTINCT SDS.FC) as SDS_FC, 
  MAX(SDS.LAST_UPDATED_DT) as MOST_RECENT_FC_LAST_UPDATED_DATE  
  FROM DW.FC SDS 
 LEFT JOIN  OPENQUERY ( ORACLE, 'Select * from cy_fc where FC iS NOT NULL') Oracle ON Oracle.FC = SDS.FC
 LEFT JOIN STAGE.dw.CY_FC STAGE on STAGE.FC = ORACLE.FC 
 WHERE 1=1
 and SDS.CO_ID IS  NULL

UNION
 
Select distinct 'TestPro Count' as Description,
 COUNT(DISTINCT Oracle.SEGMENT1) as Oracle_ProCount, 
 COUNT(DISTINCT STAGE.SEGMENT1) as STAGE_ProCount, 
COUNT(DISTINCT SDS.PRODUCT) as SDS_ProCount , 
MAX(SDS.LAST_UPDATED_DT) as MOST_RECENT_LAST_UPDATED_PRODUCT 
FROM [dw].[PRO] SDS
LEFT JOIN [Stage].dw.MTL STAGE ON STAGE.SEGMENT1 = SDS.PRODUCT
LEFT JOIN OPENQUERY ( ORACLE, 'Select SEGMENT1 from MTL') Oracle on Oracle.SEGMENT1 = STAGE.SEGMENT1
where 1=1

SET @CNT = ISNULL((SELECT COUNT(*) FROM @LIST),0)

IF @CNT > 0 
	BEGIN
		SET @BODY ='<HTML><TITLE>SDS:  PROCESS Notification Error </TITLE><BODY>
				<B>Report Run:</B> ' + CONVERT(VARCHAR(20),GETDATE(),120) 
				+ '<BR><BR>
				<TABLE BORDER="1">
				<TR><TH>Description</TH><TH>Oracle_Count</TH><TH>STAGE_Count</TH><TH>SDS_Count</TH><TH>MOST_RECENT_LAST_UPDATED_DATE</TR>'
				+	CAST(ISNULL((SELECT Description 'TD','', Oracle_Count 'TD','', STAGE_Count 'TD','', SDS_Count 'TD','', MOST_RECENT_LAST_UPDATED_DATE 'TD' 
			
								FROM @LIST
								FOR XML PATH('TR'), ELEMENTS),'<TH>No Errors</TH><TH> </TH><TH> </TH><TH> </TH><TH> </TH><TH> </TH><TH> </TH><TH> </TH><TH> </TH>')  AS NVARCHAR(MAX))
				+ '</TABLE></BODY></HTML>'

		EXEC MSDB..SP_SEND_DBMAIL
		  @PROFILE_NAME = 'SERVER',
		  @RECIPIENTS = @RECIPIENTS,
		  @SUBJECT = @SUBJECT,
		  @BODY_FORMAT = 'HTML',
		  @BODY = @BODY
	end
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,947 questions
0 comments
Accepted answer
  1. Yitzhak Khabinsky 25,956 Reputation points
    2023-09-20T18:35:46.0033333+00:00

    Hi @kkran,

    You are sending an email in the (X)HTML format. That's why it would be better if you will switch @BODY variable from NVARCHAR(MAX) to XML data type.

    It will allow you to use XQuery to interrogate XML and check for any discrepancies across environments.

    Please edit your question, and add @BODY variable as XML, i.e. its value as XML. And I will show you how to do it.

    XQuery provides the following benefits:

    • Allows to compose a proper well-formed (X)HTML "visually", instead of error prone string concatenation.
    • Allows to calculate any discrepancy for three columns in question: Oracle, STAGE, and SDS.

    Because a minimal reproducible example is not provided, I had to simulate your data setup.

    DECLARE @LIST TABLE (
	Description varchar (20)not null,
	Oracle_Count varchar (max) not null,
	STAGE_Count varchar(50) not null,
	SDS_Count varchar (50) not null,
	MOST_RECENT_LAST_UPDATED_DATE DATETIME NOT NULL
);
INSERT  @LIST
(
    Description,
    Oracle_Count,
    STAGE_Count,
    SDS_Count,
    MOST_RECENT_LAST_UPDATED_DATE
)
VALUES
('S Count',		'254815',	'254815',	'254815',	'2023-09-01T13:16:37'),
('L Count',		'5305',		'5305',		'5305',		'2023-08-18T07:40:59'),
('FC Count',	'975',		'975',		'978',		'2023-09-15T11:00:00'),
('Pro COUNT',	'127933',	'127937',	'127937',	'2023-08-31T12:05:04');

SELECT * FROM @LIST;

DECLARE @BODY NVARCHAR(MAX)
	, @xhtmlBody XML
	, @SUBJECT VARCHAR(MAX) = 'Counts - ' + CONVERT(VARCHAR(20),GETDATE(),120) 
	, @RECIPIENTS VARCHAR(MAX) = 'Testemail@email.com'
	, @ReportRun VARCHAR(20) = CONVERT(VARCHAR(20),GETDATE(),120) 
	, @CNT INT
	, @UNIQUE_COUNTER INT;

SET @CNT = ISNULL((SELECT COUNT(*) FROM @LIST),0);

SET @xhtmlBody = (
SELECT (
SELECT *
FROM @LIST FOR XML PATH('row'), TYPE, ROOT('root'))
.query('<html>
			<head>
            <meta charset="utf-8"/>
            (: including embedded CSS styling :)
            <style>
				table <![CDATA[ {border-collapse: collapse;  width: 300px;} ]]>
				th <![CDATA[ {background-color: #4CAF50; color: white;} ]]>
				th, td <![CDATA[ { text-align: left; padding: 8px;} ]]>
				tr:nth-child(even) <![CDATA[ {background-color: #f2f2f2;} ]]>
				#red <![CDATA[ {background-color: LightPink;} ]]>
			</style>
         </head>
	<title>SDS: PROCESS Notification Error </title>
	<body>
		<b>Report Run: {sql:variable("@ReportRun")}</b> 
		<p/>
		<table border="1">
		<thead>
			<tr>
				<th>Description</th>
				<th>Oracle_Count</th>
				<th>STAGE_Count</th>
				<th>SDS_Count</th>
				<th>MOST_RECENT_LAST_UPDATED_DATE</th>
			</tr>
		</thead>
		<tbody>
		{
		for $row in /root/row
		return <tr>
				{if ((count(distinct-values($row/*[position()=(2,3,4)])))[1] gt 1) then attribute id {"red"} else ()}
				<td>{data($row/Description)}</td>
				<td>{data($row/Oracle_Count)}</td>
				<td>{data($row/STAGE_Count)}</td>
				<td>{data($row/SDS_Count)}</td>
				<td>{data($row/MOST_RECENT_LAST_UPDATED_DATE)}</td>
			</tr>
		}
		</tbody>
</table></body></html>'));

-- just to see
SELECT @xhtmlBody;

SET @UNIQUE_COUNTER =  @xhtmlBody.query('
	for $row in /html/body/table/tbody/tr
	return <r>{count(distinct-values($row/td[position()=(2,3,4)]))}</r>
').value('sum(data(/r))', 'INT');
-- just to see
SELECT @UNIQUE_COUNTER;

SET @BODY = TRY_CAST(@xhtmlBody AS NVARCHAR(MAX));

-- discepancy found
IF @UNIQUE_COUNTER <> @CNT BEGIN
	EXEC MSDB..SP_SEND_DBMAIL
			  @PROFILE_NAME = 'SERVER',
			  @RECIPIENTS = @RECIPIENTS,
			  @SUBJECT = @SUBJECT,
			  @BODY_FORMAT = 'HTML',
			  @BODY = @BODY;
END;
    2 people found this answer helpful.

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.