M language script that runs a text search in stored procedures in SQL server sys.databases

[FrankKrogh]

I have tried to design a M language script that runs a text search in stored procedures in SQL server sys.databases. When I view the script code in Advanced edit mode there is a note below the script that conclude with "No script errors are registrated".

Unfortunately I cannot identify what needs to be corrected in the script when reading the error message "Incorrect syntax near ''. " returned in PowerQuery .

It seems to me that the debug information in Power Query regarding execution of first step unfortunate is not present in current version of PowerBI Desktop.

[foodd · Accepted solution]

@FrankKrogh , would this not be a case to create a view upstream, and connect to the view?

[ImkeF]

Hi @FrankKrogh ,
I don't have experience with DirectQuery mode, so cannot help you with it.

[foodd · Accepted solution]

@FrankKrogh , would this not be a case to create a view upstream, and connect to the view?

[FrankKrogh · Accepted solution]

Yes, it seems to me to be the only solution as long as only the SELECT command is accepted in Direct query mode. 

[ImkeF]

Hi @FrankKrogh ,
what I see in the first picture you've posted looks like a correct result to me: It will appear when your parameter/query "paramDBname" is empty.
This is not an error on PQ side.

[FrankKrogh]

It seems to me that PowerQuery does not accept the USE <db name> sql command in the Direct query mode. I tried to hardcode the db name but still received error message when I updated the project.