May 11, 2011 at 6:06 am
I have a strange problem here
When I execute a stored procedure with parameters, I don't get any error.
But when our JAVA application calls the same procedure it is getting an SQL Exception "Divide By Zero Error" encountered.
I also remember the same happened once previously with me and I had to debug each and every SELECT statement in the procedure to solve the problem. Is there a way by which SQL Server will throw the same error?
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 11, 2011 at 6:16 am
Any way the connection settings are different and maybe you have a divide by datediff() which can result in 0 in java?
Other than that is it works in ssms it works!
May 11, 2011 at 6:35 am
I don't have much idea about the connection settings.
But i remember in my previous case, a query in a derived table was returning a zero value for a particular column and when the final select divides a value by this column value, the error was somehow ignored and no row was returned.
Do we have ANSI SETTINGS related to this?
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 11, 2011 at 6:38 am
Arithabort maybe? Total guess.
May 11, 2011 at 6:48 am
It could be ANSI_WARNINGS or ARITHABORT.
ANSI_WARNINGS and ARITHABORT are both ON for new query windows in SSMS 2008 R2 by default which DOES allow Divide By Zero errors to bubble up as errors...however that is opposite from what you're seeing in SSMS.
DBCC USEROPTIONS
Divide by Zero errors are typically data-driven in that one call can cause the error and another may not due to the data being selected...are you calling the procedure through Java with the same parameters as in SSMS with no change in the state of the data between calls?
I would expect the error to show in both...unless you're calling it with different parameters or your server options are different on the two connections.
Server options are usually not set within connection strings...they are usually set by the driver at connection time and may be controllable through a config file that goes with your JDBC driver but that's a little out of my realm. The best way to see them is using Profiler. I had this issue with a PHP app and I found the differences using Profiler...end result the PHP dev team added a call in their data access layer to set all server options to "the way we wanted them" as part of the "get connection" routine.
Edit: Divide by Zero errors are always data driven, because frankly, we're working with data here folks
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 12, 2011 at 1:16 am
Thanks for the replies folks. I will check the ARITHABORT and the ANSI WARNINGS option along with the connection settings.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 12, 2011 at 7:06 am
No problem...if you have a moment please post back when you arrive at the root cause and/or a solution.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 12, 2011 at 7:17 am
Hi Kingston,
I would check my code for the occurancy of any "/".
When I found one, I would change the code like this:
from:
Nominator / Denominator
to:
Nominator / Nullif(Denominator,0)
Then you get a null value rather than the infamous "Devide by zero" error.
I've written about this at Stack overflow
HIH
Henrik Staun Poulsen
Stovi Software
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply