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...
nirajdubey's avatar
nirajdubey
Advocate I
10 years ago

Its a late reply, but it may help some body else, you can use openquery. Follwoing syntax will work with both import data and direct query

 


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

Zak2815's avatar
Zak2815
Regular Visitor
9 years ago

nirajdubey, I can't for the life of me get this to work for Direct Query mode:

 

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

 

I've tried each of these below:

 

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

 

SELECT *
FROM OPENQUERY ([server name],
'EXEC dbname.dbo.spname @AsOfDate = ''03-16-2017 23:59:59'' ');

 

SELECT *
FROM OPENQUERY ([server name],
'EXEC dbname.dbo.spname @AsOfDate = CONVERT(DATETIME,''03-16-2017 23:59:59'',101) ');

 

None seem to work.  The times I can get a preview loaded, there's always an error on the "Apply".

 

Please help if anyone has succeeded in calling a Stored Proceedure in Direct Import Mode.