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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
amitabhk1971
Helper I
Helper I

SQL query in direct query mode

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:

Power BI error.jpg

 

 

 

 

 

 

 

 

 

 

 

Anyone getting any clue why is this happening?

 

Thanks,

Amitabh

2 ACCEPTED SOLUTIONS


@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

 

 

View solution in original post

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

 

View solution in original post

12 REPLIES 12

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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