Stop Proccessing on Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stop Proccessing on Error

Author

Topic

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2011-04-18 : 08:50:13

I have a Sp in which i am restoring transactional log backups and after that some insert statements. The problem is like if restore is fail even then also insert statement runs. So i don't want to run insert statement if my restore fails so how can i achieve this.

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-18 : 08:56:04

How are you doing the restore?
Do you have a try catch block to detect errors (if the restore failure gives an error).
You might have to get a return code from the restore and throw the error yourself.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2011-04-18 : 09:03:04

quote:
Originally posted by nigelrivett

How are you doing the restore?
Do you have a try catch block to detect errors (if the restore failure gives an error).
You might have to get a return code from the restore and throw the error yourself.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Thanks for reply no i am not using any kind of try catch block..
what i am doing is something like this

CREATE SP
AS
BEGIN

Do Some Transactional Log Backup Restore

Insert Some data

Again Insert Some data

Update Some Data

Again Update Some data

END

How an i modify this according to my need. I don't want to execute insert and update code if restore is fail.

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-18 : 09:17:47

CREATE SP
AS
BEGIN

begin try

Do Some Transactional Log Backup Restore

Insert Some data

Again Insert Some data

Update Some Data

Again Update Some data

END

end try
begin catch
declare @ErrDesc varchar(max)
declare @ErrProc varchar(128)
declare @ErrLine varchar(20)

select @ErrProc = ERROR_PROCEDURE() ,
@ErrLine = ERROR_LINE() ,
@ErrDesc = ERROR_MESSAGE()
select @ErrProc = coalesce(@ErrProc,'<null>') ,
@ErrLine = coalesce(@ErrLine,'<null>') ,
@ErrDesc = coalesce(@ErrDesc,'<null>')

raiserror('Failed %s', 16, -1, @ErrDesc)
end catch

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources