July 28, 2009 at 1:42 pm
I've been tasked with modifying our ETL process to continue in the event of a load failure. Currently, if there is an issue our ETL process stops. Very rarely fails, but when it does it's a big deal. I've been playing around with the try-catch block and it seems to do what I want it to do, but I ran into one little issue. I wrote a simple bulk load statement to intentionally fail and it throws the message below.
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 2, column 2 (LAST_NAME).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 3, column 2 (LAST_NAME).
But when I use the try-catch block, the try portion works but the catch block never executes. Read my code below:
begin try
bulk insert users
from 'C:\Documents and Settings\jdgonzalez\Desktop\users_mod.txt'
with (firstrow = 2)
end try
begin catch
SELECT
ERROR_NUMBER() as ErrorNumber,
ERROR_MESSAGE() as ErrorMessage;
end catch
Results:
(0 row(s) affected)
Table code:
USE [SSIS]
GO
/****** Object: Table [dbo].[USERS] Script Date: 07/28/2009 14:35:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[USERS](
[FIRST_NAME] [varchar](50) NULL,
[LAST_NAME] [varchar](10) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Is this by design? If this is correct, I would have no way of knowing there is a problem. Am I missing something?
July 28, 2009 at 2:10 pm
As far as I know you cannot directly trap a terminal error. SSIS is probably a better bet.
However, I did find a post about nesting stored procs to capture the error:
July 28, 2009 at 2:18 pm
Correct. There are some errors that will return the error to the CALLING context (i.e. the context "above" the one the commands are executing in), instead of in the current context.
It's kind of explained in the BOL articule about using TRY...CATCH, but it's reasonably unclear.
That said - if you were to wrap that up into a stored proc and/or a call run through EXEC() and wrap the one of the other in a TRY...CATCH block, you will likely catch the error.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 28, 2009 at 3:28 pm
I got the same results from using an exec statement. It seems to be tied with that type of error. If I change the insert statement to point to a wrong table name, it will catch the error correctly.
That being said, shouldn't T-SQL tell me there was an issue loading? If it's wrapped in a try-catch there should be two events that occur. 1) do what I told you to do. In this case, load the data without any problems. Or 2) Tell me you had an issue.
I seem to be experiencing a third issue. It's not loading data and it's not tell me that there was an issue.
July 28, 2009 at 3:35 pm
Humm.. it seems to be working for me:BEGIN TRY
DECLARE @Sql VARCHAR(4000)
SET @Sql = 'bulk insert users
from ''C:\Documents and Settings\jdgonzalez\Desktop\users_mod.txt''
with (firstrow = 2)'
EXEC(@Sql)
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() as ErrorNumber,
ERROR_MESSAGE() as ErrorMessage
END CATCH
July 28, 2009 at 4:16 pm
I'm still getting this:
(0 row(s) affected)
I'm not surprised by this as it's written to purposely error out. Inserting 0 rows would be correct. My sample file has only two rows. I would have expected a message as to why it did not load records.
It's almost as if the error isn't severe enough to cause it to catch the error.
July 30, 2009 at 1:13 pm
Still on my quest to figure this out. I've noticed something odd. If I run the following code as a stored proc or directly as written I get
Msg 4863, Level 16, State 1, Procedure insertData, Line 3
Bulk load data conversion error (truncation) for row 2, column 2 (LAST_NAME).
Msg 4863, Level 16, State 1, Procedure insertData, Line 3
Bulk load data conversion error (truncation) for row 3, column 2 (LAST_NAME).
It is throwing an error: 4863
Code used:
bulk insert users
from 'C:\Documents and Settings\jdgonzalez\Desktop\users_mod.txt'
with (firstrow = 2)
print 'It is throwing an error: ' + cast(@@error as varchar(20))
But... If I wrap the code in a try-catch block I get this below.
(0 row(s) affected)
It is throwing an error: 0
Shouldn't the same message number get thrown? I'm really starting to believe that this is a bug.
July 30, 2009 at 1:56 pm
What happens when you run this:BEGIN TRY
DECLARE @Sql VARCHAR(4000)
SET @Sql = 'bulk insert flibbityflooo
from ''C:\filethatdoesnotexists.txt''
with (firstrow = 2)'
EXEC(@Sql)
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() as ErrorNumber,
ERROR_MESSAGE() as ErrorMessage
END CATCH
And in this one replace the table name with an existing table:BEGIN TRY
DECLARE @Sql VARCHAR(4000)
SET @Sql = 'bulk insert
from ''C:\filethatdoesnotexists.txt''
with (firstrow = 2)'
EXEC(@Sql)
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() as ErrorNumber,
ERROR_MESSAGE() as ErrorMessage
END CATCH
July 30, 2009 at 2:34 pm
I tried it both ways. When I specify the wrong table name it catches the error.
Error number: 208
Error message: Invalid object name 'susers'
Error Severity: 16
When I specify the correct table name (knowing that it will error out) it does not catch the error. It's not that it doesn't catch the error the error number returned is 0.
I went a bit further loaded the data to a staging table then over to my final one and it catches it. It's almost as if it does not like certain types of errors when you're performing bulk inserts.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply