Aborting batch processing in QA

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

SQL Server 2000 Forums

SQL Server Administration (2000)

Aborting batch processing in QA

Author

Topic

barmalej
Starting Member

40 Posts

Posted - 2006-02-15 : 05:24:31

Hi,
If I have several batches in QA separated by GO.

Is it possible to write a command to interrupt further processing under cirtain condition, for example after error or failed validation?

Usually QA interrupts current batch on serious error but starts next one after GO.

Thanks

mallier
Starting Member

24 Posts

Posted - 2006-02-15 : 07:08:06

check this generic code,

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#errortable')) DROP TABLE #errortable
GO
CREATE TABLE #errortable (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
--write SQL statment here
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0
BEGIN
INSERT INTO #errortable (Error) SELECT 1
BEGIN TRANSACTION
END
GO
--write SQL statment here
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #errortable (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF EXISTS (SELECT * FROM #errortable) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The updated succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The update failed'
GO
DROP TABLE #errortable
GO

--Note 1: u should check the error after every sql statement.
--Note 2 U should start MSDTC if ur batch query refer any linked server or remote server

cheers,
http://mallier.blogspot.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources