HANA Advanced SQL statement being changed to NativeQuery. Unable to edit SQL directly

[Syndicate_Admin]

Hi All,

Half of our team are now on the September 2022 patch and the others are on August 2022.  Those of us on Sep 2022 can no longer directly edit Advanced SQL.  We are pushed to the advanced editor, which is not ideal.

Before patch: in applied steps the cog icon is avaiable

= SapHana.Database("pcci-serverurl.us:30015", [Query=

Any new queries since the patch.  applied steps the cog icon is no longer there.  The source is change to look like that below.

= Value.NativeQuery(SapHana.Database("pcci-serverurl.us:30015", [Implementation="2.0"])

Anyone know how we can stop this?  Or at least have the ability to edit the SQL query without all the formating that the advanced editor does.

For some reason all my seraches on this trend towards folding.  At this point we do all the optimisation directly in the SQL.  Moving these to power query logic is not someting I'd want to do right now.

Thanks 

Russell

[Syndicate_Admin]

Hi GaryOJ,

I finally got to talk to Microsoft and this "feature" introduced in the September 2022 patch.  The support guy gave me a link to the August Patched version and after I installed this the issue went away.

I assume they will realize this is a bug and fix it in a future patch.  The support guy said maybe in October but I'm not sure how he knows this.    

The work-around shown by TheCreepster1 works but you have to be careful about what you replace.  However I'd say unistall Power BI and then install the August Patch version.    

When they fix this I'll post the details here.

Regards

Russell

[Syndicate_Admin]

Spoiler

Hi GaryOJ,

Yeah, sorry, think my snip is a little but wrong. I'll post the amended code tomorrow for you. 

[Syndicate_Admin]

@russellya - I glad Microsoft are working on the bug.  

I can understand how this can be frustrating because it make that useful cog disappear.  However, I would like to suggest another approach that might make is easier to maintain the Hana Query.  I would suggest inserting an earlier Applied Step to hold the SQL script.  This would look like this:

let
   sql = "SELECT TOP 100 * FROM TABLE",
   Source = Value.NativeQuery( SapHana.Database( Server , [Implementation="2.0", Query= sql])
in
   Source

The sql step is visible in the Power Query screen, so you can simply paste in new strings into the formula bar. 

However, I like to go one step further.  I recommend pasting the sql string in the Advance Editor because it will eliminate the auto addition of #(lf) and #(tab).  Something like this:

let
   sql = 
"
SELECT TOP 100
 * 
FROM 
   TABLE
",
   Source = Value.NativeQuery( SapHana.Database( Server , [Implementation="2.0", Query= sql])
in
   Source

instead of 

let
   sql = "SELECT TOP 100 #(lf)#(tab)*#(lf)FROM#(lf)#(tab)TABLE",
   Source = Value.NativeQuery( SapHana.Database( Server , [Implementation="2.0", Query= sql])
in
   Source

[Syndicate_Admin]

Hi @russellya ,

This issue has been submitted internally.（Internal ICM ID: 339515871）

The workaround is removing the Value.NativeQuery() from the source M query and using the old way with SapHana.Database().

 Please be patient to wait for fixing. If there is any news, I will update it here.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Syndicate_Admin]

Super crucial to get this fixed/reverted.
Some of us working in corporate environment don't exactly have an option to revert to the older version due to IT/Admin settings 🙂

[Syndicate_Admin]

Hey @v-stephen-msft .
Any updates on the resolution of this?

This 'fix' literally killed half of our reporting.

[Syndicate_Admin]

I was told the November patch would fix this but I've not seen anything yet.  I'll continue to use the August patch until I hear something.  If I do I'll post here.

[Syndicate_Admin]

This is still happening in the Nov release. I had to go to the AUG version to make the reports work.