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

To copy native and character format data from Microsoft® SQL Server™ 7.0 or earlier, use the –V switch. When this switch is specified, SQL Server 2000 uses data types from earlier versions of SQL Server. Use the –V switch to specify whether the bcp data file is at the level of SQL Server version 6.0 (-V 60), SQL Server version 6.5 (-V 65), or SQL Server version 7.0 (-V 70).

The –V switch extends the functionality of the –6 switch used in SQL Server 7.0. Using –6 is the same as using –V 60 or –V 65. Although SQL Server 2000 still supports the –6 switch, the use of –V is recommended.

Note  The -V switch does not apply to the BULK INSERT statement.

If you bulk copy data from SQL Server 7.0 or earlier into a data file, consider the following:

• bcp 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.
  
  
• 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.
  
  
• 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. In SQL Server 7.0 and SQL Server 2000, the value 0 represents a zero-length column.
  
  
• bigint data copied to a SQL Server 7.0, SQL Server 6.5, or SQL Server 6.0 native mode or Unicode native mode data file is stored as decimal(19,0). bigint data in a character mode or Unicode character mode data file is stored as a character or Unicode string of [-]digits, (for example, –25688904432).
  
  
• In a table with char or varchar fields, the bcp utility adds an ASCII character to the beginning of 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 internal binary data format.

Copying Date Values

In SQL Server 7.0 and SQL Server 2000, bcp uses the ODBC bulk copy API. Therefore, bcp uses the ODBC date format (yyyy-mm-dd hh:mm:ss[.f...]) to import date values. However, in SQL Server 6.5 or earlier, bcp uses the DB-Library bulk copy API and the DB-Library date format. Use the –V 65 switch to copy date formats from SQL Server 6.5 or earlier to SQL Server 7.0 and SQL Server 2000. If you specify –V 65, the bcp utility 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, however, the bcp utility 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.

Important  When importing data into a smalldatetime field using bcp, be sure the value for seconds is 00.000; otherwise the bcp operation will fail. The smalldatetime data type only holds values to the nearest minute. BULK INSERT 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.

See Also

Using bcp and BULK INSERT

SQL Server Backward Compatibility Details

CAST and CONVERT

Use6xCompatible Property