INF: QS0431: Commonly Asked BCP Utility Questions in SQL

ID Number: Q67409

1.00 1.10

OS/2

======================================================================

QS0431: COMMONLY ASKED BCP UTILITY QUESTIONS

======================================================================

Revision Date: 7/91

--------------------------------------------------------------------

| INFORMATION PROVIDED IN THIS DOCUMENT AND ANY SOFTWARE THAT MAY |

| ACCOMPANY THIS DOCUMENT (collectively referred to as an |

| Application Note) IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY |

| KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO |

| THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A |

| PARTICULAR PURPOSE. The user assumes the entire risk as to the |

| accuracy and the use of this Application Note. This Application |

| Note may be copied and distributed subject to the following |

| conditions: 1) All text must be copied without modification and |

| all pages must be included; 2) If software is included, all files |

| on the disk(s) must be copied without modification [the MS-DOS̉ |

| utility DISKCOPY is appropriate for this purpose]; 3) All |

| components of this Application Note must be distributed together; |

| and 4) This Application Note may not be distributed for profit. |

| |

| Copyright 1991 Microsoft Corporation. All Rights Reserved. |

| Microsoft, MS-DOS, and the Microsoft logo are registered |

| trademarks and Windows is a trademark of Microsoft Corporation. |

--------------------------------------------------------------------

Introduction

------------

The Bulk Copy Program (BCP) is a utility for copying large amounts of

data into or out of SQL Server. BCP is mainly used to copy data from a

SQL Server for use with other programs (spreadsheets), to upload data

files from different databases, and to copy information from one SQL

Server to another SQL Server. Below is a list of commonly asked

questions and problems that users experience while using the BCP

utility.

Using the Bulk Copy Program

---------------------------

Q. Could you please explain how to use the BCP format file?

A. The BCP format file provides an alternative way to use the BCP

utility and adds flexibility to the program. The following example

of a format file illustrates the use of BCP on an ASCII data file

BCP Format File

---------------

4.1

4

1 sybchar 0 12 "" 1 f_name

2 sybchar 0 15 "" 2 l_name

3 sybchar 0 15 "," 3 city

4 sybchar 0 2 "\r\n" 4 state

where:

Row Description

--- -----------

1 Indicates the version number of BCP.

2 Represents the number of fields in the data file.

Column Description

------ -----------

1 Designates the field number of the data file.

2 Indicates the data type that is stored in the particular

field of the data file. For ASCII data files, use "sybchar";

and for native format data files, use the default data

types.

3 Indicates the prefix length, which is used to provide the

most compact file storage. For ASCII data files, use 0.

4 Represents the length of the particular data file field.

5 Represents the field terminator. Common field terminators

are commas and end of line characters (\r\n).

6 Indicates the column number of the table in the SQL Server

database.

7 Gives the name of the field taken from the SQL table. It is

not necessary to use the actual name of the field. However,

the only condition is that it cannot be blank.

Q. What is the difference between data in ASCII format and data in

native format, and how does the data format relate to BCP?

A. The information in BCP data files can be stored in two ways: ASCII

and native format.

In ASCII data files, the information is stored as ASCII characters.

This format is useful if the data will be used with another

program, such as a spreadsheet, or if the data is to be uploaded to

SQL Server from some other database. The BCP format file described

in question 1 is an example of a format file that could be used to

BCP ASCII data out of or into SQL Server. Note that all data types

are defined to be "sybchar" and have a prefix length of 0. The SQL

Server engine performs all the data type conversions.

The native format file contains the data's "native" data types as

defined in the SQL Server database. Native format proves to be most

useful when information is to be BCP'd out of one SQL Server and

into a different SQL Server. The use of the native format saves

time by eliminating the unnecessary conversion of data types into

and out of character format. The following is an example of a

native format BCP format file:

BCP Format File

---------------

4.1

5

1 sybchar 1 512 "" 1 discnttype

2 sybchar 1 512 "" 2 stor_id

3 sybint2 1 2 "" 3 lowqty

4 sybint2 1 2 "" 4 highqty

5 sybflt8 0 8 "" 5 discount

