VB Forum / Database Access / February 2005

I have a number of applications that I have written in VB6 connecting to SQL
2000 or MSDE 2000 servers and all at one point or another seem to exhibit the
same issue.  My problem used to only occur at a few locations where the
application needed to run on a box other than the SQL server itself. however
I recently got a new HP laptopand I am experiencing the issue when my local
application attempts to connect to my local SQL server. Basically what
happens is as follows. I have a billing template detail table that will
potentially contain hundreds of thousands of detail records ( about 20,000
per month of billing history). I need to read through that table and create
invoices in our accounting software database. I need to read typically about
200-1200 records depending on the size of the given client and then create
records in about a dozen different tables in our accounting software to
create the invoices. What happens is that the system will open my clietn side
recordset then start processing the records. I use a variety of adodb
recordsets and .execute statements to retrieve, assign and set the values on
the records I am processing.  The recordset contains billing details for a
number of storesabout 2-7 records per store. The system processes through the
stores at about 2-3 stores per second (i.e. about 15-25 records per second)
but after a given number of records, varying from system to system where the
process is running (currently agout 400 on my laptop), the system seems to
hang when trying a process (either rst.open, rst.update or cnn.execute)
If I wait about 20 seconds then re-issue the same command the system returns
to processing throug the stores at the same speed as before. then after about
the same 400 records it hangs again, I wait 20 seconds and re-issue the
command and the system goes back to processing. The problem is not with the
code or the data as I can restore the database to a different SQL server and
run the code from my machine and the problem will not happen or it happens at
a different interval. I have managed to get around the error message by
setting the "connect timeout" property in my connection string to 300 and by
setting the connectiontimeout and commandtimout propertyon my
adodb.connection to 300. I am sure I only needed to do one or the other but
not all three to achieve my goal but I was desparate with frustration. I no
longer appear to get the error message but the system still stops about every
400 records and takes about a minute to get past that one record then starts
processing through the records at about 15-20 record/sec untill it has gone
through another 400. The bottom line is that the system seems to spen about
1/3 to 1/2 of it's processing time trying to recover from theis communication
delay. It seems like there is some threshold or buffer, in the number of
commands or bytes of data being sent to the SQL server, that I reach that the
SQL server then needs a few seconds to recoup from.
I have a second database that I use to copy data from an ODBC data source
into matching SQL tables. It maintains two ADODB connections one to the
Foreign ODBC source and the other to my SQL server. It opens a table on from
the ODBC source then parses through the table copying record by record into a
local table using creating an SQL insert into command that is executed with a
cnn.execute statement. then it closes the first tables and opens then next
and repeats the process. That program has about 10 tables that are coppied
and some contain upward of 18,000 records. This code exhibits the same
problem at about every 800-1400 records processed depending on the system it
is running on. what is interesting is that the number of records before
timeout are independant of the tables being processed. If my first table has
200 recods, my seconf has 1300 and my third has 1000 the system will crash at
records 400 and 1000 of table 2 and then again at records 300 and 900 of
table 3. so even though I have closed my adodb.recordsets and opened a new
set (even with rst = new adodb.recordset) if the system crashes every 600 and
it made it through 200 on the last recorset since the last timeout it will
only make it htrough 400 on the new recordset before the next timeout.

The numbers I am stating are just rough examples I have seen it hang every
411 or 1237 and it varies from program to program and system to system.

Is there anything I can do to try to help identify where the drop in
communication is coming from?

Is there any known bug with SQL server 2000 and VB6 SP5 or 6 or ADO that
causes SQL server to appear to pause.

Much of my current code was hardcoded to the old SQL server name and there
is to much code to change so I have either the SQL client or ODBC driver
setting up an alias so that when the program looks for the old server name it
goes straight to the new on. I don't believe this is the problem as this is
not the case with the second application i mentioned and it also exhibits the
problem.
In all the cases SQL is set up to communicate using TCP/IP and that is the
only network protocol installed on the workstations or servers.

I used to be running XP Pro SP1 on a toshiba laptop with vb6 sp5 and SQL
2000 sp3 I am now running XP Pro SP1 on an HP laptop with first vb6 sp5 then
sp6 and SQL 2000 sp3

Any help/guidance would be greatly appreciated.