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 deal with a lot more data connectivity issues with the gateway service I am guessing). Where is this function in the on-premise version? As it is important on-premise as it is on cloud service.

The problem is the call to the subscription succeeds though the subscription itself fails. Which is, as you say, a bit rubbish! You can add a second SQL Step to each SQLAgentJob and basically force a failure using some code like the stuff below. You can then just add your "operators" to the Notifications for the SQL Agent job and set the first step up to call the second on success and both steps to Fail the job! This will generate a SQL Agent ALERT to the operators! You could of course log the details of the query into a table somewhere before you call RAISEERROR so you would have the details of what had failed. Figuring out why the **bleep** thing failed is another matter entirely. DECLARE @SQLAgentJobID UNIQUEIDENTIFIER; SET @SQLAgentJobID = CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID))); IF EXISTS( SELECT sj.name AS SQLAgentJobName ,c.Name AS ReportName ,c.[Path] AS ReportPath ,s.[Description] AS SubscriptionName ,rs.SubscriptionID ,s.LastStatus ,s.EventType ,s.LastRunTime ,sj.date_created ,sj.date_modified FROM ReportServer.dbo.ReportSchedule rs INNER JOIN msdb.dbo.sysjobs sj ON rs.ScheduleID = CAST(sj.name AS UNIQUEIDENTIFIER) AND 101 = sj.category_id --INNER JOIN ReportServer.dbo.ReportSchedule c -- ON CAST(sj.name AS uniqueidentifier) = c.ScheduleID INNER JOIN ReportServer.dbo.Subscriptions s ON rs.SubscriptionID = s.SubscriptionID INNER JOIN ReportServer.dbo.[Catalog] c ON s.Report_OID = c.ItemID -- to find specific report last status -- WHERE e.name = 'Usage Stats' -- to find failed status WHERE LastStatus <> 'Completed Data Refresh' -- for a specific SQL Agent Job AND sj.name = @SQLAgentJobID) BEGIN --LOG the query results above to something if you want a simple way of finding out what has failed RAISERROR('Data Refresh Failed!',16,1); END -- NOTE you have to look in subscription history for any error messages/details (good luck with that!)PS Have I mentioned how shoddy and third rate the logging and monitoring is for PBI SSRS Server?

WHich one of the 150 SQL Agent jobs do I do that on. They are just labeled as a GUID. This doesn't seem like a good solution. Microsoft needs to fix this if there is no other way. Why don't they just add a task at the end of these SQL Agent jobs for the server administrator if it is setup in the Power BI Server backend? Seems like basic functionality is missing here.

In Power BI report Server, every schedule you create goes to SQL Server Agent JOB, so you can go to the SQL Server Agent JOB and then you can add one more step after the current step for sending the email. You might need to configure email in SQL in order to send the email through JOB.

FYI, I found the SQL AGent job (labeled as GUID 87B024E7-A122-4DE9-818A-8016826EB9E3) in SQL Agent. It shows as successful this morning. But the data refresh shows error on Power BI front end. So it appears SQL Agent failures do NOT occur when the data refresh fails.

Scheduled Refresh notifications (On-Premise)

12 Replies

agent_007

Advocate 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';

Anonymous
Not applicable
8 years ago
In Power BI report Server, every schedule you create goes to SQL Server Agent JOB, so you can go to the SQL Server Agent JOB and then you can add one more step after the current step for sending the email. You might need to configure email in SQL in order to send the email through JOB.
- xantari
  Helper II
  8 years ago
  WHich one of the 150 SQL Agent jobs do I do that on. They are just labeled as a GUID.
  
  This doesn't seem like a good solution.
  
  Microsoft needs to fix this if there is no other way. Why don't they just add a task at the end of these SQL Agent jobs for the server administrator if it is setup in the Power BI Server backend?
  
  Seems like basic functionality is missing here.
  - xantari
    Helper II
    8 years ago
    FYI, I found the SQL AGent job (labeled as GUID 87B024E7-A122-4DE9-818A-8016826EB9E3) in SQL Agent.
    
    It shows as successful this morning. But the data refresh shows error on Power BI front end.
    
    So it appears SQL Agent failures do NOT occur when the data refresh fails.

Forum Discussion

Scheduled Refresh notifications (On-Premise)

12 Replies

Related content

Mirroring from on-premise PostgreSQL and MySQL sources

Email notification on Scheduled job failure

On-premises data gateways

Schedule Failure Notification in Notebook and Data pipeline

Power BI Service on-premises gateway