BUG: BCP Fails w/ Binary Convert to SYBCHAR > 255 & FMT File

  - Microsoft SQL Server version 4.2

SYMPTOMS

The "System Administrator's Guide" explains that when binary and varbinary datatypes are converted to SYBCHAR, 2 characters are used to represent each byte of the binary datatype. This may be a problem when using BCP.EXE to generate a format file, as it will automatically expand the SYBCHAR definition by 2 characters for each byte of binary data.

If this binary or varbinary conversion to SYBCHAR results in a SYBCHAR field definition greater than 255 and this format file is subsequently used with BCP.EXE, the resulting data file will be 0 bytes. There will be no error message indicating that a problem has occurred. If the -e flag is also used to generate an error file, the resulting error file will have a size of 0 bytes.

However, if a format file is generated using BCP.EXE (without -c or -n) the program will create a correct output data file with an attribute defined as SYBCHAR greater than 255 characters. Subsequent use of the format file with BCP will result in the above problem.

WORKAROUND

An alternative is to use the command line options for BCP.EXE documented in the SQL Server "Language Reference" manual, or when using a format file to restrict any field listed as SYBCHAR to 255 characters or less.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 4.2. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.