I tried to trim the problem down to one column one table and ask a focused question.

I looked at DTS. I need total control or as much as I can have, there are quite a few data issues (different issues per table, not all tables. Key fields being right aligned or padded ' ' left) overall so I went the SPT route.

Looked at upsizing but felt there were the usual wizard problems. Wizards are usually not that, Wizard. Should be called "First Attempt there will be others" at something.

>PS: If you go with bulk insert good news documented it's about twice as >fast as command line BCP :)

Very Very good news. I was under the impression that BCP and BULK INSERT were two different access points for the same SQL Interanal API's







>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