begin/rollback transaction help 1

[Richey]

hi

i've got a piece of code below which works fine, i've since added in lines to try and catch any errors, using begin transaction, rollback etc.
There are 2 bulk inserts which take place, if either one fails I want to rollback completely, i.e. don't truncate the table, don't drop/re-create the indexes - don't do anything !
if the drop indexes fails I'm happy for the scipt to continue. Does my code look ok or have i over/under complicated ? (I don't think i need to rollback if the drop indexes fails)

thanks
kim

/*

	SQL		CRM_load_LLPG.sql
	Date Written 	11 October 2005
	Purpose 	To populate CAD table cad_base_table with combined LLPG data 	Amendments
	
*/

use crmcadt
go

BEGIN TRANSACTION

truncate table crmuser.cad_base_table
go

drop index crmuser.CAD_BASE_TABLE.IDX_CAD_BLDGNUMBR,
           crmuser.CAD_BASE_TABLE.IDX_CAD_SUBBLDNAME,
           crmuser.CAD_BASE_TABLE.IDX_CAD_BLDGNAME,
           crmuser.CAD_BASE_TABLE.IDX_CAD_THOROFARE,
           crmuser.CAD_BASE_TABLE.IDX_CAD_DTHOROFARE,
           crmuser.CAD_BASE_TABLE.IDX_CAD_POSTCODE,
           crmuser.CAD_BASE_TABLE.IDX_CAD_OLDPOSTCODE,
           crmuser.CAD_BASE_TABLE.IDX_CAD_PAON,
           crmuser.CAD_BASE_TABLE.IDX_CAD_SAON
go

IF @@ERROR <> 0
 BEGIN
    ROLLBACK

    RAISERROR ('Error in droping indexes.', 16, 1)
    RETURN
 END

BULK INSERT crmcadt.crmuser.cad_base_table
FROM '\\serverA\E$\LLPG\llpg1.txt'
WITH
(DATAFILETYPE = 'char',
 FIELDTERMINATOR='|')
go

BULK INSERT crmcadt.DBO.cad_base_table
FROM '\\servera\E$\LLPG\llpg2.txt'
WITH
(DATAFILETYPE = 'char',
 FIELDTERMINATOR='|')
go

IF @@ERROR <> 0
 BEGIN
    ROLLBACK

    RAISERROR ('Error in inserting llpg data.', 16, 1)
    RETURN
 END

CREATE  INDEX [IDX_CAD_BLDGNUMBR] ON [crmuser].[CAD_BASE_TABLE]([BLDGNUMBR]) ON [USER]
GO
CREATE  INDEX [IDX_CAD_SUBBLDNAME] ON [crmuser].[CAD_BASE_TABLE]([SUBBLDNAME]) ON [USER]
GO
CREATE  INDEX [IDX_CAD_BLDGNAME] ON [crmuser].[CAD_BASE_TABLE]([BLDGNAME]) ON [USER]
GO
CREATE  INDEX [IDX_CAD_THOROFARE] ON [crmuser].[CAD_BASE_TABLE]([THOROFARE]) ON [USER]
GO
CREATE  INDEX [IDX_CAD_DTHOROFARE] ON [crmuser].[CAD_BASE_TABLE]([DTHOROFARE]) ON [USER]
GO
CREATE  INDEX [IDX_CAD_POSTCODE] ON [crmuser].[CAD_BASE_TABLE]([POSTCODE]) ON [USER]
GO
CREATE  INDEX [IDX_CAD_OLDPOSTCODE] ON [crmuser].[CAD_BASE_TABLE]([OLDPOSTCODE]) ON [USER]
GO
CREATE  INDEX [IDX_CAD_PAON] ON [crmuser].[CAD_BASE_TABLE]([PAON]) ON [USER]
GO
CREATE  INDEX [IDX_CAD_SAON] ON [crmuser].[CAD_BASE_TABLE]([SAON]) ON [USER]
GO

IF @@ERROR <> 0
 BEGIN
    ROLLBACK

    RAISERROR ('Error in re-creating indexes.', 16, 1)
    RETURN
 END

COMMIT

 

[JBaileys]

Your code seems straight forward to me.

I do remember (might be prior to sql 2000) - that bulk inserts were not logged events therefore there was no way to rollback. This may be false at this time. I would just test and make sure the rollback works as expected especially after you truncate the table.

-jb

 

[Richey]

thanks

i get an error now - I know there is an error within the 2nd bulk insert, but using query analyser i get

The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
Server: Msg 50000, Level 16, State 1, Line 6
Error in inserting llpg data.................
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
Server: Msg 50000, Level 16, State 1, Line 6
Error in re-creating indexes.


so my new question is............how can i check both bulk inserts have worked, if so committ else rollback the whole thing (including truncate and droping indexes)

thank you

 

[mrdenny]

First, you need to check for an error after each statement. If the first bult insert has an error and the second one is fine then @@ERROR will be 0. when you check it where you have it.

You'll need to put everything into a single batch. When you through a level 16 error it stops the current batch, but it will then start the next one.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Richey]

