BUG: Default of @@DBTS Causes Access Violation When Using BCP

• Microsoft SQL Server, version 6.0

SYMPTOMS

When you use use @@DBTS as a default bound to a column that is defined as varbinary (8), the server receives an access violation:

   Msg 0, level 19, state 0: Server <server name>, line 1: language_exec:
   process 9 generated access violation; SQL Server is terminating this
   process  Operating-system error: ConnectionWrite(WriteFile()).
   DB-LIBRARY error: possible network error: error in sending
   out-of-band data to SQL server Connection broken" when attempting to
   BCP data in.

WORKAROUND

• On SQL Server version 4.21a, you can avoid the problem by using a datatype of timestamp, then update the table to populate the timestamp field with:

        UPDATE mytable
        SET field1 = field1
        WHERE timestamp = NULL
  

• On SQL Server version 6.0, you can avoid the problem by creating a table using the identity constraint. This example creates a table called mytable with two columns. The identity column will start at number one and increment by one each time a row is added.

        CREATE TABLE mytable
        (idcol INT IDENTITY (1,1), name VARCHAR (10))
  

STATUS

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

MORE INFORMATION

The following script causes a handled access violation in SQL Server version 6.0:

1)Set the database to pubs.

  create table testbcp (dt binary (8), str1 char (10), str2 char (10))
  go
  create default dtdef as @@DBTS
  go
  sp_bindefault dtdef, "testbcp.dt"
  go
  use master
  go
  sp_dboption pubs, "select into/bulkcopy", true
  go
  use pubs
  go
  checkpoint

      row1col2,row1col3

      row2col2,row2col3

  and a format file called c:\data.fmt similar to the following:

   6.0
   3
   1   SQLCHAR   0   0   ""      1 dummy
   2   SQLCHAR   0   10   ","      2 str1
   3   SQLCHAR    0   10   "\r\n"            3 str2

   bcp pubs..testbcp in  c:\data.dat /f c:\format.fmt /Usa /P