Forum Discussion

eferreira's avatar
eferreira
New Member
10 years ago

Use SQL Store Procedure in Power BI

I have created a basic stored procedure without parameters in SQL Server, when I use the store procedure in Power Bi Desktop the return is ok in design but when I click on Save and Close in datasourc...
Anonymous's avatar
Anonymous
Not applicable
10 years ago

stangellapallyeferreiranirajdubeyasocorro As a follow up to my previous post, i was testing this further due to another thread, and have discovered that you don't need to change any database settings or use OpenQuery. If you wrap your stored procedure in a variable you can import the data using a sproc (Doesn't work in Direct Query, only import)

 

example:

 

DECLARE @sqlCommand varchar(1000)

SET @sqlCommand = 'dbo.Testproc'
EXEC (@sqlCommand)

prasad_chengti's avatar
prasad_chengti
New Member
8 years ago

Hi,

 

I know this post might have been closed as the solutions provided here is working for many. 

However when I am trying to follow same steps getting different kinds of errors.

We were fetching the data from remote database(ORACLE) through VPN in Power BI using Server Name and Connection String.

Using below mentioned syntax, writing the query in Import section and calling the stored procedure which is created in remote database. But when we try to run the report getting the error as follows. To be double sure the stored procedure is not having syntax error or such, executed the same procedure in sql developer and it works as expected. Any help or lead on this is highly appreciated.

 

Query1: 

 

SELECT *
FROM OPENQUERY ([server name],
'EXEC dbname.dbo.spname @parametername = ''R1''');

 

For the above query, the error msg is:

 

Query2:

 

DECLARE @sqlCommand varchar(1000)   ==>  DECLARE Variable varchar(1000)

SET @sqlCommand = 'dbo.Testproc'        ==>   SET Variable = 'dbo.nid_poc_test1'
EXEC (@sqlCommand)                                ==>   EXEC (@variable)

 

For this query the error msg is:

Unable to connect

We encountered an error While trying to connect.

Details: "Oracle:ORA-06550:line 3,column 5: PLS-00488:'variable' must be a type ORA-06550:line 3,column 5:
PL/SQL: Item ignored
ORA-06550:line 5,column 1:
PLS-00221:'variable'is not a procedure or is undefined

ORA-06550:line 5,column 1:
PL/SQL: "Statement ignored"

 

Error2 while using semicolon at the end:

 

 DECLARE Variable varchar(1000);

SET Variable = 'dbo.nid_poc_test1';

EXEC (@variable);

 

Here VPN is very much connected and stored procedure was pretty much running fine in remote DB. We have also tried giving the command time out in Power BI.