Error handling with Transactions
-
If there is a Unique Key violation the below SP Code crashes and returns an error back on the Front End(MS Access). Is there anyway to prevent this from happening. I would have thought that the SP would have handled this problem.
BEGIN TRAN
INSERT INTO tblUtilityReadings(Data)
SELECT Data FROM #tmpReadings
IF @@RowCount = @DetailCOunt and @@ERROR = 0
Begin
COMMIT TRAN
SELECT @ReturnCode =1, @ReturnMsg = 'Successfully Uploaded.'
End
ELSE
Begin
ROLLBACK TRAN
SELECT @ReturnCode =0, @ReturnMsg = 'Readings Upload Failed.'
End
RETURN
Thanks,
fryere
fryere
-
The SP handled the error, but does not mask the error from the client. Since ignoring all errors from the client is a bad idea, you could create a second stored procedure, move all of the existing SQL from the existing SP to the new one, and then EXEC the new SP from the existing one instead of performing all of the SQL. You can then add an OUTPUT parameter or just use the integer return value to inform the existing SP that an ignorable/handled error occurred. The existing SP would then combine that knowledge with @@ERROR immediately after the exec to manually raise an error if a non-ignorable/handled error occurred. Since the client is only (in my experience) notified of errors produced from the top level SP it will then never notice the unique key error.
The COMMIT/ROLLBACK and SELECTs may need to be kept at the end of the existing SP and just done conditionally based on the return code. I'm not sure about performing transaction work within a nested SP, but I think it would work ok if you would rather keep it in the SP that has the INSERT.
One downside (and another reason to keep the new SP as small and simple as possible) is that you loose the error number and description should an error other than the unique key error occur. The client is only given the manually generated error number and description.
Running the code within dynamic SQL would have the same effect, but there are various downsides to using dynamic SQL that I expect you are aware of. Basically, in order to avoid the error being sent to the client it needs to be executed within a nested batch. Since SPs and dynamic SQL operate in their own batch, both abstract the errors away from the client.
-
Try this
BEGIN TRAN
INSERT INTO tblUtilityReadings(Data)
SELECT Data FROM #tmpReadings tmp
where not exists(
select 1
from tblUtilityReadings tbl
where tmp.PrimaryKey1 = tbl.PrimaryKey1
and tmp.PrimaryKey2 = tbl.PrimaryKey2
 
IF @@RowCount = @DetailCOunt and @@ERROR = 0
Begin
COMMIT TRAN
SELECT @ReturnCode =1, @ReturnMsg = 'Successfully Uploaded.'
End
ELSE
Begin
ROLLBACK TRAN
SELECT @ReturnCode =0, @ReturnMsg = 'Readings Upload Failed.'
End
RETURN
This will prevent same data added again to your table from the temporary table and avoid PK violation.
-
Thanks for the replys.
Bimal your not exists statement might just me the answer to this problem as well as some others...Thanks.
fryere
-
***Double Post****
fryere
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply