Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
mw123
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

@mw123,

 

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

View solution in original post

13 REPLIES 13
pthapa
Post Patron
Post Patron

Hello all,

I am trying to connect power bi dataset via stored procedure in SQL server and this gave me an error, how can I resolved this issue?

see error below.

pthapa_0-1722881832822.png

Thanks

pthapa

Hi @pthapa 

 

Try this Solved: Connecting Power BI Desktop to Stored Procedures: ... - Microsoft Fabric Community





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Anonymous
Not applicable

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

Anonymous
Not applicable

wowww!

GilbertQ
Super User
Super User

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 Super User!







Power BI Blog

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

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

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

 

 

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

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.

@mw123,

 

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

Anonymous
Not applicable

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.

 

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 Super User!







Power BI Blog

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors