Reply
Frequent Visitor
Posts: 9
Registered: ‎07-13-2017
Accepted Solution

Native Database Query Error

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!


Accepted Solutions
Highlighted
Advisor
Posts: 307
Registered: ‎01-16-2018

Re: Native Database Query Error

[ Edited ]

@mw123,

 

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

View solution in original post


All Replies
Super User
Posts: 2,954
Registered: ‎06-24-2015

Re: Native Database Query Error

Hi there

What is your Source database? I have queries running from SQL Server and Oracle in the past with no issues.
Did I answer your question? Mark my post as a solution!
"Proud to be a Datanaut!"
Frequent Visitor
Posts: 9
Registered: ‎07-13-2017

Re: Native Database Query Error

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

Advisor
Posts: 307
Registered: ‎01-16-2018

Re: Native Database Query Error

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.)

Frequent Visitor
Posts: 9
Registered: ‎07-13-2017

Re: Native Database Query Error

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

 

 

Advisor
Posts: 307
Registered: ‎01-16-2018

Re: Native Database Query Error

@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.

Frequent Visitor
Posts: 9
Registered: ‎07-13-2017

Re: Native Database Query Error

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.

Highlighted
Advisor
Posts: 307
Registered: ‎01-16-2018

Re: Native Database Query Error

[ Edited ]

@mw123,

 

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

Super User
Posts: 2,954
Registered: ‎06-24-2015

Re: Native Database Query Error

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.
Did I answer your question? Mark my post as a solution!
"Proud to be a Datanaut!"
Occasional Visitor
Posts: 1
Registered: ‎01-18-2019

Re: Native Database Query Error

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.