Sybase 12.5 bcp command in failed

Posted on 2004-10-10
Last Modified: 2011-09-20

When trying to run the following bcp command:
bcp  GLRjrnl..ap_dwh_invoice_interface  in app_invc.dat0922  -Spolly_new  -UDBS  -fGLRjrnl.ap_dwh_invoice_interface.fmt.

we get the following error message:
CTLIB Message:  - L0/O0/S0/N0/0/0:
blk_rowxfer(): blk layer: internal BLK-Library error: No value or default value
available and NULL not allowed.  col = 3
bcp copy in failed.

Our platform environment and EBF patch files are as follows:
unix         :   SunOS geese04 5.8 Generic_117350-02 sun4u sparc SUNW,Sun-Fire-V440
Sybase oc :   Sybase CTBCP Utility/12.5/P-EBF10071/DRV. 2.8/BUILD125-012/OPT/Wed Feb 27 13:26:22 2002
Sybase ase:  Adaptive Server Enterprise/12.5.2/EBF 12053 ESD#2/P/Sun_svr4/OS 5.8/ase1252/1844/32-bit/FBO/Thu Aug 12 23:07:03 2004
SDK Software Developer Kit - EBF 11972: 12.0 ESD #22 as it is the one supported by our application Smartstream

Question by:pollacsekv
  • 2
  • 2
LVL 19

Accepted Solution

grant300 earned 500 total points
ID: 12273357
Post the .FMT file and the first few lines of the data file.

The error means that you are trying to insert a record with no value in column 3.  The column has no default set and is marked NOT NULL.

You could have a problem with either the data file or the FMT file.  We'll take a look when you post them.


Author Comment

ID: 12274095
I tried the bcp command with format file option also , but it gives this error:

Internal Message:  - Msg 999, Level 11, State 0:
[generic/ctbcp/blk.c/276] ctbcp:executeBulkCopy(): Both terminator and prefix ca
nnot be null for nullable column 5.

bcp copy in failed.


Author Comment

ID: 12274138
Thanks for your comments..

the table is
CREATE TABLE dbo.ap_dwh_invoice_interface
    inv_vendor_nbr   char(9)  NOT NULL,
    inv_voucher_nbr  char(6)  NOT NULL,
    inv_invoice_nbr  char(16) NOT NULL,
    inv_pay_type     char(2)  NOT NULL,
    inv_entity_id    char(5)  NULL,
    inv_jrnl_id      char(10) NULL,
    trans_curr_code  char(3)  NULL,
    tip_branch       char(3)  NULL,
    tip_account      char(11) NULL,
    trans_amt        money    NULL,
    unit_number      char(10) NULL,
    po_number        char(9)  NULL,
    appr_branch      char(3)  NULL,
    inv_date         char(8)  NULL,
    inv_receipt_date char(8)  NULL,
    inv_input_date   char(8)  NULL,
    payment_due_date char(4)  NULL,
    jrnl_seq_nbr     char(5)  NULL,
    filler           char(9)  NULL

the content of the fmt file "GLRjrnl.ap_dwh_invoice_interface.fmt"  is:
1       SYBCHAR 0       9       ""      1       inv_vendor_nbr
2       SYBCHAR 0       6       ""      2       inv_voucher_nbr
3       SYBCHAR 0       16      ""      3       inv_invoice_nbr
4       SYBCHAR 0       2       ""      4       inv_pay_type
5       SYBCHAR 0       5       ""      5       inv_entity_id
6       SYBCHAR 0       10      ""      6       inv_jrnl_id
7       SYBCHAR 0       3       ""      7       trans_curr_code
8       SYBCHAR 0       3       ""      8       tip_branch
9       SYBCHAR 0       11      ""      9       tip_account
10      SYBCHAR 0       16      ""      10      trans_amt
11      SYBCHAR 0       10      ""      11      unit_number
12      SYBCHAR 0       9       ""      12      po_number
13      SYBCHAR 0       3       ""      13      appr_branch
14      SYBCHAR 0       8       ""      14      inv_date
15      SYBCHAR 0       8       ""      15      inv_receipt_date
16      SYBCHAR 0       8       ""      16      inv_input_date
17      SYBCHAR 0       4       ""      17      payment_due_date
18      SYBCHAR 0       5       ""      18      jrnl_seq_nbr
19      SYBCHAR 0       9       "\n"    19      filler

LVL 19

Expert Comment

ID: 12276952
What do the first few lines of the data file look like?  You could well have missing data.

Also, are there any triggers on the target table?

LVL 24

Expert Comment

by:Joe Woodhouse
ID: 12296381
Agree with others above this looks like a data quality issue in terms of missing columns, source data containing NULLs, or your row delimiter appearing within the data.

You can specify an error log file for the bcp, which will give more verbose error reporting and the actual row of data causing the error. Just add:

[...]  -e [error file]

to your existing command line. You'll then be able to see exactly what data is causing this error.

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

With more and more people tethered to the internet, advertisers can reach you in new and creative ways. Push notifications on your smartphone, customized advertisements based on your previous searches, and email inboxes filled with promotional pitch…
We need a new way to communicate time sensitive or critical info.   The best part of my role at xMatters is visiting our clients all over the world to learn about how they operate their businesses, share insights that xMatters has gleaned across…
Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now