Return Store Prosedure Error

[Roy Goldhammer]

Hello there

I have Store Procedure with many actions there.

In the store prodedure i have transaction to make sure that only if all the
actions succedded the entire actions will be done

If one of them fails none of the thins won't done.

But when i run the store procedure with ADP it doesn't gives me any error
that occur in the store procedure

Is there a way to get store prodedure error when it occur?

 

[Malcolm Cook]

make sure severity greater than 10.

else access wont show error message.

most use 16.

 

[Roy Goldhammer]

Sorry malcolm I didn't understand what do you mean severity

 

[Malcolm Cook]

Roy,

How does the stored procedure indicate that an error occurred?

Does it call RAISERROR?

If so, then you must make sure that the 2nd arguement to RAISERROR
(severity) is greater then 10, which will cause access to raise an error
that you can trap for (or allow to be displayed if you don't trap for it, if
I remember correctly).

Here is the argument list to RAISERROR for SQL Server Books On Line, where
you can see the 2nd argument is called 'severity'.

RAISERROR ( { msg_id | msg_str } { , severity , state }
[ , argument [ ,...n ] ] )
[ WITH option [ ,...n ] ]

If your SP does NOT call this, then I didn't understand your question, and I
wonder: How did you intend to return the fact that not "all actions
succedded".

One standard way is to call RAISERROR right before the rollback transaction.

I hope this helps,

 

[Roy Goldhammer]

Thankes Malcolm

I haven't used RAISERROR in my SP because it come by it self. Maybe the
error that rises are not in lavel of 16 and that comes a problem

Here is what i do to use it (i took it from the books online)

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN T1
RETURN @@ERROR
END

This part i do after any action that made on the SP

And i have another problem. In some actions the (error) is that no row
entered or or updated
I've tried to use @@ROWCOUNT and it don't work

Can you help me on it?

Roy,

How does the stored procedure indicate that an error occurred?

Does it call RAISERROR?

If so, then you must make sure that the 2nd arguement to RAISERROR
(severity) is greater then 10, which will cause access to raise an error
that you can trap for (or allow to be displayed if you don't trap for it, if
I remember correctly).

Here is the argument list to RAISERROR for SQL Server Books On Line, where
you can see the 2nd argument is called 'severity'.

RAISERROR ( { msg_id | msg_str } { , severity , state }
[ , argument [ ,...n ] ] )
[ WITH option [ ,...n ] ]

If your SP does NOT call this, then I didn't understand your question, and I
wonder: How did you intend to return the fact that not "all actions
succedded".

One standard way is to call RAISERROR right before the rollback transaction.

I hope this helps,


--
Malcolm Cook - (e-mail address removed)
Database Applications Manager - Bioinformatics
Stowers Institute for Medical Research - Kansas City, MO USA

Sorry malcolm I didn't understand what do you mean severity