Good :)
Since your question was about bulk insert failure I was concentrated on it. You might want to do it through DataTransformationServices or use VFP's upsizing wizard create some code for you :)
PS: If you go with bulk insert good news documented it's about twice as fast as command line BCP :)
Cetin

>Yes this is VFP 7.
>
>I understand exactly what you are saying Cetin however I think I found a another answer that takes care of a couple of other inssues I know that I am going to have (Skipping Fields). The other answer is a Format file. The whole app is table driven so I should have most of what I need to create a Format File on the fly, if not I will just add to data dictionary files. I just printed all the specs for a format file from Books Online.
>
>Thanks I may have other questions on this before I am done.
>
>>>I am in the process of writing a VFP app to migrate data from 75 VFP tables to the 75 corresponding SQL Server 2000 tables. All told around 15 million records 4 gig of data.
>>>
>>>So far app will create all tables on SQL and create the indexes for all tables on SQL. It will also COPY all of the VFP table data to txt files.
>>>
>>>The flow for the whole things is working very well (Indexes are created last after data is moved into SQL).
>>>
>>>After the tables are created then the app goes into a mode where for each VFP table:
>>>
>>>1). A "COPY TO (cSQLBCPFile) TYPE DELIMITED" is issued. cSQLBCPFile = [Location on SQL Server Hard Disk]
>>>
>>>2). An SPT - BULK INSERT Command is issued from the VFP app telling SQL Server to Bulk insert the ASCII file that was created in #1. At the moment I am using the command:
>>>
>>>cSQL = [BULK INSERT MCS.dbo.Activity ] + ;
>>> [FROM 'e:\SQLBCP\SQLBCP.txt' ]
>>>
>>>I keep getting the SQL error: Unexpected end-of-file...
>>>
>>>I have trimmed the ASCII file back to 1 column (1 field) and three records:
>>>
>>>51
>>>45
>>>69
>>>
>>>The first field or column in the SQL table is Numeric 5.
>>>
>>>I have tried both of these:
>>>
>>>cSQL = [BULK INSERT MCS.dbo.Activity ] + ;
>>> [FROM 'e:\SQLBCP\SQLBCP.txt' ] + ;
>>> [WITH (DATAFILETYPE = 'char') ]
>>>
>>>cSQL = [BULK INSERT MCS.dbo.Activity ] + ;
>>> [FROM 'e:\SQLBCP\SQLBCP.txt' ] + ;
>>> [WITH (DATAFILETYPE = 'native') ]
>>>
>>>Thanks in advance for advice.
>>>
>>>Ron
>>
>>Ron,
>>FieldTerminator and rowterminator are missing from 'bulk insert'. A delimited file has , as fieldterminator. RowTerminator defaults to newline so you wouldn't need it. Fieldterminator defaults to 'tab' character.
>>Hopefully you have VFP7, otherwise char fields would have dblquotes that you should clear somehow. With VFP7 :
>>
>>COPY to (cSQLBCPFile) TYPE DELIMITED WITH "" WITH TAB
>>
>>would give you a nice file that could be bulk inserted without specifiying field and row terminators :
>>
>>cSQL = [BULK INSERT MCS.dbo.Activity ] + ;
>> [FROM 'e:\SQLBCP\SQLBCP.txt' ] + ;
>> [WITH (DATAFILETYPE = 'char') ]
>>
>>Otherwise specify them. ie :
>>
>>cSQL = [BULK INSERT MCS.dbo.Activity ] + ;
>> [FROM 'e:\SQLBCP\SQLBCP.txt' ] + ;
>> [WITH (DATAFILETYPE = 'char', FIELDTERMINATOR = ',') ]
>>
>>Cetin