CUSTOM trigger errors NOT handled

CUSTOM trigger errors NOT handled

Post by tani » Sat, 04 Oct 2003 16:15:47



Hi

In my stored procedure I handle all errors I get.
E.g. , after any sql I say:



This catches normal sql errors raised by sql server.
HOWEVER, I have custom triggers as well that raise errors
and my error handling does not work in this case!!

Why ? PLEASE HELP !!

In my custom triggers my raiserrors look something like
this: (I do not add any messages to sysmessages)
---------------------------

rollback transaction
return  
---------------------------

Thanks
Tania

 
 
 

CUSTOM trigger errors NOT handled

Post by Jacco Schalkwij » Sat, 04 Oct 2003 18:08:06


Hi Tania,

ROLLBACK TRAN in the trigger terminates the batch, so the error handling
code never gets executed.
See the topic "Rollbacks in Stored Procedures and Triggers" in Books Online.

In addition to that:





0 is UNKNOWN and not TRUE, so the execution won't jump to SoftErr

hth

--
Jacco Schalkwijk
SQL Server MVP


> Hi

> In my stored procedure I handle all errors I get.
> E.g. , after any sql I say:



> This catches normal sql errors raised by sql server.
> HOWEVER, I have custom triggers as well that raise errors
> and my error handling does not work in this case!!

> Why ? PLEASE HELP !!

> In my custom triggers my raiserrors look something like
> this: (I do not add any messages to sysmessages)
> ---------------------------

> rollback transaction
> return
> ---------------------------

> Thanks
> Tania


 
 
 

1. Custom Task Error Handling - Displaying Custom Error Message

Hi,

I'm currently working on creating a custom task in VB6.
When it comes to error handling, the recommended error
handling procedure in MSDN is to bubble up the error to
the package event sink using oPackageEvent.onError().
Hence:

--- Sample Code Starts ---

Private Sub CustomTask_Execute(ByVal pPackage As Object,
ByVal pPackageEvents As Object, ByVal pPackageLog As
Object, pTaskResult As DTS.DTSTaskExecResult)
   On Error GoTo errHandler

   ' Business logic
   openConnection(param, param)

   pTaskResult = DTSTaskExecResult_Success
   Exit Sub

errHandler:

   pTaskResult = DTSTaskExecResult_Failure
   if Err.Number=xxxx Then closeConnection

   ' Bubble errors to the package eventsink
   If Not pPackageEvents Is Nothing Then
      pPackageEvents.OnError "CustomTask_Execute method
failed", Err.Number, Err.Source, Err.Description, "",
0, "", True
   End If

   Exit Sub
End Sub

--- Sample Code Ends ---

However, I notice that when error is raised, the specific
error message (Err.Description) will not be displayed on
the DTS Designer execution status dialog box. The only
message I'll get would be "The task reported failure on
execution".

I'm not sure if I have done it correctly. Anyone knows how
to get the custom error messages displayed?

Thanks!

Philip Tan

2. online example of HR visual model

3. Error handling and custom error messages

4. Free Informix jdbc-odbc Driver?

5. Custom Task Error Handling

6. Help

7. no custom error handling

8. ODBC row length error in DTS

9. Custom error handling for HTTPODBC.DLL ??

10. Error Handling Custom

11. Custom Task Error Handling

12. [OPAL] Custom Menus: Erratic operation, not triggering events?

13. Error handling in DB-Trigger