Forum Discussion

nikkihital22's avatar
nikkihital22
Frequent Visitor
2 years ago

SQl Stored proc in Powerbi Error

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...
Need Help
Fowmy's avatar
Fowmy
Icon for Super User rankSuper User
2 years ago

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's avatar
nikkihital22
Frequent Visitor
2 years ago

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

 

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    2 years ago

    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: