Hi

Don't know if this upsize code will help anybody else but will share it. This has been run with SQL express 2008r2 Run the upsizing wizard to send the structure with no data. Run this on a backup of the fox data.

This uses Pauls CABuilder to generate CursorAdapters for the SQL database It then scans a given folder for all DBFs, opens the CA and sends the records to the ca. If the autoIdent is important then update the records one at a time with TableUpdate(.f.) and call the DBCC function to change the ID.

This code updates every 1000 rows with tableupdate(.t.)

Released as is with no warranties...use at your own risk....blah blah

Takes about 90 minutes to run on one of my bigger databases approx. 1m records

********************************** *** *** Instructions *** *** Sometimes the upsizing wizard is not sending data, just the structure *** *** Run this and set the nulls *** Copy the compdata to the upsize folder *** Run upsize wizard, if it doesn't error fine *** If it doesn't send the data come back here, run the copy upsize data *** It takes hours to do this manually so do it late at night. *** *** May need to regenerate the CAClassesSQL and DESQL in the project ***********************************

Clear Set Status Bar On

Set TABLEVALIDATE To 0

Set Multilocks On Set Exclusive On Set Deleted On Close Databases For i =1 To 255 Select (i) Use Next

Wait Window "Copy files to SQL Server Database" Nowait Noclear

Set Default To d:\app\Source

Set Multilocks On If Type("gcConnString")="U" gcConnString="Driver=SQL Server;server=sql;database=test;uid=admin;pwd=password;trusted_connection=no;" Set Classlib To d:\app\source\desql Additive && Data environment class with all the CAs Set Classlib To d:\app\Source\caclassessql Additive && CAs for SQL Endif

nHandle=Sqlstringconnect(gcConnString) odata=Createobject("deallfilessql") odata.OpenTables()

starttime=Time()

If Messagebox("Read file fox",36,"Confirm")=6

oldPath=Justpath(Locfile("*.dbc","DBC","Please locate original database")) Set Deleted On Set Default To &oldPath nNum=Adir(aFiles,"*.dbf") For N=1 To nNum DoEvents DoEvents DoEvents

cFile=aFiles[n,1]

cFile=Left(cFile,Len(cFile)-4) cSQL="delete from "+cFile nStat=EXECSQL(nHandle,cSQL) ca=cFile

? "Converting " +cFile+" to " + ca+" start time "+Left(Time(),5)

If PEMSTATUS(odata,ca,5) If Select(cFile)>0 Use In &cFile Endif Select 0

** SET STEP ON Use &cFile Alias old Go Top odata.&ca..lNoDataOnLoad=.F. odata.&ca..cSelectCMDFilter="f_rn=0" odata.&ca..cselectorderby="f_rn" odata.&ca..CursorFill()

If Select("qte_d")>0 Select &ca

Select old SCAN

Scatter Memvar Memo Select 0 * odata.&ca..cSelectCMDFilter="f_rn=0" * odata.&ca..CursorFill() Select old If Recno()/25=Int(Recno()/25) OR .t. Wait Clear Wait Window "Processing "+cFile+" "+Str(Recno())+" of "+Str(Reccount()) Nowait Noclear ** ?"Processing "+cFile+" "+Str(Recno())+" of "+Str(Reccount()) DoEvents Force DoEvents DoEvents Endif

Select &ca Append Blank Gather Memvar Fields Except f_rn Memo * EXECSQL(nHandle,"DBCC CHECKIDENT ('"+cFile+"',reseed,"+Alltrim(Str(m.f_rn-1))+")") * Tableupdate(.f.)

IF Recno()/1000=Int(Recno()/1000) =TABLEUPDATE(.t.) odata.&ca..cSelectCMDFilter="f_rn=0" odata.&ca..CursorFill() ENDIF

Select old Endscan Select &ca Tableupdate(.t.)

Use In old

Else Wait Window "Unable to open table "+ca ENDIF ? "End time "+TIME()

Else Wait Window "No CA for "+cFile Nowait Noclear

Endif If Select("old")>0 Use In old Endif

Next Endif ?

?? "Conversion started " ?? starttime ?? " ended at " ?? Time() ?

Endif

Function EXECSQL Lparameters nHandle,cSQL

nTimes=1 Do While .T. DoEvents Force

nStat=SQLEXEC(nHandle,cSQL) If nStat<>0 Exit Endif If nTimes>5 Wait Window cSQL+"("+Alltrim(Str(nTimes))+")" Nowait Noclear DoEvents Force Endif

If nTimes=100 Exit Endif nTimes=nTimes+1 DoEvents Force DoEvents Force

Enddo

Return .T.

Function setnull()

cPath=oldPath Set Default To &cPath numfiles=Adir(a,"*.dbf")

lPack=Messagebox("Pack the local tables first",36,"Confirm")=6 For N=1 To numfiles cDBF=a[n,1] Wait Window cDBF Nowait Noclear

If File(cDBF) cDBF=Strtran(cDBF,".DBF","",1,999,1)

Use &cDBF If lPack Wait Window cDBF+Chr(13)+Chr(10)+"Pack" Nowait Noclear Pack Endif numfields=Afields(b) Use For F=1 To numfields cfield=b[f,1] If Upper(cfield)="F_RN" Loop Endif

Wait Window cDBF+Chr(13)+Chr(10)+cfield+Chr(13)+Chr(10)+"Set Null" Nowait Noclear Alter Table &cDBF Alter &cfield Null Next Endif Next

Return