Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Export is too large for specified buffer size.

Posted on 2006-10-25
5
Medium Priority
?
704 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

636 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