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 datasource edit the Power Bi gives me an errormessage like below, I'm trying to use this procedure as Direct Query.
Microsoft SQL: Incorrect syntax near the keyword 'EXECUTE'. Incorrect syntax near ')'.
I tried to change the statement but I got nothing.
The sql statement to execute the procedure is:
I'm using the AdventureWorks database sample from Microsoft and my procedure statement is:
CREATE PROCEDURE [Person].[SelectpersonByType] AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
-- Insert statements for procedure here select * from Person.Person END
My customer is testing Power BI and are facing the same error, thanks in advanced.
@stangellapally@eferreira@nirajdubey@asocorro 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)
DECLARE @sqlCommand varchar(1000)
SET @sqlCommand = 'dbo.Testproc' EXEC (@sqlCommand)