Export (0) Print
Expand All

Importing Native and Character Format Data from Earlier Versions of SQL Server

To use bcp to import native and character format data from Microsoft SQL Server 2000 or earlier, use the -V switch. When the -V switch is specified, Microsoft SQL Server 2005 uses data types from earlier versions of Microsoft SQL Server, and the data file format will be the same as the one in that earlier version.

ms191212.note(en-US,SQL.90).gifNote:
The -V switch extends the functionality of the -6 switch that is used in Microsoft SQL Server 7.0. Using -6 is the same as using -V 60 or -V 65. Although SQL Server still supports the -6 switch, it is deprecated. Therefore, using -V is recommended.

To specify whether the data file is at the level of an earlier version of SQL Server, use the -V switch as follows:

SQL Server version Qualifier

Microsoft SQL Server 6.0

-V 60

Microsoft SQL Server 6.5

-V 65

SQL Server 7.0

-V 70

SQL Server 2000

-V 80

SQL Server 2005 had added support for some new types. When you want to import a new data type from SQL Server 2005 into SQL Server 2000 or earlier, it must be stored in a format that readable by the older bcp clients. The following table summarizes how the new data types of SQL Server 2005 are converted for compatibility with the earlier versions of SQL Server.

New data types in SQL Server 2005 Compatible data types in version 6x Compatible data types in version 70 Compatible data types in version 80

bigint

decimal

decimal

*

sql_variant

text

nvarchar(4000)

*

varchar(max)

text

text

text

nvarchar(max)

ntext

ntext

ntext

varbinary(max)

image

image

image

XML

ntext

ntext

ntext

UDT1

image

image

image

* This type is natively supported.

1 UDT indicates a user defined type.

When you bulk export data from SQL Server 2005, using the –V 80 switch, nvarchar(max), varchar(max), varbinary(max), XML, and UDT data in native mode are stored with a 4-byte prefix, like text, image, and ntext data, rather than with an 8-byte prefix, which is the default for SQL Server 2005.

When you bulk export data from SQL Server 7.0 or earlier, consider the following:

  • The storage format for bigint data exported from SQL Server 7.0, SQL Server 6.5, or SQL Server 6.0 depends on data format of the data file:
    • In a native mode or Unicode native-format data file, bigint data is stored as decimal(19,0).
    • In a character mode or Unicode character-format data file, bigint data is stored as a character or Unicode string of [-]digits, (for example, –25688904432).
  • For a table with char or varchar columns, when exporting data in SQL Server 6.0 or SQL Server 6.5, bcp adds a 1-byte prefix for each data file field equivalent to the length of the data. In a table with numeric data, the information is written to the data file in the SQL Server native format.
  • In SQL Server 7.0 and SQL Server 2000, the value 0 represents a zero-length column.

In addition, when you bulk export data SQL Server 6.5 or SQL Server 6.0, consider the following:

  • In SQL Server 6.5 or earlier, bcp represented null values as a length value of 0, whereas null is now stored as the length value -1.
  • Null values in bit columns are written as the value 0 because SQL Server 6.5 and earlier versions do not support nullable bit data.
  • The bcp utility does not generate SQL Server 6.0 or SQL Server 6.5 date formats for any datetime or smalldatetime data. Dates are always written in ODBC format.
  • Using the -V 65 switch can affect performance because of the overhead required to support multiple date conversions.
  • Data values differ in SQL Server 6.5 or earlier. For more information, see the following section, "Copying Date Values".

Beginning with SQL Server 7.0, bcp uses the ODBC bulk copy API. Therefore, to import date values into SQL Server 7.0 or later, bcp uses the ODBC date format (yyyy-mm-dd hh:mm:ss[.f...]).

In contrast, in SQL Server 6.5 or earlier, bcp uses the DB-Library bulk copy API and the DB-Library date format. To export date formats to SQL Server 7.0 and later from SQL Server 6.5 or earlier, use the -V 65 switch. If you specify -V 65, the bcp command first attempts to convert the date value in the data file using ODBC date format. If the conversion fails, bcp attempts to convert the date value using DB-Library formats.

Even if -V 65 is specified, the bcp command always exports character format data files using the ODBC default format for datetime and smalldatetime values. For example, a datetime column containing the date 12 Aug 1998 is bulk copied to a data file as the character string 1998-08-12 00:00:00.000.

ms191212.note(en-US,SQL.90).gifImportant:
When importing data into a smalldatetime field using bcp, be sure the value for seconds is 00.000; otherwise the operation will fail. The smalldatetime data type only holds values to the nearest minute. BULK INSERT and INSERT ... SELECT * FROM OPENROWSET(BULK...) will not fail in this instance but will truncate the seconds value.

Using the -V 65 switch can affect performance because of the overhead required to support multiple date conversions.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft