Native Database Query Error

[mw123]

Hello-

I am getting an error when refreshing a table in PowerBI stating the following:

"This native database query isn't currently supported".

I have already unchecked the checkbox under Options-->Security--> "Require user approval for new native database queries".  I still get the same error with this unchecked. 

Does anyone know what might be still causing this? 

Thanks!

[Anonymous]

just click retry, you'll get a prompt. shikina

[Anonymous]

wowww!

[GilbertQ]

Hi there

What is your Source database? I have queries running from SQL Server and Oracle in the past with no issues.

[mw123]

SQL Server is the source. It ran successfully for multiple days but now the error is occuring. 

[zoloturu]

Hi @mw123,

Let me know if you receive this error in Power BI Service/Desktop/both?

Can you give a screenshot of your SQL query? Or provide a list of SQL commands you use (SELECT, CAST, EXEC etc.)

[mw123]

It is occurring in Power BI Desktop. I am trying to refresh a stored procedure that is being executed in PowerBI. It runs fine in SQL Server.

The procedure looks something like this:

declare @d date
set @d=(select max(added) from table1)

set @d=dateadd(day,7,@d)

if getdate()>=@d
begin
drop table table1

select
field1,
@d as added,
field3,
field4
into
table1
from
table2
where substring(field2,1,1)='4'

update a
set a.field=date_convert(b.field)
from table1 as a,
table3 as b where
a.field1=b.field1

[zoloturu]

@mw123,

You need to add SELECT step there because it should return dataset. I see only SELECT INTO and UPDATE and some other steps but nothing about SELECT FROM.

[mw123]

I don't actual want a set of data returned, I am just updating that table. The problem is the update fails with that error being returned.

[zoloturu · Accepted solution]

@mw123,

Could you just add simple SELECT 1 FROM some_table after UPDATE statement in Native Query? 

[Anonymous]

Hi @zoloturu,

  I'm facing the same issue, while using the direct query method with the below code.

DECLARE @SD DATETIME,@ED DATETIME, @StartDate DATETIME, @EndDate DATETIME ,@DateRange Nvarchar(max)
select @SD =
CASE WHEN @DateRange='Last 24 Hours' THEN CONVERT(DATE,DATEADD(hh,-24, GETDATE()))
WHEN @DateRange='Last 7 Days' THEN CONVERT(DATE,DATEADD(d,-7, GETDATE()))
WHEN @DateRange='Last 30 Days' THEN CONVERT(DATE,DATEADD(d,-30, GETDATE()))
WHEN @DateRange='Last 60 Days' THEN CONVERT(DATE,DATEADD(d,-60, GETDATE()))
WHEN @DateRange='Last 90 Days' THEN CONVERT(DATE,DATEADD(d,-90, GETDATE()))
WHEN @DateRange='Yesterday' THEN CONVERT(DATE,DATEADD(d,-1,GETDATE()))
WHEN @DateRange='This Week' THEN CONVERT(DATE,DATEADD(wk,DATEDIFF(wk,0,GETDATE()),-1))
WHEN @DateRange='Last Week' THEN CONVERT(DATE,DATEADD(wk,DATEDIFF(wk,7,GETDATE()),-1))
WHEN @DateRange='Fortnight' THEN CONVERT(DATE,DATEADD(d,-14,GETDATE()))
WHEN @DateRange='This Month' THEN CONVERT(DATE,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))
WHEN @DateRange='Last Month' THEN CONVERT(DATE,DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0))
ELSE @StartDate END

SET @ED =
CASE WHEN @DateRange IN ('Last 24 Hours','Last 7 Days','Last 30 Days','Last 60 Days','Last 90 Days') THEN CONCAT(CONVERT(DATE,GETDATE()),' 23:59:59')
WHEN @DateRange='Yesterday' THEN CONCAT(CONVERT(DATE,DATEADD(d,-1,GETDATE())),' 23:59:59')
WHEN @DateRange='This Week' THEN IIF(CONVERT(DATE,GETDATE())=CONVERT(DATE,DATEADD(wk,DATEDIFF(wk,0,GETDATE()),-1)),CONVERT(datetime,DATEADD(d,0,GETDATE())),CONCAT(CONVERT(DATE,DATEADD(d,-1,GETDATE())),' 23:59:59'))
WHEN @DateRange='Last Week' THEN CONCAT(CONVERT(DATE,DATEADD(wk,DATEDIFF(wk,7,GETDATE()),5)),' 23:59:59')
WHEN @DateRange='Fortnight' THEN CONCAT(CONVERT(DATE,DATEADD(d,-1,GETDATE())),' 23:59:59')
WHEN @DateRange='This Month' THEN IIF(CONVERT(DATE,GETDATE())=CONVERT(DATE,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)),CONVERT(datetime,DATEADD(d,0,GETDATE())),CONCAT(CONVERT(date,DATEADD(d,-1,GETDATE())),' 23:59:59'))
WHEN @DateRange='Last Month' THEN CONCAT(CONVERT(DATE,DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()),-1)),' 23:59:59')
ELSE @EndDate END

SELECT COUNT(DISTINCT ALARMSID) AS [Unique Alarm Count],COUNT(*) AS [Alarm Count],SUM(CASE WHEN AHF.ACKTIME IS NULL THEN 1 ELSE 0 END) AS [Never Acknowledged],
SUM(CASE WHEN AHF.ENDTIME IS NULL THEN 1 ELSE 0 END) AS [Open Alarms],
SUM(CASE WHEN AHF.ENDTOSTATE=0 THEN 1 ELSE 0 END) AS [Return To Normal]FROM ALARMHISTORYFACT AHF
INNER JOIN ALARMCONFIGDIM AC ON AC.SID=AHF.ALARMSID
WHERE AHF.Begintime>=@SD AND AHF.BeginTime<=@ED AND AC.SID IN (@SID)

I'm getting this error: This native database query isn't currently supported. 

Thanks in Advance.

[GilbertQ]

I agree, Power BI always expects some data to be returned from a data source.

So even if you had to PRINT something at the end which Power BI could view would allow your query to work.