问题:

Why in the following code TRY didn't catch the error and how can I catch this error?

BEGIN TRY
  BULK INSERT [dbo].[tblABC]
  FROM 'C:\temp.txt'
  WITH (DATAFILETYPE = 'widechar',FIELDTERMINATOR = ';',ROWTERMINATOR = '\n')
END TRY

BEGIN CATCH
  select error_message()
END CATCH

I just get this:

Msg 4860, Level 16, State 1, Line 2
Cannot bulk load. The file "C:\temp.txt" does not exist.

回答1:

This is one option that helps to catch this error:

BEGIN TRY
 DECLARE @cmd varchar(1000)
 SET @cmd = 'BULK INSERT [dbo].[tblABC] 
  FROM ''C:\temp.txt'' 
  WITH (DATAFILETYPE = ''widechar'',FIELDTERMINATOR = '';'',ROWTERMINATOR = ''\n'')'
 EXECUTE (@cmd)
END TRY

BEGIN CATCH
 select error_message()
END CATCH

After this I got the following error in CATCH:

Cannot bulk load. The file "C:\temp.txt" does not exist.

回答2:

You should add MAXERRORS parameter as zero, the default value is 10:

SET @cmd = 'BULK INSERT [dbo].[tblABC] 
  FROM ''C:\temp.txt'' 
WITH (MAXERRORS = 0, DATAFILETYPE = ''widechar'',FIELDTERMINATOR = '';'',ROWTERMINATOR = ''\n'')'