Re-raising error

25 Nov 2005 4:21 PM

Tumurbaatar S.

In a stored procedure I usually check @@ERROR after every
INSERT/UPDATE/DELETE. If any error, then I exit SP with this error
code. A client application does not receive much information
with this code, so it displays a message like "Cannot insert/update record.
Error : NNN".
Is there a way to get more detailed information about an error?
RAISERROR only throws user-defined errors. Any ideas?

25 Nov 2005 5:13 PM

David Browne

"Tumurbaatar S." <spam_tumur@magicnet.mn> wrote in message
news:u6%23Wmxd8FHA.3132@TK2MSFTNGP12.phx.gbl...
> In a stored procedure I usually check @@ERROR after every
> INSERT/UPDATE/DELETE. If any error, then I exit SP with this error
> code. A client application does not receive much information
> with this code, so it displays a message like "Cannot insert/update
> record. Error : NNN".
> Is there a way to get more detailed information about an error?
> RAISERROR only throws user-defined errors. Any ideas?
>

Unless you use TSQL TRY/CATCH the client will recieve both the original
error and the stored procedure return code. Different client libraries
interpret this data differently, but most have a way to grab the error.

David

25 Nov 2005 5:19 PM

marcmc

Is your client application SQLServer or something else (dotnet, java etc)?

If SQL then @@error is fine unless you want to catch the specific error from
sysmessages. I only ever use raiserror(@text,1,1) for triggers and i don't
use triggers so I don't really use it.

If it is an external App and you are not a GOTOless programmer then try
something like below then just call the entries from the log table in the
external App:

If @@ERROR <> 0 or @@ROWCOUNT <= 0
    begin
       select @text = 'Error -50: Could not update TableAdata for week.'
       select @result = -50
       GOTO ERROR_POINT
    end

ERROR_POINT:
    PRINT 'ERROR_POINT'
    GOTO FINISH

FINISH:
    select @resultText = 'RoutineName: ' + @text + ' from User ' + @pWho + ' at
' + convert(varchar(15),getdate(),3) + ' ' + convert(varchar(15),getdate(),14)

INSERT INTO log VALUES (@resultText, 'RoutineName', getdate(), 'Y')

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET NOCOUNT OFF
GO

25 Nov 2005 10:46 PM

ML

No pun intended, but please explain how this one works.

There must be something missing from the sample you posted.

All statements will be executed - even if there is no error and the rowcount
is above 0, and in such a case a null value (or an unexpected one) will be
inserted into the log.

Do you actually use this in your production code?

ML

25 Nov 2005 11:29 PM

Tumurbaatar S.

Thank you!
But you are both talking about SQL2005? If I'm not mistaken,
SQL2000 does not support TRY/CATCH exception handling. And that
is only reason why I asked for how to re-raise (read "rethrow") an error.
If there was try/catch handling in SQL2000, I had no problem.

25 Nov 2005 11:38 PM

David Browne

"Tumurbaatar S." <spam_tumur@magicnet.mn> wrote in message
news:%235tAGhh8FHA.3984@TK2MSFTNGP11.phx.gbl...
> Thank you!
> But you are both talking about SQL2005? If I'm not mistaken,
> SQL2000 does not support TRY/CATCH exception handling. And that
> is only reason why I asked for how to re-raise (read "rethrow") an error.
> If there was try/catch handling in SQL2000, I had no problem.
>

IN SQL 2000 there is no way to prevent the error message from propagating to
the client. If the client is using, for instance, .NET the calling code
will get a SqlException. Only in SQL 2005 is there a way to stop the error
from going to the client (CATCH), and so only there is there any need to
"rethrow" the error in SQL Server.

David

26 Nov 2005 5:04 PM

Tumurbaatar S.

> IN SQL 2000 there is no way to prevent the error message from propagating
> to the client. If the client is using, for instance, .NET the calling
> code will get a SqlException.

But how does a client receive an error? For example, a SP executes
INSERT that fails due to some constraint violation:

INSERT ....
IF @@ERROR <> 0
....

What happens in this case? SP execution stops before IF @@ERROR,
exits with an error notification and the client engine receives a standard
SQL error. Or SQL server remembers this error, the SP continues processing
and when SP exits normally (i.e. RETURN @some_value) the client engine
receives
this return code (@some_value), but also it receives the previously saved
error too?

27 Nov 2005 4:16 AM

David Browne

Show quote

"Tumurbaatar S." <spam_tumur@magicnet.mn> wrote in message
news:e2m$$tq8FHA.472@TK2MSFTNGP15.phx.gbl...
>> IN SQL 2000 there is no way to prevent the error message from propagating
>> to the client. If the client is using, for instance, .NET the calling
>> code will get a SqlException.
>
> But how does a client receive an error? For example, a SP executes
> INSERT that fails due to some constraint violation:
>
> INSERT ....
> IF @@ERROR <> 0
> ...
>
> What happens in this case? SP execution stops before IF @@ERROR,
> exits with an error notification and the client engine receives a standard
> SQL error. Or SQL server remembers this error, the SP continues processing
> and when SP exits normally (i.e. RETURN @some_value) the client engine
> receives
> this return code (@some_value), but also it receives the previously saved
> error too?

Sort of, yes. The client gets the return code and any errors which occured.
Most client libraries don't expose both through the API, however. If an
error occurs, you will likely not get a return code from the procedure.

David

29 Nov 2005 2:56 AM

Tumurbaatar S.

Many thanks!

Show quote

"David Browne" <davidbaxterbrowne no potted m***@hotmail.com> wrote in
message news:e$Zbolw8FHA.740@TK2MSFTNGP11.phx.gbl...
>
> "Tumurbaatar S." <spam_tumur@magicnet.mn> wrote in message
> news:e2m$$tq8FHA.472@TK2MSFTNGP15.phx.gbl...
>>> IN SQL 2000 there is no way to prevent the error message from
>>> propagating to the client. If the client is using, for instance, .NET
>>> the calling code will get a SqlException.
>>
>> But how does a client receive an error? For example, a SP executes
>> INSERT that fails due to some constraint violation:
>>
>> INSERT ....
>> IF @@ERROR <> 0
>> ...
>>
>> What happens in this case? SP execution stops before IF @@ERROR,
>> exits with an error notification and the client engine receives a
>> standard
>> SQL error. Or SQL server remembers this error, the SP continues
>> processing
>> and when SP exits normally (i.e. RETURN @some_value) the client engine
>> receives
>> this return code (@some_value), but also it receives the previously saved
>> error too?
>
> Sort of, yes. The client gets the return code and any errors which
> occured. Most client libraries don't expose both through the API, however.
> If an error occurs, you will likely not get a return code from the
> procedure.
>
> David
>
>
>
>