This particular format file was created by BCP'ing data out of the

"discount" table from the "pubs" database and taking all default

values for the fields.

Q. What is the proper way to use the field length column and the

delimiter column? How can they work with or against each other?

A. Data can be field delimited either by a character set or by a

specified fixed length. It can also be delimited using a

combination of both character set and fixed length. However,

caution must be taken. The BCP utility will take the first form of

the delimiter it finds. For example, if the field is comma

delimited and all elements in the field have at least 10

characters, and the length is set at 8, BCP will copy only the

first 8 characters of all elements in that field. This mistake will

most likely result in an error message from BCP. It is easiest to

use the default option of fixed-length terminators for native

format files.

Q. What is the best way to create a format file to BCP my ASCII data

into SQL Server?

A. To create a format file that is easily modified, start the BCP

utility by using the following command line:

bcp <db_name>.<owner>.<table> out <data_file> /S<server> /Usa /P<password>

When you start the program, a series of questions is asked. Choose

all the default options. When prompted to save the information in a

BCP format file, press Y, then ENTER. Then edit the format file to

have data types of "sybchar" and a prefix length of 0 (zero), and

enter the proper length and/or field terminators.

After this process is completed, run the BCP utility with the

/f <bcp.fmt> option. This option tells BCP to use the specified BCP

format file.

Q. Now that I know the structure and usage of the BCP format file, how

can I use it to ignore extra columns in my ASCII file?

A. If you have a data file that contains more fields than the table on

SQL Server, you can redirect these unneeded fields to a null field,

as in the following example, where the user wants to copy in the

fields "f_name", "l_name", and "state", but does not want to make

use of the "city" field that exists in the data file. The following

is an example of both the data file and the BCP format file:

ASCII Data File

---------------

Howard Jones San Jose,CA

William Smith Seattle,WA

Anthony Henry Portland,OR

BCP Format File

---------------

4.1

4

1 sybchar 0 10 "" 2 f_name

2 sybchar 0 10 "" 1 l_name

3 sybchar 0 15 "," 0 city

4 sybchar 0 2 "\r\n" 3 state

Please note that on the fifth line of the BCP format file, the SQL

Server table field designation (column 6) is 0 and the table field

name (column 7) still has some form of an entry. The use of 0 in

column 6 will allow any information between column 20 and the comma

in the data file to be placed in the null field device (the city

fields). The use of column 7 is to avoid a BCP syntax error and is

useful in self documentation.

Q. What are the most common problems seen when using BCP? What are the

symptoms? And how do you solve the problems?

A. The most common problems users have when using BCP fall into the

following categories:

* Trying to load native format.

Users often try to load an ASCII file using the SQL Server

native format. This results in all the hexadecimal values of the

ASCII file being misinterpreted. An "unexpected end of file"

error message is very common in this instance. The proper way to

load the ASCII file is to represent the fields as character

strings and to let SQL Server perform the data conversion.

* Using the default size of the data type.

Using the default size can sometimes lead to the same error as

above ("unexpected end of file"). This is most common with the

SQL Server money and date data types. For example, if the user

does not insert the entire date string, but only a fraction of

the entire date (that is, mm/dd/yy with no time), the default

size option reads 25 characters, the length of the date type.

The solution is to use delimiters or a user-defined, fixed-

length ASCII file.

* Hidden characters in the data file.

Often a hidden character in an ASCII data file can cause

problems when the BCP command is used. This problem usually

masks itself in an "unexpected null found" error. This character

usually can be found at the bottom of the file. Many utilities

display hidden characters; finding and removing this hidden

character should resolve the problem.

* Using spaces for delimiters in a data file.

Using the BCP command with the format file on a data file

delimited with spaces returns the following error message "dblib

error: column number out of range." To avoid this problem, run

BCP without a format file by using the -t option on the command

line. For example:

bcp dbase..table out file.out -SServer -Usa -P -r\r\n -c -t" "

When properly understood, BCP can be used to transfer large amounts of

data into or out of SQL Server. Using this application note and

Chapter 10 (pages 177-194) of the "Microsoft SQL Server System

Administrator's Guide" should help you use BCP successfully.