>In the VCX, is the FetchSize property of the CA Base Class set to -1?
Yes it is

>Also make sure this is set at -1 in the CA objects in the DE of your form.
Already done

>Just a guess, but check to see what SQLGETPROP(0, 'Asynchronous') returns. If it returns .T., use SQLSETPROP(0, 'Asynchronous', .F.) before you open any connection. Also, the BatchMode property should be .T. [use SQLGETPROP/SETPROP to determine/set this].

SQLGETPROP(0, 'Asynchronous') returns .F.
BatchMode = .T.

>By using ZERO for the connection handle parameter, these properties and any others you may need will then be the 'default" whenever you open a new connection.

I am begining to suspect that this is a server or ODBC problem and that VFP is working correctly. Just that the status bar updates itself in increments of 100 and gives the impression that the FetchSize is 100. If I issue the select for the first time, it is slow and subsequent calls to the same select are fast.

I will let you know if I come across anything.

I just need you top confirm to me that FetchSize actually works and the setting is NOT being ignored.

Awaiting the release of the SCX of the CABuilder.

>
>I will also look at making the Builder UI into an SCX/SCT instead [or in addition to] of PRG based.
>
>>Hi Mark
>>
>>I have a huge problem. The CA property "FetchSize" with a default of 100 exists in the CA. I have tried setting this as in the Help file to -1 but it has NO effect on the fetch speed. The data still comes down in lots of 100.
>>
>>I have tested this on a local SQL table with > 50 000 records. My select will bring down about 2800 records for a report.
>>
>>When I run this cursor from a test form, I can see in the status bar rge number of records incrementing in 100 lots whatever I change the FetchSize to. This also happens with SPT.
>>
>>My code for the spt is similar to that in the CA:
>>oConn=sqlstringconnect([Driver={Sql Server};Server=localhost;Database=zest;Int Security;])
>>
>>csql= "select * from client where LEFT(clientno,3) = 'QJI'"
>>?SQLEXEC(oconn,csql,"myc")
>>
>>returns the cursor but in lots of 100.
>>
>>I tried issueing:
>>
>>CURSORSETPROP("FetchSize" ,-1)
>>and
>>CURSORSETPROP("FetchSize" ,3000)
>>
>>and even
>>CURSORSETPROP("FetchSize" ,-1,0)
>>
>>but I still get the data down in lots of 100. I have even SET RESOURCE OFF, changed the Options/RemoteData to select ALL and even tried Syncronous/asyncronous mode with the same result.
>>
>>I have tried this code on my work as well as home computer with the same result, both using XP and VFP8SP1.
>>
>>My SQLODBC driver is v2000.85.1022.00 dated 20/02/2003
>>
>>I am literally pulling my hair out in frustration as I cannot for the life of me find a way of getting the data down in one lot :(
>>
>>Even the MS Sample form in samples/solution/toledo/casample.scx displays the same behaviour.
>>
>>Please help as I am in the middle of a VFP>SQL2000 conversion and have based my dataaccess on CA's and SPT, both of which refuse to heed the FetchSize Property.
>>
>>Also I have found a way to get the CA's to work with transactions by setting the = SQLSETPROP(gnHandle, 'Transactions', 2), doing all my updates, doing a SQLCOMMIT or SQLROLLBACK (if failed) and finally
>>= SQLSETPROP(gnHandle, 'Transactions', 1). I have tested this and it works.
>>(this was a query I had addressed to you)
>>
>>Any help with my problem would be much appreciated.
>>
>>TIA
>>
>>Bernard
>>
>>ps. Glad I could be of help with the CABuilder and views.
>>pps. Is it possible that you have the CA builder as a FORm rather than a prg as I have some ideas and find it difficult to edit objects in a prg.:)
>>
>>
>>
>>
>>TIA
>>
>>Bernard