Sybase 12.5 bcp command in failed

Posted on 2004-10-10
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 19

Accepted Solution

grant300 earned 1500 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Hey fellow admins! This time, I have a little fairy tale for you. As many tales do, it starts boring and then gets pretty gory. I hope you like it. TL;DR: It is about an important security matter, you should read it if you run or administer Windows …
This month, Experts Exchange’s free Course of the Month is focused on CompTIA IT Fundamentals.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

762 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