• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 706
  • Last Modified:

Export is too large for specified buffer size.

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
0
i2mental
Asked:
i2mental
  • 3
1 Solution
 
sanjaykpCommented:
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

0
 
i2mentalAuthor 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.
0
 
sanjaykpCommented:
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
0
 
sanjaykpCommented:
I forgot to add that you could use the command UPDATETEXT as well.
0
 
rw3adminCommented:
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
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now