BUG: Problems Inserting NULL Text Data Using BCP API

• Microsoft SQL Server Programmer's Toolkit, version 4.2

SYMPTOMS

When inserting NULL data into a TEXT column using the BCP DB-Library API, one of the following symptoms occurs:

• bcp_sendrow fails and the row is not inserted.

  -or-

• bcp_sendrow succeeds and the row is inserted into SQL Server, but you see extra text pages allocated for the TEXT column.

CAUSE

The bcp_bind function is not correctly handling the insertion of text data when the buffer for the text data is initialized/set to null.

In the example below, the buffer that holds the text data is defined in the program as:

   DBCHAR text_buffer[8000] = "";

Symptom 1

bcp_sendrow fails if you attempt to insert NULL data by specifying "varlen" as -1 along with an appropriate "terminator" and "termlen." Note that these parameters are part of the bcp_bind call. Even when varlen is set to 0, along with a terminator of NULL and termlen of 0, bcp_sendrow fails. The row is not inserted into the SQL Server table when bcp_sendrow is called.

Symptom 2

bcp_sendrow succeeds if you attempt to insert data by specifying varlen as the allocated buffer length, along with a terminator and termlen.

However, when the "dbcc checktable" command is run on the inserted table, you will see that extra pages have been allocated, and that the number of pages allocated is equal to varlen/1968 (rounding off the integer to the next highest number).

In the above example, if bcp_bind is called with varlen set at 8000 for the 8000-character buffer with termlen set to 1 and terminator set to "" (signifying a null string), BCP will incorrectly allocate four pages for the data (8000/1968 rounded off to 5).

WORKAROUND

To insert NULL text data, follow the steps that lead to Symptom 2. Next, update the text column to NULL by issuing the following query:

   update my_table
   set text_column = NULL
   where text_column like " ".

STATUS

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