Our client has existing batch job with code to save sales invoice to PDF & then the PDF is sent in an email. The job was running perfectly since a long time. No errors we can see in the last 6 months.
However, since last week, the job gets stuck. We cannot debug on prod, but trying to figure out where the issue is coming from, we saw that the query to select records from SRSServers when running report goes to sleep on SQL server & then it further becomes a block to any other select statement executing after it. I have tried select statement on multiple reports, including on table that were previously not part of the class, but it results in the same outcome.
If we kill the SQL session which is on sleep, then the entire batch job fails with error:
“Cannot select a record in Vendors (VendTable). Name: 0.
Error accessing database connection.”
We tried running the same code in a job & it is working fine. We also tried running the class manually, with options set to called from, client and server & in all cases we face the same issue.
Any help on this would be appreciated.