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 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 

Need Help

3 Replies

  • 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: