"Object does not exist" error in transaction. How to rollback?

Question

Post reply

"Object does not exist" error in transaction. How to rollback?

JMI

SSChampion

Points: 11831
More actions
May 8, 2008 at 7:15 am

#182040

"Object does not exist" error occured
in a transaction with TRY/CATCH block.
Rollback din't work.
How to rollback?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

John Mitchell-245523

SSC Guru

Points: 148809

May 8, 2008 at 7:23 am

#812403

Please post your code.

Thanks

John

JMI SSChampion Points: 11831 More actions · Answer 2

BEGIN TRY
set nocount on
BEGIN TRAN
print 'BEGIN TRAN'
....other code
INSERT INTO
SELECT ...
FROM
USFPIMS..prodextend mpe
INNER JOIN USFPIMS..product mp ON ISNULL(mpe.din,'') = ISNULL(mp.din,'') AND ISNULL(mpe.pkgsize,0) = ISNULL(mp.pkgsize,0)
INNER JOIN USFPIMS..client mc ON ISNULL(mp.client,'') = ISNULL(mc.code,'')
LEFT OUTER JOIN USFRET..prodextend rpe ON ISNULL(mpe.din,'') = ISNULL(rpe.din,'') AND ISNULL(mpe.pkgsize,0) = ISNULL(rpe.pkgsize,0)
WHERE
mc.active = 'Y'
AND rpe.din IS NULL AND rpe.pkgsize IS NULL
AND mpe.din NOT IN (SELECT din FROM USFRET..v_ProdExtend_Exclude)
.....other code
COMMIT TRAN
print 'COMMIT TRAN'
END TRY
BEGIN CATCH
ROLLBACK TRAN
print 'ROLLBACK TRAN'
EXEC USFPIMS..MTI_ErrorDetails
END CATCH

Error:

Msg 208, Level 16, State 1, Line 215

Invalid object name 'USFRET..v_ProdExtend_Exclude'.

jaypee_s Right there with Babe Points: 793 More actions · Answer 3

Hello

If object exists, u can proceed.

For example..

if exists (select * from tempdb.dbo.sysobjects where name like ''USFRET..v_ProdExtend_Exclude#')

Then go ahead for insertion...

This way you can check for all object....

Thanks

jaypee.s

JMI SSChampion Points: 11831 More actions · Answer 4

Grasshopper,

That's not my goal.

My goal is to understand if I can handle "Object does not exist" type of errors within transaction

and rollback it in case of error.

Rob

Carl Federl One Orange Chip Points: 25384 More actions · Answer 5

For error handling, please be sure to read Erland Sommarskog's article at

http://www.sommarskog.se/error-handling-I.html

In short, catch/try only works for some errors and I do not recommend any error checking inside of SQL Server except for the return code from a stored procedure execution. Please read http://www.sqlservercentral.com/Forums/Topic491962-8-1.aspx#bm492546

SQL = Scarcely Qualifies as a Language