SQl Stored proc in Powerbi Error

[nikkihital22]

Microsoft SQL: Cannot process the object "exec HumanResources..GetTempEmployeeHours_PowerBi"12/1/2023","1/19/2023" ". The OLE DB provider "MSOLEDBSQL" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.
Details:
DataSourceKind=SQL
DataSourcePath=sqlt4costagedw;HumanResources
Message=Cannot process the object "exec HumanResources..GetTempEmployeeHours_PowerBi"12/1/2023","1/19/2023" ". The OLE DB provider "MSOLEDBSQL" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.
ErrorCode=-2146232060
Number=7357
Class=16
State=1

--Syntax used ----

let

Source =Sql.Database("SQLT4COSTAGEDW", "HumanResources", [Query="SELECT * FROM #(lf)OPENROWSET('SQLNCLI','trusted_connection=yes', 'exec HumanResources..GetTempEmployeeHours_PowerBi"""&Date.ToText(@StartDate) & """,""" &Date.ToText(@EndDate) & """ ')", CreateNavigationProperties=false])

in
Source

Getting This error Using the admin account which has all the permissions 

[Fowmy]

@nikkihital22 

Could you try this please?

let
    Source = Sql.Database(
        "SQLT4COSTAGEDW", 
        "HumanResources", 
        [
            Query = "exec HumanResources..GetTempEmployeeHours_PowerBi '" & Date.ToText(@StartDate) & "','" & Date.ToText(@EndDate) & "'",
            CreateNavigationProperties = false
        ]
    )
in
    Source

[nikkihital22]

Was able to pull data : getting this error after apply.

[Fowmy]

@nikkihital22 

I creted a simple SP in my SQL Server and called it with start and end date parameters as follows, you may apply the same in yours:

My SP:

CREATE PROCEDURE [dbo].[GetSalesByDateRange]
    @StartDate DATE,
    @EndDate DATE
AS
BEGIN
    -- Your SQL statements here
    SELECT ProductKey, [Order Date]
    FROM Sales
    WHERE  [Order Date] BETWEEN @StartDate AND @EndDate;
END;

I created two parameters in Power Query for start and end dates:

I connected to SQL with out any T-SQL at the start, just provided server and db then modified the M code as follows:

let
     Source = 
        Sql.Database( 
            ".", 
            "ContosoRetailDW_2019", 
            [
                Query = "EXEC GetSalesByDateRange @StartDate = '" & Text.From(#"@StartDate") & "' , @EndDate = '" & Text.From(#"@EndDate") & "'"
            ]
        )
    
in
   Source

The result: