cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mw123 Frequent Visitor
Frequent Visitor

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!

1 ACCEPTED SOLUTION

Accepted Solutions
zoloturu
Advisor

Re: Native Database Query Error

@mw123,

 

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

9 REPLIES 9
Super User
Super User

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!"


Power BI Blog
mw123 Frequent Visitor
Frequent Visitor

Re: Native Database Query Error

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

zoloturu
Advisor

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

mw123 Frequent Visitor
Frequent Visitor

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

 

 

Highlighted
zoloturu
Advisor

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.

mw123 Frequent Visitor
Frequent Visitor

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.

zoloturu
Advisor

Re: Native Database Query Error

@mw123,

 

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

Super User
Super User

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!"


Power BI Blog
rayalasagar Occasional Visitor
Occasional Visitor

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.

 

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 282 members 3,039 guests
Please welcome our newest community members: