Stored procedure parameter call with power bi

---

censoft17 wrote:

Hi,

I want to call my stored procedure written in Azure SQL Server to Power BI. I am facing an error like

 

Details: "Microsoft SQL: 'OPENQUERY' rowset provider not supported in this version of SQL Server."

 

The query i have written is 

SELECT *
FROM OPENQUERY ([servername],
@'EXEC dbname.dbo.spname @StartDate = "StartDate",@EndDate = "EndDate"');

 

I have created two parameters in PowerBI namely - StartDate,EndDate.

I want to enter values manually at run-time for startdate & enddate parameter

 

Please provide your valuable feedback.

Thanks & Regards

 

---

censoft17

1. Azure SQL Server doesn't support  the OPENQUERY syntax.
2. If using OPENQUERY syntax is to bypass the syntax error when executing a stored procedure in DirectQuery mode, no, it is an dead end.
3. In Import mode, to execute a stored procedure with the parameters, you can try to modify in the advanced editor.
   

   let
       Source = Sql.Database("xxxxxx.database.windows.net", "AdventureWorksLT", [Query="exec DBNAME.dbo.SPNAME '"&Date.ToText(StartDate,"yyyyMMdd")&"','"&Date.ToText(EndDate,"yyyyMMdd")&"'"])
   in
       Source