Help bcp import reports row size too large error

Hi
I want to use bcp tool to export a table from one machine to another
machine. And now the export works fine. But when I want to import the
text file generated by the export process to the target database, bcp
reports an error, and the operation fails. The error is
"Cannot sort a row of size 22416, which is greater than the allowable
maximum of 8094."
I think this error happens because my target table row size is too
big. Because I can not change the schema of target table, I do not how
to resolve it. Anybody can help me? Thanks.Hi
Your row size of 22416 is far more than 8094, therefore I would expect that
the row terminator is not being specified correctly. What command did you use
to BCP out the file and how are you trying to load it?
Are the versions of SQL Server on each machine the same or different?
John
"Hailin.Cai@.gmail.com" wrote:
> Hi
> I want to use bcp tool to export a table from one machine to another
> machine. And now the export works fine. But when I want to import the
> text file generated by the export process to the target database, bcp
> reports an error, and the operation fails. The error is
> "Cannot sort a row of size 22416, which is greater than the allowable
> maximum of 8094."
> I think this error happens because my target table row size is too
> big. Because I can not change the schema of target table, I do not how
> to resolve it. Anybody can help me? Thanks.
>|||If you are using Microsoft's BCP utility to extract the data from the
source database, then I assume the source is SQL Server 2005 and is
using something like VARCHAR(MAX) to allow row size greater than the
normal limit.
If the target table can not be changed you have to decide what is the
proper thing to do with the rows that have the problem. Should they
be discarded? Should the problem column(s) be truncated?
Once you know how you want to deal with the problem, I suggest writing
a VIEW on the source system. The view should truncate the output (if
that is the choice), or skip the rows, whatever you have to do so the
output from the view matches the table on the target system. Then BCP
out from the view, rather than the table.
Roy Harvey
Beacon Falls, CT
On Thu, 27 Sep 2007 16:29:51 -0700, Hailin.Cai@.gmail.com wrote:
>Hi
>I want to use bcp tool to export a table from one machine to another
>machine. And now the export works fine. But when I want to import the
>text file generated by the export process to the target database, bcp
>reports an error, and the operation fails. The error is
>"Cannot sort a row of size 22416, which is greater than the allowable
>maximum of 8094."
>I think this error happens because my target table row size is too
>big. Because I can not change the schema of target table, I do not how
>to resolve it. Anybody can help me? Thanks.|||On Sep 28, 9:42 am, "Roy Harvey (SQL Server MVP)"
<roy_har...@.snet.net> wrote:
> If you are using Microsoft's BCP utility to extract the data from the
> source database, then I assume the source is SQL Server 2005 and is
> using something like VARCHAR(MAX) to allow row size greater than the
> normal limit.
> If the target table can not be changed you have to decide what is the
> proper thing to do with the rows that have the problem. Should they
> be discarded? Should the problem column(s) be truncated?
> Once you know how you want to deal with the problem, I suggest writing
> a VIEW on the source system. The view should truncate the output (if
> that is the choice), or skip the rows, whatever you have to do so the
> output from the view matches the table on the target system. Then BCP
> out from the view, rather than the table.
> Roy Harvey
> Beacon Falls, CT
>
> On Thu, 27 Sep 2007 16:29:51 -0700, Hailin...@.gmail.com wrote:
> >Hi
> >I want to use bcp tool to export a table from one machine to another
> >machine. And now the export works fine. But when I want to import the
> >text file generated by the export process to the target database, bcp
> >reports an error, and the operation fails. The error is
> >"Cannot sort a row of size 22416, which is greater than the allowable
> >maximum of 8094."
> >I think this error happens because my target table row size is too
> >big. Because I can not change the schema of target table, I do not how
> >to resolve it. Anybody can help me? Thanks.- Hide quoted text -
> - Show quoted text -
Thanks for your suggestion. I will try.