Losing connections with SQL 2005

We have a VB6 application that I recently converted over from SQL Server 2000
to SQL Server 2005. At numerous customer locations I am now experiencing
occasional "lost connection" problems. This never happened with SQL 2000.
Our client app does not use pooled connections and I have not been able to
find a clean way to recover from the broken connection problem. If I check
the status of the Connection it claims that it is open, it is only if I try
to make a database request that I get the error back. Re-opening the
connection solves the problem.
Is anyone aware of SQL Server 2005 doing anything different with respect to
keeping database connections open?
Thanks,
Intermittent connectivity issues are generally difficult to
troubleshoot. That being said...what do you define as a
"lost connection". What are the errors? What errors do the
clients receive? What errors are in the logs? Have you run
profiler traces to try to get more information on what may
be going on?
Does the application keep connections open for some reason
after processing a database request?
-Sue
On Fri, 9 Feb 2007 13:36:01 -0800, Darrel Miller
<DarrelMiller@.discussions.microsoft.com> wrote:

>We have a VB6 application that I recently converted over from SQL Server 2000
>to SQL Server 2005. At numerous customer locations I am now experiencing
>occasional "lost connection" problems. This never happened with SQL 2000.
>Our client app does not use pooled connections and I have not been able to
>find a clean way to recover from the broken connection problem. If I check
>the status of the Connection it claims that it is open, it is only if I try
>to make a database request that I get the error back. Re-opening the
>connection solves the problem.
>Is anyone aware of SQL Server 2005 doing anything different with respect to
>keeping database connections open?
>Thanks,
|||Hi Sue,
The error number reported is either 3604 or 16389 from Microsoft OLE DB
provider for SQL Server.
The best way I can describe "lost connection" is that you can no longer do
Connection.Execute or pass the connection object to Recordset.Open.
The behaviour is exactly as if you unplugged a network cable and then
replugged it. In fact I have been doing this to try and develop error
handling code to recover from the situtation.

> Does the application keep connections open for some reason
> after processing a database request?
As I mentioned our application does not use connection pooling and therefore
we obviously keep the connection open after a database request. Each client
application maintains two connections for the lifetime of the application.
One connection is a read-only connection, the other is a read write. Our
target customer is less than 50 users, so keeping a 100 connection objects
open is not a problem for the server.
My problem is that SQL 2005 seems to have much more fragile database
connections than SQL 2000 did. I was just wondering if there were some SQL
settings that could increase the robustness.
Thanks,
Darrel
"Sue Hoegemeier" wrote:
> Intermittent connectivity issues are generally difficult to
> troubleshoot. That being said...what do you define as a
> "lost connection". What are the errors? What errors do the
> clients receive? What errors are in the logs? Have you run
> profiler traces to try to get more information on what may
> be going on?
> Does the application keep connections open for some reason
> after processing a database request?
> -Sue
> On Fri, 9 Feb 2007 13:36:01 -0800, Darrel Miller
> <DarrelMiller@.discussions.microsoft.com> wrote:
>
>
|||On Tue, 13 Feb 2007 09:04:01 -0800, Darrel Miller
<DarrelMiller@.discussions.microsoft.com> wrote:

>Hi Sue,
>The error number reported is either 3604 or 16389 from Microsoft OLE DB
>provider for SQL Server.
>The best way I can describe "lost connection" is that you can no longer do
>Connection.Execute or pass the connection object to Recordset.Open.
Then that's an application issue or address from the
application end. Those are things you see in the app not in
SQL Server.

>As I mentioned our application does not use connection pooling and therefore
>we obviously keep the connection open after a database request.
That doesn't necessarily mean you would or should keep two
connections open for the lifetime of the application but
that's a whole different thing.

