DOBCP bulks in or out all tables in a database, using native format (the default) or a predefined character format for the BCP files. DOBCP does not use any BCP API; rather DOBCP is just a wrapper on command-line BCP.
dobcp [-Server server] [-User user] [-Password pwd] -database db [-[no]fmtfile] [-[no]native] [-[no]unicode] in | out
||The server on which the database resides. Default is local server.|
||Which user to run as. Default value is sa.|
|Password for user. If you do not specify |
|Which database to bulk in/out from. You must specify |
|When bulking out and |
When bulking in and
The default is
||Instructs DOBCP to use native (i.e. binary) format for the BCP files. Without |
||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 option, or else Unicode
data will be exported as 8-bit characters, which could lead to information
If you use native format, the reason to use
Note: there is a bug in BCP for SQL 2000, so if you use the combination
The default is
||Which way to bulk. You must specify either |
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.
‑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 fail depending on load order). This will leave you with a database where SQL Server considers the constraints as "not trusted", meaning that the optimiser will ignore the constraints, which can lead to less efficient query plans. To address this you can run the utility procedure ap_zz_enable_constraints_sp that will enable all constraints that are disabled or not trusted.
If you have table names with characters and extend beyond your ANSI character set, this will fail with DOBCP, since the Windows Command-Line is not Unicode-aware.
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.
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:
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
page for 8-bit characters.) This conversion is not necessarily roundtrip and can
lead to data loss.
Beware that when using character format for sql_variant columns, the base type will always be (n)varchar after the import.
Copyright © 1996-2011,
Erland Sommarskog SQL-Konsult AB.
All rights reserved. AbaPerls is available under Perl Artistic License
This page last updated 11-05-04 15:33