Forum Discussion

xantari's avatar
xantari
Icon for Helper II rankHelper II
8 years ago

Scheduled Refresh notifications (On-Premise)

We can not find a way to get Refresh Notification failure emails when a scheduled data refresh occurs on-premise.   Apparently this has existed for a long time on Power BI (because they have to dea...
Power BI Report Server
agent_007's avatar
agent_007
Icon for Advocate I rankAdvocate I
8 years ago

This is my solution as second step in SQL Agent. Preliminarily you should configure MS SQL MAIL and find out GUID of your Catalog.ItemID = '????'. This step sends notification by mail after successfully refreash data (1 step) to all users of this report

 

DECLARE	
	@xml  XML,
	@str varchar(8000) = ''
SELECT @xml = CAST(XmlDescription as XML)
FROM Catalog WITH (NOLOCK)
INNER JOIN Policies WITH (NOLOCK) ON Catalog.PolicyID = Policies.PolicyID
LEFT OUTER JOIN SecData WITH (NOLOCK) ON Policies.PolicyID = SecData.PolicyID AND AuthType = 1
WHERE Catalog.ItemID = 'E3280AC8-EFF4-4AF6-8ED3-583C6112139A' 
AND PolicyFlag = 0
declare
	@t table (mail_to varchar(100))
SET QUOTED_IDENTIFIER ON;
insert 
	@t
SELECT	xmlData.A.value('.', 'VARCHAR(100)') AS mail
FROM	@xml.nodes('Policies/Policy/GroupUserName') xmlData(A)
delete @t
where mail_to = 'BUILTIN\Administrators'
update @t
set mail_to = REPLACE(REPLACE(mail_to, 'KCELL.KZ\',''), '@kcell.kz','') + '@kcell.kz'

SELECT @str = @str + ISNULL(t.mail_to + ';', '') FROM @t t

SET @str = SUBSTRING(@str, 1, LEN(@str) - 1)

WAITFOR DELAY '00:10:00'; 

DECLARE @body NVARCHAR(MAX)
SET @body = '
<html>
	<body>
		<H2>Daily RSSS-OU-CCHT statistics</H2>
		<H3>Data were updated successfully!</H3>
		<p><a href="http://192.168.223.13/Reports/powerbi/CCD%20reports/Daily%20RSSS-OU-CCHT%20statistics">Open report</a></p>
	</body>
</html>'

EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'power_bi_report_server',
    @recipients = @str,
    @copy_recipients = '[email protected]',
    @body = @body,
    @subject = 'Power BI Report Server Alert',
    @body_format = 'HTML';