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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
h4tt3n
Helper V
Helper V

DirectQuery SQL statement fails when starting with declare or with keywords

Hi,

 

I am attempting to show real-time data from an Azure SQL database using DirectQuery. However, the data needs to be manipulated with a complex SQL query, not just a simple select. When copy-pasting a known working SQL query into the sql statement window in the DirectQuery setup menu, it works fine within the setup menu, but when I click Ok it fails with a "Microsoft SQL: Incorrect syntax" error. (See screenshots below)

 

DirectQuery setup menu, with complex SQL query pasted into the SQL statement window:

2022-05-18 12_46_23-Untitled - Power BI Desktop.png

 

After clicking Ok, a sample dataset is fetched, so we know the credentials and SQL statement are working:

2022-05-18 12_47_05-Untitled - Power BI Desktop.png

After clicking either Load or Transform Data, the query fails:

2022-05-18 12_48_07-Untitled - Power BI Desktop.png

I get the exact same error if I connect with DirectQuery and then manually add the SQL statementr in the Transform Data menu.

 

The only thing I could get working was a simple, naiive select query, but this is not useful to me.

What might be the cause of this error? There is nothing wrong with the SQL query.

 

The documentation states that "If the SQL statement is overly complex it will fail". What exactly is the definition of "overly complex"? Without a clear definition I would have to play guessing-game with Power BI until something accidentally works, but I wouldn't know why or if it would keep working.

 

Cheers, Mike

3 REPLIES 3
amitchandak
Super User
Super User

@h4tt3n , can share where declare is used.

If you trying a proc/function code to return a table, that will not work, You can call SP of SQL server for that

 

refer

https://www.c-sharpcorner.com/article/execute-sql-server-stored-procedure-with-user-parameter-in-pow...

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

@amitchandak Thanks for replying

 

I noticed that the linked tutorial uses import, and not DirectQuery. Do you know if this will work in DirectQuery too?

 

My SQL statement contains a list of variable declarations and a row of common table elements, followed by a select query. No functions. It appears to be failing if I start it with anything else than the select keyword.

 

Cheers, Mike

You can't declare variables or use cte

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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