>My problem is that SQL 2005 seems to have much more fragile database
>connections than SQL 2000 did. I was just wondering if there were some SQL
>settings that could increase the robustness.
Part of the problem in try to address it from a SQL Server
end is that you can't really quantify it. It's not real
clear what data access changes may have happened with the
application either - did you also switch over to ADO.Net,
change MDAC versions, drivers/providers? You need to look at
network, OS, potential issues as well.
-Sue
|||Darrel, we have exactly this issue, but with a different front end language.
We converted in Nov 06 and immediately our remote clients began having
serious issues just as you describe. We reduced that somewhat by
implementing an ado connection object that "pings" periodically to keep the
connection alive, but in recent weeks the problem has gotten worse again.
I ran into Bill Vaughn at a user group meeting late last year and picked his
brain (or should I say "opened the tap"). The bottom line seemed to be: the
most effective solution is to upgrade the app to dotnet; changing to sql
native client can help somewhat; "pinging" can also help. Upgrading our app
is a really big task, and we haven't done it. We did the ping thing and the
native client thing, but as I said, those things alone are not the solution.
We opened a ticket with MS and spend hours in emails and on the phone with
overseas MS support people on this issue. They really didn't have a clue.
By the way, look at Bill's posting on 3/2/07 in which he says unequivocably
that for a win app, connecting and staying connected is a preferred
practice. Obviously Sue Hoegemeier does not agree.
My view is that MS is either unaware of, in denial about, or unwilling to
acknowledge and correct this issue.
Jeremy
"Darrel Miller" <DarrelMiller@.discussions.microsoft.com> wrote in message
news:46643664-0C8E-4C94-8DE4-4704518EAE8A@.microsoft.com...
> We have a VB6 application that I recently converted over from SQL Server
> 2000
> to SQL Server 2005. At numerous customer locations I am now experiencing
> occasional "lost connection" problems. This never happened with SQL 2000.
> Our client app does not use pooled connections and I have not been able to
> find a clean way to recover from the broken connection problem. If I
> check
> the status of the Connection it claims that it is open, it is only if I
> try
> to make a database request that I get the error back. Re-opening the
> connection solves the problem.
> Is anyone aware of SQL Server 2005 doing anything different with respect
> to
> keeping database connections open?
> Thanks,
>
|||Bill, I do appreciate your detailed and thoughtful response. However, our
application did not experience dropped connections under sql 2k (or they
were so insignificant no one bothered me about it), whereas despite our best
efforts, I see many error reports every day with sql 2k5. Seems quite clear
that the problem is dramatically worse with the new version.
Jeremy
"William (Bill) Vaughn" <billvaRemoveThis@.betav.com> wrote in message
news:OaM5uelbHHA.4808@.TK2MSFTNGP04.phx.gbl...
> Ahem... the ability to stay connected once connected has been a perennial
> issue. I can remember back to the earliest versions of SQL Server when
> connections "aged" out for some unknown reason. No, I don't know the
> actual reason, but symptoms lead me to believe that there is an ack/nak
> protocol in the TDS that basically pings the application at the driver
> level to see if the server or client is still there. This makes sense as
> SQL Server automatically detects if the client falls into the sea or gets
> disconnected for some reason. This polling has to be done on the server as
> it scans the current connections to see if there is activity (work to be
> done) or if there is still a client (albeit inactive) attached. I suspect
> that if the client is busy (running Age of Empires or Windows OneCare) and
> can't respond in time or the event is simply lost (as can happen in
> Windows message-loop-based applications) the server thinks the client has
> dropped off. My solution to this is to keep polling the server (once every
> 30 seconds or so seems to be more than enough) to keep the connection
> alive. Does the Connection pooling mechanism do this on its own? BHOM, but
> I would not be surprised. I have not seen evidence of this in the Profiler
> logs though. I suspect it's done at the TDS provider level. Since we don't
> have access to those APIs, one can constantly close and reopen the
> connection (use the Connection pool) or poll (which is pretty easy and
> faster as it does not require reauthenication, resetting the connection
> and loss of the server state). To poll you can execute any SQL like "USE
> <initial catalog>" or "DECLARE i as INT".
> I'm not sure I said that keeping a connection open is a "preferred"
> method, just a perfectly viable method--assuming you know the costs.
> hth
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speaker
> www.betav.com/blog/billva
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> __________________________________
> Visit www.hitchhikerguides.net to get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ------
> "Jeremy" <jeremy-nospam@.ninprodata.com> wrote in message
> news:uAzf37ibHHA.2088@.TK2MSFTNGP04.phx.gbl...
>
|||Ok, there are a lot of "fixes" in SS2K5 that might have had this unintended
side-effect. Have you installed the latest SP2? I think there is a SP2a (or
b).
I would log a bug with MS. The (more or less) monitor this group (but pay
more attention to the MSDN forums).
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"Jeremy" <jeremy-nospam@.ninprodata.com> wrote in message
news:Oa3%2321nbHHA.2300@.TK2MSFTNGP06.phx.gbl...
> Bill, I do appreciate your detailed and thoughtful response. However, our
> application did not experience dropped connections under sql 2k (or they
> were so insignificant no one bothered me about it), whereas despite our
> best efforts, I see many error reports every day with sql 2k5. Seems
> quite clear that the problem is dramatically worse with the new version.
> Jeremy
>
> "William (Bill) Vaughn" <billvaRemoveThis@.betav.com> wrote in message
> news:OaM5uelbHHA.4808@.TK2MSFTNGP04.phx.gbl...
>
sql

