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
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: https://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: https://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: https://leafe.com/archives
This message:
https://leafe.com/archives/byMID/003001d9f9e2$445c6010$cd152030$@compsys.co.uk
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
