Wednesday, September 21, 2022

PowerBI run SP in DirectQuery

When I try to run a Stored Procedure in PowerBI getting this error 

Incorrect syntax near the keyword 'EXEC'. Incorrect syntax near ')'.


You may need to configure SQL Server , but after all setup , you have to do following

Create the SP, and use in OPENROWSET as following , test this is in SSMS


SELECT * FROM OPENROWSET('SQLNCLI','server=DEVSERVER;Persist Security Info=True;UID=User;PWD=Password',

'exec budget.[dbo].[SP_TB_Test_Delthis_new]')


If this is working fine create a view  as following:-

Create view [dbo].[VTest_dataTB_Delthis] as

 SELECT * FROM OPENROWSET('SQLNCLI','server=DEVSERVER;Persist Security Info=True;UID=User;PWD=Password',

'exec budget.[dbo].[SP_TB_Test_Delthis_new]')

 

use this in Power BI as following :




No comments: