>Hi Bernard.
>
>Thanks for sharing your experience. Some additional questions below.
>
>
>>--The only "problems" I had which I worked around is that since we use a single connection, sometimes VFP moves too fast when there are a number of CA's so the connection shows up as busy. You have to have a wait loop.
>
>Have you set the fetchsize to -1 and maxrecords to -1 ? If so, then it sound strange to find the connection busy (Bug?). What exactly is your workarround?

FetchSize is -1
MaxRecords is -1

When opening a form the CursorFill for each CA on a form (and there can be upto 20 for complex forms) was called and on certain forms if the CursorFill took a bit longer because of getting more data (lookups), I would get this error of connection busy.

To solve it I have a method called FillData that is called instead of CursorFill which actually calls CursorFill() and this is part of the code:

WITH This
	.lnodataonload = IIF(tcdataonload = "YES",.F.,.T.)
	DO WHILE SQLGETPROP(oConn,"ConnectBusy")
		IF INKEY(.25,"H") = 27
			EXIT
		ENDIF
	ENDDO
	IF !.CursorFill()
		AERROR(aEr)
		SQLROLLBACK(oConn)
                SQLSETPROP(oConn,"Transactions",1)
		MESSAGEBOX(aEr[2]+[: ]+This.Name,16,"Error",0)
		ThisForm.logsqlerror(this.alias)
		RETURN .F.
	ENDIF
ENDWITH

>
>>--You have to cater for SQLServer's page locking which sometimes causes blocking if a number of users update at the same time.
>
>I guess that is not really a problem of the cursor adapter, but rather of SQL server. So you have a way of detecting and resolving a deadlock? How is this specifically implemented?

I am not sure about this but based on advice from our DBA who created a sp_applock, that was called to lock off certain records. We still get some deadlocks especially when the number of users is high but these are managed manually.

>
>>--Also SQLServer will give an error if a field name is used that is a reserved word e.g.a field called PLAN
>>I solved this and catered to all such future additions of reserved words by enclosing ALL field names with square brackets [PLAN] works. Since I built this into the builder (Mark's) it was a case of change once and reap the rewards.
>
>I'm aware of that problem. We have unique fieldnames that have a three character prefix, so each field in the database has a unique name and does not clash with reserved words.
>
>>I still use the CA builder Mark did for VFP8 although we use VFP9
>>You can download the VFP8 one (the one I modified) from
>>http://www.foxite.com/downloads/default.aspx?id=161
>
>
>>and the VFP9 one is here:http://www.foxite.com/downloads/default.aspx?id=162
>
>Thanks
>
>>A Tip:
>>I have adapted the VFP8 one to use SQL Views as well as I hate having to construct SQL statements for different cases. I just create Views rather that complicated SQL statements. So while the database is normalised, I have views for the hard lifting so that I don't need complicated joins for them.
>
>Not sure whether I understand what you mean here.

The complete framework is data driven from VFP tables. So all settings for the CA like its name, the SELECT, FROM, WHERE, ORDERBY & KEYFIELD etc are fields in a VFP table that can be changed. The framework builds the select statement from these on the fly as needed for each form.

Rather that having complex Select statements in these vfp tables, the SQL Database is fully normalised but I have created SQLViews in SQLServer so that when I want data I just do a select * from myview
That way the Join A to B etc is internal to SQLServer and data is retrieved very fast.

For example in a view that is used often, there is this statement:

SELECT     dbo.sqlDebitInf.addeddate, dbo.sqlDebitInf.advpmt, dbo.sqlDebitInf.aerobic, dbo.sqlDebitInf.allacc, 
                      CASE WHEN dbo.sqldebitinf.upgradeamt > 0 THEN dbo.sqldebitinf.amount + dbo.sqldebitinf.upgradeamt ELSE dbo.sqldebitinf.amount END AS Amount, 
                      dbo.sqlDebitInf.aptotamt, dbo.sqlDebitInf.assesallow, dbo.sqlDebitInf.assesfee, 
                      CASE WHEN dbo.sqldebitinf.upgradeamt > 0 THEN dbo.sqldebitinf.balance + dbo.sqldebitinf.upgradeamt ELSE dbo.sqldebitinf.balance END AS balance,
                       dbo.sqlDebitInf.billed, dbo.sqlDebitInf.billingid, CASE WHEN dbo.sqldebitinf.upgradeamt <> 0 AND RIGHT(RTRIM(dbo.sqlcampaign.name), 3) 
                      = 'STD' THEN SUBSTRING(dbo.sqlcampaign.name, 1, LEN(RTRIM(dbo.sqlcampaign.name)) - 3) 
                      + '(PRM)' ELSE dbo.sqlcampaign.name END AS campaign, dbo.sqlCampaign.campaignid, dbo.sqlDebitInf.canceldte, dbo.sqlDebitInf.cancelled, 
                      dbo.sqlDebitInf.cardio, dbo.sqlDebitInf.cash1, dbo.sqlDebitInf.cash1date, dbo.sqlDebitInf.cash2, dbo.sqlDebitInf.cash2date, dbo.sqlDebitInf.cash3, 
                      dbo.sqlDebitInf.cash3date, dbo.sqlDebitInf.cashamt, dbo.sqlDebitInf.cashrecpt, dbo.sqlDebitInf.ccardamt, dbo.sqlDebitInf.chequeamt, 
                      dbo.sqlDebitInf.chqbank, dbo.sqlDebitInf.chqbranch, dbo.sqlDebitInf.chqdetail, dbo.sqlDebitInf.claimno, dbo.sqlDebitInf.clientno, 
                      dbo.sqlDebitInf.clrehablnk, dbo.sqlDebitInf.clubsinc, dbo.sqlDebitInf.comheld, dbo.sqlDebitInf.commencing, dbo.sqlDebitInf.comreason, 
                      dbo.sqlDebitInf.curexpiry, dbo.sqlDebitInf.curinstal, dbo.sqlDebitInf.curlstpay, dbo.sqlDebitInf.datestamp, dbo.sqlDebitInf.dc00, dbo.sqlDebitInf.dc99, 
                      dbo.sqlDebitInf.debitday, dbo.sqlDebitInf.debitinfid, dbo.sqlDebitInf.debref, dbo.sqlDebitInf.debtcoll, dbo.sqlDebitInf.descript, dbo.sqlDebitInf.discallow, 
                      dbo.sqlDebitInf.downpayby, dbo.sqlDebitInf.downpmt, dbo.sqlDebitInf.eftamt, dbo.sqlDebitInf.exgst, dbo.sqlDebitInf.expiry, dbo.sqlDebitInf.feedue, 
                      dbo.sqlDebitInf.firstinstal, dbo.sqlDebitInf.firstpay, dbo.sqlDebitInf.fitness, dbo.sqlDebitInf.freewght, 
                      CASE WHEN dbo.sqldebitinf.freezemax > dbo.sqldebitinf.freezeused THEN dbo.sqldebitinf.freezemax - dbo.sqldebitinf.freezeused ELSE 0 END AS freezeLEFT,
                       dbo.sqlDebitInf.freezemax, dbo.sqlDebitInf.freezeused, 
                      CASE WHEN dbo.sqldebitinf.freezemax < dbo.sqldebitinf.freezeused THEN - dbo.sqldebitinf.freezemax + dbo.sqldebitinf.freezeused ELSE 0 END AS freezexces,
                       dbo.sqlDebitInf.freetimeused, dbo.sqlDebitInf.gst, dbo.sqlGym.name AS gym, dbo.sqlDebitInf.gymid, dbo.sqlDebitInf.holdmail, 
                      dbo.sqlDebitInf.holdpay, dbo.sqlDebitInf.icontact, dbo.sqlDebitInf.include, dbo.sqlDebitInf.installmnt, dbo.sqlDebitInf.insuranceid, 
                      dbo.sqlDebitInf.iphone, dbo.sqlDebitInf.killpmt, dbo.sqlDebitInf.lifestyle, dbo.sqlDebitInf.machweight, dbo.sqlDebitInf.memcat, dbo.sqlDebitInf.memfee, 
                      dbo.sqlDebitInf.missed, dbo.sqlDebitInf.newmember, dbo.sqlDebitInf.notes, dbo.sqlDebitInf.numvis, dbo.sqlDebitInf.ok2golive, 
                      dbo.sqlDebitInf.ongoing, dbo.sqlDebitInf.other1, dbo.sqlDebitInf.other2, dbo.sqlDebitInf.other3, dbo.sqlDebitInf.otheramt, dbo.sqlDebitInf.otherfreq, 
                      dbo.sqlDebitInf.otherstr1, dbo.sqlDebitInf.otherstr2, dbo.sqlDebitInf.otherstr3, dbo.sqlDebitInf.outstandin, dbo.sqlDebitInf.overduecat, 
                      ISNULL(dbo.PmtFreqs.PayFreq, dbo.sqlDebitInf.payfreq) AS payfreq, dbo.sqlDebitInf.paymethod, dbo.sqlDebitInf.[plan], dbo.sqlDebitInf.pmtbalance, 
                      dbo.CurrPayDate.pmtdate, dbo.sqlDebitInf.pmtdue, dbo.sqlDebitInf.pmtlast, dbo.sqlDebitInf.program, dbo.sqlDebitInf.refund, dbo.sqlDebitInf.refundhow, 
                      dbo.sqlDebitInf.refundreas, dbo.sqlDebitInf.refundwho, dbo.sqlDebitInf.regfee, dbo.sqlDebitInf.regpaidby, dbo.sqlDebitInf.renewal, 
                      dbo.sqlDebitInf.reversflag, dbo.sqlDebitInf.sauna, dbo.sqlDebitInf.settlementdate, dbo.sqlDebitInf.spapool, dbo.sqlDebitInf.squash, 
                      dbo.sqlDebitInf.standard, dbo.sqlDebitInf.status, dbo.sqlDebitInf.subamount, dbo.sqlDebitInf.subb4reg, dbo.sqlDebitInf.subvarins, 
                      dbo.sqlDebitInf.supercct, dbo.sqlDebitInf.swimming, dbo.sqlDebitInf.swipecode, dbo.sqlDebitInf.tagsissued, dbo.sqlDebitInf.term, dbo.sqlDebitInf.termt, 
                      dbo.sqlDebitInf.termtot, dbo.sqlDebitInf.termwk, dbo.sqlDebitInf.tmavet, dbo.sqlDebitInf.trade, dbo.sqlDebitInf.tradeexpir, dbo.sqlDebitInf.tradesub, 
                      dbo.sqlDebitInf.tradeterm, dbo.sqlDebitInf.transfer, dbo.sqlDebitInf.transid, dbo.sqlDebitInf.upgradeamt, dbo.sqlDebitInf.varins, dbo.sqlDebitInf.vcard, 
                      dbo.sqlDebitInf.visleft, dbo.sqlDebitInf.whoupdate, dbo.sqlDebitInf.witness, dbo.sqlDebitInf.witnessdat, dbo.sqlDebitInf.workout, 
                      dbo.sqlDebitInf.corporateid, dbo.sqlCampaign.corporate, dbo.sqlDebitInf.presentedid, dbo.sqlDebitInf.transferid, dbo.sqlDebitInf.commpass, 
                      dbo.sqlDebitInf.commdate, dbo.sqlDebitInf.signed
FROM         dbo.sqlDebitInf LEFT OUTER JOIN
                      dbo.sqlGym ON dbo.sqlGym.gymid = dbo.sqlDebitInf.gymid INNER JOIN
                      dbo.sqlCampaign ON dbo.sqlDebitInf.campaignid = dbo.sqlCampaign.campaignid LEFT OUTER JOIN
                      dbo.CurrPayDate ON dbo.sqlDebitInf.debitinfid = dbo.CurrPayDate.invoiceno LEFT OUTER JOIN
                      dbo.PmtFreqs ON dbo.CurrPayDate.payfreq = dbo.PmtFreqs.ID

and in my Framework table I have "SELECT * from debitinf where debitinfid = 'XXXXXXXXX' "
This gets me the data I need as a single line and is very fast.

That is what I mean by using SQLViews and I have added support to use them in Mark's Builder in the link I posted.

Bernard