Solved

Sybase 12.5 bcp command in failed

Posted on 2004-10-10
5
2,437 Views
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.12.5.0.1/SPARC/Solaris 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

0
Comment
Question by:pollacsekv
[X]
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
5 Comments
 
LVL 19

Accepted Solution

by:
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.

BIll
0
 

Author Comment

by:pollacsekv
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.


0
 

Author Comment

by:pollacsekv
ID: 12274138
Hi,
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:
10.0
19
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

0
 
LVL 19

Expert Comment

by:grant300
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?

Bill
0
 
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.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SYBASE Query combine date spans 7 697
Generating a Format File in Sybase ASE BCP 1 1,243
SQL Substring Extraction 4 560
Cannot create ODBC connection over the network 3 1,243
Facebook has became the #1 social media platform. People share many funny videos there, yet you don't know how to download them? Now you can download Videos from Facebook in just 3 simple steps.
We have put together a white paper that aims to explain how MSPs can both improve their offering and ease the pain of after-hours service by: -Suggesting changes to workflow -Indicating how to rework policy to suit your team -Providing ConnectW…

726 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