August 20, 2009 at 9:01 am
Hi,
In SQL Server 2000 i had this stored procedure, that i use to import data from a
file to a table.
By default in the bulk statement in SQL server 2000 if you don't specify the max errors permited, the bulk will ignore 10 errors, and then, if more then 10 errors are raised the bulk stops and raises an error.
This works very good in my SQl Server 2000 database.
When i converted this database to SQL Server 2005 it seems that the bulk statement did not ignored the 10 errors by default. It seems that if there is a single error during loading of the data, then the bulk process stops and reports the error.
In SQL Server 2005 do i have to specify the max errors permited by bulk? or by default it is like 2000 and can permite 10 errors?
In SQL Server 2000 and 2005 i have a table , this table as a column of data type float.
In one record of the file, the bulk tries to import to this column the value:
1,096459E+16
In 2000 the row is ignored (i think) but in 2005 the error is reported and the bulk stops...
The two databases have the exact same data (record) and the same column and columns data types...
Procedure:
BULK INSERT bulk_table
FROM 'c:\test.txt'
WITH
(
FIRSTROW =2,
FIELDTERMINATOR =''|'',
ROWTERMINATOR ='''',
tablock,
DATAFILETYPE =''widechar''
)
August 20, 2009 at 9:42 am
i get these as well and i usually go and try to fix them
you can try in SSIS to move all the garbage rows that error out to a separate path and just drop them. the Wrox SSIS book for 2005 has an example of this
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply