BUG: SQL Server 2000 BCP Utility Fails to Produce Proper Prefix-Length for Varbinary Column in Format File (279224)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
This article was previously published under Q279224
BUG #: 234709 (Shiloh_bugs)

SYMPTOMS

Running the bcp utility to get the data out of a table with varbinary columns, generates an incorrect prefix-length value in the format file.

Use of a varbinary column, with a width greater than 255 bytes, generates a format file that has a prefix-length of 1 byte instead of 2 bytes and leads to the truncation of the actual data that is copied in.

WORKAROUND

When you specify the parameters for the format file, when BCPing out the data, you must manually set the "Prefix length of field FIELD_NAME" to 2 for the varbinary columns that are 255 bytes or more.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 2000.

MORE INFORMATION

Steps to Reproduce Problem

  1. Create a table in the pubs database that has two columns:
    Use pubs
go

create table vb_type(
   c1	   int	     identity       NOT NULL,
   c2	   varbinary (1000)         NOT NULL,
)
  2. BCP the data out by using the following command:
    bcp pubs..vb_type out c:\test.bcp /T
  3. As soon as you run the preceding command, the user is prompted to enter details to create a format file. Choose the defaults provided and select Yes to store the format information. Name the format file BCP.fmt (default).
  4. Open the BCP.fmt file, and you will find the following rows in the BCP.fmt file: 

    1       SQLINT        0       4       ""       1     c1      ""
2       SQLBINARY     1       1000    ""       2     c2      ""

    The output clearly indicates the prefix-length to be 1 for the varbinary column, which is more than 255 characters. 1000 in this case. The prefix length should have been 2 instead of 1.


Steps to Resolve Problem

  1. As soon as the BCP command is run, the user is prompted for details to create a format file, or after step 2 in the "Steps to Reproduce Problem" section enter:
    Enter the file storage type for field c1 [int]:
Enter prefix-length of field c1 [0]:
Enter field terminator [none]: 

Enter the file storage type of field c2 [binary]:
Enter prefix-length of field c2 [1]: 2
Enter field terminator [none]:
    Leave all the other values at their defaults, and specify the prefix-length for the varbinary columns, as 2 in the table, if their lengths are greater than 255 bytes up to 8060 bytes because 8060 bytes is the maximum length of a row in SQL Server 2000.

  2. The utility prompts you as to whether you want to save the format information. Select Yes, and name the format file bcp.fmt (default). The BCP process proceeds and successfully completes.

  3. Now, open the bcp.fmt format file. The output now looks like this: 

    1     SQLINT        0         4         ""           1       c1        ""
2     SQLBINARY     2      1000         ""           2       c2        ""

    as expected.
Thus, manually specifying the prefix-length to be of length 2 bytes leads to the non-truncation of the varbinary data, as the end-user expects.
Modification Type:MajorLast Reviewed:10/3/2003
Keywords:kbBug kbCodeSnippet kbpending KB279224
©2003 Microsoft Corporation. All rights reserved.