Losing connections with SQL 2005

We have a VB6 application that I recently converted over from SQL Server 200
0
to SQL Server 2005. At numerous customer locations I am now experiencing
occasional "lost connection" problems. This never happened with SQL 2000.
Our client app does not use pooled connections and I have not been able to
find a clean way to recover from the broken connection problem. If I check
the status of the Connection it claims that it is open, it is only if I try
to make a database request that I get the error back. Re-opening the
connection solves the problem.
Is anyone aware of SQL Server 2005 doing anything different with respect to
keeping database connections open?
Thanks,Intermittent connectivity issues are generally difficult to
troubleshoot. That being said...what do you define as a
"lost connection". What are the errors? What errors do the
clients receive? What errors are in the logs? Have you run
profiler traces to try to get more information on what may
be going on?
Does the application keep connections open for some reason
after processing a database request?
-Sue
On Fri, 9 Feb 2007 13:36:01 -0800, Darrel Miller
<DarrelMiller@.discussions.microsoft.com> wrote:

>We have a VB6 application that I recently converted over from SQL Server 20
00
>to SQL Server 2005. At numerous customer locations I am now experiencing
>occasional "lost connection" problems. This never happened with SQL 2000.
>Our client app does not use pooled connections and I have not been able to
>find a clean way to recover from the broken connection problem. If I check
>the status of the Connection it claims that it is open, it is only if I try
>to make a database request that I get the error back. Re-opening the
>connection solves the problem.
>Is anyone aware of SQL Server 2005 doing anything different with respect to
>keeping database connections open?
>Thanks,|||Hi Sue,
The error number reported is either 3604 or 16389 from Microsoft OLE DB
provider for SQL Server.
The best way I can describe "lost connection" is that you can no longer do
Connection.Execute or pass the connection object to Recordset.Open.
The behaviour is exactly as if you unplugged a network cable and then
replugged it. In fact I have been doing this to try and develop error
handling code to recover from the situtation.

> Does the application keep connections open for some reason
> after processing a database request?
As I mentioned our application does not use connection pooling and therefore
we obviously keep the connection open after a database request. Each client
application maintains two connections for the lifetime of the application.
One connection is a read-only connection, the other is a read write. Our
target customer is less than 50 users, so keeping a 100 connection objects
open is not a problem for the server.
My problem is that SQL 2005 seems to have much more fragile database
connections than SQL 2000 did. I was just wondering if there were some SQL
settings that could increase the robustness.
Thanks,
Darrel
"Sue Hoegemeier" wrote:
> Intermittent connectivity issues are generally difficult to
> troubleshoot. That being said...what do you define as a
> "lost connection". What are the errors? What errors do the
> clients receive? What errors are in the logs? Have you run
> profiler traces to try to get more information on what may
> be going on?
> Does the application keep connections open for some reason
> after processing a database request?
> -Sue
> On Fri, 9 Feb 2007 13:36:01 -0800, Darrel Miller
> <DarrelMiller@.discussions.microsoft.com> wrote:
>
>|||On Tue, 13 Feb 2007 09:04:01 -0800, Darrel Miller
<DarrelMiller@.discussions.microsoft.com> wrote:

>Hi Sue,
>The error number reported is either 3604 or 16389 from Microsoft OLE DB
>provider for SQL Server.
>The best way I can describe "lost connection" is that you can no longer do
>Connection.Execute or pass the connection object to Recordset.Open.
Then that's an application issue or address from the
application end. Those are things you see in the app not in
SQL Server.

