Cancel or Stop a database upgrade script

Question

Post reply

Cancel or Stop a database upgrade script

SPR-244445

SSC Enthusiast

Points: 133
More actions
January 24, 2008 at 1:52 am

#62335

Currently, I am tracking database structure changes through upgrade scripts. The database have a version number that each upgrade script update.
I would like to check, in the beginning of each upgrade script if the current version of database is correct.
The script look like this
USE Database
GO
DECLARE @CurrentVersion int,
@Msg varchar(255)
SELECT @CurrentVersion = dbo.GetVersion()
IF @CurrentVersion <> 2
BEGIN
SELECT @Msg = 'Unable to upgrade. Current version is '
+ CAST(@CurrentVersion as varchar(5))
RAISERROR (@Msg, 16, 1)
END
GO
ALTER FUNCTION
...
GO
CREATE PROCEDURE
...
GO
My question is: how can I stop or cancel the script execution when RAISERROR is throw?
Thanks

Viewing 10 posts - 1 through 10 (of 10 total)

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

Sergiy SSC Guru Points: 110209 More actions · Answer 1

Backup database and if error happens during deployment restore the copy from backup.

_____________
Code for TallyGenerator

ChiragNS One Orange Chip Points: 26137 More actions · Answer 2

Hi

If u mean the Raiserror u hv in shown in ur code

use a GOTO whihc goes to the last line of your code.

How ever if u have other error handling in there u are better off using transactions. You will be able to rollback the transaction if any error occurs.

"Keep Trying"

SPR-244445 SSC Enthusiast Points: 133 More actions · Answer 3

Thanks for reply,

That was my first solution, but if you use command GO, you can't refer to label out the scope, that is, defined after GO command. The scope of commands is delimited by command GO.

If quit command GO, I can't use DDL commands, like ALTER FUNCTION and others.

Sergiy SSC Guru Points: 110209 More actions · Answer 4

Did you try to run scripts from osql?

_____________
Code for TallyGenerator

SPR-244445 SSC Enthusiast Points: 133 More actions · Answer 5

Sergiy (1/24/2008)
Did you try to run scripts from osql?

Yes, but have the same result; the script continuous running after raiseerror.

Sergiy SSC Guru Points: 110209 More actions · Answer 6

You must have missed parameter -b.

_____________
Code for TallyGenerator

ChiragNS One Orange Chip Points: 26137 More actions · Answer 7

what about using transactions ? then you can use GOTO .

"Keep Trying"

Sergiy SSC Guru Points: 110209 More actions · Answer 8

Chirag (1/25/2008)
what about using transactions ? then you can use GOTO .

OP already answered this question/

"If quit command GO, I can't use DDL commands, like ALTER FUNCTION and others."

CREATE VIEW/FUNCTION/PROCEDURE statement must be 1st statement in a batch.

_____________
Code for TallyGenerator

ChiragNS One Orange Chip Points: 26137 More actions · Answer 9

Hi

can we use sp_executesql for all the procs/functions and put them inside a transaction.

also have a try/catch.

"Keep Trying"