?
Solved

Export is too large for specified buffer size.

Posted on 2006-10-25
5
Medium Priority
?
696 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

777 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