Catching an uncaptured error in SQL
This is a batch of SQL code I’ve been using:
BEGIN TRY
begin
BULK INSERT DBXMRPAS2.dbo.[STG_drive] FROM 'Wdmyitpxmdev1xmmsbcpcsvfile20080715drive.200807151100.extracting'
WITH
( MAXERRORS =1000,
FIELDTERMINATOR = '^',
ROWTERMINATOR = '|n'
)
end
END TRY
BEGIN CATCH
SELECT 'Error Caught'
SELECT
ERROR_MESSAGE(),
ERROR_NUMBER()
END CATCH
Why would an error from this statement not be captured by the catch if the .CSV file is missing for the given directory?
Software/Hardware used:
Software/Hardware used:
ASKED:
July 15, 2008 6:52 PM
UPDATED:
July 16, 2008 12:10 AM
Answer Wiki:
I'm not sure why it isn't catching the error. I would assume because BULK INSERT is throwing an OS error not a SQL error.
I would recommend checking to see if the file exists prior to attempting the BULK INSERT command. You can do this via the xp_fileexist. You can see an example of use <a href="http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1270919,00.html">here</a>.
To see all answers submitted to the Answer Wiki: View Answer History.
Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.