DOBCP

DOBCP bulks in or out all tables in a database, using native format (the default) or a pre-defined character format for the BCP files. DOBCP does not use any BCP API; rather DOBCP is just a wrapper on command-line BCP.

Contents:
    Command-Line Syntax
    Files etc
    Using Format Files
    Format of BCP Files

Command-Line Syntax

dobcp [-Server server] [-User user] [-Password pwd] -database db
      [-[no]fmtfile] [-[no]native] [-[no]unicode] in | out

`-Server`	The server on which the database resides. Default is local server.
`-User`	Which user to run as. Default value is sa.
`-Password`	Password for user. If you do not specify `-Password`, DOBCP will use Windows authentication. Specify `-Password ""` if you have a blank password (which you should not).
`-database`	Which database to bulk in/out from. You must specify `-database`.
`-fmtfile`	When bulking out and `-fmtfile` is in effect, DOBCP will first run BCP with the `format` option to generate format files, according to the combination of `-native` and `-unicode` you have selected. DOBCP will then use these format files when bulking out the files. When bulking in and `-fmtfile` in effect, DOBCP assumes that for each table there is a format file named tbl.FMT. The default is `-fmtfile`. Specify `-nofmtfile` if you do not want to use format files.
`-native`	Instructs DOBCP to use "native" format for the BCP files. Without `-native`, DOBCP uses the character format described below. `-native` is the default. Specify `-nonative` to use character format.
`-unicode`	Instructs DOBCP to use Unicode for all character data. If you use character format, and your database has Unicode columns (nchar etc), you need to use this options, or else Unicode data will be exported as 8-bit characters, which could lead to information loss. If you use native format, the reason to use `-unicode` is less compelling, since BCP will then export all Unicode columns as 16-bit characters. If you use `-unicode` with `-native`, DOBCP also exports 8-bit data (char etc) as Unicode. This could be useful to avoid character-set conversion problems when you are not using format files. (But format files is definitely to recommend.) Note: there is a bug in BCP for SQL 2000, so if you use the combination `-nonative` `-unicode` `-fmtfile`, BCP generates the format file incorrectly. You need to use BCP for SQL 2005 to resolve this problem. The default is `-nounicode`.
`in` \| `out`	Which way to bulk. You must specify either `in` or `out`; there is no default.

Files etc

The BCP file for the table tbl has the name tbl.BCP. The format file is tbl.FMT.

DOBCP does always use a batch size of 1000 rows.

DOBCP always preserves the value of IDENTITY columns when bulking in data. (The switch -E to BCP.)

The BCP commands are put in a file with the BCP-db-inout.BAT, where inout is either IN or OUT. All output from BCP is written a log file with the same name, but the extension .LOG.

In difference to DBBUILD, DOBCP does not detect empty files when bulking in data, but these may cause BCP to print error messages. DOBCP does not detect the format of the file when you bulk in, but you must specify which format to use.

DOBCP loads the files without constraint checks enabled (as foreign keys could fails depending on load order). This will leave you with a database where SQL Server considers the constraints as "not trusted", meaning that the optimizer will ignore the constraints, which can lead to less efficient query plans. To address this you can run the utility prociedure ap_zz_enable_constraints_sp that will enable all constraints that are disabled or not trusted.

Using Format Files

If you use DOBCP to export data from one database to another, there is little reason not to use format files. By using format files, you can move data between databases with different collations in native format. This is, because DOBCP generates the format files for you when you specify out. (However, beware that sql_variant columns will retain their collation, as the collation in this case is part of the value.)

The one situation where format file can cause you trouble is when you use an older version of BCP for the import than you used for the export. In this case, the format files will have a version number that the old version of BCP will not cope with.

Native Format and Character format

DOBCP can use two formats for the BCP files. The default format is native format and this format is also what works best in most situations when you want to copy data from one database to another.

When you specify -nonative, DOBCP will use a character format with with @!@ as the column delimiter, and <-> on a line of its own as the row delimiter. Or in BCP switches:

-t@!@ r"\n<->\n"

Character format could be of interest when you export data to a target that is not an SQL Server database. You may also have to use character format if you export data from a higher version of SQL Server to a lower version, if there are data types that exists in the higher version of SQL Server only.

If you use character format, and do not use format files and neither specify -unicode, BCP will convert character data to the OEM code page on export, and convert back to ANSI when you import. (Unless your database uses an OEM code page for 8-bit characters.) This conversion is not necessarily roundtrip and can thus lead to data loss.

Beware that when using character format on SQL 6.5 and exporting data, you will not get seconds and milliseconds for datetime data.

Beware that when using character format for sql_variant columns, the base type will always be (n)varchar after the import.