I am using the below sql to import some data from a file from the intranet. However every once a while, there will be a timeout error and the proc would fail, which is why I am using a transaction. If the transaction fails, I want the ImportedTable to get cleared. However this does not seem to happen. Is there anything I am missing here?
ALTER PROCEDURE [dbo].[pr_ImportData]
@StoreCode varchar(10),
@UserId varchar(100)
AS
BEGIN TRANSACTION
-- 1) Clear the data
exec pr_INTRANET_ClearData @StoreCode, @UserId
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
GOTO EXIT1
END
-- 2) Add the new data to the history Table
INSERT INTO data_History (...)
SELECT ... from ImportedTable WHERE StoreCode = @StoreCode and UserId = @UserId
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
GOTO EXIT1
END
-- 3) Add the data to the live table
INSERT INTO data_Live (...)
SELECT ... from ImportedTable WHERE StoreCode = @StoreCode and UserId = @UserId
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
GOTO EXIT1
END
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
GOTO EXIT1
END
EXIT1:
-- 4) Delete the rows from the temp table
DELETE FROM ImportedTable WHERE StoreCode = @StoreCode and UserId = @UserId
COMMIT TRANSACTION
Update 1: I am running this against SQL 2000 and SQL2005.
Update 2: To clarify: The ImportedTable never gets cleared at Exit1.