• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 482
  • Last Modified:

trap "invalid object name" error

I have a stored procedure with a transaction.  Inside the transaction is an update statement containing an invalid table name.  I want to trap the error and rollback the transaction.

sample code:
ALTER procedure test_test
as
SET NOCOUNT ON
begin transaction insert_tran
     insert into proy
     select top 1 * from proxy
     raiserror 5000 ';ASDJFAKLSF'
     IF @@ERROR <> 0 GOTO error
     
commit transaction insert_tran

goto finished

error:
     rollback transaction insert_tran
     select 'rolled back tran'
finished:
     select 'finished'
0
gretzky_owns_u
Asked:
gretzky_owns_u
1 Solution
 
Scott PletcherSenior DBACommented:
Certain errors can't be trapped before SQL Server acts on them.  I think this is one of them.


You could try using:

SET XACT_ABORT ON


which should cause SQL Server to rollback the transaction for you.
0
 
nigelrivettCommented:
You can't trap this because the SP will fail to compile (can't generate a query plan) - i.e. it won't run the code.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now