October 20, 2014 at 4:01 pm
I am parsing a bunch of files using TSQL stored procedure and writing values to the tables. I have try-catch block and transaction to handle the errors within the proc.If an error occurs I'd like to log the error but continue with the next file.I can't seem to find a way to do that. I tried using Goto but it din't work. Please shed some light on this. Thanks.
October 20, 2014 at 4:32 pm
Do you have a try...catch block for each table/file?
Can you post what have you tried?
October 20, 2014 at 4:44 pm
Luis Cazares (10/20/2014)
Do you have a try...catch block for each table/file?Can you post what have you tried?
The proc loads multiple tables using a file at a time. I have try catch bloc for the whole proc. If an error occurs anywhere in the proc, i rollback and trap the error in the catch and mark that file with an error and the process exits. But i need to find a way to continue the process despite the error. Due to sensitivity of the data i am unable to post my code here.Thanks.
October 20, 2014 at 5:07 pm
The problem is with your way to handle errors. You need to have a try-catch block for each statement.
I guess that you have something like this:
BEGIN TRY
BEGIN TRAN
BULK INSERT Table1
FROM '\\SomePath\File1.txt'
WITH( FIELDTERMINATOR = '|' , TABLOCK)
BULK INSERT Table2
FROM '\\SomePath\File2.txt'
WITH( FIELDTERMINATOR = '|' , TABLOCK)
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
PRINT 'Error handling in here'
END CATCH;
You could change it to this:
BEGIN TRY
BEGIN TRAN
BULK INSERT Table1
FROM '\\SomePath\File1.txt'
WITH( FIELDTERMINATOR = '|' , TABLOCK)
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
PRINT 'Error handling in here'
END CATCH;
BEGIN TRY
BEGIN TRAN
BULK INSERT Table2
FROM '\\SomePath\File2.txt'
WITH( FIELDTERMINATOR = '|' , TABLOCK)
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
PRINT 'Error handling in here'
END CATCH;
October 20, 2014 at 5:25 pm
Luis Cazares (10/20/2014)
The problem is with your way to handle errors. You need to have a try-catch block for each statement.I guess that you have something like this:
BEGIN TRY
BEGIN TRAN
BULK INSERT Table1
FROM '\\SomePath\File1.txt'
WITH( FIELDTERMINATOR = '|' , TABLOCK)
BULK INSERT Table2
FROM '\\SomePath\File2.txt'
WITH( FIELDTERMINATOR = '|' , TABLOCK)
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
PRINT 'Error handling in here'
END CATCH;
You could change it to this:
BEGIN TRY
BEGIN TRAN
BULK INSERT Table1
FROM '\\SomePath\File1.txt'
WITH( FIELDTERMINATOR = '|' , TABLOCK)
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
PRINT 'Error handling in here'
END CATCH;
BEGIN TRY
BEGIN TRAN
BULK INSERT Table2
FROM '\\SomePath\File2.txt'
WITH( FIELDTERMINATOR = '|' , TABLOCK)
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
PRINT 'Error handling in here'
END CATCH;
BEGIN TRY
BEGIN TRAN
BULK INSERT Table1
FROM '\\SomePath\File1.txt'
WITH( FIELDTERMINATOR = '|' , TABLOCK)
BULK INSERT Table2
FROM '\\SomePath\File1.txt'
WITH( FIELDTERMINATOR = '|' , TABLOCK)
BULK INSERT Table3
FROM '\\SomePath\File1.txt'
WITH( FIELDTERMINATOR = '|' , TABLOCK)
BULK INSERT Table4
FROM '\\SomePath\File1.txt'
WITH( FIELDTERMINATOR = '|' , TABLOCK)
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
PRINT 'Error handling in here'
END CATCH;
It looks more like this where I pass one file at a time to the proc. That file loads a whole bunch of tables.If an error occurs anyhere in the proc id like log error, rollback that file transaction and move to the next file instead of stopping the process.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply