question

MatthewPruett-6544 avatar image
0 Votes"
MatthewPruett-6544 asked ShaikMaheer-MSFT commented

ADF stored procedure executed multiple times

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"
}
}

azure-data-factory
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @MatthewPruett-6544 ,

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?

0 Votes 0 ·

That's the odd thing. The stored procedure activity is not part of a foreach activity.

0 Votes 0 ·

Hi @MatthewPruett-6544 ,

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.

0 Votes 0 ·

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

0 Votes 0 ·

1 Answer

MatthewPruett-6544 avatar image
0 Votes"
MatthewPruett-6544 answered ShaikMaheer-MSFT commented

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @MatthewPruett-6544 ,

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.

0 Votes 0 ·

Hi @MatthewPruett-6544 ,

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

0 Votes 0 ·