SQL Server

SQL Server Integration Services : Using bcp (part 4)

11/30/2010 3:12:53 PM
File Storage Types

The storage type is a description of how the data is stored in the data file. Table 2 lists the definitions used during interactive bcp and what appears in the format file. The storage type allows data to be copied as its base type (native format), as implicitly converted between types (tinyint to smallint), or as a string (in character or Unicode format).

Table 2. Storage Data Types
File Storage TypeInteractive PromptHost File Data Type


If the table makes use of user-defined data types, these customized data types appear in the format file as their base data type.

If you are having problems loading certain fields into your table, you can try the following tricks:

  • Copy the data in as char data types and force SQL Server to do the conversion for you.

  • Duplicate the table and replace all the SQL Server data types with char or varchar of a length sufficient to hold the value. This trick allows you to further manipulate the data with T-SQL after it is loaded.

Prefix Lengths

To maintain compactness in native data files, bcp precedes each field with a prefix length that indicates the length of the data stored. The space for storing this information is specified in characters and is called the prefix length.

Table 3 indicates the value to specify for prefix length for each of the data types.

Table 3. Prefix Length Values
Prefix LengthData Types to Use
0Non-null data of type bit or numerics (int, real, and so on). Use this value when no prefix characters are wanted. This value causes the field to be padded with spaces to the size indicated for the field length.
1Non-null data of type binary or varbinary or null data, with the exception of text, ntext, and image. Use this value for any data (except bit, binary, varbinary, text, ntext, and image) that you want stored using a character-based data type.
2When storing the data types binary or varbinary as character-based data types, 2 bytes of char file storage and 4 bytes of nchar file storage are required for each byte of binary table data.
4For the data types text, ntext, and image.

Prefix lengths are likely to exist only within data files created using bcp. It is unlikely that you will encounter a reason to change the defaults bcp has chosen for you.

Field Lengths

When using either the native or character data format, you must specify the maximum length of each field. When converting data types to strings, bcp suggests lengths large enough to store the entire range of values for each particular data type. Table 4 lists the default values for each of the data formats.

Table 4. Default Field Lengths for Data Formats
Data TypeLength (/c)Length (/n)
binaryColumn length × 2Column length
decimal41up to 17
numeric41up to 17


You must specify a field length that is long enough for the data being stored. bcp error messages regarding overflows indicate that the data value has been truncated in at least one of the fields. If the operation is a load, an overflow error usually results in bcp terminating. However, if you are dumping the data to a file, the data is truncated without error messages.

The field length value is used only when the prefix length is 0 and you have specified no terminators. In essence, you are doing a fixed-length data copy. bcp uses exactly the amount of space stated by the field length for each field; unused space within the field is padded out.


Preexisting spaces in the data are not distinguished from added padding.

Other -----------------
- SQL Server Integration Services : Connection Projects in Visual Studio
- SQL Server Integration Services : The Package Execution Utility (part 3) - The dtutil Utility
- SQL Server Integration Services : The Package Execution Utility (part 2) - Running Packages
- SQL Server Integration Services : The Package Execution Utility (part 1)
- SQL Server Integration Services : The SSIS Designer
- SQL Server Integration Services : Running the SSIS Wizard
- SQL Server Integration Services : A Data Transformation Requirement
- SQL Server 2008 : SSIS Tools and Utilities
- SQL Server 2008 : SSIS Architecture and Concepts
- SQL Server 2008 : SQL Server Integration Services - SSIS Basics
- Defensive Error Handling : Using Transactions and XACT_ABORT to Handle Errors
- Managing Security Within the Database Engine : Securables
- Managing Security Within the Database Engine : Database Security
- Managing Security Within the Database Engine : Creating SQL Server Principals
- SQL Server 2008 : Performance Tuning - Locks, Blocking, and Deadlocks
- SQL Server 2008 : Performance Tuning - Tracing
- SQL Server 2008 : Implementing Error Handling - Managing and Raising User-Defined Errors
- SQL Server 2008 : Implementing Error Handling - Understanding Errors
- Implementing SQL Server Objects Using Managed Code (part 2)
- Implementing SQL Server Objects Using Managed Code (part 1)
- First look: Apple Watch

- 10 Amazing Tools You Should Be Using with Dropbox

- Sigma 24mm f/1.4 DG HSM Art

- Canon EF11-24mm f/4L USM

- Creative Sound Blaster Roar 2

- Alienware 17 - Dell's Alienware laptops

- Smartwatch : Wellograph

- Xiaomi Redmi 2
Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8