The information in this article applies to:
- Microsoft SQL Server versions 1.x and 4.2 for OS/2
- Microsoft SQL Server version 4.2
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.)
|