Usage for bcp

There are a number of considerations when using bcp.

  • bcp_r is a threaded version of bcp.

  • You cannot use named pipes to copy files in or out.

  • Using --hide-vcc improves performance because the SAP ASE server does not transfer and calculate data from virtual computed columns.

  • Although you can use any Transact-SQL command with --initstring as an initialization string for bcp, reset possible permanent changes to the server configuration after running bcp. You can, for example, reset changes in a separate isql session.

  • slice_number is included for backward compatibility with SAP ASE 12.5.x and earlier, and can be used only with round-robin partitioned tables.

  • Specify either partition_id or partition_name, but not both.

  • If you provide no partition name, bcp copies to the entire table.

  • You can specify multiple partitions and data files. Separate each partition name or data file with commas.

  • bcp provides a convenient and high-speed method for transferring data between a database table or view and an operating system file. bcp can read or write files in a wide variety of formats. When copying in from a file, bcp inserts data into an existing database table; when copying out to a file, bcp overwrites any previous contents of the file.

  • Upon completion, bcp informs you of the number of rows of data successfully copied, the total time the copy took, the average amount of time in milliseconds that it took to copy one row, and the number of rows copied per second.

  • bcp does not insert any row that contains an entry exceeding the character length of the corresponding target table column. For example, bcp does not insert a row with a field of 300 bytes into a table with a character-column length of 256 bytes. Instead, bcp reports a conversion error and skips the row. bcp does not insert truncated data into the table. The conversion error is as follows:
    cs_convert: cslib user api layer: common library
    error: The result is truncated because the 
    conversion/operation resulted in overflow
    To keep track of data that violates length requirements, run bcp with the -e log-file name option. bcp records the row and the column number of the rejected data, the error message, and the data in the log file you specify.
    To restrict the functionality of bcp to that of a previous version, set the CS_BEHAVIOR property in the [bcp] section of the ocs.cfg file:
    [bcp]
    CS_BEHAVIOR = CS_BEHAVIOR_100
    If CS_BEHAVIOR is not set to CS_BEHAVIOR_100, you can use functionality for bcp 11.1 and later.
  • If bcp is invoked and no value is supplied for the -c, -f, or -n parameters, a bcp prompt requests the file storage type. The file storage type can be any valid SAP ASE datatype. Storage types for the bigdatetime and bigtime SAP ASE datatypes are specified as:

    Storage Type

    Table Datatype

    A

    bigdatetime

    B

    bigtime

  • You can specify these datatypes for a bcp format file using the bigdatetime or bigtime datatypes:

    Storage Format

    SAP ASE Datatype

    SYBBIGDATETIME

    bigdatetime

    SYBBIGTIME

    bigtime

  • Use bcp to copy encrypted data in and out of the server.

  • bcp versions 15.7 and later allow you to copy data into tables that contain nonmaterialized columns.

  • Error message format is different than in versions of bcp earlier than 15.7. If you have scripts that perform routines based on the values of these messages you may need to rewrite them, for example:
    The display message that indicates the number of rows
    transferred has been changed. During a session, this 
    version of bcp periodically reports a running total 
    of rows transferred. This message replaces the "1000 
    rows transferred" message displayed by the previous 
    bcp.
  • When using bcp out:
    • If partition_name and datafile are both specified, then either datafile must specify a single data file, or specify a one-to-one mapping between partition names and data files.

    • If datafile is not specified, data from each partition is copied to a file named for the named partition with a .dat extension. For example, if the partition name is ptn1, the data file is ptn1.dat.

  • You may use initstring to run any Transact-SQL command, but you must reset any permanent changes to the server initstring causes after bcp finishes. For instance, as in the example for the password (-p) parameter, if the SAP ASE account does not have the appropriate permissions, the SAP ASE server returns an error message for the initialization string. bcp displays the server error message and stops before any data is transferred.

    Result sets issued by the initialization string are silently ignored unless an error occurs.

  • When using bcp in, if partition_name is specified, datafile must specify a corresponding number of data files

  • If you see a message similar to the following, the character translation file specified with the -q parameter is missing, or you mistyped the name:
    Error in attempting to load a view of translation tables.
See also:
  • Open Client™ and Open Server™ Configuration Guide – the description for libtcl.cfg for security mechanism names
  • Performance and Tuning Guide – on how changing certain parameters can affect bcp for large batches
  • Reference Manual: Commandsinsert
  • Reference Manual: Proceduressp_audit, sp_dboption, sp_displayaudit
  • System Administration Guide – character sets and associated flags