How to run stored procedure in Azure Data Factory DataFlow SQL Server source

0
votes

When using an Azure SQL Server source, I use the Query option and specify a stored procedure to run. When I paste in the same code in Management Studio, it works, but when executed from ADF source using Query option, it errors with the following condition. How can I call a stored procedure using Query option?

{"message":"at Source 'Source': com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'EXECUTE'.. Details:at Source 'Source': com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'EXECUTE'.","failureType":"UserError","target":"SyncData","errorCode":"DFExecutorUserError"}

Here is the query I'm passing that works when called from SSMS:

EXECUTE [dbo].[sp_ReplicaGetChanges] @ReplicaVersion = 0, @FirstTimeFlag = 1, @SourceSchema = 'dbo', @SourceTable = 'Brand', @UpdateColumns = NULL

enter image description here

2
azure-data-factory
I tried it on my side and get the same error. I think it may not supported. - Leon Yue
But something is strange, we could run EXECUTE... statement in Copy Active Source query operation, but can not in Data Flow Source query. - Leon Yue

2 Answers

0
votes

Some analytics system does not like execute and dbo. Try to change execute to exec and/or remove dbo. From sp E.g. Exec sp_ReplicaGetChanges