Not enough storage is available to complete this operation - Import Wizard

Question

Not enough storage is available to complete this operation - Import Wizard

homebrew01

SSC Guru

Points: 55539
More actions
April 29, 2015 at 1:52 pm

#307529

I have a flat file from a vendor, with 244 columns, pipe delimited. Some columns have dates, email, status codes etc, but some have a few sentences of text entered by users. Rather than define each of the 244 columns for the correct length, I thought I could just specify all columns globally as varchar(6000) into a staging table, and sort it out later.
I tried importing a small file, which is only 3 rows, plus column names, but the last step of the import wizard fails with:
"Not enough storage is available to complete this operation"
The flat file is only 14 K.
What limit did I hit ??

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

g.britton SSChampion Points: 13863 More actions · Answer 1

homebrew01 (4/29/2015)
I have a flat file from a vendor, with 244 columns, pipe delimited. Some columns have dates, email, status codes etc, but some have a few sentences of text entered by users. Rather than define each of the 244 columns for the correct length, I thought I could just specify all columns globally as varchar(6000) into a staging table, and sort it out later.
I tried importing a small file, which is only 3 rows, plus column names, but the last step of the import wizard fails with:
"Not enough storage is available to complete this operation"
The flat file is only 14 K.
What limit did I hit ??

Dunno but why not save the package then open it in BIDS and see if you can run it from there. You may get more information or at least see where it crashes.

Gerald Britton, Pluralsight courses

Bill Talada SSChampion Points: 11954 More actions · Answer 2

Hmm, I'd try starting with varchar(100) globally and adjust a few upwards. I'm thinking 244 * 6000 is over 1 MB and causing the problem.

I've gotten the message plenty of times before. It often has to do with the carriage return and line feed declared delimiters or column delimiter not matching the file delimiters.

Bill Talada SSChampion Points: 11954 More actions · Answer 3

I had the error until I did this workaround for a unix line ending.

I couldn't do a bulk insert directly with just a linefeed. I had to do it through an exec().

create table import_data (line varchar(max));

go

declare @cmd varchar(1000);

set @cmd = 'bulk insert import_data from ''C:\Program Files (x86)\WordNet\2.1\dict\data.verb'' with (ROWTERMINATOR = '''+char(10)+''')';

exec (@cmd);