SQL reporting invalid syntax when run in Power BI

Question

I have written an SQL script which runs fine when executed directly in SQL Management Studio. However, when entering it into Power BI as a source, it reports that it has an incorrect syntax.

This is the query:

            EXEC "dbo"."p_get_bank_balance" '2'

However, the syntax is apparently incorrect? See Picture:

Any help is much appreciated.

EDIT ***

When the double quotes are removed (as per Tab Alleman's suggestion):

Answer 1

I found time ago the same problem online on power bi site:

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.

Answer 2

In DirectQuery mode, PowerBI automatically wraps your query like so: select * from ( [your query] ), and if you attempt this in SSMS with a stored procedure i.e.

select * from (exec dbo.getData)

You get the error you see above.

The solution is you have to place your stored procedure call in an OPENQUERY call to your local server i.e.

select * from OPENQUERY(localServer, 'DatabaseName.dbo.getData')

Prerequisites would be: enabling local server access in OPENQUERY with

exec sp_serveroption @server = 'YourServerName' 
   ,@optname = 'DATA ACCESS' 
   ,@optvalue = 'TRUE' 

And then making sure you use three-part notation in the OPENQUERY as all calls there default to the master database