SQL query in direct query mode

[amitabhk1971]

Hi,

I am running a sql query in power bi to get data in direct query mode. The query throws correct result on sql server without any syntax error. However, when I run the same query in direct query mode, connecting to Azure SQL Database, in power bi's advanced options box under get data it throws correct result when I click the OK button but when I click the Load button on the same resultset it throws a syntax error message. The part where it is throwing the error is below:

DECLARE @LatestPackageVersion int;
SELECT
@LatestPackageVersion = MAX(PackageId)
FROM [Packages];
DECLARE @CurrentTime AS datetime;
SELECT
@CurrentTime = GETUTCDATE()

;

with cte_.....-- code continues.

The error message is:

Anyone getting any clue why is this happening?

Thanks,

Amitabh

[Eric_Zhang · Accepted solution]

---

@amitabhk1971 wrote:

Well I am using only the select part of the stored procedue. I am not using exec procname. 

---

@amitabhk1971

In my test on SQL Server, in DQ mode, Power BI sends a query wrapped as below.

SELECT XXXX, XXXX, XXX, XX FROM (

your query in you input in the pbi desktop

) t

So in your case, the actually query is 

SELECT XXXX, XXXX, XXX, XX FROM (

declare ....
...
select..

) t

That's why the you got the SQL syntax error. This is on SQL Server, and I think Power BI does the same thing to Azure SQL. As a workaround, instead of DECLARE statement, replace those variables in the SQL with a scalar value sub query.The CTEs can't work in this case as well, Try to recompose the SQL like.

From

FROM

DECLARE @LatestPackageVersion int;
SELECT
@LatestPackageVersion = MAX(PackageId)
FROM [Packages];
DECLARE @CurrentTime AS datetime;
SELECT
@CurrentTime = GETUTCDATE()

;with cte as(
....
where datetime = @CurrentTime
and version = @LatestPackageVersion
)
select * from cte

TO

SELECT * FROM (
..
where datetime = GETUTCDATE() 
and version = (select MAX(PackageId) FROM [Packages])

) cte

[amitabhk1971]

Hi,

I am trying to use MAX function in PowerBI using DAX/SQL in direct query mode but it says MAX is not supported in direct query mode. I checked the microsoft site for supported commands in DQ mode and MAX is included, but it is throwing this error on power bi. I went to options and checked "Allow unrestricted measures in direct query mode" under DirectQuery. Did not work. Does anyone know the solution. I need DQL mode because in DQ mode the report is real time while in import mode it needs to be refreshed as per a schedule. Any clue anyone? Thanks

[Eric_Zhang]

---

@amitabhk1971 wrote:

Hi,

I am trying to use MAX function in PowerBI using DAX/SQL in direct query mode but it says MAX is not supported in direct query mode. I checked the microsoft site for supported commands in DQ mode and MAX is included, but it is throwing this error on power bi. I went to options and checked "Allow unrestricted measures in direct query mode" under DirectQuery. Did not work. Does anyone know the solution. I need DQL mode because in DQ mode the report is real time while in import mode it needs to be refreshed as per a schedule. Any clue anyone? Thanks

---

@amitabhk1971

The MAX function is allowed in DQ mode, based on my test on SQL Server. What database are you using and could you upload a snapshot of the "MAX is not supported in DQ mode"?

By the way, for real time purpose, you could also reference  Real-time streaming in Power BI

[amitabhk1971]

Hi Eric I am again stuck at the same point. In Direct query mode the following code is not running. Please see the error screenshot.

DECLARE @LatestPackageVersion int;
SELECT
@LatestPackageVersion = MAX(PackageId)
FROM [Packages]

The error:

Any solution or workaround?

Amitabh

[Eric_Zhang]

---

@amitabhk1971 wrote:

Hi Eric I am again stuck at the same point. In Direct query mode the following code is not running. Please see the error screenshot.

@DECLARE @LatestPackageVersion int;
SELECT
@LatestPackageVersion = MAX(PackageId)
FROM [Packages]

The error:

Any solution or workaround?

Amitabh

---

@amitabhk1971

Those are SQL to do variable value assignment and it doesn't return any rows, that's why I think Power BI doesn't accept it. What's your goal when trying to run such SQL statements in Power BI desktop?

[amitabhk1971]

It is part of a stored procedure. When I run the stored procedure in PowerBI it says syntax arror at the Declare statement. This is the only place where there is a DECLARE statement. While the same stored procedure runs ok in sql server.

Amitabh

[amitabhk1971]

In fact the same stored procedure throws the correct result set in Power BI also when I click OK on the get data box. But it throws the error when I click LOAD button on the result box.

Amitabh

[Eric_Zhang]

---

@amitabhk1971 wrote:

In fact the same stored procedure throws the correct result set in Power BI also when I click OK on the get data box. But it throws the error when I click LOAD button on the result box.

Amitabh

---

@amitabhk1971

I'm afraid that in DQ mode, executing a stored procedure is not supported. See this similar thread.

[amitabhk1971]

Well I am using only the select part of the stored procedue. I am not using exec procname. 

[Eric_Zhang · Accepted solution]

---

@amitabhk1971 wrote:

Well I am using only the select part of the stored procedue. I am not using exec procname. 

---

@amitabhk1971

In my test on SQL Server, in DQ mode, Power BI sends a query wrapped as below.

SELECT XXXX, XXXX, XXX, XX FROM (

your query in you input in the pbi desktop

) t

So in your case, the actually query is 

SELECT XXXX, XXXX, XXX, XX FROM (

declare ....
...
select..

) t

That's why the you got the SQL syntax error. This is on SQL Server, and I think Power BI does the same thing to Azure SQL. As a workaround, instead of DECLARE statement, replace those variables in the SQL with a scalar value sub query.The CTEs can't work in this case as well, Try to recompose the SQL like.

From

FROM

DECLARE @LatestPackageVersion int;
SELECT
@LatestPackageVersion = MAX(PackageId)
FROM [Packages];
DECLARE @CurrentTime AS datetime;
SELECT
@CurrentTime = GETUTCDATE()

;with cte as(
....
where datetime = @CurrentTime
and version = @LatestPackageVersion
)
select * from cte

TO

SELECT * FROM (
..
where datetime = GETUTCDATE() 
and version = (select MAX(PackageId) FROM [Packages])

) cte

[amitabhk1971 · Accepted solution]

Thanks Eric. That is what I did. I replaced the parameters with their select queries and now it works. 

[Eric_Zhang]

---

@amitabhk1971 wrote:

Thanks Eric. That is what I did. I replaced the parameters with their select queries and now it works. 

---

@amitabhk1971

Glad to hear you've figured it out. If no further question, could you please mark the replies making sense as solution to close this thread?

[amitabhk1971]

Hi Eric,

I am actually using a long stored procedure in sql querying Azure SQL database. But anyways my purpose is to make my report real time. Thanks for referring to the Microsoft page regarding making a repport real time.