January 27, 2003 at 2:07 pm
I am calling a Stored Procedure from VB. Within this sp is a Transaction block. The problem is I occasionally get a time out error or connection error from VB when running this procedure. If SQL is in the middle of executing the transaction (ie after BEGIN TRANS but before COMMIT TRANS) when the error occurs, it is left hanging and I have to go into SQL and manually commit or rollback the transaction. Is there some method of trapping the error within SQL Server so the transaction will rollback itself?
January 27, 2003 at 3:01 pm
Do you have a proper error handling inside SP? Do you check IF @@ERROR <> 0 after each SQL statement and then GOTO error_handler? Did you put IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION in your error handler?
Why is it timing out? If this is a connection timeout (database connection?), why is it lasting for too long? Is your query slow or you are processing too much data at once? Why not increase the connection timeout interval?
January 29, 2003 at 8:41 am
I was using the @@Error handler for any SQL errors, but I was not using the @@Transcount check.
The error is usually caused by a stored proc that is called from the main procedure which I call from VB. I have the error handler in the main proc. Will this handle the error in the sub proc? It doesn't seem to work for me.
The connection error is usually caused by network issues rather than a timeout. But they both generate the same issues with the transaction, so I use a small timeout to test the error handling. When I the error handler working properly, I will extend the timeout value.
January 29, 2003 at 11:33 am
If you are calling one procedure from another, make sure you handle transactions properly in both procedures. Do not nest transactions; do this:
SET @local_transaction = 0
IF @@TRANCOUNT = 0
BEGIN
BEGIN TRANSACTION
SET @local_transaction = 1
END
IF @@TRANCOUNT > 0 AND @local_transaction = 1 COMMIT TRANSACTION
IF @@TRANCOUNT > 0 AND @local_transaction = 1 ROLLBACK TRANSACTION
When your inner procedure fails, it should return a RETURN value other than 0 to the caller. Check for it immediatelly and handle error properly. In fact, check for return value and @@ERROR in the same time just in case:
EXECUTE @return = usp_do_something @param_1 = @value_1
IF @@ERROR <> 0 OR @return <> 0 GOTO error_handler
Have fun.
Michael
Edited by - mromm on 01/29/2003 11:34:57 AM
January 29, 2003 at 1:05 pm
Also, If you are getting run-time errors in your stored-procs, you can use the SET XACT_ABORT ON option in the proc. This will automatically rollback any transactions that are pending.
-Dan
Edited by - dj_meier on 01/29/2003 1:05:09 PM
-Dan
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply