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