Solved

Export is too large for specified buffer size.

Posted on 2006-10-25
5
670 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
0
Comment
Question by:i2mental
  • 3
5 Comments
 
LVL 3

Expert Comment

by:sanjaykp
ID: 17803299
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
 
LVL 8

Author Comment

by:i2mental
ID: 17803324
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
 
LVL 3

Accepted Solution

by:
sanjaykp earned 500 total points
ID: 17804158
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
 
LVL 3

Expert Comment

by:sanjaykp
ID: 17804392
I forgot to add that you could use the command UPDATETEXT as well.
0
 
LVL 11

Expert Comment

by:rw3admin
ID: 17807774
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now