Power BI Microsoft SQL: Incorrect syntax near the keyword 'EXEC'. Incorrect syntax near ')' on stored proc with no parameters (DirectQuery)

Question

I have looked at the articles on stackoverflow about this issue.

I have also reviewed the article for calling stored procedures with parameters at https://www.c-sharpcorner.com/article/execute-sql-server-stored-procedure-with-user-parameter-in-power-bi/.

In my case, I have a stored procedure with no parameters.

I am unclear on how I would apply a fix-up to the M script in Power Query Editor to call a stored procedure with no parameters so that the stored procedure can be recognized and used by Power BI.

Could someone provide guidance for my scenario and steps below?

Scenario

• I am using a Power BI with DirectQuery.
• I need an ordered list or rows from my database. So I created a stored procedure in my SQL database that simply wraps a SQL SELECT statement with an ORDER BY clause.
• The stored procedure has no parameters.

Steps

1. In SQL Server Management Studio, I create and test my stored procedure.

CREATE PROCEDURE [dbo].[pbiGetFileInfo]
AS
BEGIN

    SET NOCOUNT ON;

    SELECT dbo.CurrentReport.JobId AS CurrentJobId, 
          dbo.jobs.id AS JobId, 
          dbo.JobInstruments.Id AS JobInstrumentId, 
          dbo.JobInstruments.InstrumentDescription, 
          dbo.JobInstruments.Notes, 
          dbo.JobInstruments.Latitude, 
          dbo.JobInstruments.Longitude, 
          dbo.JobInstruments.Depth,
          dbo.jobinstrumentimport.filename, 
          dbo.jobinstrumentimport.mindate AS FromDate, 
          dbo.jobinstrumentimport.maxdate AS ToDate, 
          DATEDIFF(hour, dbo.jobinstrumentimport.mindate, dbo.jobinstrumentimport.maxdate) AS duration_hours
   FROM   dbo.CurrentReport INNER JOIN
          dbo.jobs ON dbo.CurrentReport.JobId = dbo.jobs.id INNER JOIN
          dbo.JobInstruments ON dbo.jobs.id = dbo.JobInstruments.JobId INNER JOIN
          dbo.jobinstrumentimport ON dbo.JobInstruments.Id = dbo.jobinstrumentimport.jobinstrumentid
   ORDER BY JobInstruments.Id, FromDate

END
GO

2. In Power BI, I click the Transform Data button to launch the Power Query Editor.
3. Under queries, I right-click the first empty entry in the Queries pane and highlight the New Query item and click SQL Server from the context menu.
4. In the SQL Server Database dialog, I enter the Server and Database.
5. In the SQL Server Database dialog, I click the Advanced Options link to expand the dialog and show the SQL statement (optional, requires database) field.
6. In the SQL statement (optional, requires database), I enter EXEC [dbo].[pbiGetFileInfo] and click the OK button.
7. A truncated preview of the data returned by the stored procedure is displayed.
8. I click OK at the bottom of the preview.
9. A new entry Query1 appears in the Queries pane.
10. I right-click the new Query1 entry and rename it to pbiGetFileInfo. The M syntax that appears for the query at this point is:

= Sql.Database("Server Name", "NWBDatabase", [Query="EXEC [dbo].[pbiGetFileInfo]"])

At this point, if I click "Apply" from the Power Query Editor Ribbon, I will get the error message:

Incorrect syntax near the keyword 'EXEC'. Incorrect syntax near ')'

11. I click the Advanced Editor button on the toolbar. The M script for the pbiGetFileInfo query is:

let
    Source = Sql.Database("Server Name", "NWBDatabase", [Query="EXEC [dbo].[pbiGetFileInfo]"])
in
    Source

At this point, I am stuck.

My questions are:

1. The stored procedure has no parameters. Do I need to add a SQLSource prefix to the M script? If I do need a SQLSource, what would that look like?

let
    SQLSource ...
    let
       Source = Sql.Database("Server Name", "NWBDatabase", [Query="EXEC [dbo].[pbiGetFileInfo]"])
   in
       Source
in
   SQLSource

2. One thought is to create a view in SQL that calls the stored procedure. I have tried this and found that the view returns the same warning in Power BI as you would see if you tried to create a View with an ORDER BY in SQL. Calling views from Power BI is problematic at best.
3. Is there any way to write a stored procedure in SQL that minimizes the workarounds required to use them from Power BI?

Updates

• I cannot call a stored procedure from Power BI under DirectQuery. It returns the same error `Incorrect syntax near 'EXEC' message. I need to see the DAX that is created to find the source of this error.

• If I try the raw SQL Select from the stored procedure that I am trying to call, I get the following error:

Microsoft SQL: The ORDER BY clause is invalid in views.

Note: this is using straight SQL SELECT. The word VIEW is does not exist in the SQL SYNTAX at all.

• A SQL Select that calls a VIEW only works if the calling outer SELECT contains a TOP (100) PERCENT clause. For example:

My view named [pbiGetFileInfo] contains the following SELECT statement:

    SELECT dbo.CurrentReport.JobId AS CurrentJobId, 
          dbo.jobs.id AS JobId, 
          dbo.JobInstruments.Id AS JobInstrumentId, 
          dbo.JobInstruments.InstrumentDescription, 
          dbo.JobInstruments.Notes, 
          dbo.JobInstruments.Latitude, 
          dbo.JobInstruments.Longitude, 
          dbo.jobinstrumentimport.filename, 
          dbo.jobinstrumentimport.mindate AS FromDate, 
          dbo.jobinstrumentimport.maxdate AS ToDate, 
          DATEDIFF(hour, dbo.jobinstrumentimport.mindate, dbo.jobinstrumentimport.maxdate) AS duration_hours
   FROM   dbo.CurrentReport INNER JOIN
          dbo.jobs ON dbo.CurrentReport.JobId = dbo.jobs.id INNER JOIN
          dbo.JobInstruments ON dbo.jobs.id = dbo.JobInstruments.JobId INNER JOIN
          dbo.jobinstrumentimport ON dbo.JobInstruments.Id = dbo.jobinstrumentimport.jobinstrumentid

The view itself does not contain an ORDER BY clause.

When I try to call this from a SQL SELECT statement:

SELECT * FROM [dbo].[pbiGetFileInfo] ORDER BY Id,FromDate

I get the error:

Microsoft SQL: The ORDER BY clause is invalid in views...

It works if I revise the SELECT to:

SELECT TOP (100) PERCENT * FROM [dbo].[pbiGetFileInfo] ORDER BY Id,FromDate

But, I am not sure it this will work correctly in Power BI DirectQuery.

• My first thought is that Power BI seems to treat everything as a SQL VIEW. So all data sources are subject to the limitations of views. None of the advantages of sorting on a SQL Server are actually available in Power BI under DirectQuery. If you have to set the sort order in Power BI, there may be significant performance penalties.

• I am experimenting with Table-Valued Functions (but have no faith that this will work in Power BI).

Answer 1

After searching for an answer to one of the most insane "feature, not a bug" issue in Power BI, I found this answer:

I had same problem 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

I have no words for the need for any workaround to calling Stored Procedure via DirectQuery in Power Bi in 2024, let alone one that inserts empty function along the way...