 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / Programming / SQL / July 2008is there _ANY_ way to show the row that is throwing an error in a query?
I have the query below that is throwing the error 'Msg 8115, Level 16, State 2, Line 1 Arithmetic overflow error converting expression to data type int.': select l.heading_code, ROUND(LMIimp - YPCimp*LMIclicks/cast(YPCclicks as decimal(19,10)),0) as Ideal_Reduction_in_Impressions from #lmiMetric l LEFT OUTER JOIN #YPCMetric y ON l.heading_code = y.heading_code where ROUND(LMIimp - YPCimp*LMIClicks/cast(YPCClicks as decimal(19,10)),0) > 0 All I want to do is to find the record that is causing the error, but the RESULTS pane stops short of the offending row. Is there not an easy way to tell SQL Server to go ahead and show the row and just don't do the darn calculation. I though set arithabort off would do it, but it doesn't seem to have any effect. My workmates have no idea and suggest trial and error or using a cursor, but I just can't believe there is not an easier way. This would be so easy in any other developement language! Does #lmiMetric have a primary key or something that is predictably order-able? You could order by that and when the query fails you can then determine what the next row is... On 7/24/08 9:10 PM, in article 462f1f1b-751d-4d5f-91b3-fcf80320a472@v1g2000pra.googlegroups.com, > I have the query below that is throwing the error 'Msg 8115, Level 16, > State 2, Line 1 Arithmetic overflow error converting expression to [quoted text clipped - 16 lines] > believe there is not an easier way. This would be so easy in any > other developement language! On Jul 24, 6:26 pm, "Aaron Bertrand [SQL Server MVP]" <ten....@dnartreb.noraa> wrote: > Does #lmiMetric have a primary key or something that is predictably > order-able? You could order by that and when the query fails you can then [quoted text clipped - 25 lines] > > - Show quoted text - Heading_Code is a PK, but when I added 'ORDER BY l.heading_code', I actually got an EMPTY result set, which is worse that what i had before. I think this is because the error occurs BEFORE the order by. Yeah it is tough to control that, I suggest following Dan's advice about figuring out which data points will not actually fit into decimal(19,10) or into INT... On 7/24/08 10:20 PM, in article 3a09f39c-ce6a-4625-98cb-1eb2ce2335fd@u6g2000prc.googlegroups.com, > On Jul 24, 6:26 pm, "Aaron Bertrand [SQL Server MVP]" > <ten....@dnartreb.noraa> wrote: [quoted text clipped - 31 lines] > actually got an EMPTY result set, which is worse that what i had > before. I think this is because the error occurs BEFORE the order by. > All I want to do is to find the record that is causing the error, but > the RESULTS pane stops short of the offending row. Is there not an [quoted text clipped - 4 lines] > believe there is not an easier way. This would be so easy in any > other developement language! Why not increase the precision (at least temporarily) to identify the problem data. For example: select l.heading_code, ROUND(LMIimp - YPCimp*LMIclicks/cast(YPCclicks as decimal(38,10)),0) as Ideal_Reduction_in_Impressions from #lmiMetric l LEFT OUTER JOIN #YPCMetric y ON l.heading_code = y.heading_code where ROUND(LMIimp - YPCimp*LMIClicks/cast(YPCClicks as decimal(38,10)),0) > 999999999  SignatureHope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/ >I have the query below that is throwing the error 'Msg 8115, Level 16, > State 2, Line 1 Arithmetic overflow error converting expression to [quoted text clipped - 16 lines] > believe there is not an easier way. This would be so easy in any > other developement language! On Jul 24, 6:27 pm, "Dan Guzman" <guzma...@nospam- online.sbcglobal.net> wrote: > > All I want to do is to find the record that is causing the error, but > > the RESULTS pane stops short of the offending row. Is there not an [quoted text clipped - 44 lines] > > - Show quoted text - That's an interesting idea, but I don't actually think the error is happening due to that field that is being converted to decimal, but rather with the multiplication in the numerator. However, it's an interesting idea - I will try it - that may be the only way. I just can't believe there is not an easier way though - why doesn't 'set arithabort off' work? From 2000 BOL: If SET ARITHABORT is OFF and one of these errors occurs, a warning message is displayed, and NULL is assigned to the result of the arithmetic operation. > That's an interesting idea, but I don't actually think the error is > happening due to that field that is being converted to decimal, but > rather with the multiplication in the numerator. However, it's an > If SET ARITHABORT is OFF and one of these errors occurs, a warning > message is displayed, and NULL is assigned to the result of the > arithmetic operation. I agree that it's the multiplication expression rather than the underlying value. The explicit CAST will promote the final (and intermediate) result to greater precision and the WHERE clause ( >999999999) will show only the relevant rows. Note that need to set ANSI_WARNINGS OFF for ARITHABORT ON to be honored. SignatureHope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/ On Jul 24, 6:27 pm, "Dan Guzman" <guzma...@nospam- online.sbcglobal.net> wrote: > > All I want to do is to find the record that is causing the error, but > > the RESULTS pane stops short of the offending row. Is there not an [quoted text clipped - 48 lines] > > - Show quoted text - That's an interesting idea, but I don't actually think the error is happening due to that field that is being converted to decimal, but rather with the multiplication in the numerator. However, it's an If SET ARITHABORT is OFF and one of these errors occurs, a warning message is displayed, and NULL is assigned to the result of the arithmetic operation. Oh, ok, I see what you're getting at. I assume you picked nine 9's because I used decimal (19,10). Thanks that's helpful and has the added benefit of seeing the actual value. And as you said, the key to getting SET ARITHABORT OFF to work was to also SET ANSI_WARNINGS OFF. I kinda liked the latter method not only due to ease of use, but it also pointed out an addition divide by zero error that I didn't see before b/c the overflow error was triggering first. Anyway, good to know - I had no idea that this group was so active - thanks for the quick and helpful responses - and I'll check out your blog. Glad to know that usenet isn't dead yet (in spite of the major telecom's recent attempts to kill it!). Thanks both of you for the helpful advice - turns out that I just hadn't read BOL enough: If either SET ARITHABORT or SET ARITHIGNORE is OFF and SET ANSI_WARNINGS is ON, SQL Server still returns an error message when encountering divide-by-zero or overflow errors. Key is to set arithabort AND ansi_warnings OFF! Then it shows a NULL in the calculated field, but keeps on going (it even gave more helpful info on the messages tab: Arithmetic overflow occurred. Division by zero occurred. >I have the query below that is throwing the error 'Msg 8115, Level 16, >State 2, Line 1 Arithmetic overflow error converting expression to >data type int.': I know I am way late on this one, but an approach I have found production is to get the MIN and MAX for the columns in the calculations. SELECT MIN(LMIimp), MAX(LMIimp), MIN(LMIclicks), MAX(LMIclicks) FROM #lmiMetric SELECT MIN(YPCimp), MAX(YPCimp), MIN(YPCclicks), MAX(YPCclicks) FROM #YPCMetric Often this shows an unexpected value, which I can then use in a WHERE clause test to get back to the problem row. Roy Harvey Beacon Falls, CT |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.