Stored procedure parameter call with power bi

[censoft17]

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

[Eric_Zhang · Accepted solution]

---

@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

[censoft17]

hi Eric,

StartDate, EndDate parameters are now working fine.

How to use these parameters as filter, once you publish the report.

I have added StartDate,EndDate parameter in slicer so that i can get filtered data in the table.

Please help me in using these parameters as filter on published report

Thanks & Regards