@@Error query

Hi,
This is sort-of a follow up to a question I posted yesterday about turning
SQL Server error messages off.
After some more looking around I have found reference that if you use
@.@.ERROR to get the last error, this should stop the error being reported bac
k
to the client. However when I try this the error still gets reported in, fo
r
example, query analyser, should this be the case?
My test SP is listed below.
Thanks,
Steve
CREATE PROCEDURE [dbo].[divzero]
AS
DECLARE @.ErrNum int
SELECT @.ErrNum = 100/0
SELECT @.ErrNum = @.@.ERROR
SELECT 'DivZero'
GOYou cannot supress an error in T-SQL. Of course, you can check the @.@.ERROR
and take an action based on the @.@.ERROR value, but the client will still
receive the error message.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Steve Norman" <SteveNorman@.discussions.microsoft.com> wrote in message
news:B06126DE-8D34-4912-8011-42186196F6D2@.microsoft.com...
Hi,
This is sort-of a follow up to a question I posted yesterday about turning
SQL Server error messages off.
After some more looking around I have found reference that if you use
@.@.ERROR to get the last error, this should stop the error being reported
back
to the client. However when I try this the error still gets reported in,
for
example, query analyser, should this be the case?
My test SP is listed below.
Thanks,
Steve
CREATE PROCEDURE [dbo].[divzero]
AS
DECLARE @.ErrNum int
SELECT @.ErrNum = 100/0
SELECT @.ErrNum = @.@.ERROR
SELECT 'DivZero'
GO|||> After some more looking around I have found reference that if you use
> @.@.ERROR to get the last error, this should stop the error being reported
> back
> to the client.
No, using @.@.ERROR will not stop errors from being raised on the client.
Even if you handle errors in Transact-SQL, your client app needs to be aware
that errors will be raised.
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve Norman" <SteveNorman@.discussions.microsoft.com> wrote in message
news:B06126DE-8D34-4912-8011-42186196F6D2@.microsoft.com...
> Hi,
> This is sort-of a follow up to a question I posted yesterday about turning
> SQL Server error messages off.
> After some more looking around I have found reference that if you use
> @.@.ERROR to get the last error, this should stop the error being reported
> back
> to the client. However when I try this the error still gets reported in,
> for
> example, query analyser, should this be the case?
> My test SP is listed below.
> Thanks,
> Steve
> CREATE PROCEDURE [dbo].[divzero]
> AS
> DECLARE @.ErrNum int
> SELECT @.ErrNum = 100/0
> SELECT @.ErrNum = @.@.ERROR
> SELECT 'DivZero'
> GO
>|||Still it's reported.. with an annoying side-effect : If you are running
some code lauched by the SQL Agent, the job will finished after the first
error is raised, no matter the code you may have written to handle the
error... not that nice so to speak.
Chris
________________________________________
______
It's still better that if it would have been worst, isn't it ?
C'est toujours mieux que si c'etait pire !
"Steve Norman" <SteveNorman@.discussions.microsoft.com> wrote in message
news:B06126DE-8D34-4912-8011-42186196F6D2@.microsoft.com...
> Hi,
> This is sort-of a follow up to a question I posted yesterday about turning
> SQL Server error messages off.
> After some more looking around I have found reference that if you use
> @.@.ERROR to get the last error, this should stop the error being reported
back
> to the client. However when I try this the error still gets reported in,
for
> example, query analyser, should this be the case?
> My test SP is listed below.
> Thanks,
> Steve
> CREATE PROCEDURE [dbo].[divzero]
> AS
> DECLARE @.ErrNum int
> SELECT @.ErrNum = 100/0
> SELECT @.ErrNum = @.@.ERROR
> SELECT 'DivZero'
> GO
>|||So, is there no way of stopping SQL Server from reporting this divide-by-zer
o
error? The problem is that I am running the SP from ASP.NET, and even thoug
h
I handle the error in the SP, the ASP.NET code gets an Exception and errors
before I get the data back.
Any ideas of how to get round this (there is no way of stopping the divide
by zero error in the SP, I have to run SQL from a field in a table which may
or may not cause it)
Thanks,
Steve
"Chris V." wrote:

