Forum Discussion

jsBrizius's avatar
jsBrizius
Helper I
10 years ago
Solved

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

 

 

Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    10 years ago

    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,

15 Replies

  • Vicky_Song's avatar
    Vicky_Song
    Impactful Individual
    10 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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?

       

       

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
    Anonymous
    Not applicable
    7 years ago

    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

    • vissvess's avatar
      vissvess
      Helper V
      6 years ago

      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.

      • HarshVidap's avatar
        HarshVidap
        Frequent Visitor
        4 years ago

        I have the same issue, were you able to find the solution?

         

  • jsBrizius's avatar
    jsBrizius
    Helper I
    10 years ago

    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's avatar
      jsBrizius
      Helper I
      10 years ago

       

       

       

       

       

      So apparently you cannot use DECLARE?  I still get the same error with the following query.

       

    • ankitpatira's avatar
      ankitpatira
      Community Champion
      10 years ago

      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.

       

      • jsBrizius's avatar
        jsBrizius
        Helper I
        10 years ago

        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.