Programming4us
         
 
 
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
charc[har]SQLCHAR
varcharc[har]SQLCHAR
ncharwSQLNCHAR
nvarcharwSQLNCHAR
textT[ext]SQLCHAR
ntextWSQLNCHAR
binaryxSQLBINARY
varbinaryxSQLBINARY
imageI[mage]SQLBINARY
datetimed[ate]SQLDATETIME
smalldatetimeDSQLDATETIM4
decimalnSQLDECIMAL
numericnSQLNUMERIC
floatf[loat]SQLFLT8
realrSQLFLT4
inti[nt]SQLINT
smallints[mallint]SQLSMALLINT
tinyintt[inyint]SQLTINYINT
moneym[oney]SQLMONEY
smallmoneyMSQLMONEY4
bitb[it]SQLBIT
uniqueidentifieruSQLUNIQUEID
timestampxSQLBINARY

Note

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)
bit11
binaryColumn length × 2Column length
datetime248
smalldatetime244
float308
real304
int124
smallint72
tinyint51
money308
smallmoney304
decimal41up to 17
numeric41up to 17
uniqueidentifier3716

Note

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.

Note

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)
 
 
Most View
- Unit Testing in Visual Studio 2010 (part 2) - Running a battery of tests
- Adding Macs to Your Windows 7 Network : Connecting to the Windows Network
- SharePoint 2010 : Installing Windows PowerShell
- Windows Phone 7 : Working with the Calendar - Adding Appointments
- Windows Server 2008 : Migrating Printers with printbrm, Controlling the Print Queue with prnqctl.vbs
- Exchange 2007 : Choose a High Availability Solution
- Windows Phone 7: Customizing Your Contacts List
- An OLAP Requirements Example: CompSales International (part 3) - Creating Data Source Views
- jQuery 1.3 : AJAX - Loading data on demand (part 2) - Working with JavaScript objects
- SharePoint 2010 : Implementing and Configuring a Records Center (part 1) - Creating and Managing a Content Type & Creating the Records Center
Top 10
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 3) - Configuring Recipient Filtering
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 2)
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 1)
- Implementing Edge Services for an Exchange Server 2007 Environment : Installing and Configuring the Edge Transport Server Components
- What's New in SharePoint 2013 (part 7) - BCS
- What's New in SharePoint 2013 (part 6) - SEARCH
- What's New in SharePoint 2013 (part 6) - WEB CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 5) - ENTERPRISE CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 4) - WORKFLOWS
- What's New in SharePoint 2013 (part 3) - REMOTE EVENTS