Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
@amitabhk1971 wrote:
Well I am using only the select part of the stored procedue. I am not using exec procname.
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
Thanks Eric. That is what I did. I replaced the parameters with their select queries and now it works.
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |