Runtime error 3265: ＶＢ6 + ADO on Vista (2008)

[Hello Worker]

Dear,
I have a very difficult & strange problem.

On the application of VB6, when getting data from SQL Server by ADO, runtime
error 3265 sometimes occurs.
(3265: Item cannot be found in the collection corresponding to the requested
name or ordinal)
And, this problem occurred only in a specific environment.

<NG> Windows Vista (multi-core)
<NG> Windows Server 2008 (multi-core)
<OK> Windows XP
<OK> Windows Server 2003
<OK> Windows 7
<OK> Windows Vista (single-core)
<OK> Windows Server 2008 (single-core)
<OK> Windows Vista (multi-core) with XPSP2 Compatibility
<OK> Windows Server 2008 (multi-core) with XPSP2 Compatibility

The timing to occur is random (after 5 minutes, after 3 hours, etc,).
Moreover, it happens also to the timing which started other programs
accessed to SQL Server (eg. SQL Server Management Studio).

This application was running normally in Windows XP.
However, error occurs in Vista.
Now, it has avoided by [XPSP2 Compatibility] reluctantly...

I think that MDAC of Vista (2008) will be a criminal, maybe.
(Naturally, SP and KB of test environment are the newest.)

Tahnks.

'------------------------------------------------------------------------------
Public g_AdoConn As ADODB.Connection
Public Type tagFooTable
I00 As String
I01 As String
I02 As String
I03 As String
I04 As String
I05 As Long
I06 As Long
I07 As Long
I08 As Long
I09 As Long
End Type
Public g_FooRecords() As tagFooTable
Public g_FooRecordsCount As Long
'------------------------------------------------------------------------------
Public Sub Main()
Open_AdoConnection
Do
ReadFooTable
Loop
' Close_AdoConnection
End Sub
'------------------------------------------------------------------------------
Public Function ReadFooTable() As Long
Dim rs As ADODB.Recordset
Erase g_FooRecords
g_FooRecordsCount = 0
Set rs = ADOExecute("SELECT * FROM FOO")
Do While rs.EOF = False
ReDim Preserve g_FooRecords(g_FooRecordsCount)
With g_FooRecords(g_FooRecordsCount)
.I00$ = rs![item00] & ""
.I01$ = rs![item01] & ""
.I02$ = rs![item02] & ""
.I03$ = rs![item03] & ""
.I04$ = rs![item04] & ""
.I05 = rs![item05]
.I06 = rs![item06]
.I07 = rs![item07]
.I08 = rs![item08]
.I09 = rs![item09]
End With
g_FooRecordsCount = g_FooRecordsCount + 1
rs.MoveNext
Loop
rs.Close
ReadFooTable = g_FooRecordsCount
End Function
'------------------------------------------------------------------------------
Public Function ADOExecute(ByVal strQuery As String) As ADODB.Recordset
Dim rec As New ADODB.Recordset
Set rec = g_AdoConn.Execute(strQuery)
Set ADOExecute = rec
Set rec = Nothing
End Function
'------------------------------------------------------------------------------
Public Sub Open_AdoConnection()
Set g_AdoConn = New ADODB.Connection
g_AdoConn.ConnectionString = "Provider=SQLOLEDB.1;Data
Source=(local);Initial Catalog=FOODB;User Id=foo;Password=foo;"
g_AdoConn.CursorLocation = adUseClient
g_AdoConn.CommandTimeout = 0
g_AdoConn.Open
End Sub

[MikeD]

I see a couple things you're doing that I would not consider "good" but
these aren't probably the cause of the problem. However, I do see 2 thing
you're doing that *may* be the problem....but it's just a guess for each
one. I will point these out inline.

Post by Hello Worker
On the application of VB6, when getting data from SQL Server by ADO, runtime
error 3265 sometimes occurs.
(3265: Item cannot be found in the collection corresponding to the requested
name or ordinal)
And, this problem occurred only in a specific environment.
<NG> Windows Vista (multi-core)
<NG> Windows Server 2008 (multi-core)
<OK> Windows XP
<OK> Windows Server 2003
<OK> Windows 7
<OK> Windows Vista (single-core)
<OK> Windows Server 2008 (single-core)
<OK> Windows Vista (multi-core) with XPSP2 Compatibility
<OK> Windows Server 2008 (multi-core) with XPSP2 Compatibility
The timing to occur is random (after 5 minutes, after 3 hours, etc,).
Moreover, it happens also to the timing which started other programs
accessed to SQL Server (eg. SQL Server Management Studio).

Not exactly sure I understand what you mean here. Are you saying you don't
get this error consistently?

Post by Hello Worker
'------------------------------------------------------------------------------
Public Sub Main()
Open_AdoConnection
Do
ReadFooTable
Loop

I don't understand this loop. There's no condition for exiting the loop and
I don't see the purpose of the loop to begin with. The only thing I can
guess is that this program perpetually queries data and does something with
it which you're not showing.

Post by Hello Worker
'------------------------------------------------------------------------------
.I00$ = rs![item00] & ""
.I01$ = rs![item01] & ""
.I02$ = rs![item02] & ""
.I03$ = rs![item03] & ""
.I04$ = rs![item04] & ""
.I05 = rs![item05]
.I06 = rs![item06]
.I07 = rs![item07]
.I08 = rs![item08]
.I09 = rs![item09]

This "shortcut" you're taking might be the problem. Write these assignments
out fully:

.I00$ = rs.Fields("item00").Value & ""
etc.

It's possible this shortcut was eliminated with ADO in Vista and later.

Post by Hello Worker
End With
g_FooRecordsCount = g_FooRecordsCount + 1
rs.MoveNext
Loop
rs.Close
ReadFooTable = g_FooRecordsCount
End Function
'------------------------------------------------------------------------------
Public Function ADOExecute(ByVal strQuery As String) As ADODB.Recordset
Dim rec As New ADODB.Recordset

No reason to use the New keyword since you're assigning a "new" recordset
object returned by the Execute method below. You should never use New in
your object variable declarations. It can lead to problems if you're not
careful. For that matter, since your function is returning the recordset
object, there's no need for the local variable at all. Just assign the
Execute method's return value directly to your function. And that means you
can probably eliminate this ADOExecute function entirely since it'd only
have one line of code.

Post by Hello Worker
Set rec = g_AdoConn.Execute(strQuery)
Set ADOExecute = rec
Set rec = Nothing

Not hurting anything, but no reason to explicitly set this local variable to
Nothing.

Post by Hello Worker
End Function
'------------------------------------------------------------------------------
Public Sub Open_AdoConnection()
Set g_AdoConn = New ADODB.Connection
g_AdoConn.ConnectionString = "Provider=SQLOLEDB.1;Data
Source=(local);Initial Catalog=FOODB;User Id=foo;Password=foo;"

What version of SQL Server are you connecting to? It's best to use provider
for the version of SQL Server. The provider you're using is for SQL Server
2000. If you're connecting to SQL Server 2005 or SQL Server 2008, you could
have problems or you might only get limited functionality using the SQL
Server 2000 provider, and you're dependent on the backwards compatibility
components being installed. For SQL Server 2005, use "SQLNCLI" (or
"SQLNCLI.1"). For SQL Server 2008, use "SQLNCLI10". This might also be the
cause of the problem.

Try these suggestions and see if they solve the problem, which might be
caused by the recordset value shortcut you're taking or using the wrong
provider.

[Hello Worker]

Thank you for your reply.

Not exactly sure I understand what you mean here. Are you saying you don't
get this error consistently?

This error occurs at random.
This error starts the program and will occur in 5 minutes after.
Or, it may not occur until after 3 hours.

I don't understand this loop. There's no condition for exiting the loop and
I don't see the purpose of the loop to begin with. The only thing I can
guess is that this program perpetually queries data and does something with
it which you're not showing.
No reason to use the New keyword since you're assigning a "new" recordset
object returned by the Execute method below. You should never use New in
your object variable declarations. It can lead to problems if you're not
careful. For that matter, since your function is returning the recordset
object, there's no need for the local variable at all. Just assign the
Execute method's return value directly to your function. And that means you
can probably eliminate this ADOExecute function entirely since it'd only
have one line of code.
Not hurting anything, but no reason to explicitly set this local variable to
Nothing.

This source code was made in order to investigate an error only.
Since it does not know when this error occurs, it loop infinitely.
In actual application, an infinite loop is not exist and there is also no
definition of a mysterious variable.
From the first, The application was created for XP, and was used on XP.
The error had not occurred on XP at all.
When it checked whether this application would be run on Vista and later,
3265 errors sometimes occurred.
Moreover, the environment where the error occurs is only multi-core of
Server 2008 and Vista...
Why does not the error occur on 7?

This "shortcut" you're taking might be the problem. Write these assignments
.I00$ = rs.Fields("item00").Value & ""
etc.
It's possible this shortcut was eliminated with ADO in Vista and later.

This method was already tried. Regrettably the result was NG.
Besides this, I also tried cleanup processing and declaration of a variable.
A result is NG too.

What version of SQL Server are you connecting to? It's best to use provider
for the version of SQL Server. The provider you're using is for SQL Server
2000. If you're connecting to SQL Server 2005 or SQL Server 2008, you could
have problems or you might only get limited functionality using the SQL
Server 2000 provider, and you're dependent on the backwards compatibility
components being installed. For SQL Server 2005, use "SQLNCLI" (or
"SQLNCLI.1"). For SQL Server 2008, use "SQLNCLI10". This might also be the
cause of the problem.
Try these suggestions and see if they solve the problem, which might be
caused by the recordset value shortcut you're taking or using the wrong
provider.

SQL Server connected from the application on Vista is 2005 and 2008.
An error occurs both.
As a test, The connection string for SQL Server 2005 was changed into
"Provider=SQLNCLI; DataTypeCompatibility=80;".
Still, 3265 errors occur.


I note the following point.

* The error occurs only on Vista(2008) of multi-core.
* Set to [XPSP2 Compatibility], the error doesn't occur.
* The error doesn't occur on single-core.
* The error doesn't occur on XP and 7.

From this fact, I suspect whether a problem is in operation of the memory by
Common DLL (MDAC? msvbvm60.dll?).
In addition,If there is a lot of data, an error will occur easily.

Thanks.