ADF stored procedure executed multiple times

Question

ADF stored procedure executed multiple times

Matthew Pruett 96

I have a stored procedure activity in a pipeline that is triggered daily. This activity seems to be executing once, but for some reason the procedure itself executes multiple times and causing a deadlock.

The retry value is set to 0, so I am not sure what would be causing this behavior.

{
"name": "Erroring stored procedure",
"description": "Call a Stored Procedure",
"type": "SqlServerStoredProcedure",
"dependsOn": [
{
"activity": "ForEachProductionTable",
"dependencyConditions": [
"Succeeded"
]
}
],
"policy": {
"timeout": "7.00:00:00",
"retry": 0,
"retryIntervalInSeconds": 30,
"secureOutput": false,
"secureInput": false
},
"userProperties": [],
"typeProperties": {
"storedProcedureName": "[dbo].[MyStoredProcedure]"
},
"linkedServiceName": {
"referenceName": "<redacted>",
"type": "LinkedServiceReference"
}
}

ShaikMaheer-MSFT 29,886 Reputation points Microsoft Employee

2021-07-07T05:44:13.413+00:00
Hi @Matthew Pruett ,

Welcome to Microsoft Q&A Platform. Thank you for posting your query here.

Could you please check below once. And share updates to understand issue better. Thank you.

Is your Stored procedure activity inside for each activity?

If yes, Could you please check, how may items you have in array which you are passing to For each activity?
Matthew Pruett 96 Reputation points

2021-07-07T11:51:18.27+00:00

That's the odd thing. The stored procedure activity is not part of a foreach activity.
ShaikMaheer-MSFT 29,886 Reputation points Microsoft Employee

2021-07-09T09:56:43.433+00:00

Hi @Matthew Pruett ,

It would be great if you can share your entire pipeline json along with pipeline snippets to understand better and help on resolution. Thank you.
Princy Jaiswal 1 Reputation point

2021-07-22T14:38:08.697+00:00

I am facing the same issue, having one execute pipeline activity and inside that I have multiple stored procedures in series. these are getting executed multiple times

ShaikMaheer-MSFT 29,886 Reputation points Microsoft Employee

2021-07-07T05:44:13.413+00:00

Hi @Matthew Pruett ,

Welcome to Microsoft Q&A Platform. Thank you for posting your query here.

Could you please check below once. And share updates to understand issue better. Thank you.

Is your Stored procedure activity inside for each activity?

If yes, Could you please check, how may items you have in array which you are passing to For each activity?
Matthew Pruett 96 Reputation points

2021-07-07T11:51:18.27+00:00

That's the odd thing. The stored procedure activity is not part of a foreach activity.
ShaikMaheer-MSFT 29,886 Reputation points Microsoft Employee

2021-07-09T09:56:43.433+00:00

Hi @Matthew Pruett ,

It would be great if you can share your entire pipeline json along with pipeline snippets to understand better and help on resolution. Thank you.
Princy Jaiswal 1 Reputation point

2021-07-22T14:38:08.697+00:00

I am facing the same issue, having one execute pipeline activity and inside that I have multiple stored procedures in series. these are getting executed multiple times

Answer 1

Matthew Pruett 96

What ended up working for me was splitting the primary stored procedure up into multiple procedures. I don't think ADF handles very well the case of a single monolithic procedure that calls many other procedures.

ShaikMaheer-MSFT 29,886 Reputation points Microsoft Employee

2021-07-23T05:36:10.347+00:00

Hi @Matthew Pruett ,

Could you please accept below as answer? Accepting answer will help community too. Thank you.

Could you please share your entire pipeline json to try at our end too. Also, you mean your stored procedure internally calling many other store procedures too? Kindly confirm.
ShaikMaheer-MSFT 29,886 Reputation points Microsoft Employee

2021-07-27T06:19:06.803+00:00

Hi @Matthew Pruett ,

Could you please accept below as answer? Accepting answer will help community too. Thank you.

ADF stored procedure executed multiple times

0 additional answers

Activity