web service job runs Ok then suddenly lost connection

Question

web service job runs Ok then suddenly lost connection

Anne 281

We have a weekend job that runs a web service.
Most time is OK. but recently we found if the job runs over 6 hrs or more, the job may fail randomly.
with below message:
Status: CreateDriverDirectionsReport.ProcessRoutePath: [DBNETLIB][ConnectionWrite (send()).]General network error. Check your network documentation

We are sure there is no issues with network, and SQL server is online.
Tried to troubleshoot in all I can think of, but cannot find out reason.

Could you advice?
Also this happens in our test server, and production server randomly, the job usually runs 5 hours or at beginning of quarter 8-12 hours. It usually happens when it runs to over 6 hours or 8 hrs or more.

I can resume the job then it runs the rest of the processing without problem.
so it is not data issue.

Thanks

Duane Arnold 3,216 Reputation points

2021-05-17T06:56:44.813+00:00

Maybe you should look into using MS SQL Server Service Broker to run this long running process, which SB can do long running tasks. You send a message into the queue that the Service sees and in execute the .NET C# or VB.NET that SB can host or SB can call out the external .NET code.

https://www.sqlservercentral.com/articles/service-broker-part-1-service-broker-basics

IYou should also post to a Q&A tag for MS SQL Server, becuase the error message is database error message. Maybe someone there has seen it and knows of a possible solution.
Anne 281 Reputation points

2021-05-17T15:36:19.243+00:00

Thank you.
The long job runs one time at weekend is actually a very complicated SSIS package with different child packages, one of the step in the package is to call the web service. I don't know much about service broker and how it works with SSIS packages.
I will post this in SQL server to see if I can find any help there
Anonymous

2021-05-18T09:00:42.527+00:00

Hi @Anne ,

Whether the job using Asp.net core hosted services or another third-party package? Or it is a SQL Server Agent job? Besides, according to your description, if the issue relates SSIS, you could modify the question and add the "sql-server-integration-services" tag.

Your answer

Duane Arnold 3,216 Reputation points

2021-05-17T06:56:44.813+00:00

Maybe you should look into using MS SQL Server Service Broker to run this long running process, which SB can do long running tasks. You send a message into the queue that the Service sees and in execute the .NET C# or VB.NET that SB can host or SB can call out the external .NET code.

https://www.sqlservercentral.com/articles/service-broker-part-1-service-broker-basics

IYou should also post to a Q&A tag for MS SQL Server, becuase the error message is database error message. Maybe someone there has seen it and knows of a possible solution.
Anne 281 Reputation points

2021-05-17T15:36:19.243+00:00

Thank you.
The long job runs one time at weekend is actually a very complicated SSIS package with different child packages, one of the step in the package is to call the web service. I don't know much about service broker and how it works with SSIS packages.
I will post this in SQL server to see if I can find any help there
Anonymous

2021-05-18T09:00:42.527+00:00

Hi @Anne ,

Whether the job using Asp.net core hosted services or another third-party package? Or it is a SQL Server Agent job? Besides, according to your description, if the issue relates SSIS, you could modify the question and add the "sql-server-integration-services" tag.

Share via

web service job runs Ok then suddenly lost connection

Your answer