Invalid syntax SQL message when running in Power BI

Question

I wrote an SQL script that works great when executed directly in SQL Management Studio. However, when you enter it in Power BI as a source, it reports that it has the wrong syntax.

This is the request:

EXEC "dbo"."p_get_bank_balance" '2' 

However, does the syntax seem to be wrong? See image:

Any help is much appreciated.

EDIT ***

When double quotes are removed (as suggested by Tab Alleman):

Answer 1

Some time ago, I discovered the same problem on the Internet at Power Bi:

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

You must use DirectQuery mode in which you cannot connect to data using stored procedures. Try again using the import mode or just use the SELECT statement directly.

Answer 2

In DirectQuery mode, PowerBI automatically wraps your query as follows: select * from ( [your query] ) , and if you try to do this in SSMS with the stored procedure ie

 select * from (exec dbo.getData) 

You will receive the error message that you see above.

The solution is that you should put the stored procedure call in the OPENQUERY call on your local server ie

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

Prerequisites would be: enabling access to the local server in OPENQUERY with

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

And then make sure you use the three-digit notation in OPENQUERY, since all calls are by default for the master database

Answer 3

In the data connection mode "Import", stored procedures work. In the "Direct request" data connection mode, the query syntax should be as follows:

declare @sqlCommand varchar (100) = 'dbo.p_get_bank_balance' declare @ p1 int = 2

exec @sqlCommand @ p1 = @ p1

Remerber: Maximum one connection to a data source using Direct Query. If you want to call a lot of SP, only one can be in direct request mode, the rest in import mode

Answer 4

Try using import instead of direct query. May show an error because you are using a temporary table in it. Create a query using a subquery, delete the temporary table and try. Or you can use it as an import instead of Direct Query, it will work.