Arithmetic overflow occured - SQL Server - PowerBuilder Application

When I Execute the following Select Statement I am getting Arithmetic overflow occured.
But my scenario is quiet different.

I am using PowerBuilder as my client application development tool.
In that, When I execute,

Select Isnull(Max(PersonCode),0)+1 Into :NextPerson From Person

PersonCode is of type Numeric(6,0).
Personcode is the PrimaryKey and it has already 10500 rows.
Variable is of type long which can accomodate upto (-2,147,483,648 to +2,147,483,647).

If I execute the above statment in the SQL Query Analyzer, it executes well and it returns the maximum + 1 value.
If I execute the same through my PowerBuilder application, it gives this error.

This is very very urgent.

LVL 4
batchakamalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

diasroshanCommented:
Hi,

why dont u try the following... see if the error persists...

Select Isnull(Max(PersonCode),0) Into :NextPerson From Person

If IsNull(NextPerson) Then NextPerson = 0
NextPerson ++


Hope this helps...

Cheers,
Rosh

0
batchakamalAuthor Commented:
The problem is with the sql server statement not with the variable. Note that I already have PersonCode 1 to 10500 already.
It returns a database error.
As per your idea I will get 1, which will give Violation of Primary Key,
but I want to get the 10501.
0
diasroshanCommented:
Hi,

sorry... a small mistake...
remove the isnull from ur select

Select Max(PersonCode) Into :NextPerson From Person

If IsNull(NextPerson) Then NextPerson = 0
NextPerson ++


Cheers,
Rosh
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

diasroshanCommented:
hi,

the last post of mine will return the right value...

in ur case... 10501


Cheers,
Rosh
0
batchakamalAuthor Commented:
Problem still exist.
When I debug, the select statement got error "Arithmetic overflow occured".
Hence your statement returns 0 to NextPerson.
Then NextPerson++ returns 1, so I got Primary Key Violation error.

I want to solve the Select Statement Problem. I hope then, my problem will be solved.
0
diasroshanCommented:
Hi,

can u write the same code that im posting below...

Long NextPerson
Select Max(PersonCode) Into :NextPerson From Person;

If IsNull(NextPerson) Then NextPerson = 0
NextPerson ++


Ok... also can u tell me the result of the below select in Query Analyser
Select Max(PersonCode) From Person;

Cheers,
Rosh
0
batchakamalAuthor Commented:
I put the same code.
When I Debug  I put a breakpoint in that Select Statment, it puts 0 in NextPerson.
And it has the following sqlca error message.

sqlca.sqldbcode = 3606
sqlca.sqlcode = -1
sqlca.sqlerrtext = "Arithmetic overflow occured"

If I execute the select statement in Quey Analyzer, it returns, 10500
0
diasroshanCommented:
Hi,

im lookin into the same... in the mean time can u tell me if this code was workin b4 and the problem started now after reaching a count of 10500

also can u just check this select in PB and see if it gives any error...

Select PersonCode
Into :NextPerson
From Person
Where personcode = 10500;

also check this select in PB and see if it give an error...
Select PersonCode
From Person
Where personcode = 10500;

Cheers,
Rosh
0
batchakamalAuthor Commented:
Select PersonCode
Into :NextPerson
From Person
Where personcode = 10500;
 
Gives the same error "Arithmetic overflow occured."

The same application, I have it in PB7, it works fine.
This one migrated to PB8. But it was working fine, for the past few months even in PB8.


0
diasroshanCommented:
hi,

what abt
Select PersonCode
From Person
Where personcode = 10500;

does it give an error... i wanna know if the prob is with the variable NextPerson or with the select...

Cheers,
Rosh
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
batchakamalAuthor Commented:
Yes it gives error too inside the application script.

When I execute the same in Database Painter, Interactive SQL and Query Analyzer, it works fine.
0
berXpertCommented:
Hi,

Just a bet, but maybe there is an error in a previous SQL statement, maybe a call to a stored procedure or a dw.Retrieve( ),  try to see if this error is present before you call your select. If your Select works OK in DB painter, then it MUST work on scritp.

Other idea, try to change your NextPerson variable from long to decimal.

Regards,

BerX
0
batchakamalAuthor Commented:
I solved this problem by, executing

SET ARITHABORT ON
reconfigure

on master database of SQL SERVER.

Thanks diasroshan, for ur continuos support.
I am giving this point for ur support (not for the answers.)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Editors IDEs

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.