Insert Data into AS400

55 pts.
Tags:
AS/400
SQL Server
I am trying to insert 'int' (integer) datatype into a AS400 table. The fields on the AS400 are of type 'S' (numeric). I have tried to cast the data as numeric but I get an error. Can anyone help? INSERT INTO ebbillhp (BHNROV, BHCRTDT, BHBILLHWEB, BHUSERID) VALUES (convert(numeric,@Site), convert(numeric,@Account), @BillCode, @UserName) I get the following error in SQLServer 2005: "Msg 8115, Level 16, State 8, Line 1 Arithmetic overflow error converting numeric to data type numeric. The statement has been terminated."

Answer Wiki

Thanks. We'll let you know when a new response is added.

I doubt that you need to do any conversion.

Phil

Discuss This Question: 9  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Jtaylor7
    I tried to cast the fields as numeric. I get the same error. INSERT INTO ebbillhp (BHNROV, BHCRTDT, BHBILLHWEB, BHUSERID) VALUES (cast(321 as numeric), cast(123456789 as numeric), 'O', 'UserTest') ERROR: Msg 8115, Level 16, State 8, Line 1 Arithmetic overflow error converting numeric to data type numeric. The statement has been terminated.
    55 pointsBadges:
    report
  • philpl1jb
    Right - you're way to smart for this. To SQL numeric is numeric. You shouldn't need a cast or a convert, just pass it the fields. Phil
    50,505 pointsBadges:
    report
  • Jtaylor7
    Thanks for replying Philpl1jb. When you try to update the AS400 table with a datatype of int, it still gives you a similar error. That is why I tried to cast them as numeric. INSERT INTO ebbillhp (BHNROV, BHCRTDT, BHBILLHWEB, BHUSERID) VALUES (321, 123456789, 'Z', 'UserTest') Msg 8115, Level 16, State 8, Line 1 Arithmetic overflow error converting int to data type numeric. The statement has been terminated. Any ideas?
    55 pointsBadges:
    report
  • philpl1jb
    Is BHCRTDT 9 digits as 9 S 0 ??? 1. are these all of the fields in the table 2. what is the size of these fields BHNROV, BHCRTDT, BHBILLHWEB, BHUSERID Phil
    50,505 pointsBadges:
    report
  • Jtaylor7
    Yes. Field Name: BHNROV Field Type: S Field Length: 3 Field Name: BHCRTDT Field Type: S Field Length: 9 Field Name: BHBILLHWEB Field Type: A Field Length: 1 Field Name: BHUSERID Field Type: A Field Length: 32
    55 pointsBadges:
    report
  • philpl1jb
    Odd I've created this file
      A          R EBBILLHPR      
      A            BHNROV         3S 0  
     A            BHCRTDT        9S 0    
     A            BHBILLHWEB     1A     
    A            BHUSERID      32A     
    and issued this command from the interactive SQL Insert into ebbillhp (BHNROV, BHCRTDT, BhBILLHWEB, BHUSERID) values (321, 123456789, 'Z' ,'UserTest') and it inserts a row. Are you sure those fields are 3S 0 and 9S 0? What environment are you sending your SQL from?
    50,505 pointsBadges:
    report
  • Jtaylor7
    SQL server 2005 is what I am using with a linked server link to the as400.
    55 pointsBadges:
    report
  • philpl1jb
    http://www.sql-server-helper.com/faq/error-messages-p09.aspx 42. Arithmetic overflow error converting numeric to data type numeric. Error Message: Server: Msg 8115, Level 16, State 8, Line 2 Arithmetic overflow error converting numeric to data type numeric. Causes: This error is usually encountered with decimal or numeric data types wherein the precision of the column or variable is not enough to hold the value being assigned to it. ------------------- It still seems that you are trying to put the data into a table with either field BHNROV or BHCRTDT that is not wide enough 1. you are hitting another version of the table. 2. one of these fields has a decimal part ie: 9 0 will accept your data but 9 2 would not -- it would accept 1234567.89 Phil
    50,505 pointsBadges:
    report
  • Jtaylor7
    #1 was the answer. Thanks for your help!
    55 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following