Ignore SQL Server errors?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I am executing a SQL Server SP to get a DataSet through SqlDataAdapter.Fill.
The problem I have is that sometimes the SP will throw a divide by zero
error which causes a SqlException, even though the error is handled in the SP.

Is there anyway of stopping this error being thrown, as the SP will return
the correct data after the divide by zero error has occured? I have tried
setting ContinueUpdateOnError = True but this does not seem to do anything.

Thanks,
Steve
 
Hello Steve,

I don't know if the SqlDataAdapter looks for the @@ERROR variable. If so,
then you might try to reset the value by putting RAISERROR('Ignore
error',0,1) at the end of your stored procedure.

Of course it is best to avoid the 'divide by zero' at all. Maybe you could
use a function or CASE statement that filters out the 0 value. You might use
something like this to avoid divide by zero's in the SELECT statement:

SELECT [number]/(CASE [Divider] WHEN 0 THEN NULL ELSE [Divider])

Of course you can put something like this in a function, which makes your
code a better to read (and you can use it in other occasions too).

Greetings,
Ramon de Klein
 
Ramon,

Thanks for the suggestion, but adding RAISERROR('Ignore error',0,1) at the
end did not work, I guess because the error has already been thrown by SQL
Server.

I can't use the Case statement becuase the formula that the select is doing
comes from a user input on an ASP.NET page, so the SP does not actually know
what it is about to calculate, only that there is a formula it must calculate.

I guess I'll keep looking around for a solution.

Thanks
 
I would have to see the code, as a captured error should not throw up the
stack. Is this a long running sproc? If so, you may actually have a timeout
instead of an error thrown up. The easiest test would be to implement a sproc
you know will only take a second but throw an error. Then, catch with @@ERROR
and see if you still get the SqlException. If not, the problem is not SQL
error handling.


---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************
 
Hello,
I can't use the Case statement becuase the formula that the select is doing
comes from a user input on an ASP.NET page, so the SP does not actually know
what it is about to calculate, only that there is a formula it must calculate.
Click to expand...

I don't know the exact nature of your application, but this kind of code
injection makes your code extremely vunerable for malicious code. Please
consider the security risks involved with such a solution.
I guess I'll keep looking around for a solution.
Click to expand...

Good luck. I know how tought some SQL issues can be :-)

Greetings,
Ramon de Klein
 
As suggested, I have written a small SP to throw the Divide by Zero error,
and catch it, then select a string to display (see below).
When this is run in Query Analyser I get the error thrown and displayed, and
also the string is returned in the grid view. What would you change to stop
this throwing up the error?

Thanks,
Steve

CREATE PROCEDURE [dbo].[divzero]
AS
DECLARE @ErrNum int
SELECT @ErrNum = 100/0

SELECT @ErrNum = @@ERROR
SELECT 'DivZero'
GO
 
Back
Top