Microsoft KB Archive/279224

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

Article ID: 279224

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



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 &quot;Prefix length of field FIELD_NAME&quot; 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.



Steps to Reproduce Problem
  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, )   BCP the data out by using the following command: bcp pubs..vb_type out c:\test.bcp /T  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). Open the BCP.fmt file, and you will find the following rows in the BCP.fmt file:

1      SQLINT        0       4       &quot;&quot;       1     c1      &quot;&quot; 2      SQLBINARY     1       1000    &quot;&quot;       2     c2      &quot;&quot;

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.</ol>

Steps to Resolve Problem
<ol>  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 &quot;Steps to Reproduce Problem&quot; section enter: <pre class="fixed_text">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.

</li> 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.

</li> Now, open the bcp.fmt format file. The output now looks like this:

<pre class="fixed_text">1    SQLINT        0         4         &quot;&quot;           1       c1        &quot;&quot; 2    SQLBINARY     2      1000         &quot;&quot;           2       c2        &quot;&quot;

as expected.</li></ol>

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.

Additional query words: BCP Format file Prefix-length

Keywords: kbbug kbcodesnippet kbpending KB279224

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.