Stored Procedure not Executing from normal operation in Azure Data Factory but works from "Rerun from activity"

Anonymous

I am just not sure how to resolve this issue.

I have a pipeline where it executes a stored procedure (after one step called "Copy data").

When I run the Pipeline, the second step where stored procedure has to do the job does not do the job as expected.

There is no issue with the stored procedure because when I run the stored procedure directly from SQL Server management studio, it works.

This is normal operation, but there is no output (inserting data into table) from Stored Procedure even though it says 'Succeeded':

Bottom option is available in "Activity runs" mode (after job is done):
When I press "Rerun from activity" (on the Stored procedure step), stored procedure does work.

I am not sure where to test or fix on this issue.

Update 1/21/2022
This is working now as shown:

Eland209 1 Reputation point

2022-01-21T05:00:28.817+00:00

Hi @KingJava , Did you resolve this?

I am encountering the same issue in Synapse.

Thanks
Anonymous

2022-01-21T17:41:21.79+00:00

@Eland209
Sorry, I am not able to replicate the issue now.
It has been a while, and I think by looking at current pipeline, there is no issue..
It looks like I just add the name of sp and it runs fine.
Please see the updated post as I had to add an image there as I was not able to add on this comment section on the bottom (maybe b/c the post was closed)..
Montoya, Julian 1 Reputation point

2022-06-15T01:10:19.31+00:00

Hi @KingJava I'm having the same issue with a pipeline in Azure Data Factory too. Exactly the same problem exposed here.
Anonymous

2022-06-15T18:15:56.693+00:00

@Montoya, Julian
Honestly, I cannot recall how I fixed the issue. It has been a while.

As far as I know, there is too many processes into it after all.
I would double check whether previous step 'Copy data' has any issue (like Mapping).
Let me know if you want me to have a session with you.
Pratiksha Dubey (OSV) 1 Reputation point

2022-09-12T16:16:59.85+00:00

How to resolve this issue, can anyone help ?
Pratiksha Dubey (OSV) 1 Reputation point

2022-09-12T16:17:11.573+00:00

How to resolve this issue, can anyone help ?
Vigneswara, Darshan 6 Reputation points

2022-09-30T11:29:20.617+00:00

I had the exact same issue, but did manage to find a fix.

My exact situation was that I was running 2 SQL scripts directly from ADF (simple update) as steps then my Stored procedure few steps down.

After I moved both my SQL scripts inside the stored procedure and deleted them as steps from the pipeline, and now the Stored Procedure is working properly.

Have read in several places that ADF may not play friendly with multiple stored procedures & SQL scripts, this seems to have been the case for me.
Nanda G 0 Reputation points

2023-05-01T02:18:10.87+00:00

I am facing similar issue please let me know if it is fixed