Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
It seems for some reason a DECLARE in the SQL Statement does not work?
Here is a simple example...
Solved! Go to Solution.
Hi @jsBrizius,
We reproduced same issue as yours. Power BI Desktop will always resolve the front end input SQL statement as a derived table. So when passing the SQL statement, the "Decalare"statement cause syntax error. See the fetched query in SQL profiler:
We have reported this issue internally.
Regards,
Hi,
I made a new function (new source ---> Blank query) and had same issue with this query:
let
UserNameSQL = Sql.Database("Servername","DatabaseName",[Query="EXEC [proj].[TestOrganisation2]"])
in
UserNameSQL
but i solved it by changing it to this:
let
UserNameSQL = () =>
Sql.Database("Servername","DatabaseName",[Query="EXEC [proj].[TestOrganisation2]"])
in
UserNameSQL
Hope it can help you guys 🙂
regards
But what is this in case of Direct query..!
This converting the query into function and then invoking to a query and loading is converting the query to import mode..
What should I do for Direct query.
I have the same issue, were you able to find the solution?
@jsBrizius, I got the information from PG that the issue you get is a by design behavior. Please check the followings as detailed information:
This is currently working as designed. DirectQuery and Import use the SQL statement differently. Import will execute the SQL statement directly, but DirectQuery uses the SQL statement as a sub-query such as:
SELECT [Column1], SUM([Column2])
FROM (<SQL statement>) AS t
GROUP BY [Column1]
As a result, there is a requirement that the SQL statement must be a valid subquery which has restrictions not applied to standalone SQL statements. Not allowing local variables is one of the restrictions. To work around the restriction, the user can define a table valued function which does allow local variables and then import the table through the table valued function instead of the SQL statement directly.
Thanks @Vicky_Song for the explanation. It does seem that the direct query has serious limitations. For example, you cannot execute a stored procedure (i.e. exec usp_something) as a direct query. As a result, you lose out on the auto update of the data model. So either
If you need your stored procedures for your other apps, make the view first (for the direct query) then wrap the view in a stored proc (for your other apps).
Not great but just about workable (and a year old - has there really been no change to accommodate this?).
I tried to create a function but I get the error "Invalid use of a side-effecting operator 'EXECUTE STRING' within a function. What I read was that you can't use dynamic SQL in a UDF. Any alternatives to get my dynamic SQL to pull into Power BI as a direct query?
I'm having trouble working around the restrictions. Any chance you can elaborate, please?
I am getting an error when I try to Close & Apply the Query Editor. My query seems to work fine until I try to Close and Apply.
@jsBrizius I can reproduce your issue. But looking at your query you can get away without using Declare. You can instead use each of those functions in line.
Is this a bug?
My query is much harder to read without the ability to declare a variable. Seems strange that it works fine in the Query Editor.
Hi @jsBrizius,
We reproduced same issue as yours. Power BI Desktop will always resolve the front end input SQL statement as a derived table. So when passing the SQL statement, the "Decalare"statement cause syntax error. See the fetched query in SQL profiler:
We have reported this issue internally.
Regards,
Any news on an ETA when this will work??
Was the issue resolved?
Update: The issue wasn't resolved and you still can't use temp tables or declare statements.
Maybe we can make this into a new Topic, since this is probably not at the top of their list, or even close to the first 5 pages of features.
That way, we can trigger a new response
So apparently you cannot use DECLARE? I still get the same error with the following query.
User | Count |
---|---|
123 | |
96 | |
89 | |
73 | |
63 |
User | Count |
---|---|
138 | |
115 | |
111 | |
98 | |
95 |