>As I mentioned our application does not use connection pooling and therefor
e
>we obviously keep the connection open after a database request.
That doesn't necessarily mean you would or should keep two
connections open for the lifetime of the application but
that's a whole different thing.

>My problem is that SQL 2005 seems to have much more fragile database
>connections than SQL 2000 did. I was just wondering if there were some SQL
>settings that could increase the robustness.
Part of the problem in try to address it from a SQL Server
end is that you can't really quantify it. It's not real
clear what data access changes may have happened with the
application either - did you also switch over to ADO.Net,
change MDAC versions, drivers/providers? You need to look at
network, OS, potential issues as well.
-Sue|||Darrel, we have exactly this issue, but with a different front end language.
We converted in Nov 06 and immediately our remote clients began having
serious issues just as you describe. We reduced that somewhat by
implementing an ado connection object that "pings" periodically to keep the
connection alive, but in recent weeks the problem has gotten worse again.
I ran into Bill Vaughn at a user group meeting late last year and picked his
brain (or should I say "opened the tap"). The bottom line seemed to be: the
most effective solution is to upgrade the app to dotnet; changing to sql
native client can help somewhat; "pinging" can also help. Upgrading our app
is a really big task, and we haven't done it. We did the ping thing and the
native client thing, but as I said, those things alone are not the solution.
We opened a ticket with MS and spend hours in emails and on the phone with
overseas MS support people on this issue. They really didn't have a clue.
By the way, look at Bill's posting on 3/2/07 in which he says unequivocably
that for a win app, connecting and staying connected is a preferred
practice. Obviously Sue Hoegemeier does not agree.
My view is that MS is either unaware of, in denial about, or unwilling to
acknowledge and correct this issue.
Jeremy
"Darrel Miller" <DarrelMiller@.discussions.microsoft.com> wrote in message
news:46643664-0C8E-4C94-8DE4-4704518EAE8A@.microsoft.com...
> We have a VB6 application that I recently converted over from SQL Server
> 2000
> to SQL Server 2005. At numerous customer locations I am now experiencing
> occasional "lost connection" problems. This never happened with SQL 2000.
> Our client app does not use pooled connections and I have not been able to
> find a clean way to recover from the broken connection problem. If I
> check
> the status of the Connection it claims that it is open, it is only if I
> try
> to make a database request that I get the error back. Re-opening the
> connection solves the problem.
> Is anyone aware of SQL Server 2005 doing anything different with respect
> to
> keeping database connections open?
> Thanks,
>|||Ahem... the ability to stay connected once connected has been a perennial
issue. I can remember back to the earliest versions of SQL Server when
connections "aged" out for some unknown reason. No, I don't know the actual
reason, but symptoms lead me to believe that there is an ack/nak protocol in
the TDS that basically pings the application at the driver level to see if
the server or client is still there. This makes sense as SQL Server
automatically detects if the client falls into the sea or gets disconnected
for some reason. This polling has to be done on the server as it scans the
current connections to see if there is activity (work to be done) or if
there is still a client (albeit inactive) attached. I suspect that if the
client is busy (running Age of Empires or Windows OneCare) and can't respond
in time or the event is simply lost (as can happen in Windows
message-loop-based applications) the server thinks the client has dropped
off. My solution to this is to keep polling the server (once every 30
seconds or so seems to be more than enough) to keep the connection alive.
Does the Connection pooling mechanism do this on its own? BHOM, but I would
not be surprised. I have not seen evidence of this in the Profiler logs
though. I suspect it's done at the TDS provider level. Since we don't have
access to those APIs, one can constantly close and reopen the connection
(use the Connection pool) or poll (which is pretty easy and faster as it
does not require reauthenication, resetting the connection and loss of the
server state). To poll you can execute any SQL like "USE <initial catalog>"
or "DECLARE i as INT".
I'm not sure I said that keeping a connection open is a "preferred" method,
just a perfectly viable method--assuming you know the costs.
hth
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"Jeremy" <jeremy-nospam@.ninprodata.com> wrote in message
news:uAzf37ibHHA.2088@.TK2MSFTNGP04.phx.gbl...
> Darrel, we have exactly this issue, but with a different front end
> language. We converted in Nov 06 and immediately our remote clients began
> having serious issues just as you describe. We reduced that somewhat by
> implementing an ado connection object that "pings" periodically to keep
> the connection alive, but in recent weeks the problem has gotten worse
> again.
> I ran into Bill Vaughn at a user group meeting late last year and picked
> his brain (or should I say "opened the tap"). The bottom line seemed to
> be: the most effective solution is to upgrade the app to dotnet; changing
> to sql native client can help somewhat; "pinging" can also help.
> Upgrading our app is a really big task, and we haven't done it. We did
> the ping thing and the native client thing, but as I said, those things
> alone are not the solution. We opened a ticket with MS and spend hours in
> emails and on the phone with overseas MS support people on this issue.
> They really didn't have a clue.
> By the way, look at Bill's posting on 3/2/07 in which he says
> unequivocably that for a win app, connecting and staying connected is a
> preferred practice. Obviously Sue Hoegemeier does not agree.
> My view is that MS is either unaware of, in denial about, or unwilling to
> acknowledge and correct this issue.
> Jeremy
>
> "Darrel Miller" <DarrelMiller@.discussions.microsoft.com> wrote in message
> news:46643664-0C8E-4C94-8DE4-4704518EAE8A@.microsoft.com...
>|||Bill, I do appreciate your detailed and thoughtful response. However, our
application did not experience dropped connections under sql 2k (or they
were so insignificant no one bothered me about it), whereas despite our best
efforts, I see many error reports every day with sql 2k5. Seems quite clear
that the problem is dramatically worse with the new version.
Jeremy
"William (Bill) Vaughn" <billvaRemoveThis@.betav.com> wrote in message
news:OaM5uelbHHA.4808@.TK2MSFTNGP04.phx.gbl...
> Ahem... the ability to stay connected once connected has been a perennial
> issue. I can remember back to the earliest versions of SQL Server when
> connections "aged" out for some unknown reason. No, I don't know the
> actual reason, but symptoms lead me to believe that there is an ack/nak
> protocol in the TDS that basically pings the application at the driver
> level to see if the server or client is still there. This makes sense as
> SQL Server automatically detects if the client falls into the sea or gets
> disconnected for some reason. This polling has to be done on the server as
> it scans the current connections to see if there is activity (work to be
> done) or if there is still a client (albeit inactive) attached. I suspect
> that if the client is busy (running Age of Empires or Windows OneCare) and
> can't respond in time or the event is simply lost (as can happen in
> Windows message-loop-based applications) the server thinks the client has
> dropped off. My solution to this is to keep polling the server (once every
> 30 seconds or so seems to be more than enough) to keep the connection
> alive. Does the Connection pooling mechanism do this on its own? BHOM, but
> I would not be surprised. I have not seen evidence of this in the Profiler
> logs though. I suspect it's done at the TDS provider level. Since we don't
> have access to those APIs, one can constantly close and reopen the
> connection (use the Connection pool) or poll (which is pretty easy and
> faster as it does not require reauthenication, resetting the connection
> and loss of the server state). To poll you can execute any SQL like "USE
> <initial catalog>" or "DECLARE i as INT".
> I'm not sure I said that keeping a connection open is a "preferred"
> method, just a perfectly viable method--assuming you know the costs.
> hth
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speaker
> www.betav.com/blog/billva
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> __________________________________
> Visit www.hitchhikerguides.net to get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ----
---
> "Jeremy" <jeremy-nospam@.ninprodata.com> wrote in message
> news:uAzf37ibHHA.2088@.TK2MSFTNGP04.phx.gbl...
>|||Ok, there are a lot of "fixes" in SS2K5 that might have had this unintended
side-effect. Have you installed the latest SP2? I think there is a SP2a (or
b).
I would log a bug with MS. The (more or less) monitor this group (but pay
more attention to the MSDN forums).
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"Jeremy" <jeremy-nospam@.ninprodata.com> wrote in message
news:Oa3%2321nbHHA.2300@.TK2MSFTNGP06.phx.gbl...
> Bill, I do appreciate your detailed and thoughtful response. However, our
> application did not experience dropped connections under sql 2k (or they
> were so insignificant no one bothered me about it), whereas despite our
> best efforts, I see many error reports every day with sql 2k5. Seems
> quite clear that the problem is dramatically worse with the new version.
> Jeremy
>
> "William (Bill) Vaughn" <billvaRemoveThis@.betav.com> wrote in message
> news:OaM5uelbHHA.4808@.TK2MSFTNGP04.phx.gbl...
>

Loosing ODBC connection

Hi all,

We recently converted an application from accecc97 to access2003.
We used to have 2 databases : 1 with all and only the data (axelD.mbd), 1 with all the forms, query's, ... (axelP.mdb)

We replaced the data (axelD.mdb) with an MSDE database, through UPSIZE and so on. That all worked fine.
This database is placed on the dataserver (DATA1), the converted programm is on the FRONT2 server (FRONT1 stil usess access97 until conversion is totally ready).

We are able to connect to the MSDE by using ADODB recordsets.
We are also perfect able to link the MSDEtables in our axelP.mbd database

We started to test some heavier parts of our application and started to get the following error:

"ERROR 3151"
"ODBC--connection to 'JOROSOFT' failed"

Nothing more.

This happened in a quite complex routine, whitch updates a lot of records, several times and so on.
We could reproduce the same error when executing the following code:

Public Function TestBestellingen()
Dim sql As String
Dim intervalset As DAO.Recordset
Dim planset As DAO.Recordset
Dim tellerke

x = Opendatabases()
tellerke = 0
sql = "Select bestelnummer from vkpbestellijn where bestelnummer > 100000"
Set planset = Db_AxelP.OpenRecordset(sql, dbOpenDynaset)
Do While Not planset.EOF
tellerke = tellerke + 1
Debug.Print tellerke & " - " & planset!Bestelnummer & " --> " & IsKlantStof(planset!Bestelnummer)
planset.MoveNext
Loop
End Function

