||Execution fail against sql server. Please contact SQL Server team if you need further support. Sql error number: 121. Error Message: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The sem

Question

Hi,

We have azure data factory pipeline and got error message below couple of times over week-end.

ADF pipeline calls azure sql database stored procedure which failed with the message - stored procedure just removes rows from table.

Intermittent and resolved itself but wondered as to best long term resolution to prevent - query only running 47 seconds before got his and no changes in months.

On Friday 19th and Saturday 20th July couple of times got intermittent ||Execution fail against sql server. Please contact SQL Server team if you need further support. Sql error number: 121. Error Message: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)

Know was issue Friday 19th globally but this intermittent.

Thanks

Answer 1

Based on this old thread :

Error 121 has always been considered a network related error as you can read in this Microsoft Support article. The Internet service you receive, network adapters are things you should consider examine. Please consider to click the "Options" button of SQL Server Management Studio, on the "Connection Properties" tab, try setting a greater value for the "Connection time-out" setting. If you need to use a Corporate VPN to connect to Azure SQL Database, make sure you are connected to the VPN.

More links :

https://learn.microsoft.com/en-us/answers/questions/1191911/azure-data-factory-sql-operationfailed-error-when?cid=kerryherger

Answer 2

As Amira says, this is likely be a network issue. The way to handle it is to have retry logic, so that the pipeline doesn't fail.

The other alternative would be that there is an internal error on the SQL Server side, but then it would not be equally transient. You say that you get this error after 45 seconds of execution. When this procedure terminates normally, for how long time does it run?

Answer 3

Thanks for info.

When you mention "some posts suggests that increasing the connection timeout" do you have example of where this actually set within Azure e.g. post mentioning this.

Is it configurable to us fact it is in cloud?