PowerBuilder 8.01/Microsoft SQL Server 2000 arithmetic overflow error.

[Peter J. Brechun]

Perhaps someone out there has encountered this and it's solution already.
Help would be greatly appreciated.

In a windows function we make an embedded SQL call. It does not matter what
the query is - the first query run in the function fails with and arithmetic
overflow error . This occurs whether it's inline SQL or a stored procedure
call. As a temporary work around we're putting in a dummy query - but I
don't want this to be the solution, especially since it doesn't occur every
where. One other thing, the application was migrated from PB 5.x to PB
8.01. Any help or pointers to a solution would be appreciated.

[Peter_J._Brechun]

Re: PowerBuilder 8.01/Microsoft SQL Server 2000 arithmetic overflow error.

1. does this dummy query that makes the sql work fine also users same
client variables as that used for embedded sql?

-- It doesn't matter what the query is, it can anything, even identical to
the failing query - it is truly a throwaway to get past the error. The
"failing query" works after the "throwaway" is executed.

2. do you use only 'select into' in the embedded sql or any other
insert/update sql stmts?

-- Here's the "failing" SQL

SELECT ISNULL(VF.vendor_fac_nm, " "),
ISNULL(VPA.city_nm, " "),
ISNULL(VPA.state_abbr, " ")
INTO :ls_name,
:ls_city,
:ls_state_abbr
FROM VENDOR_PHYS_ADDRESS VPA,
VENDOR_FACILITY VF
WHERE VF.vendor_fac_id = :ll_disposer_id and
VF.vendor_fac_id *= VPA.vendor_fac_id
USING SQLCA ;

3. Did you match/check datatypes of database to PB variables if all are of
same 'acceptable' datatypes?

-- Yes, of the three text fields, two are fixed length CHAR fields and the
third is a VARCHAR defined with a max length of 40. The retrieval parm
value is defined as an INTEGER in the database.

Also, embedded SQL on certain databases does not compile properly for
builds < 9838 ..[CR 200147] Upgrading the PB build might help you to debug
it.

-- CR 200147 is an Oracle CR, this problem is in MS SQL Server 2000. Also,
the SQL executes outside of the application, ie. - in the database painter
for example.

Thanks for the info...I'll continue to search.

[Mike Kruchten]

Have your DBA use the Profiler to capture all the statements that the app is
sending and look for anything unusual.

Mike Kruchten

<Peter_J._Brechun> wrote in message
news:8DE6A38F5CA9352E006C944985256D66.0052E62685256D66@webforums...
> Thanks in advance for your response - I need to check with the DBA's as to
> what Service Pack MS SQL Server 2000 is on, but as for the other
questions:
>
> 2. The error number is 3606.
> 3. I get the error no matter what the query is, even just retrieving the
> vendor name from the vendor facility table with the vendor facility id.
> 4. We're using the "native" API - MSS. I know Microsoft is no longer
> enhancing it, but this worked before.
>
> Let me add a little additional insight. The application was recently
> migrated from PB 5 to PB 8 - I've been brought in to help resolve issues
> from the migration. I know that Powersoft/Sybase tightened some DB
> interface issues in PB 6/6.5 - this is probably one of them.
>

[Peter J. Brechun]

Let me add a little more to my previous post.

1. SQL Server is on SP3
2. The queries work outside of the PowerBuilder application - if I run the
Database Painter ISQL window or any other database tool, they work fine and
return only one row. This error is only occurring when the application is
run, whether compiled or run interpretively in the development environment
(even when stepping through the debugger). Also, the query doesn't matter -
I can run a select count(*) into query and it still gets an arithmetic
overflow error. Additionally, this application has nearly identical queries
in another function and it works fine - actually the queries are identical,
it's just the function is for a different report.

Talk about a mystery...

<WS> wrote in message news:eYU0O5GTDHA.145@forums-2-dub...
> If the select returns more than one row, SQL Server will protest with an
> arithmetic overflow.
> whats the value with
> select count(*) into :ls_mycount

> FROM VENDOR_PHYS_ADDRESS VPA,
> VENDOR_FACILITY VF
> WHERE VF.vendor_fac_id = :ll_disposer_id and
> VF.vendor_fac_id *= VPA.vendor_fac_id
> USING SQLCA ;
>

> If its <= 1 try the following
>
> 1. what service pack of sqlsever you have? pack3 fixed "some problems" on
> arithmetic overflow.
> 2. whats the error number?
> 3. do you still get this error if you remove inner join and make it a
simple
> join?
> 4. what connection type do you use to connect? obdc?
>
> good luck
>
> WS
>
> <Peter_J._Brechun> wrote in message
> news:9F9D5BA1E010B077006CBAB785256D65.00734EF885256D64@webforums...