cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sekic Regular Visitor
Regular Visitor

Re: Use SQL Store Procedure in Power BI

Hi,

table value function is just fine, but not work if you call it  with parameter which is not constant 😞

I tried to send any info about userid, dax expresion about userid, or anything similar which is not constant, didn't successed.

select * from TableFunction('abc') run fine, but

select * from TableFunction(USERPRINCIPALNAME()) fail...

 

sfiros2003 Frequent Visitor
Frequent Visitor

Re: Use SQL Store Procedure in Power BI

Its very useful, thank you all.

 

My question is how to pass parameter value from a list or report as per some selection (not hard-coding in query).

Re: Use SQL Store Procedure in Power BI

JackSprat Regular Visitor
Regular Visitor

Re: Use SQL Store Procedure in Power BI

This code executes as intended in Desktop, but fails in Power BI cloud.

DECLARE @CustomerNumberIDList dbo.IdList
Declare @Success int

INSERT INTO @CustomerNumberIDList
SELECT Distinct CustomerNumberId
FROM [dbo].[vw_ReportSummary]

EXEC @Success=[dbo].[LogCustomerView] @CustomerNumberIDList

If @Success=0
SELECT *
FROM
[dbo].[vw_ReportSummary]

 

My requirement is to log the key values anytime a customer is used in a report for privacy resason.   The portion that does the logging: 'EXEC @Success=[dbo].[LogCustomerView] @CustomerNumberIDList' works like a charm whenever I refreesh in Power BI desktop, but doesn't log the key values when I run the exact same report after uploading it to the cloud.  It does still get the dataset though.

An y ideas on why that call to log the keys only works in desktop?

DBekker Frequent Visitor
Frequent Visitor

Re: Use SQL Store Procedure in Power BI

Openquery did not work for us as we have an SP that utilized a temp table.

but we have had some success using "with result sets" option of exec when that issue comes up.

for instance

SELECT *
FROM OPENQUERY ("snapserver", 
'EXEC specialprojects.dbo.CFE_DASHBOARD_Summary_BugTrendX
WITH RESULT SETS
(
( [rel] nvarchar(16) NOT NULL,
  [value] int NOT NULL,
  [year] int NOT NULL,
  [series] nvarchar(16) NOT NULL,
  [sortorder] int NOT NULL
))') 

 

 

 

This seems to work without issue. Not always ideal as it requires changing the result set definition if the query changes but as these are bi reports they are likely using the same output scheme for most queries.

 

the second half of this is trying to get parameters to work.

 

i think that this would work if the declare statement is removed. bi doesnt seem to like thing like declare or with unless part of the dynamic sql which wont work in this case.

I assume the declare statement can be removed and @team replaced with a bi parameter. but i have not tried it yet.

declare @team nvarchar(max)= 'winet'
exec ('
SELECT *
FROM OPENQUERY ("snapserver", 
''EXEC specialprojects.dbo.CFE_DASHBOARD_Summary_BugTrendX @team='+@team+'
WITH RESULT SETS
(
( [rel] nvarchar(16) NOT NULL,
  [value] int NOT NULL,
  [year] int NOT NULL,
  [series] nvarchar(16) NOT NULL,
  [sortorder] int NOT NULL
))'')') 

 

JackSprat Regular Visitor
Regular Visitor

Re: Use SQL Store Procedure in Power BI

Thanks for this reply.  I will try this today.  I posted previously but must not have hit save or something.  Thanks for the feedback.

prasad_chengti Frequent Visitor
Frequent Visitor

Re: Use SQL Store Procedure in Power BI

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. 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

nirvana_moksh Established Member
Established Member

Re: Use SQL Store Procedure in Power BI

@Seth_C_Bauer

 

I know this is an old post, but what's the use/benefit of using:

 

DECLARE @sqlCommand varchar(1000)

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

 

Opposed to a plain and simple:

 

EXEC STORED_PROCEDURE

 

 

Super User
Super User

Re: Use SQL Store Procedure in Power BI

@nirvana_moksh At the time of the previous post when you ran a stored procedure against the database in Direct Query mode it woud fail. This work around got it to work, I have not tested it in awhile, so I don't know if that behaviour has changed. If you use "import" then you can just use a straight execute statement.


Near SE WI? Join our PUG Milwaukee Brew City PUG
Highlighted
nirvana_moksh Established Member
Established Member

Re: Use SQL Store Procedure in Power BI

@Seth_C_Bauer - thanks for the reply Seth!

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 169 members 1,519 guests
Please welcome our newest community members: