[thelist] Weird MS SQL Import from large CSV file...

Anthony Baratta anthony at baratta.com
Mon Jul 17 15:44:43 CDT 2006


I'm helping someone  I work with track down a weird "bug/feature" in MS SQL. We have a 27 MB export file from "elsewhere" that is in a comma seperate format. When we run the following code via SQL at line 86214 nulls start appearing in the phone number and source column .

	create table #tempa
		phone_idx int IDENTITY(1,1),
		phone_nbr varchar(10),
		source varchar(50)

	insert into #tempa (phone_nbr, source)
	select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
	'select * from source_file.csv')

	select min (phone_idx) from #tempa where phone_nbr is null  

All the records in the CSV file "load" with no errors. But starting with line 86214, the nulls start appearing. This number appears to be a "universal" constant, if we remove lines before or after this line number - the error appears at the same Identity number: 86214.

The machine running MS SQL has 2 GBs RAM, 700+ MB free. 50 GBs of free hard drive space. And the msdb database is about 250 MBs in size, log file is small and not imposing.

Any clues are most welcome.

More information about the thelist mailing list