COMPREHENSIVE SOLUTION FOR SQL CONNECTION HANGING IN LOOPS
ROOT CAUSE:
The Microsoft.ACE.OLEDB.12.0 provider has known issues with connection pooling and resource management in repetitive operations, causing connections to hang after 2-3 iterations.
SOLUTION 1: IMPLEMENT ROBUST CONNECTION HANDLING (RECOMMENDED)
Modified Flow Structure:
1. Loop from 0 to 10
2. Set variable BasePath to Excel file path
3. **ADD: Try-Catch Block around database operations**
4. **ADD: Wait/Delay before connection attempt**
5. Open SQL connection with timeout
6. Execute SELECT * FROM [Sheet1$]
7. Execute UPDATE [Sheet1$] SET [Example] = 'In Progress' WHERE [Name] = 'Raj' + LoopIndex2
8. **ENSURE: Explicit Close SQL connection in Finally block**
9. **ADD: Additional wait after connection close**
10. **ADD: Error handling with retry logic**
Detailed Implementation:
STEP 1: Add Variables at beginning of flow
- ConnectionAttempts = 0
- MaxRetries = 3
- IsConnected = False
STEP 2: Modify your loop structure:
For each iteration:
-> Set ConnectionAttempts = 0
-> Set IsConnected = False
-> While (ConnectionAttempts < MaxRetries AND IsConnected = False):
-> Try:
-> Wait 2 seconds // Critical: Add delay before connection
-> Open SQL connection (with 30-second timeout)
-> Set IsConnected = True
-> Execute SELECT * FROM [Sheet1$]
-> Execute UPDATE [Sheet1$] SET [Example] = 'In Progress' WHERE [Name] = 'Raj' + LoopIndex2
-> Catch (any exception):
-> Set ConnectionAttempts = ConnectionAttempts + 1
-> Wait 5 seconds // Longer delay on error
-> Continue to next retry
-> Finally:
-> If connection is open: Close SQL connection
-> Wait 1 second // Critical: Add delay after close
-> If IsConnected = False after all retries:
-> Log error and either skip iteration or terminate flow
SOLUTION 2: CONNECTION STRING OPTIMIZATIONS
Replace your current connection string with:
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + BasePath + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";Connection Timeout=30;Command Timeout=30;Pooling=false;Persist Security Info=False"
Key additions:
- Connection Timeout=30
- Command Timeout=30
- Pooling=false (Critical - disables connection pooling)
- Persist Security Info=False
SOLUTION 3: ENVIRONMENT CONSISTENCY CHECKS
Ensure on ALL VMs:
1. Install Microsoft Access Database Engine 2016 Redistributable (64-bit)
Download from: https://www.microsoft.com/en-us/download/details.aspx?id=54920
2. Registry check - Run on each VM:
- Open Registry Editor
- Navigate to: HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Microsoft.ACE.OLEDB.12.0
- Verify entry exists
3. Architecture consistency:
- Ensure all VMs use same Office/Access Engine version (32-bit vs 64-bit)
- Power Automate Desktop must match the architecture
SOLUTION 4: ALTERNATIVE APPROACH - SINGLE CONNECTION
Instead of opening/closing in each iteration:
Before Loop:
-> Open SQL connection (outside the loop)
Inside Loop (iterations 0-10):
-> Execute SELECT * FROM [Sheet1$]
-> Execute UPDATE [Sheet1$] SET [Example] = 'In Progress' WHERE [Name] = 'Raj' + LoopIndex2
After Loop:
-> Close SQL connection
SOLUTION 5: EXCEL FILE LOCKING PREVENTION
Add these actions in each iteration:
1. Before opening connection: Check if file is locked
2. If locked: Wait 3 seconds and retry
3. After operations: Explicitly release file handles
SOLUTION 6: RESOURCE MONITORING
Add logging to track:
- Memory usage before each connection
- Connection time duration
- File handle count
- Process threads
SOLUTION 7: MACHINE-SPECIFIC TROUBLESHOOTING
For machines where it fails:
1. Check Windows Event Logs for OLE DB errors
2. Run Process Monitor during flow execution
3. Monitor file locks on Excel file
4. Check antivirus interference
5. Verify Windows updates are consistent across VMs
RECOMMENDED IMPLEMENTATION ORDER:
1. Implement Solution 1 (Robust connection handling) - This should fix 90% of cases
2. Apply Solution 2 (Connection string optimization)
3. Verify Solution 3 (Environment consistency)
4. If still failing, try Solution 4 (Single connection approach)
TESTING APPROACH:
1. Test on one problematic VM first
2. Gradually increase loop iterations (start with 5, then 10, then 15)
3. Monitor system resources during execution
4. Validate on all VMs before production deployment
If I have answered your question, please mark it as the preferred solution ✅ . If you like my response, please give it a Thumbs Up 👍.
Regards,
Riyaz
