>Here is some code I'm fighting with. I want to create or overwrite a table in a SQL Server database. I have a FoxPro (visual 5.0) database (xfile) that includes a connection to the SQL Server database (connection name camextest, SQL Server name CAMEX2000). Any ideas on how to get this to work? I am getting a syntax error on the SELECT statement as this code reads. It matches the syntax in the FoxPro help file.
>TIA!
>Tom.
>
>***************************
>LOCAL cSourceName, cDestName, nCols, cSQLFrom
>
>*-- Make sure we're in the correct directory.
>SET DEFAULT TO c:\camex
>
>*-- Open database.
>OPEN DATABASE xfile EXCLUSIVE
>
>*-- Open the file that contains all the data
>*-- about where to get data and where to put it.
>SELECT 0
>USE xfile
>SET ORDER TO TAG from_table
>GO TOP
>
>*-- Walk through the source file, record by record.
>DO WHILE !EOF()
>
> nCols = 0
> cSourceName = ALLT(xfile.from_table)
> cDestName = ALLT(xfile.to_table)
>
> *-- While the record has the same source table name,
> *-- process content into an array we can work with.
> SCAN WHILE xfile.from_table = cSourceName AND !DELETED()
> nCols = nCols + 1
> IF nCols = 1
> cSQLFrom = ALLT(from_field)
> ELSE
> cSQLFrom = cSQLFrom + ", " + ALLT(from_field)
> ENDIF
> ENDSCAN
> IF nCols > 0
>
> *-- Select the current destination and source tables.
> SELECT 0
> USE (cSourceName) SHARED
>
> *-- Add a record to the destination file for each
> *-- one in the source file.
> SELECT &cSQLFrom FROM &cSourceName WHERE !DELETED() INTO DATABASE CAMEX2000 NAME (cDestName)
> ENDIF
> SELECT xfile
>
> *-- Make sure the skip doesn't go beyond the end of file and
> *-- generate an error.
> IF !EOF()
> SKIP
> ENDIF
>ENDDO
>
>*-- Close the database.
>CLOSE DATA ALL

Tom,

You can't update a SQL Server Database with a VFP Select statement.
You have several option:

1) Use a SQL Server DTS package to do the import for you
2) Create the table in the SQL server database and then do the inserts in the SQL Server Table all using sql pass through (See SQLEXEC in VFP Help)
3) Create the table in the SQL Server Database using SPT, the create a VFP remote view pointing to that table. Then you can just 'Append' the records to the Remote view and 'Tableupdate' it.

This are the ones i'm remembering. May there's something else.