Connection name is busy vfp error 1541

Question

Hello All,

I am using sql server 2008rt express as back end. I faced the above error when I migrated my client's data (dbf files) to sql database through import and export wizard. First I copied each dbf table to .xls file and then through import and export wizard migrated to sql database. import and export wizard sucessfully imported the data without any error.

When run the app. all the tables is opening in app. except one. That one is hitting the above error. Tracing the error I found that just waiting a second or less, it works.

While input data through the app. works fine without error.

Any suggestions? 

Regards.

Tariq Iftikhar

Answer 1

Look at the message, it's rather a warning than an error, the connection is busy.

Seems you're using remote views and have a connection object in a DBC. Open that dbc and MODIFY DATABASE, right click, choose "Connections", in the connection list (most probably just having one connnection) click Modify. If "Asynchronous execution" is checked, uncheck this. You seem to overload the server with too much concurrent queries running. You can also higher or lower the paket size and see how that affects the runtime.

So some questions:

How is the connection set up?

How many tables do you open at once?

And how are your remote views defined? Do you simply select all data without filtering with any where clause?

SQLEXPRESS limits you, so if the problem stays, SQLEXPRESS may be inappropriate, even if the size limitation does not apply.

There are a few more settings you can do at runtime, DBSETPROP() allows you to set a WAITTIME, set this higher, so VFP does not look too early and too often, if a query finished. 

Bye, Olaf.

Answer 2

Thanks Olaf,

I am not using DBC. Database and tables creation is done by running a script through a MS Dos Batch file.

An open connection is set up in main.prg and the resulting handle is used throughout the app.

In some modules 10 to 15 tables are open. In some modules only 2-3 tables are open.

Error is coming when stock file is open. stock file contains 300 records.

I am using cursoradapter in DE. Fetching data for only current year.

In ca class of stock file, modifying fetchsize to -1, stopped the error.

Should i modify in all ca class, fetchsize to -1

Regards.

Tariq Iftikhar

Answer 3

Hm, it seemed to me Error 1541 only is related to Connection objects of dbcs and not connections made via sql(string)connect, as the error message specifies a connection name, and a sql(string)connect connection does not have a name, as it's not an object.

Ok, so using a conn handle you should also see what SQLGetProp(handle,"Asynchronous") is.

In regard to Fetchsize, well, depends. But -1 makes sure the CA is never idle keeping a connection busy, because only 100 of the records are fetched at max.

And to prevent the error you could also precheck SQLGetPRop(handle,"ConnectBusy") to see you're not running into trouble, before doing anything. Not using the visual DE, but programming in the DE.OpenTables() method (for example) or on form.load, pays to have that possibility to call such things in between instanciation of CAs or inbetween calling their CursorFill().

Bye, Olaf.

Answer 4

Hi Olaf

in DE opentabels(), SQLGetPRop(myhandle,"ConnectBusy") returns .f.

in the init() of form where i have intialized the ca of defferent tables, in one table sqlgetprop() returns .t.

can i set fetchsize to -1 for all the ca, any problem?

When the data will grow in sql tables what will be the seen?

Regards

Tariq Iftikhar

Answer 5

When the data grows in SQL Tables that can mean you query more data, and it will take longer and longer to load a CA, but you will only fetch all data matching the query.

So it's up to you to limit data queries with where clauses, but not limit the result by only fetching top 100 or so. Actually I have not tested, if that keeps connections busy.

What I can say from my customer using SQL Server for hundreds of users is, that fetchsize -1 works with so many users. I don't use anything but SQL to limit the query results, I don't use CAs which only fetch as needed and then reactivate, once you scan the result cursor, but I limit what I query anyway, one recipe, one test, one day, one result, maybe an array, but no user will work on all data at once anyway.

Bye, Olaf.

Answer 6

Thanks Olaf

The table in which the error is coming is having only 232 records. That is why i am surprising and thinking is there any mistake in my code.

Can you please tell me code so i loop in my ca baseclass until connection is not busy.

i have created ca baseclass through builder.

Regards.

Tariq Iftikhar

Answer 7

Can't help you with that. I can't imagine 232 records keep a connection busy. It's got to be some setting or repeatedly calling CursorFill() or CursorRefresh().

Bye, Olaf.

Answer 8

Thanks Olaf

You are helping me. After your reply i checked my ca base class. there was check on 'use Dataenvironemnt data source' to test i unchecked it and selected ODBC and then 'use connection string' and saved. Error stopped coming.is it correct?

I have also created in some tmp files ca programmetically. there the same connection busy error is comming. here is the code i have used to  create ca. connection handle is open throughout the program.

thisform.ca_tmpstk=CREATEOBJECT("CursorAdapter")
thisform.ca_tmpstk.DataSourceType = "ODBC"
thisform.ca_tmpstk.DataSource = myhandle

thisform.ca_tmpstk.Alias = "tmpstk"
thisform.ca_tmpstk.SelectCmd = "SELECT it.itemname,st.* FROM itmmst it,stktrn st where it.itemcode=st.itemcode order by 1"
thisform.ca_tmpstk.tables ="stktrn" 		
thisform.ca_tmpstk.keyfieldlist = "stockid"
thisform.ca_tmpstk.updatablefieldlist = "salerate,opnqty,qty" 
thisform.ca_tmpstk.updatenamelist = "stockid stktrn.stockid,salerate stktrn.salerate,opnqty stktrn.opnqty,qty stktrn.qty"
thisform.ca_tmpstk.buffermodeoverride = 5

IF !thisform.ca_tmpstk.CursorFill()
	LOCAL laError
	DIMENSION laError[1]
	AERROR(laError)
	MESSAGEBOX(laError[2]+"   ",0+16,"ICMS")
	thisform.Release()
	RETURN .f.
ENDIF
ENDPROC 

Regards.

Tariq Iftikhar

 

Answer 9

> thisform.ca_tmpstk=CREATEOBJECT("CursorAdapter")

You are not using "your" ca baseclass here, you are using the native CA class. And that does not connect by itself.

Having many CAs in a DE and all using the DE data source can make a connection busy, perhaps.

But using the same connection handle throughout all CAs is normal and I also do it that way and have no problems with busy connections.

If it works for now, it works.

The select commad in this sample code is just having a join condition of it and st in it's where clause and nothing more, are you sure you want to query all the data of tmmst and related data of stktrn?

I don't know how much data is in there, but reading in much data of course renders a connection busy.

Bye, Olaf.

Answer 10

Hello Olaf,

Sorry for so late in replying. I was very busy these days.

In DE i have created all the ca using my ca baseclass for those tables which i have to update with tableupdate and transactions. in addition i need many tables for which i have created ca using vfp native CA class except in above sample code.

Now the error is coming from those which i have created using vfp native CA class.

I could not reach to a conclusion so i used fetchsize = -1 which stopped the error.

Regards

Tariq Iftikhar