SData Core Specification

Documentation for the Sage SData Core Specification.

SQLOLEDB, SQLNCLI connection pooling issue‏

This is exactly the crazy problem I was running into yesterday, in processing a text-file import from a back-end ERP into SLX:

We have some VB6 code accessing MS SQL Server with ADODB using the SQLOLEDB provider which gives Timeout Expired errors when a large number of executes are performed against the same ADODB connection and when there is a recordset open on the same connection. The application opens a single ADODB connection at startup and closes it on shut down. I have determined the timeout error is based on the ConnectionTimeout value (set it low and it happens more quickly/often).

I tried using one ADODB connection for all cursors and another ADODB connection for all executes. This solved the timeouts however we use begintrans/open a recordset/run an execute based on the recordset contents/committrans sequence in our code so that doesn’t work.

I discovered the MS articles 258697 and 271128 which suggest that the problem is when a firehose cursor is used. Yes, our default cursor was a firehose, so I tried all combinations of the CursorType and LockType properties when opening the recordsets but no combination stopped the subsequent executes from using the same explicit connection.

I then discovered the MS article 907264 which suggests using the new (with SQL Server 2005) SQLNCLI provider. This provider supports multiple active result sets and so its behaviour is supposedly different firehose cursors. Initial testing with this provider suggested it did indeed solve the problem but performing a much larger number of executes caused varying failures. All recovered on retry but??? The errors seen were ‘Named Pipes Provider: No process is on the other end of the pipe.’ and ‘Failed to read prelogin response.’ Named pipes are turned off at the server and weren’t being used by the client app. It was almost as if the SQL Server had run out of TCP ports and so decided to fall back to named pipes. This was certainly a better result than that we were getting with SQLOLEDB but still not bulletproof.

When you’re iterating through a recordset with thousands (in my case, over 100,000) of values in it (e.g., PK ID’s, used to grab the rest of the info for each record, in turn), one of the things you can do is to throw the recordset into an array (e.g., via objRs.GetRows, or with a Do While … Loop) as soon as you get it back from the server, and then immediately close the objRs. For me, in addition to using the SQL Native client rather than the SLX OLEDB Provider, that appears to have fixed the issue.