Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2008
    Location
    India
    Posts
    96

    Unanswered: Format file & IDENTITY

    Hi All,

    I am facing some issues while doing BCP IN to the identity column.

    i have a table with fixed character length colums. I created the format file.

    say for example,

    create table eno
    (
    cusip char(9),
    secno char(9),
    sign char(1),
    id int identity
    )

    BCP <dbname>.dbo.<tablename> in <filename> -feno.fmt -U<username> -P<password> -S<servername>


    when i execute the above command


    Starting copy...
    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 = 15
    bcp copy in failed

    i tried -E and -N seperately. But no use.

    Inputs are welcome!

    Regards
    Karthik

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Show your input and format file

  3. #3
    Join Date
    Jun 2008
    Location
    India
    Posts
    96
    10.0
    14
    1 SYBCHAR 0 8 "" 1 acno
    2 SYBCHAR 0 2 "" 2 filler
    3 SYBCHAR 0 1 "" 3 type
    4 SYBCHAR 0 9 "" 4 cusip
    5 SYBCHAR 0 5 "" 5 secno
    6 SYBCHAR 0 9 "" 6 symbol
    7 SYBCHAR 0 9 "" 7 price
    8 SYBCHAR 0 1 "" 8 pr_sign
    9 SYBCHAR 0 1 "" 9 recno
    10 SYBCHAR 0 1 "" 10 pos_type
    11 SYBCHAR 0 9 "" 11 qty
    12 SYBCHAR 0 1 "" 12 qty_sign
    13 SYBCHAR 0 11 "" 13 mv
    14 SYBCHAR 0 1 "\n" 14 mv_sign

    create table position
    (
    acno char(8),
    filler char(2) ,
    type char(1),
    cusip char(9),
    secno char(5),
    symbol char(9),
    price char(9),
    pr_sign char(1),
    recno char(1),
    pos_type char(1),
    qty char(9),
    qty_sign char(1),
    mv char(11),
    mv_sign char(1),
    id int identity
    )
    Attached Files Attached Files

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    I am unable to recreate your problem
    Code:
    $ bcp tempdb..position in position.txt -Ume -P*** -Smysrv -f eno.fmt
    
    Starting copy...
    
    5 rows copied.
    Clock Time (ms.): total = 1  Avg = 0 (5000.00 rows per sec.)
    
    $ isql -D tempdb -Ume -P*** -Smysrv -w222
    1> select * from position
    2> go
     acno     filler type cusip     secno symbol    price     pr_sign recno pos_type qty       qty_sign mv          mv_sign id          
     -------- ------ ---- --------- ----- --------- --------- ------- ----- -------- --------- -------- ----------- ------- ----------- 
     21111113        1    252631AB2 NWQ24           000121018 +       1     B        100286000 +        00002730450 +                 1 
     21111113        2    295084EA1 QERW9           000106299 +       1     A        000258000 +        00002657465 +                 2 
     21111113        2    312729FK7 GHIC5           000102450 +       1     A        000259000 +        00002561280 +                 3 
     21111113        2    59259NMA8 DCET3           000102313 +       1     A        000257000 +        00002557895 +                 4 
     21111113        2    59259R2P7 ABCP2           000105064 +       1     A        000253000 +        00002626600 +                 5 
    
    (5 rows affected)

  5. #5
    Join Date
    Jun 2008
    Location
    India
    Posts
    96
    I will try one more time and get back to you.

    meanwhile the actual file size which i am going to upload in the table is
    2,343,059 kb.

    how to upload this file into the table very fast?

    my table doesn't have any indexes,triggers,constraints, defaults, rules etc.,

    It is a heap table.

    is it possible to upload it within 5 minutes?

  6. #6
    Join Date
    Jun 2008
    Location
    India
    Posts
    96
    I jsut want to see the .fmt file which you used. can you attach here?

  7. #7
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    I copied and pasted your format file - no changes
    Are you sure your actual table has an identity column for column 15

  8. #8
    Join Date
    Jun 2008
    Location
    India
    Posts
    96
    but it is not working for me...


    Are you sure your actual table has an identity column for column 15
    yes...

  9. #9
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    Karthik
    but it is not working for me...
    So there is something different between your case and PDeyer's; something not communicated yet. Ok, pls post the DDL for the actual table.
    Check that the owner of the table is dbo (ie. that you do not accidentally have a non-dbo version of the table in the same database).
    Re -E and -N. You need to understand what they do, they are not worth "trying". Choose exactly what you want for the bcp-in:
    __you have an Identity column, and you want new values automatically generated by the server, and you have a placeholder/value in the datafile = <no switches>
    __you have an Identity column, and you want it set from the datafile = -E
    __you have an Identity column, and you want new values automatically generated by the server, and you do not have a placeholder in the datafile = -N
    With the format and data files provided, it looks like you need -N.
    Defaults and Rules are good, they do not need to be dropped for the duration of the bcp, they are (correctly) used during the bcp in. You do need Indices dropped and Triggers disabled.
    If you are unfamiliar with bcp and format files: do some tests on a simple table; create a format file and test bcp-out first; then use it for bcp-in.
    Check the bcp -v version against the dataserver -v version (there are a few peculiarities).
    Last edited by Derek Asirvadem; 10-09-09 at 00:36.
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

  10. #10
    Join Date
    Jun 2008
    Location
    India
    Posts
    96
    Table:

    create table position
    (
    acno char(8),
    filler char(2) ,
    type char(1),
    cusip char(9),
    secno char(5),
    symbol char(9),
    price char(9),
    pr_sign char(1),
    recno char(1),
    pos_type char(1),
    qty char(9),
    qty_sign char(1),
    mv char(11),
    mv_sign char(1),
    id int identity
    )

    Format File:

    10.0
    14
    1 SYBCHAR 0 8 "" 1 acno
    2 SYBCHAR 0 2 "" 2 filler
    3 SYBCHAR 0 1 "" 3 type
    4 SYBCHAR 0 9 "" 4 cusip
    5 SYBCHAR 0 5 "" 5 secno
    6 SYBCHAR 0 9 "" 6 symbol
    7 SYBCHAR 0 9 "" 7 price
    8 SYBCHAR 0 1 "" 8 pr_sign
    9 SYBCHAR 0 1 "" 9 recno
    10 SYBCHAR 0 1 "" 10 pos_type
    11 SYBCHAR 0 9 "" 11 qty
    12 SYBCHAR 0 1 "" 12 qty_sign
    13 SYBCHAR 0 11 "" 13 mv
    14 SYBCHAR 0 1 "\n" 14 mv_sign

    I have already attached the file.Pls take a look into this.

  11. #11
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    I have already attached the file.Pls take a look into this.
    Thanks. What I meant was ACTUAL table, that is not a dummy or test table.

    1 I Checked again. I see nothing. Remember, it works for Pdreyer. So there is something not yet identified or communicated.

    Have you tried the remaining items I gave you:

    2 Check that the owner of the table is dbo (ie. that you do not accidentally have a non-dbo version of the table in the same database).

    3 Re -E and -N. You need to understand what they do, they are not worth "trying". Choose exactly what you want for the bcp-in:
    you have an Identity column, and you want new values automatically generated by the server, and you have a placeholder/value in the datafile = <no switches>

    you have an Identity column, and you want it set from the datafile = -E

    you have an Identity column, and you want new values automatically generated by the server, and you do not have a placeholder in the datafile = -N
    With the format and data files provided, it looks like you need -N.

    4 Defaults and Rules are good, they do not need to be dropped for the duration of the bcp, they are (correctly) used during the bcp in. You do need Indices dropped and Triggers disabled.

    5 If you are unfamiliar with bcp and format files: do some tests on a simple table; create a format file and test bcp-out first; then use it for bcp-in.
    Check the bcp -v version against the dataserver -v version (there are a few peculiarities).
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •