Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 how to capture all row errors on bulk insert.

Author  Topic 

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-07-16 : 08:56:13
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
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-16 : 09:20:24
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

462 Posts

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

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

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





Go to Top of Page

sapator
Constraint Violating Yak Guru

462 Posts

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

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-16 : 10:48:20
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
   

- Advertisement -