2542 Connection xxx is busy 1

[MarkButler]

I have an application which has been running in production for about a year. The user has just uncovered a situation which causes the application to bomb with error "1541 - Connection XXX is Busy". What does this mean and is there a connection parameter or ODBC parameter which may cause this. The error is not localized to an individual PC or user. I can also force the error which is returned from a TABLEUPDATE(.F.,.T.) command after doing a replace statement on a remote view using a MS SQL table.

Can someone point me in the right direction. When run in debug all looks normal until the TABLEUPDATE() fails.

TIA
Mark

 

[MarkButler]

It is late at night and the fingers just ain't doing their thing. The above topic should state 1541 as the error number.

 

[Mike Lewis]

Mark,

This usually happens when more than one remote view is using the same connection. The easy way to avoid that is to turn off connection sharing. You do that in the Advanced Options dialogue in the view designer (for all views).

However, doing this will increase the number of connections to the server, which could in turn decrease performance. It might also be necessary for the administrator to increase the number of connections with the server will permit.

There are ways of avoiding that situation, but you should take one step at a time. Perhaps you could try the above suggestion and see if it solves the problem.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[fluteplr]

The most common reason for this to suddenly show up is the following.

You have the number of records to fetch at a time set to something other than all.

The number of records in the table has grown to more than that number.

So when you open the view it retrieves the first X records but since there are more than that in the table the connection stays busy until you either retrive the rest of the records or do a sqlcancel.

You have several options to fix this depending on what makes sense in your application. Fix the view parameter, code to sense when it has happend. Make sure you use sqlcancel when appropriate. My last choice would be to stop sharing the connection. Creating connections is very time consuming and can kill the performance of your app.

 

[MarkButler]

As it turns out the problem was as Fluteplr suggested. The view was set to a fetchsize of 50 records yet the database had many more records than that. Changing the fetchsize to -1 solved the problem. The problem is that the process issued a requery() on the view and started changing records as follows:

Replace x with y
=tableupdate(.f.,.t.)

Yet the view was still fetching more records (up to the maxrecords value). What made this hard to find is that when I was tracing through code in debug mode, I was slow enough for the requery() to have fetched all the records and the code would not fail. Originally the fetchsize was established to allow the screens/forms to return faster, rather than wait untill all records were retreived.

Thanx for your help