?
Solved

trap "invalid object name" error

Posted on 2003-03-11
2
Medium Priority
?
480 Views
Last Modified: 2008-02-01
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
Comment
Question by:gretzky_owns_u
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 200 total points
ID: 8114924
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
 
LVL 18

Expert Comment

by:nigelrivett
ID: 8115565
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

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question