INF: Differences Between BCP Format Files Versions 1.x and 4.2

• Microsoft SQL Server for OS/2, version 4.2
• Microsoft SQL Server version 4.2x

SUMMARY

As stated in Chapter 10 "Transferring Data to and from SQL Server" of the Microsoft SQL Server version 4.2 "System Administrator's Guide," format files for Bulk Copy Program (BCP) are not compatible between versions 1.x and 4.2. This article discusses how the structure of the format file has changed in version 4.2, thus enabling you to make the appropriate changes to existing 1.x format files if needed.

(NOTE: The above reference is on page 91 of the SQL Server for OS/2 4.20 and on page 210 of the SQL Server for Windows NT "System Administrator's Guide.")

MORE INFORMATION

There are two distinct changes made to the structure of the format file in version 4.2, which are as follows:

• The version number changed from 4.0 to 4.2. The first row in all format files represents the version of BCP that the format file was created for. Under SQL Server 1.x, BCP was based on Sybase 4.0 BCP, thus the format file would have 4.0 as a version number. Running BCP that ships with SQL Server 4.2 is now based on code written for SQL Server 4.2, which means the format file needs to have 4.2 as the version.



• The column delimiter changed from tabs to fixed length. Starting on line 3 in the format file, there is a row for each field in the data file. Each row in the format file has seven columns of information about the data file fields. In version 1.x, these columns are separated by a tab, and BCP parses the format file based on these tabs. In version 4.2, the columns are now fixed- length, and BCP now parses the format file based on these fixed lengths.
  
  A format file in version 1.x (<tab> represents a tab character):
  

    
        4.0
        2
        1<tab>SYBCHAR<tab>0<tab>14<tab>","<tab>1<tab>name
        2<tab>SYBINT<tab>0<tab>4<tab>"\r\n"<tab>age
   

  In version 4.2 ("." represents spaces):
  

  
        4.2
        2
        1.......SYBCHAR.......0.......30......",".......1........name
        2.......SYBINT........0.......4......."\r\n"....2........id
   

  The length of each column of information in the format file for BCP version 4.2 is:
  

  
        Host file column order:  8
        Host file datatype:      14
        Host file data length:   8
        Prefix length:           8
        Terminator:              10
        Server column number:    8
        Server column name:      \r\n
   

  (NOTE: The column name is terminated with a carriage return line feed.)

Additional query words: 4.20 1.10 Format FMT Windows NT

Keywords          : kbtool SSrvBCP 
Version           : 1.x 4.2
Platform          : 
Issue type        : 

Last Reviewed: March 13, 1999