Bill - Great stuff here!!!

I can confirm that this code works. I just used it to do a test upload of 400,000 records to SQL Server 2008.

For some reason, the Sql Server Upsizing Wizard (Sedna version from VFPx), was erroring out on this particular table from my app. I could not solve the issue, so I found this code, and gave it a try, and it works fine.

I converted it to use a SqlStringConnect(), and I used string values for the configuration settings rather than pulling them from a lookup table as your original code did.

The only thing your code does not do is handle indexes, which I will manually do after the data is upsized to SQL Server.

I pasted my (slightly edited version) here: http://codepaste.net/1s3u97

Many thanks.

Other notes... I have seen that memo fields are best handled with a data type of VarChar(Max), rather then Text, starting in Sql Server 2005. Reference here: http://stackoverflow.com/questions/834788/using-varcharmax-vs-text-on-sql-server

One benefit of the Sql Server Upsizing wizard and its DBC requirement, is that it will handle Identity columns, longer field names, and it will add the indexes. There are probably other befits too. Here a details article on it at Code Magazine: http://www.code-magazine.com/Article.aspx?quickid=0703052







>>I have an application written in VFP 6.0 that is being rewritten in C# with SQL Server backend.
>>
>>I will need to migrate the VFP data to the SQL Server tables. I haven't written any VFP code in ~10 years so would like to inquire as to the best mechanisms to migrate the data.
>>
>>I am figuring the most straightforward way will be some VFP prgs with custom code iterating the VFP tables. If so, can anyone provide the basic commands and syntax to use?
>>
>>Thanks,
>>Bill
>
>Here's some code that I wrote a long time ago and have used dozens of times.
>It uses a table called convdata.dbf for names that vary.
>A nice feature is that you can direct it to create the SQL table if it doesn't already exist, or it will use an existing table.
>By convention, our identity columns are named idcol, so there's special logic for them.
>I wrote this because the upsizing wizards really sucked, and they required the tables to be in a DBC and most of mine were free tables.
>I've heard that the SEDNA version is better.
>
>Here's the code.
>
>
>

>CLOSE ALL
>CLEAR ALL
>SET SAFETY OFF
>SET ESCAPE ON
>SELECT 0
>USE convdata
>
>*-- SQL Connection name
>lc_user = "CONV"
>
>ll_cont = .T.
>
>
>*-- ODBC Connection
>gc_sqlconnect = ALLTRIM(convdata->sqlconn)
>gc_sqluser = ALLTRIM(convdata->sqluser)
>gc_sqlpassword = ALLTRIM(convdata->sqlpw)
>
>
>*-- Source Table
>lc_source = ALLTRIM(convdata->source)
>*-- Target SQL Table
>lc_target = ALLTRIM(convdata->target)
>*-- Target Database
>gc_database = ALLTRIM(convdata->sqldb)
>*-- Create indicator
>ll_createtarget = convdata->createtab
>*-- ZAP indicator
>ll_zaptarget =  convdata->zaptarg
>*--
>ll_displaytarget  = convdata->disptarg
>USE IN convdata
>
>SELECT 0
>USE &lc_source ALIAS a_source
>
>
>
>*-- Connect to SQL Server
>SET MESSAGE TO "connecting"
>gnsqlHandle = SQLCONNECT(gc_sqlconnect, gc_sqluser, gc_sqlpassword)
>
>= SQLSETPROP(gnsqlHandle, 'Transactions', 2)  && Manual transactions
>= SQLSETPROP(gnsqlHandle, 'asynchronous', .F.)
>= SQLPREPARE(gnsqlHandle, "USE " + gc_database)
>ln_exec = SQLEXEC(gnsqlHandle)
>
>IF ll_createtarget
>
>  *-- Drop the target table
>  = SQLPREPARE(gnsqlHandle, "DROP TABLE " + lc_target)
>  ln_exec = SQLEXEC(gnsqlHandle)
>  ln_commit = SQLCOMMIT(gnsqlHandle)  && Commit the changes
>
>  ld_datetime = DATETIME()
>  lc_time = TIME()
>
>  ll_cont = .T.
>
>  SELECT a_source
>  = AFIELDS(la_fields)
>
>  *-- Create the table if needed
>
>  lc_createcomm = "CREATE TABLE " + ALLTRIM(lc_target) +  "("
>  FOR x = 1 TO ALEN(la_fields,1)
>    *-- Add the commm separator if necessary
>    IF x <> 1
>      lc_createcomm = lc_createcomm + ","
>    ENDIF
>    *-- Field name
>    lc_createcomm = lc_createcomm + " " + LOWER(ALLTRIM(la_fields(x,1))) + " "
>    *-- Type
>    lc_type = la_fields(x,2)
>    DO CASE
>    CASE lc_type = "D"
>      * Datetime
>      lc_createcomm = lc_createcomm + " DATETIME "
>    CASE lc_type = "N"
>      * Numeric
>      lc_createcomm = lc_createcomm + " DECIMAL (" + ALLTRIM(STR(la_fields(x,3))) + "," + ALLTRIM(STR(la_fields(x,4))) + ")"
>    CASE lc_type = "B"
>      * Double
>      lc_createcomm = lc_createcomm + " FLOAT "
>    CASE lc_type = "Y"
>      * Currency
>      lc_createcomm = lc_createcomm + " MONEY "
>    CASE lc_type = "L"
>      * Logical
>      lc_createcomm = lc_createcomm + " BIT "
>    CASE lc_type = "M"
>      * Memo
>      lc_createcomm = lc_createcomm + " TEXT "
>    CASE lc_type = "I"
>      * Integer
>      lc_createcomm = lc_createcomm + " INT "
>    OTHERWISE
>      * Character
>      lc_createcomm = lc_createcomm + " CHAR (" + ALLTRIM(STR(la_fields(x,3)))+ ")"
>    ENDCASE
>    lc_createcomm = lc_createcomm + " " + " NULL" + CHR(13)
>  ENDFOR
>  lc_createcomm = lc_createcomm +  ")"
>  = SQLPREPARE(gnsqlHandle, lc_createcomm)
>  ll_cont = ln_execsql(gnsqlHandle)
>  IF ll_cont
>    ln_commit = SQLCOMMIT(gnsqlHandle)  && Commit the changes
>  ENDIF
>ENDIF  ll_createtarget
>
>IF ll_cont
>  *-- ZAP target if necessary
>  IF ll_zaptarget
>    *-- Zap the target table
>    = SQLPREPARE(gnsqlHandle, "DELETE FROM " + lc_target)
>    ll_cont = ln_execsql(gnsqlHandle)
>  ENDIF
>ENDIF ll_cont
>
>
>IF ll_cont
>
>  *-- Create the insertion command
>  lc_insertcommand = " INSERT INTO " + lc_target
>  lc_tostring = "("
>  lc_fromstring = " VALUES ("
>  SELECT a_source
>  = AFIELDS(la_fields)
>  FOR x = 1 TO ALEN(la_fields,1)
>    IF UPPER(ALLTRIM(la_fields(x,1))) <> "ID_COL"
>      lc_tostring = lc_tostring +  IIF(x =1, "", ", ") + LOWER(la_fields(x,1))
>      lc_fromstring = lc_fromstring + IIF(x =1, "", ", ") + " ?m." + la_fields(x,1)
>    ENDIF
>  ENDFOR
>  lc_tostring = lc_tostring + ")"
>  lc_fromstring = lc_fromstring + ")"
>  lc_insertcommand = lc_insertcommand + lc_tostring + lc_fromstring
>
>  SELECT a_source
>  GO TOP
>  SCAN WHILE NOT EOF()
>    SELECT a_source
>    SCATTER MEMVAR MEMO
>    = SQLPREPARE(gnsqlHandle, lc_insertcommand)
>    ll_cont = ln_execsql(gnsqlHandle)
>    IF NOT ll_cont
>      EXIT
>    ENDIF
>  ENDSCAN
>ENDIF ll_cont
>
>IF ll_cont
>  ln_commit = SQLCOMMIT(gnsqlHandle)  && Commit the changes
>  WAIT WINDOW "Conversion Complete"
>  IF ll_displaytarget
>    = SQLPREPARE(gnsqlHandle, "SELECT * FROM " + lc_target, "curtarget")
>    ln_exec = SQLEXEC(gnsqlHandle)
>    SELECT curtarget
>    GO TOP
>    BROW
> endif
>ENDIF ll_cont
> = SQLDISCONNECT(gnsqlHandle)
>
>FUNCTION ln_execsql
>*-- Execute SQL Command and display errors, if any
>PARAMETERS pc_ConnHandle
>PRIVATE ll_return, ln_exec,la_error
>ll_return = .T.
>ln_exec = SQLEXEC(pc_ConnHandle)
>IF ln_exec < 1
>  ll_return = .F.
>  WAIT WINDOW "SQL ERROR Has Occurred "
>  = AERROR(la_error)
>  WAIT WINDOW la_error(1,2)
>ENDIF
>RETURN ll_return
>
>
>
>