Results 1 to 7 of 7
  1. #1
    Join Date
    May 2011
    Posts
    14

    Unanswered: Error 1120 while inserting data into a table

    hello all,
    i need ur help.
    while inserting data we got an error as 1120 as an illegale lenghth was received. but in same table we are able to insert other location entry other than that location. also m attached the print screen of that.
    please reply.
    Attached Thumbnails Attached Thumbnails error.JPG  

  2. #2
    Join Date
    May 2011
    Posts
    4
    have you tried matching the datatype of the column and the value you are inserting ?

  3. #3
    Join Date
    May 2011
    Posts
    14
    Quote Originally Posted by hegde66 View Post
    have you tried matching the datatype of the column and the value you are inserting ?
    yes the data type is same as contained by table but still there is problem while inserting
    also m able to insert more lenght data than that of location.......... i.e. m able to insert 1000000 but not 600000

    reply

  4. #4
    Join Date
    May 2011
    Posts
    4
    Please post the table definition.

  5. #5
    Join Date
    May 2011
    Posts
    14

    error in vouref_no while inserting data in fvouhdr table

    Quote Originally Posted by hegde66 View Post
    Please post the table definition.

    print 'fvouhdr'
    SETUSER 'fin'
    go
    create table fin.fvouhdr (
    loc_cd char(3) not null,
    div_cd char(3) not null,
    vou_ty char(2) not null,
    vou_no numeric(7, 0) null,
    vouref_no numeric(7, 0) not null,
    vousl_no numeric(7, 0) null,
    vou_dt smalldatetime not null,
    narr varchar(100) null,
    party_ty char(1) null,
    party_cd char(6) null,
    refdoc_ty char(2) null,
    refdoc_no numeric(7, 0) null,
    refdoc_dt smalldatetime null,
    p_i_s_no numeric(7, 0) null,
    vou_amt numeric(13, 2) null,
    bank_cd char(3) null,
    acct_cd numeric(4, 0) null,
    dc_flg char(1) null,
    tax_amt numeric(13, 2) null,
    tds_amt numeric(13, 2) null,
    tds_pcnt numeric(5, 2) null,
    tds_flg char(1) null,
    tds_ty char(6) null,
    tds_no numeric(7, 0) null,
    chqprt_flg char(1) null,
    brecon_flg char(1) null,
    prep_by char(8) not null,
    auth_ind char(1) not null,
    auth_by char(8) null,
    auth_dt smalldatetime null,
    post_flg char(1) not null,
    prep_dt smalldatetime null,
    screen_id varchar(40) null,
    bran_ind char(1) not null,
    upd_id char(8) not null,
    upd_dt smalldatetime not null,
    txfr_flg char(1) not null,
    txfr_loc char(3) null,
    txfr_dt smalldatetime null,
    post_no int null,
    memo text null,
    vousub_ty char(1) null,
    co_ty char(1) null,
    serreg_no varchar(25) null,
    constraint pk_f2a_01 PRIMARY KEY CLUSTERED (loc_cd, vouref_no )
    )
    on 'default'
    go
    print 'fvouhdr_idx_01'
    create nonclustered index fvouhdr_idx_01
    on fin.fvouhdr (party_cd, bank_cd)
    on 'default'
    go

    print 'fvouhdr_idx_03'
    create nonclustered index fvouhdr_idx_03
    on fin.fvouhdr (vou_dt, vousl_no, vou_no)
    on 'default'
    go

    go

    SETUSER
    go
    print 'iu_fvouhdr'
    SETUSER 'fin'
    go

    create trigger iu_fvouhdr
    on fvouhdr
    for INSERT, UPDATE as

    update fvouhdr
    set upd_dt = getdate()
    where loc_cd in (select inserted.loc_cd from inserted)
    and vouref_no in (select inserted.vouref_no from inserted)

    update fvouhdr
    set acct_cd = (select acct_cd from fbanks
    where bank_cd in (select inserted.bank_cd from inserted))
    where loc_cd in (select inserted.loc_cd from inserted)
    and vouref_no in (select inserted.vouref_no from inserted)
    and bank_cd in (select inserted.bank_cd from inserted)
    and post_flg = 'N'
    go

    SETUSER
    go

    exec sp_bindefault 'fin.def_f_y', 'fvouhdr.co_ty'
    go
    exec sp_bindefault 'fin.def_f_c', 'fvouhdr.dc_flg'
    go
    exec sp_bindefault 'fin.def_f_n', 'fvouhdr.tds_flg'
    go
    exec sp_bindefault 'fin.def_f_n', 'fvouhdr.auth_ind'
    go
    exec sp_bindefault 'fin.def_f_n', 'fvouhdr.post_flg'
    go
    exec sp_bindefault 'fin.def_f_n', 'fvouhdr.txfr_flg'
    go
    exec sp_bindefault 'fin.def_f_x', 'fvouhdr.bran_ind'
    go
    exec sp_bindrule 'fin****l_f_dc_flg', 'fvouhdr.dc_flg'
    go
    exec sp_bindrule 'fin****l_f_yn_flg', 'fvouhdr.tds_flg'
    go
    exec sp_bindefault 'fin.def_f_n', 'fvouhdr.brecon_flg'
    go
    exec sp_bindefault 'fin.def_f_n', 'fvouhdr.chqprt_flg'
    go
    exec sp_bindrule 'fin****l_f_yn_flg', 'fvouhdr.auth_ind'
    go
    exec sp_bindrule 'fin****l_f_yn_flg', 'fvouhdr.post_flg'
    go
    exec sp_bindrule 'fin****l_f_yn_flg', 'fvouhdr.txfr_flg'
    go
    exec sp_bindefault 'fin.def_f_sysdate', 'fvouhdr.upd_dt'
    go
    exec sp_bindrule 'fin****l_f_bran_ind', 'fvouhdr.bran_ind'
    go
    exec sp_bindrule 'fin****l_f_party_ty', 'fvouhdr.party_ty'
    go
    exec sp_bindrule 'fin****l_f_yns_flg', 'fvouhdr.brecon_flg'
    go
    exec sp_bindrule 'fin****l_f_chqprt_flg', 'fvouhdr.chqprt_flg'
    go
    alter table fin.fvouhdr
    add constraint fin.fl_fvhdr_loc foreign key (loc_cd) references baps.fin.hloc(loc_cd)
    alter table fin.fvouhdr
    add constraint fin.fk_fvhdr_div foreign key (div_cd) references baps.fin.hdiv(div_cd)
    go

  6. #6
    Join Date
    May 2011
    Posts
    14

    its urgent

    Quote Originally Posted by rameshthorat View Post
    print 'fvouhdr'
    SETUSER 'fin'
    go
    create table fin.fvouhdr (
    loc_cd char(3) not null,
    div_cd char(3) not null,
    vou_ty char(2) not null,
    vou_no numeric(7, 0) null,
    vouref_no numeric(7, 0) not null,
    vousl_no numeric(7, 0) null,
    vou_dt smalldatetime not null,
    narr varchar(100) null,
    party_ty char(1) null,
    party_cd char(6) null,
    refdoc_ty char(2) null,
    refdoc_no numeric(7, 0) null,
    refdoc_dt smalldatetime null,
    p_i_s_no numeric(7, 0) null,
    vou_amt numeric(13, 2) null,
    bank_cd char(3) null,
    acct_cd numeric(4, 0) null,
    dc_flg char(1) null,
    tax_amt numeric(13, 2) null,
    tds_amt numeric(13, 2) null,
    tds_pcnt numeric(5, 2) null,
    tds_flg char(1) null,
    tds_ty char(6) null,
    tds_no numeric(7, 0) null,
    chqprt_flg char(1) null,
    brecon_flg char(1) null,
    prep_by char(8) not null,
    auth_ind char(1) not null,
    auth_by char(8) null,
    auth_dt smalldatetime null,
    post_flg char(1) not null,
    prep_dt smalldatetime null,
    screen_id varchar(40) null,
    bran_ind char(1) not null,
    upd_id char(8) not null,
    upd_dt smalldatetime not null,
    txfr_flg char(1) not null,
    txfr_loc char(3) null,
    txfr_dt smalldatetime null,
    post_no int null,
    memo text null,
    vousub_ty char(1) null,
    co_ty char(1) null,
    serreg_no varchar(25) null,
    constraint pk_f2a_01 PRIMARY KEY CLUSTERED (loc_cd, vouref_no )
    )
    on 'default'
    go
    print 'fvouhdr_idx_01'
    create nonclustered index fvouhdr_idx_01
    on fin.fvouhdr (party_cd, bank_cd)
    on 'default'
    go

    print 'fvouhdr_idx_03'
    create nonclustered index fvouhdr_idx_03
    on fin.fvouhdr (vou_dt, vousl_no, vou_no)
    on 'default'
    go

    go

    SETUSER
    go
    print 'iu_fvouhdr'
    SETUSER 'fin'
    go

    create trigger iu_fvouhdr
    on fvouhdr
    for INSERT, UPDATE as

    update fvouhdr
    set upd_dt = getdate()
    where loc_cd in (select inserted.loc_cd from inserted)
    and vouref_no in (select inserted.vouref_no from inserted)

    update fvouhdr
    set acct_cd = (select acct_cd from fbanks
    where bank_cd in (select inserted.bank_cd from inserted))
    where loc_cd in (select inserted.loc_cd from inserted)
    and vouref_no in (select inserted.vouref_no from inserted)
    and bank_cd in (select inserted.bank_cd from inserted)
    and post_flg = 'N'
    go

    SETUSER
    go

    exec sp_bindefault 'fin.def_f_y', 'fvouhdr.co_ty'
    go
    exec sp_bindefault 'fin.def_f_c', 'fvouhdr.dc_flg'
    go
    exec sp_bindefault 'fin.def_f_n', 'fvouhdr.tds_flg'
    go
    exec sp_bindefault 'fin.def_f_n', 'fvouhdr.auth_ind'
    go
    exec sp_bindefault 'fin.def_f_n', 'fvouhdr.post_flg'
    go
    exec sp_bindefault 'fin.def_f_n', 'fvouhdr.txfr_flg'
    go
    exec sp_bindefault 'fin.def_f_x', 'fvouhdr.bran_ind'
    go
    exec sp_bindrule 'fin****l_f_dc_flg', 'fvouhdr.dc_flg'
    go
    exec sp_bindrule 'fin****l_f_yn_flg', 'fvouhdr.tds_flg'
    go
    exec sp_bindefault 'fin.def_f_n', 'fvouhdr.brecon_flg'
    go
    exec sp_bindefault 'fin.def_f_n', 'fvouhdr.chqprt_flg'
    go
    exec sp_bindrule 'fin****l_f_yn_flg', 'fvouhdr.auth_ind'
    go
    exec sp_bindrule 'fin****l_f_yn_flg', 'fvouhdr.post_flg'
    go
    exec sp_bindrule 'fin****l_f_yn_flg', 'fvouhdr.txfr_flg'
    go
    exec sp_bindefault 'fin.def_f_sysdate', 'fvouhdr.upd_dt'
    go
    exec sp_bindrule 'fin****l_f_bran_ind', 'fvouhdr.bran_ind'
    go
    exec sp_bindrule 'fin****l_f_party_ty', 'fvouhdr.party_ty'
    go
    exec sp_bindrule 'fin****l_f_yns_flg', 'fvouhdr.brecon_flg'
    go
    exec sp_bindrule 'fin****l_f_chqprt_flg', 'fvouhdr.chqprt_flg'
    go
    alter table fin.fvouhdr
    add constraint fin.fl_fvhdr_loc foreign key (loc_cd) references baps.fin.hloc(loc_cd)
    alter table fin.fvouhdr
    add constraint fin.fk_fvhdr_div foreign key (div_cd) references baps.fin.hdiv(div_cd)
    go

    also while deleting the same table i got the same error as follws in attached file
    Attached Thumbnails Attached Thumbnails error table deleteion.bmp  

  7. #7
    Join Date
    May 2011
    Posts
    14

    got the result

    Quote Originally Posted by rameshthorat View Post
    also while deleting the same table i got the same error as follws in attached file
    hi buddy thanks for ur reply and support.

    I got the solution , there is problem in the table entry some entry are posted wrong in 1997 so i just changed that entry and now it works perfectly.

    Thanks for ur quick reply.


Posting Permissions

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