Function IsKlantStof(p_bestelnummer)
Dim sql As String, rst As DAO.Recordset
Dim hulp_klantstof As Boolean

hulp_klantstof = False
x = Opendatabases()
sql = "select klantstof from vkpbestellijn where bestelnummer = " & p_bestelnummer
Set rst = Db_AxelP.OpenRecordset(sql, dbOpenSnapshot)

With rst
Do While Not .EOF
If !KlantStof = True Then
hulp_klantstof = True
.MoveLast
End If
.MoveNext
Loop
End With
rst.Close
Set rst = Nothing
IsKlantStof = hulp_klantstof
End Function

Function Opendatabases()
If Db_AxelP Is Nothing Then
Set Db_AxelP = DBEngine(0)(0)
End If
End Function
I know this code makes no sence in a real-time environment, but it was to reproduce the error.
This code basically runs trough a 30.000 records and does a little check for certain fields
After 1981 records we get the above error.
(sometimes after 1979 or 1980 or 1982 records)

1977 - 100859 --> True
1978 - 100860 --> True
1979 - 100860 --> False
1980 - 100861 --> True
1981 - 100861 --> True

"vkpbestellijn" is a table that is linked in the AxelP.mdb trough an ODBC connection.
We get the same error (and after 1981 times) if I change the where to a totally other range of "bestelnummer"

I have installed the latest patches of Jet 4.0, MSDE-server, ...

I've been searching the internet for abour 3 days now, and just cant even find the smallest clue what could make this happen.

I hope someone here has an idea.
If you need more details, just ask.

tia,
Axel.

P.S. I'm from Belgium, so my english aint "correct to the point".MSDE auto closes when the last user disconnects.

ALTER DATABASE MyDB AUTO_CLOSE OFF might take care of this.|||I tried that, but it didn't changed a single thing.

The exact same problem still occurs. :eek: :eek:

Getting desperate here. :o|||Is there no one around who recognises the problem or came across that problem?

I've tried playing with the Query governor, but that didn't changed a single thing.

The strange thing is that when i try the same code on a identically server that is in use by users, i have it less frequent.
It looks like the MSDE server cant keep up with all the quesry's executed. And when the server has more users working, it has enough time to release some things.

does that makes sence top anyone?