?
Solved

Capture an overflow error in a function

Posted on 2009-04-30
7
Medium Priority
?
253 Views
Last Modified: 2012-05-06
I am wondering what's a best way to capture/debug an error in SQL Server's 2005 function ?

I have the following query:
Select ID, dbo.fn_myfunction(id)
from TABLE_a

The query returns about 100 of expected 2000 rows and fails with the error:

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type money.

It's obvious that that error is somewhere in the custom function: dbo.fn_myfunction

What's the best and easier way to find where ?
0
Comment
Question by:marper
  • 4
  • 2
7 Comments
 
LVL 43

Expert Comment

by:pcelba
ID: 24270203
Simply copy the function code to the query window in MS SQL Management studio, assign the value to the input parameter and execute it.
0
 
LVL 4

Author Comment

by:marper
ID: 24270511

Hi pcelba,
yes, I figured out that would work, but I do not have time to try all possible values from the table until I find which value actually causes an error.

As I said, I am expecting about 2000 rows as an output from the query and I am looking an easier way to find where it fails.

It could be row 145, it could be row 1245, etc, etc...
0
 
LVL 43

Expert Comment

by:pcelba
ID: 24270630
So do the following:

Select ID, dbo.fn_myfunction(id)
from TABLE_a
ORDER BY Id

and the line which causes the error will be the first one not included in the result set.

The full result set you obtain by
Select ID
from TABLE_a
ORDER BY Id

Another option is to post the fn_myfunction here and we may look at it and decide what's wrong.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 4

Author Comment

by:marper
ID: 24271890

The result set just briefly flashes on the screen before the error appears, so what you suggested unfortunately does not help a lot
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24272152
Unfortunately without seeing the function fn_myfunction all we can do is resort to crystal ball reading and I am not very good at that.
0
 
LVL 43

Accepted Solution

by:
pcelba earned 400 total points
ID: 24272274
Do it in the loop, the last successful row appears in Message window:

DECLARE @row int
SET @row = 1
 
WHILE @row < 2000
  BEGIN
    PRINT @row
    SELECT row, id, dbo.fn_myfunction(id) from 
       (SELECT row_number() OVER(order by Id) AS row, ID from TABLE_a) XX
    WHERE row = @row
  END

Open in new window

0
 
LVL 43

Expert Comment

by:pcelba
ID: 24272403
Thanks for the points, I just forgot to increment the @row variable in a loop:

SET @row = @row + 1
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question