How to Abort Entire QA Script?
-
How do you handle conditional processing that spans across batches in SS2K SP3a Query Analyzer?
For example, in psuedo code:
================================================== IF Function Exists BEGIN Print message Abort the entire script <==== HOW DO I DO THIS?? END
-- FLOW SHOULD BE HERE ONLY -- IF FUNCTION DOES NOT EXIST --
EXEC SP_CONFIGURE 'ALLOW UPDATES', 1 RECONFIGURE WITH OVERRIDE USE MASTER GO
CREATE FUNCTION ... GO
EXEC SP_CONFIGURE 'ALLOW UPDATES', 0 RECONFIGURE WITH OVERRIDE GO
-- Also, I'd like to restore the active DB -- to that prior to the above script
USE @OldDB -- won't work because variables -- are all LOCAL to a batch. =======================================================
Any ideas on how to:
1. Abort the entire script, not just the batch
2. Restore the active DB to that prior to start of script
-
There is no easy way to do this. I would suggest to script the creation of the system function into an other file, and do something like this : ( suppose the script to create your function is on your server, named 'd:\temp\fn__mysystemfunction.sql' , and the use master, sp_configure, ... is in that file )
----------------------------------------------------------------
if object_id('tempdb..#batch') is not null
begin
drop table #batch
end
go
if not exists ( select 1 from master..sysobjects where name = 'fn__mysystemfunction' and xtype ='FN' )
begin
select OldDB = db_name(),
ToDo = 'd:\temp\fn__mysystemfunction.sql'
into #batch
end
else
begin
print 'Function exists'
end
go
if object_id('tempdb..#batch') is not null
begin
declare @cmd varchar(4000)
select @cmd = 'isql -E -n -i ' + ToDo from #batch
exec master.dbo.xp_cmdshell @cmd
end
go
if object_id('tempdb..#batch') is not null
begin
drop table #batch
end
go
----------------------------------------------------------------
-
I this case I would divide the script into several Files
One per batch and handle that with a front end tool so that next file does not get executed unless the previous succeedsJust my $0.02
* Noel -
Noel,
What front end tool would you use?
-
You can create a user defined error message with a severity of 20:
exec sp_addmessage 60000, 20, N'Your error message here.', null, 'true', replace
When that error is raised, it will terminate the client connection and thus abort the script.
IF Function Exists
BEGIN
Print message
RAISERROR (60000, 20, 1) WITH LOG
END
-
Jim,
In this type of situation I would make sure that all my objects are scripted out to a file. Then I would create a script that would be something like the following.
DECLARE @vMsg varchar(255)
, @iErr int
--------------------------------------------------
IF object_id('<FunctionName>') IS NOT NULL
BEGIN
SET @vMsg = 'Function Exists'
GOTO ERRHANDLER
END
DECLARE @Cmd nvarchar(4000)
SET @Cmd = 'EXEC SP_CONFIGURE ''ALLOW UPDATES'', 1RECONFIGURE WITH OVERRIDEUSE MASTER'
EXEC sp_executesql @Cmd
SET @iErr = @@ERROR
IF @iErr != 0 GOTO ERRHANDLER
SET @Cmd = 'osql -S<ServerName> -d<DBName> -T -n -b -i<Function_Script_Path>.SQL'
EXEC master.dbo.xp_cmdshell @Cmd
SET @iErr = @@ERROR
IF @iErr != 0 GOTO ERRHANDLER
SET @Cmd = 'EXEC SP_CONFIGURE ''ALLOW UPDATES'', 1RECONFIGURE WITH OVERRIDEUSE MASTER'
EXEC sp_executesql @Cmd
SET @iErr = @@ERROR
IF @iErr != 0 GOTO ERRHANDLER
---------------------------------------------------------
GOTO BAIL -- Jump over error handler
---------------------------------------------------------
ERRHANDLER:
---------------------------------------------------------
RAISERROR(@vMsg,16,1)
-- Do any thing else needed to handle errors here.
---------------------------------------------------------
BAIL:
---------------------------------------------------------
GO
This would allow you to put the object(funtion in your case) in any database as the osql connection string can be dynamically built. The other option would be to simply create a dos batch file similar to what you can create in Visual Studio when you have a database project. This is actually what I usually do rather than the attached script.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply