I have a workflow that executes SQL to see if a flag is 1 or 0. If it is 0, it needs to keep checking every 5 minutes until it is 1. I have put this execute SQL action inside a loop.
The first time the loop is entered and the execute SQL action is executed, it works fine. Every subsequent loop of the execute SQL action results in the following error:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
I cannot work out why. It should surely work all the time, or none of the time. I can see this was posted a while back and there was never an accepted answer. I need an answer in the next 24 hours as this is preventing me put my solution into UAT so am desperate for an answer or some pointers on where to look. @andrewg do you have any ideas please?
Update to this:
If i use SQL authentication instead of windows it works fine. I think it might be to do with the workflow being passed to the timer service during the loop. More investigation in progress.