DirectQuery SQL statement fails when starting with declare or with keywords

[h4tt3n]

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:

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

 

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

 

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

[amitchandak]

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

[h4tt3n]

@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

[Ry88]

You can't declare variables or use cte