SQLEXEC stop during record fetching

[keithchansc]

I find a problem when I select all records from a large table, the process will stop randomly during record fetching (The record counter in the status bar stopped), but sometime it success. It seems only happen in my Notebook (ACER Aspire 6920) so far, but both VFP6 & VFP9 got the same problem.

My environment is:
- Windows XP SP3 (ODBC version 03.85.1132)
- ZoneAlarm version:7.0.483.000

Server side :
- SQL2000 / SQL2005

Any idea ?

 

[Olaf Doschke]

do you reach the 2GB limit? What are the connection settings? Does it happen after a certain time always (Timeout?)

Bye, Olaf.

 

[keithchansc]

About 2GB limit : For the whole DB it's over 2GB, but I am connecting to a SQL server, not MSDE. For each table, it's within 2 GB

For the Connection settings, you mean the SQL connect string, or the SQLSETPROP() settings ?

About Timeout, seems not, as the whole table can be retrieved within one to two minutes in case it success. And the mention problem may appear in a few seconds just after the record fetching start.

 

[JRB-Bldr]

"the process will stop randomly during record fetching"

When the process stops, is any error message displayed?

One issue that I have encountered if I am using SQL Pass Through is to have the SQL Server time out and not tell VFP. In that situation, no error message is displayed, but no records are returned which may or may not stop processing depending on the code's need for records.

In order to resolve that I had to change the QUERYTIMEOUT value of the VFP Database Connection to a larger value.

Good Luck,
JRB-Bldr

 

[keithchansc]

Mike,
Yes, the query is the same every time. I simply issue a "SELECT * FROM XXX"

JRB-Bldr,
There is a error message "Invalid cursor state", and then the SQL connection become disconnected.

Now I am tring to config a much larger PacketSize using SQLSETPROP(0,"PacketSize",1024*20), and it seems better now, my first test of 20 loops all passed. and I will keep on testing.
Do you think the problem may relate to buffer-overrun, in a fast CPU and fast network card ?
In case it is, then we may face the problem frequently in the future, although selecting a large set of records back into VFP is quite a rare case.