>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