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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

February 24, 2017 — On February 23, Travis Ormandy, a vulnerability researcher at Google, reported on Twitter ( that massive stores of data have been leaked by CloudFlare, a company that provide…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

810 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