>>>HI
>>>Has anybody come across a tool that 'Blindly' copies across, and creates, a SQL Server database from a Foxpro database
>>>Don' t want to have to go into any wizards if possible
>>>This is something that is required on a nightly run, which takes VFP databses and creates SQL database
>>>Reason.....The VFP data needs to go to SQL server for use by a third party tool (which only uses SQL)
>>>
>>>Not too concerned if a Char field in VFP is transferred into a VarChar field etc, as long as all the data gets transferred
>>>
>>>Tia
>>>Gerard
>>
>>Do you mean a VFP table?
>>If yes, I have a VFP program that does that.
>
>Any chance I could see that program too ???

Sure, Victor

Here it its.

Note that is requires a convdata.dbf table that gives paths, DSN, etc, etc.

I've probably used this code or variations of it a couple of hundred times over the last 15 years, since I've converted many DBF apps to keep their data in SQL Server.

There are probably more elegant ways, but this has been handy because I could futz with it in special situations.

There's some code in there that is specific to some of my apps, but that shouldn't do any harm.

I have another one that does the reverse. I use it less frequently but it's a handy way to move data in a table from a server in one location to a server in another location.

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