We help IT Professionals succeed at work.

Export is too large for specified buffer size.

i2mental
i2mental asked
on
Medium Priority
734 Views
Last Modified: 2012-05-05
I've got a very large text column that has grown out of control that I need to export. The datalength of this field is approximately 6600000. I'm using sql server 2000 and would like to get this out to a file that I could manipulate and then update with the manipulated string. I'm getting a message that the the data column is too large for the specified buffer size.

Alternatively, I would like to just be able to view this entire field without exporting it and just update it in the table. Query analyzer just shows the first bit of the entire string and if I try to view it by opening the table, it just shows nothing in that field.

Any suggestions? I'm at a client site and have to get this resolved quickly.

Thanks
Comment
Watch Question

Commented:
Please clarify the following;

1. Is the column a designated text field in a table (sounds like it) ?
2. When you say that the datalength is 6.6MB, do you mean the size of the text data you're trying to update?
3. Where are you geting the message '... too big for specified buffer size'

One suggestion I can make is that if you want to view more than 256 chars in Query Analyzer, then go to Tools/Options Results Tab in QA and increase the  option 'Max chars per column' to what you desire (max 8192).

Regards

Author

Commented:
1. It is an ntext field.
2. That's the datalength of the data that needs to be manipulated or exported.
3. I get the message during the export from the dts exporter.
Commented:
Hi, sorry for the delay. You could check if the option 'Select into/Bulk Copy'  is set to true and try your export again. (sp_dboption). If that doesn't work, you could try the WRITETEXT command to update the contents of the ntext column directly in a SQL command from Query Analyzer.

This is from SQL Server help;

"WRITETEXT
Permits nonlogged, interactive updating of an existing text, ntext, or image column. This statement completely overwrites any existing data in the column it affects. WRITETEXT cannot be used on text, ntext, and image columns in views."

More information in SQL Server help, with examples.

Cheers

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Commented:
I forgot to add that you could use the command UPDATETEXT as well.

Commented:
create a temp table as
create table #T(id int Identity(1,1), Txt Varchar(8000))
now you can write a loop to you can get 8000 chunks of your text column into a holding table

declare
@Start int,
@End int

Select @Start=1,@End=7999+@Start

While @Start<(Select DataLength(YourTextCol) from YourTexttable)
Begin
insert #T (txt)
Select Substring(YourTextCol,@Start,@End)
Select @Start=@End+1,@End=7999+@Start
End

this way atleast you are able to see the col in SQL (although in chunks) you can do a reverse logic of this loop to append data back to a txt col but use UPDATETEXT to append data back .. not very sophisticated approach but might work.....

rw3admin
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.