>I am trying to get a dynamically generated SQL insert or COPY TO command to execute. In both cases I get a syntax error. Also, in both cases, I can synthesize the commands in the command window and they run fine and return the epected result.
>
>An example is:
>COPY TO &cDestName FIELDS &cSQLFrom ALL FOR !DELETED()
>
>Where cDestName is obtained from a field name column in a source table and cSQLFrom is built from the same table.
>
>The source table layout is:
>source_name, source_field, dest_name, dest_field
>
>dest_name is usually a remote view name that is in the database container.
>
>The program is intended to allow the user to put the source and destination tables and fields in a table and fire off a routine that will build the destination files based in the source fields. It is a Visual Foxpro 5.0 front end and SQL Server back end.
>
>TIA!
>Tom.
Your above command works fine for me with macroized expressions. Are you positive about the contents of the variables?
You could also use:
SELECT &cSQLFrom FROM originaltable WHERE !DELETED() INTO TABLE (cDestName)
DELETED() will work in the WHERE clause as long as you are only pulling from one table in your SQL statement. Otherwise, just use SET DELETED ON before you do a SQL without a WHERE clause.