Stopping the execution of a multi-batch script

Question

 

We have a number of customers using the same database and ASP application.  We need to run a script that modifies the database to the latest version.  If the script runs twice it will cause problems so we need to build in a fail safe way of stopping it running a second time.

 

To do this we can update a version table at the end of the script.  At the start of the script we check that the version is the previous one.  If it isn't then we need to abort the enitre script.  The problem is that the RETURN statement will only exit the current batch and execution of the script will continue from the next GO statement.

 

Is there any way to stop a multi-batch script running if a certain condition is met in one batch in such a way that the remaining batches do not run?

Answer 1

What is this script, is it T-SQL?

 

Are you running these from DTSexec or c#....?

 

Can you give a sample of the script style?

How are you doing the batching?

 

Answer 2

You have a few options here.  One option is to put your scripts into a stored procedures, you can use return statement to abort the stored procedure, if the version already exists.  Another option closely resembles the one you suggested about the version table.  You simply create a table that holds the current version of the application and then wrap your script in an IF statement that checks the version and either runs the script or does not.

 

In my environment we have a .bat file for each version.  The .bat calls multiple .sql scripts that update our database to a particular version.  In each .sql file we check the version before running the script (version is stored in a table).  All we have to do is run our bat file from command line and sit back twidling our thumbs.

Answer 3

Here is one of the scripts.  We are running this from the SQL 2005 Management Studio Query Window.

DECLARE @DBVer varchar(10)
DECLARE @PrevVer varchar(10)
DECLARE @ThisVer varchar(10)
SELECT  @PrevVer = '1.0.2.10'
SELECT  @ThisVer = '1.0.2.11'

-- check correct version for update
SELECT @DBVer = dbvlVerNo FROM appDBVerLog WHERE dbvlID IN (SELECT MAX(dbvlID) FROM appDBVerLog)
IF @DBVer != @PrevVer
    BEGIN
    PRINT 'Incorrect version for this update: ' + @DBVer + '.  Should be ' + @PrevVer
    RETURN  -- this RETURN will continue after the next GO statement, I need to abort the script at this point
    END

PRINT 'Running script for version ' + @ThisVer

-- Fixing invalid field value issues
UPDATE appComponents SET compUrl = null WHERE compurl='NULL'
UPDATE appComponents SET compUrl = 'Main.aspx' WHERE compId=1
UPDATE appComponents SET compUrl = 'admin/Tamanager.aspx' WHERE compId=3

IF EXISTS (SELECT 1 FROM dbo.sysobjects
    WHERE id = OBJECT_ID(N'[dbo].[Permission_SelectComponentsByPeople]')
    AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
  DROP PROCEDURE [dbo].[Permission_SelectComponentsByPeople]
GO

CREATE PROCEDURE [dbo].[Permission_SelectComponentsByPeople]
( @username    varchar(255))
AS

BEGIN
    -- contents removed
END
GO

IF EXISTS (SELECT 1 FROM dbo.sysobjects
    WHERE id = OBJECT_ID(N'[dbo].[Permission_SelectPeopleById]')
    AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
  DROP PROCEDURE [dbo].[Permission_SelectPeopleById]
GO

CREATE PROCEDURE [dbo].[Permission_SelectPeopleById]
(    @peopleID        INT)
AS
BEGIN
    -- contents removed
END
GO   

-- update the version number
INSERT INTO appDBVerLog (dbvlVerNo) VALUES (@ThisVer)
GO

Answer 4

Thanks Adam for you comments.

When I say 'batch' I mean everything between the GO statements.  So a multi-batch script is one with multiple DDL statements delimited by GO statements.

From what you are saying it sounds like we need to wrap the IF...THEN statement around each individual batch and that each version increment should include only a single batch - ie only one DDL statement.  I was hoping to check the version at the top of the script and then abort the multi-batch script if not correct.

Answer 5

OK now I see what you are trying to do.

 

Yes you could have the test written into each block ( but there looks to be a lot of blocks )

 

You could create two scripts and only execute the second if the version is correct.

From SSMS you are you are doing this manually anyway the first script could do some clever stuff like rename the extention of the second script from ".onlyifcorrectversion" to ".scr" so whoever is running the scripts would have to 'work around' this little issues if teh first script did not work. ( also the second script should after completing copy it back).

 

My preference is a complied program to call the scripts, then you can have all the control built in.

 

 

 

Answer 6

 

You can raise a fatal error stop all the batch. The fatal error will terminate the connection actually so all the batches will not run.

 

RAISERROR(N'Can not run the script again. It has run before',20,1) WITH LOG

Answer 7

Great idea and almost worked.  Only problem is that Sys Admin role is required to raise a level 20 error.  Unfortunately some of our databases run on managed server.

Answer 8

Did you ever find a solution to this problem? There must be a way to stop script execution without being a sys admin.

 

I would mark this as not answered if there is no solution other than the RAISEERROR solution because it does not solve the problem.

 

Thanks

 

Answer 9

you could try this small app

http://scriptzrunner.codeplex.com/