>Thanks,
>
>> creating a schema than can be picked up from VFP using XMLTOCURSOR()
>I cannot because it's not VFP but Foxpro-DOS :-)
>That's the reason why I would like to do a service on SQL Server.

Marc,

The good news is that this can be done! The bad news is that it will be a little complicated. The idea is to create a FoxPro command file that has the CREATE TABLE command that matches the structure of the SQL Server table and a second command to import a CSV or fixed field file into the table.

You will have to construct a SQL Server query that builds up the column list portion of CREATE TABLE by querying information_schema.columns.

E.g.:

declare @columnlist varchar(4000)
set @columnlist = ''

select @columnlist = @columnlist + ',' + i.column_name + ' ' + 
case i.data_type
when 'char' then 'char'
when 'varchar' then 'char'
when 'int' then 'numeric'
when 'real' then 'real'
when 'decimal' then 'numeric'
when 'tinyint' then 'numeric'
when 'money' then 'float'
else i.data_type
end + when 'char' then '(' + str(i.character_maximum_length) + ')'
when 'varchar' then '(' + str(i.character_maximum_length) + ')'
when 'int' then '(12)'
when 'bigint' then '(21)'
when 'decimal' then '(' + str(i.numeric_scale) + ',' + str(i.numeric_precision) + ')'
when 'tinyint' then '(6)'
when 'money' then 'float'
from information_schema.columns
where table_name = 'MyTable'

set @columnlist = right(@columnlist, len(@columnlist)-1) -- remove first comma

Once you have the column list squared away, prepend the 'CREATE TABLE' portion and append the closing punctuation.

Now append the foxpro command to import the data from a text file (csv or fixed-field - whichever you choose).

Next you have to use this query in a DTS package that saves the result to the command file (it's a plain text file with a cmd file extension I think).

Finally, in the same DTS package you can export the data to a text file and then launch foxpro and pass the command filename as the first argument. This tells foxpro to execute the file as a command file.

Viola! You have a table and the data.

Good Luck!