sspatel80
asked on
How do you see max number of errors after executing sql statement?
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
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
appari
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.
ASKER
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')
Bulk insert Catalogue from '\\bccfile\Information Technology\ix\Text Files to Import\catalogue.txt'
WITH (MAXERRORS = 250, Firstrow = 1, FIELDTERMINATOR = '|', ROWTERMINATOR = '\n')
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?
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?
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Thanks again.