Direct Query using Stored Procedure from Azure SQL Database: Incorrect syntax near the keyword.

[Leise]

Hi there,

I have an Azure SQL Database that has a Stored Procedure that I need to call using Direct Query.

My Stored Procedure has no paramters.

I have scoured the forums and I have tried all 3 of the workarounds mentioned below using Advanced Options or Advanced Editor and I still get the following error:

Microsoft SQL: Incorrect syntax near the keyword 'DECLARE'. Incorrect syntax near ')'.

The workarounds I have tried are as follows:

Workaround # 1

DECLARE @SP VARCHAR(100) = 'SCHEMA.STOREDPROCEDURE'
EXEC (@SP)

Workaround # 2

let
Source = Sql.Database("SQL-AZURE-DATABASE", "SQL-AZURE-DATABASENAME", [Query="SELECT * FROM #(lf)OPENROWSET('SQLNCLI','trusted_connection=yes', 'exec SCHEMA.STOREDPROCEDURE')", CreateNavigationProperties=false])
in
Source

Workaround # 3

let
Source = Sql.Database("SQL-AZURE-DATABASE", "SQL-AZURE-DATABASENAME", [Query="DECLARE#(tab)@return_value int = 0; #(lf)#(lf)EXEC#(tab)@return_value = [SCHEMA].[STOREDPROCEDURE]; #(lf)#(lf)SELECT#(tab)'Return Value' = @return_value; "])
in
Source

Has anyone managed to do this in the above scenario?

Many thanks.

[Burma]

The problem is that behind the scenes Power BI executes

select * from (
--your query, e.g. EXEC spReport
) SourceQuery where 1 = 2

To test source validity. This is incorrect syntax when query is EXEC

[v-chuncz-msft · Accepted solution]

@Leise,

Try to specify database name inside OPENROWSET.