rs.MoveNext triggers "Operation is not allowed when the object is closed"

Wait, please don't ignore this one ... I've already tried all the obvious stuff.

I am currently on a contract assignment, and so cannot post the exact code (at least not at this time), but I should be able to layout the problem enough anyway.

I have a section of code that hits a remote database (same network, same subnet, but different city than the primary SQL database) to retrieve information logged by a digital phone system for display on an intranet screen.

The code is your typical recordset iteration, and looks basically like this.

Note: the connection is created and opened inside the PhoneCenter class. SQL statement used has been verified correct in SQL Server Management Studio.

<snip>

....

Dim rs as adodb.recordset = objPhoneCenter.Conn.execute(SQL)

If rs.recordcount > 0 then

while not rs.eof

.... Data processed here

rs.MoveNext

end while

end if

....

</snip>

This code runs flawlessly, every time, on the development platform but fails at "rs.MoveNext" when pushed out to the production server. What mystifies me is that the "Operation is not allowed when the object is closed" should not occur on code inside an rs.eof test, since the recordset must be open and have data in order to enter such a block.

This is, however, exactly what is happening. In 7 years working with SQL Server and ADO, I've never seen anything quite like this. Does anyone have any insight into what might be going on here?

P.S. I am in the process of attempting to gather more detailed information about this error, which I will post as it arrives if needed. At the moment, the above message is all I have to go on.

This is a wild guess since the information is not sufficient, but could you try specifying the cursor location like
objPhoneCenter.Conn = ADODB.CursorLocationEnum.adUseClient

and see if there are any changes in behavior?|||

Ok, I didn't provide much info, I'll grant that. The connection is already using the above cursor. What other information would you like to see to make a more educated guess?

The current connection is defined by the following lines of code:

objPhoneCenter = New ADODB.Connection

objPhoneCenter.CursorLocation = ADODB.CursorLocationEnum.adUseClient

objPhoneCenter.Open("Provider=SQLOLEDB.1;Persist Security Info=True;Password=" & password & ";" & _

"User ID=" & username & ";Data Source=" & servername & ";" & _

"Network Library=dbmssocn;Initial Catalog=" & database & ";")

objPhoneCenter.IsolationLevel = ADODB.IsolationLevelEnum.adXactReadUncommitted

objPhoneCenter.CommandTimeout = 300

Servername is the actual machine name, not an IP address, and is resolved by an internal DNS server.

Does this help any?