> Still it's reported.. with an annoying side-effect : If you are running
> some code lauched by the SQL Agent, the job will finished after the first
> error is raised, no matter the code you may have written to handle the
> error... not that nice so to speak.
> Chris
> --
> ________________________________________
______
> It's still better that if it would have been worst, isn't it ?
> C'est toujours mieux que si c'etait pire !
> "Steve Norman" <SteveNorman@.discussions.microsoft.com> wrote in message
> news:B06126DE-8D34-4912-8011-42186196F6D2@.microsoft.com...
> back
> for
>
>|||Well, so far, the way I used is to have a SP calling the SP..
SP_lancher call the SP_active with OSQL, if SP_Active raises an error, it
raises it to OSQL which discards it, hidding it for the client.
I'm not pretending it's an elegant solution..but it's working :)
Chris
________________________________________
______
It's still better that if it would have been worst, isn't it ?
C'est toujours mieux que si c'etait pire !
"Steve Norman" <SteveNorman@.discussions.microsoft.com> wrote in message
news:C0E40943-7BF2-470D-947D-910564408693@.microsoft.com...
> So, is there no way of stopping SQL Server from reporting this
divide-by-zero
> error? The problem is that I am running the SP from ASP.NET, and even
though
> I handle the error in the SP, the ASP.NET code gets an Exception and
errors
> before I get the data back.
> Any ideas of how to get round this (there is no way of stopping the divide
> by zero error in the SP, I have to run SQL from a field in a table which
may
> or may not cause it)
> Thanks,
> Steve
> "Chris V." wrote:
>
first
turning
reported
in,|||The only thing I can think of is if you use ADO.NET in which case you can
trap the error there.
"Steve Norman" wrote:
> So, is there no way of stopping SQL Server from reporting this divide-by-z
ero
> error? The problem is that I am running the SP from ASP.NET, and even tho
ugh
> I handle the error in the SP, the ASP.NET code gets an Exception and error
s
> before I get the data back.
> Any ideas of how to get round this (there is no way of stopping the divide
> by zero error in the SP, I have to run SQL from a field in a table which m
ay
> or may not cause it)
> Thanks,
> Steve
> "Chris V." wrote:
>|||"Steve Norman" <SteveNorman@.discussions.microsoft.com> wrote in message
news:C0E40943-7BF2-470D-947D-910564408693@.microsoft.com...
> So, is there no way of stopping SQL Server from reporting this
divide-by-zero
> error? The problem is that I am running the SP from ASP.NET, and even
though
Sure there is -- make sure you don't have a divide-by-zero error to
begin with.
CASE denominator
WHEN 0.0 THEN 0.0
ELSE numerator / denominator
END
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Apologies about the brain dead reply.
Silly question time...
Why can you have a check on the amount that your dividing by i.e
if @.Amount > 0
Set @.Division = @.Division / @.Amount.
Formally Known as 'Peter The Spate'
"Never ascribe to malice, that which can be explained by incompetence."
Napoleon
"Peter" wrote:
> The only thing I can think of is if you use ADO.NET in which case you can
> trap the error there.
> "Steve Norman" wrote:
>|||I don't think in my case I would be able to do that. Reason being that a
user will enter a formula in an ASP.NET page which can be any equation they
want based upon any number of data columns they choose, my SP will then do
they calculation and return the result.
So unless I do some pretty complicated string manipulation on the formula to
add in case statements, I cannot stop the divide by zero error. However I d
o
handle the error and the SP will continue on and correct the data itself, bu
t
the calling ASP.NET page only reports the error that gets raised by SQL
Server, so I need a way of stopping this error getting back to the ASP.NET
page (SQLClient object).
I'm beginning to think I cannot do it?
Steve
"Adam Machanic" wrote:

> "Steve Norman" <SteveNorman@.discussions.microsoft.com> wrote in message
> news:C0E40943-7BF2-470D-947D-910564408693@.microsoft.com...
> divide-by-zero
> though
> Sure there is -- make sure you don't have a divide-by-zero error to
> begin with.
> CASE denominator
> WHEN 0.0 THEN 0.0
> ELSE numerator / denominator
> END
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>