ok...............I was using the code below, would that do the trick now then as i am checking for an error after each statement (bar the use crmwht statements but I'm not bothered about those really)?
so any error with any of the bulk inserts or truncate/indexes will neither insert take place and the truncate table crmuser.cad_base_table won't happen ?

thank you


so

/*

	SQL		CRM_load_LPG.sql
	Date Written 	11 October 2005
	Purpose 	To populate CAD table information on 
	Amendments	
	23/11/2005	R.G.HUGHES	include bulk insert for wards in warehouse sql table plus add begin and rollback 					transaction.
	
*/

use crmwht
go

truncate table crmwht.dbo.wards

BULK INSERT crmwht.dbo.Wards
FROM '\\servera\E$\LPG\wards.txt'
WITH
(DATAFILETYPE = 'char',
 FIELDTERMINATOR='|')
go

use crmcadt
go

begin transaction
truncate table crmuser.cad_base_table
go
IF @@ERROR <> 0
 BEGIN
    -- Rollback the transaction
    ROLLBACK

    -- Raise an error and return
    RAISERROR ('Did not truncate CAD_TABLE.', 16, 1)
    RETURN
 END

drop index crmuser.CAD_BASE_TABLE.IDX_CAD_BLDGNUMBR,
           crmuser.CAD_BASE_TABLE.IDX_CAD_SUBBLDNAME,
           crmuser.CAD_BASE_TABLE.IDX_CAD_BLDGNAME,
           crmuser.CAD_BASE_TABLE.IDX_CAD_THOROFARE,
           crmuser.CAD_BASE_TABLE.IDX_CAD_DTHOROFARE,
           crmuser.CAD_BASE_TABLE.IDX_CAD_POSTCODE,
           crmuser.CAD_BASE_TABLE.IDX_CAD_OLDPOSTCODE,
           crmuser.CAD_BASE_TABLE.IDX_CAD_PAON,
           crmuser.CAD_BASE_TABLE.IDX_CAD_SAON
go

IF @@ERROR <> 0
 BEGIN
    -- Rollback the transaction
    ROLLBACK

    -- Raise an error and return
    RAISERROR ('Did not remove indexes.', 16, 1)
    RETURN
 END

BULK INSERT crmcadt.crmuser.cad_base_table
FROM '\\servera\E$\LPG\llpg.txt'
WITH
(DATAFILETYPE = 'char',
 FIELDTERMINATOR='|')
go

IF @@ERROR <> 0
 BEGIN
    -- Rollback the transaction
    ROLLBACK

    -- Raise an error and return
    RAISERROR ('Did not load lpg data.', 16, 1)
    RETURN
 END

BULK INSERT crmcadt.crmuser.cad_base_table
FROM '\\servera\E$\LPG1\llpg1.txt'
WITH
(DATAFILETYPE = 'char',
 FIELDTERMINATOR='|')
go

IF @@ERROR <> 0
 BEGIN
    -- Rollback the transaction
    ROLLBACK

    -- Raise an error and return
    RAISERROR ('Did not load lpg1 data.', 16, 1)
    RETURN
 END

CREATE  INDEX [IDX_CAD_BLDGNUMBR] ON [crmuser].[CAD_BASE_TABLE]([BLDGNUMBR]) ON [USER]
GO
CREATE  INDEX [IDX_CAD_SUBBLDNAME] ON [crmuser].[CAD_BASE_TABLE]([SUBBLDNAME]) ON [USER]
GO
CREATE  INDEX [IDX_CAD_BLDGNAME] ON [crmuser].[CAD_BASE_TABLE]([BLDGNAME]) ON [USER]
GO
CREATE  INDEX [IDX_CAD_THOROFARE] ON [crmuser].[CAD_BASE_TABLE]([THOROFARE]) ON [USER]
GO
CREATE  INDEX [IDX_CAD_DTHOROFARE] ON [crmuser].[CAD_BASE_TABLE]([DTHOROFARE]) ON [USER]
GO
CREATE  INDEX [IDX_CAD_POSTCODE] ON [crmuser].[CAD_BASE_TABLE]([POSTCODE]) ON [USER]
GO
CREATE  INDEX [IDX_CAD_OLDPOSTCODE] ON [crmuser].[CAD_BASE_TABLE]([OLDPOSTCODE]) ON [USER]
GO
CREATE  INDEX [IDX_CAD_PAON] ON [crmuser].[CAD_BASE_TABLE]([PAON]) ON [USER]
GO
CREATE  INDEX [IDX_CAD_SAON] ON [crmuser].[CAD_BASE_TABLE]([SAON]) ON [USER]
GO

IF @@ERROR <> 0
 BEGIN
    -- Rollback the transaction
    ROLLBACK

    -- Raise an error and return
    RAISERROR ('Did not create indexes.', 16, 1)
    RETURN
 END

COMMIT

 

[mrdenny]

You need to put all the commands into a single batch (remove the "GO" statementes from within the transaction).

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com