How do you see max number of errors after executing sql statement?

sspatel80
sspatel80 used Ask the Experts™
on
I am trying to do a bulk insert of 2339 records. I managed to insert 2002 records.
In the message's section at the bottom it shows a max of 10 errors.  What do I have to do so that I'm able to see the maximum number of errors?

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
in your bulk insert statement add MAXERRORS  option and set it to a higher value. if MAXERRORS is not set default is 10 errors, after 10 errors bulk insert stops execution.

Author

Commented:
Is this correct? I still only get 10 errors.  Sorry I'm sure it's simple but I'm very new to SQL.  Thanks.

Bulk insert Catalogue from '\\bccfile\Information Technology\ix\Text Files to Import\catalogue.txt'

WITH (MAXERRORS = 250, Firstrow = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '\n')
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
yes that's correct.
with the above sql your bulk insert should run either till it encounters 250 errors or completes inserting all the records from the text file.

what errors are shown in your message window?

Author

Commented:
These are the errors that I get:

Only 10 are displayed but I know that there are 137 records missing when I do the insert.  I presume there should be at least one error msh for each of these records.

Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 1, column 3 (ID_Prefix).
Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 1712, column 34 (Whole_Part).
Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 2110, column 70 (Created_By).
Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 2128, column 70 (Created_By).
Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 2182, column 34 (Whole_Part).
Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 2183, column 34 (Whole_Part).
Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 2184, column 34 (Whole_Part).
Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 2185, column 34 (Whole_Part).
Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 2186, column 34 (Whole_Part).
Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 2187, column 34 (Whole_Part).
Msg 2601, Level 14, State 3, Line 1
Cannot insert duplicate key row in object 'Catalogue' with unique index 'IX_Catalogue_ID_Number'.
The statement has been terminated.
Commented:
from the last error, its trying to insert duplicate row.
The MAXERRORS option does not apply to constraint checking. so eventhough maxerrors is set to 250 it stops at the first occurance of critical error in this Unique index violation.

Author

Commented:
Thanks Appari the maxerror option was working fine.  You were corrct about the constrain checking issue.  I have managed to fix all the errors.  

Thanks again.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial