SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 how to capture all row errors on bulk insert.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sapator
Constraint Violating Yak Guru

Greece
383 Posts

Posted - 07/16/2014 :  08:56:13  Show Profile  Reply with Quote
Hi. So far i can capture the overall error of a bulk insert.

Truncate table [dbo].[dbfile];
Begin Try
begin
    
BULK INSERT [dbo].[dbfile]
FROM 'C:\works\databasebulks\test.txt'
WITH (MAXERRORS = 500000,LASTROW = 100, --CODEPAGE ='ACP',  
--CODEPAGE = '65001',
--CODEPAGE ='1252',
--CODEPAGE ='RAW',
FORMATFILE='C:\work\databasebulks\dbxmlformatfiletests.xml'); 
end
End Try
Begin CATCH
begin
INSERT INTO [dbo].DBInsertErrors
(ErrorNumber, ErrorSeverity, ErrorState, ErrorProcedure, ErrorLine, ErrorMessage, DateTimeStamp)
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
'CSV File Bulk_Insert: ' + ERROR_MESSAGE() as ErrorMessage,
GetDate() as DateTimeStamp;
Print('** Bulk Insert Process error: see table dbo.ProcessCSV_Error for details.**')
end
end catch


This will give an overall error on only one row.
I was wondering if it is possible to capture the row errors. What i mean is that if only run this portion:


BULK INSERT [dbo].[dbfile]
FROM 'C:\works\databasebulks\test.txt'
WITH (MAXERRORS = 500000,LASTROW = 100,  
FORMATFILE='C:\work\databasebulks\dbxmlformatfiletests.xml'); 

It will start, for example, complaining that line 3 is not well formatted, line 8 codepage is wrong,etc. HOWEVER, it will continue the validation and insert lines since the MAXERRORS i set, let it slide. I would like though to be able to record these exceptions, even though they will not affect the final bulk insert.
Thanks.

MichaelJSQL
Posting Yak Master

172 Posts

Posted - 07/16/2014 :  09:20:24  Show Profile  Reply with Quote
you can use the error file option and then if you really want the errors in a table - write a script to load the error file.
Go to Top of Page

sapator
Constraint Violating Yak Guru

Greece
383 Posts

Posted - 07/16/2014 :  10:09:36  Show Profile  Reply with Quote
What is the "error file option" ?
Thanks.
Go to Top of Page

MichaelJSQL
Posting Yak Master

172 Posts

Posted - 07/16/2014 :  10:19:55  Show Profile  Reply with Quote
http://msdn.microsoft.com/en-us/library/ms188365(v=sql.100).aspx





Go to Top of Page

sapator
Constraint Violating Yak Guru

Greece
383 Posts

Posted - 07/16/2014 :  10:28:11  Show Profile  Reply with Quote
Thanks.Can this be more human readable, since it give only error codes?
Thanks.
Go to Top of Page

MichaelJSQL
Posting Yak Master

172 Posts

Posted - 07/16/2014 :  10:48:20  Show Profile  Reply with Quote
there isn't control over that as far as I know. if you do import the errors , you can always join to sys.messages using languageid is 1033
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000