topic Gettin Error while loading the SP data using DirectQuery in Report Server

Gettin Error while loading the SP data using DirectQuery

arun_1047 — Thu, 15 Mar 2018 18:05:50 GMT

Hello,

I was trying to use the Stored procedure using Direct Query in Power BI on Prem OCT Version

I am getting the below error message while loading the data into Power BI desktop itself

"Incorrect Syntax near the Keyword EXEC. Incorrect Syntax near )".

Steps:

Connected source using GetData-> SQL Server-> DirectQuery ->Advance Option-> EXEC SPName

Power BI Desktop Version: 2.51.4885.1423 64-bit (October, 2017)

Re: Gettin Error while loading the SP data using DirectQuery

Anonymous — Fri, 16 Mar 2018 08:35:55 GMT

Hi arun_1047

I have played a bit with your scenario. Using just 'exec sp_proc' does not work indeed, the statement sent to the SQL Server is not syntactically correct:

select * from (exec sp_test) SourceQuery where 1 = 2

You can use OPENQUERY as mentioned here, however you'll need your SQL Server setup for data access.

Regards,

Maria

Re: Gettin Error while loading the SP data using DirectQuery

Anonymous — Tue, 24 Jul 2018 07:08:34 GMT

http://community.powerbi.com/t5/Desktop/Use-SQL-Store-Procedure-in-Power-BI/td-p/20269

You must be using DirectQuery mode, in which you cannot connect to data with stored procedures. Try again using Import mode or just use a SELECT statement directly.