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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
jsBrizius
Helper I
Helper I

Why is DECLARE not supported (Error)?

 

 

It seems for some reason a DECLARE in the SQL Statement does not work?

 

Here is a simple example...

 

Select.PNG

Result.PNG

 

Error.PNG

1 ACCEPTED 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:

 

Ca44pture.PNG

 

Captur5656e.PNG

 

We have reported this issue internally.

 

Regards,

View solution in original post

14 REPLIES 14
Anonymous
Not applicable

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?

 

Vicky_Song
Impactful Individual
Impactful Individual

@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

  • Rewrite your data collection stored procedure as a view (if possible/practical) OR
  • Use the Data Connectivity mode import instead of direct query - and schedule your updates through the gateway.

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?). 

Anonymous
Not applicable

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?

 

 

Capture.PNG

 

Anonymous
Not applicable

I'm having trouble working around the restrictions. Any chance you can elaborate, please?

jsBrizius
Helper I
Helper I

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.

 

Data.PNG

Query.PNG

ApplyQueryChanges.PNG

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

 

Ca44pture.PNG

 

Captur5656e.PNG

 

We have reported this issue internally.

 

Regards,

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.

 

simpleQuery.PNG

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.