ERROR USING BULK INSERT

Hi
I'm executing
declare @.ARCHIVOidx varchar(300)
set @.ARCHIVOidx = (Select top 1 MyFile from task)
declare @.sentencia varchar(300)
set @.sentencia = 'bulk insert a from ''' + @.ARCHIVOidx+'''' + ' with
(formatfile ='''+'c:\input\bcpfmt.txt' + ''',batchsize=100) '
exec (@.sentencia)
and the SQL says:
Server: Msg 3624, Level 20, State 1, Line 1
Location: record.cpp:825
Expression: newColLen >= WideTextPointer::OVERHEAD
SPID: 51
Process ID: 3420
ODBC: Msg 0, Level 16, State 1
Communication link failure
Connection Broken
The event viewer says
18052 : Error: 3624, Severity: 20, State: 1.
THE machine has (SQL DEV):
WIN XP SP1 (Pentium 4), with 40 Gb HD (free 10 Gb), Memory 512 Mb
SQL WITH SP3
What can I do?
Thanks a lot> exec (@.sentencia)
Change this to PRINT @.sentencia and see if what it yields makes sense, also
try executing this manually.|||ok
The output is:
bulk insert a from 'C:\input\bankAA.idx' with (formatfile
='c:\input\bcpfmt.txt',batchsize=100)
"Aaron Bertrand [SQL Server MVP]" wrote:

> Change this to PRINT @.sentencia and see if what it yields makes sense, als
o
> try executing this manually.
>
>|||The Sintax is OK.
The server inserts 600.000 records and generate error, however
using a smaller file it does not generate error. The original file has 2
million registries, the second 600,000 registries (with errors).
The Tsql generates error with the greatest archives
"Macisu" wrote:
> ok
> The output is:
> bulk insert a from 'C:\input\bankAA.idx' with (formatfile
> ='c:\input\bcpfmt.txt',batchsize=100)
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||Okay, now, what happened when you ran this command manually?
"Macisu" <Macisu@.discussions.microsoft.com> wrote in message
news:98CA0497-3E9A-4407-8628-9D51BBBE1434@.microsoft.com...
> ok
> The output is:
> bulk insert a from 'C:\input\bankAA.idx' with (formatfile
> ='c:\input\bcpfmt.txt',batchsize=100)
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||> The Sintax is OK.
I understand that. Which is why I suggested RUNNING the code, not just
looking at it.

> The server inserts 600.000 records and generate error, however
> using a smaller file it does not generate error.
So have you considered using the MAXERRORS parameter? Or cleaning the file
before builk inserting it? Obviously the one with errors in it is failing
because there are rows with errors.
By "smaller file" do you really mean "a file that does not happen to include
any rows that cause an error"?|||The command ran ok, the command generates error 6 mins after executing itsel
f,
when the table has 600.000 records aprox.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Okay, now, what happened when you ran this command manually?
>
>
> "Macisu" <Macisu@.discussions.microsoft.com> wrote in message
> news:98CA0497-3E9A-4407-8628-9D51BBBE1434@.microsoft.com...
>
>|||> The command ran ok, the command generates error
Uh, could you be more specific?|||"Aaron Bertrand [SQL Server MVP]" wrote:

> Uh, could you be more specific?
>
>
the window output says
(100 row(s) affected)
(100 row(s) affected)
(100 row(s) affected)
(100 row(s) affected)
(100 row(s) affected)
(100 row(s) affected)
Server: Msg 3624, Level 20, State 1, Line 1
Location: record.cpp:825
Expression: newColLen >= WideTextPointer::OVERHEAD
SPID: 53
Process ID: 3420
ODBC: Msg 0, Level 16, State 1
Communication link failure
Connection Broken
Query batch completed with errors|||This may be a bug, but I have a couple of questions:
Can you post the format file?
Can you try MAXERRORS = 1 and also MAXERRORS = 100, and see if the
problem moves to a different place than around 600000 rows? (By
default, MAXERRORS is 10.)
Can you watch the computer's memory and virtual memory use to see if it
is going up and up to the max before the crash?
Steve Kass
Drew University
Macisu wrote:

>Hi
>I'm executing
>declare @.ARCHIVOidx varchar(300)
>set @.ARCHIVOidx = (Select top 1 MyFile from task)
>declare @.sentencia varchar(300)
>set @.sentencia = 'bulk insert a from ''' + @.ARCHIVOidx+'''' + ' with
>(formatfile ='''+'c:\input\bcpfmt.txt' + ''',batchsize=100) '
>exec (@.sentencia)
>and the SQL says:
>Server: Msg 3624, Level 20, State 1, Line 1
>Location: record.cpp:825
>Expression: newColLen >= WideTextPointer::OVERHEAD
>SPID: 51
>Process ID: 3420
>ODBC: Msg 0, Level 16, State 1
>Communication link failure
>Connection Broken
>
>The event viewer says
>18052 : Error: 3624, Severity: 20, State: 1.
>THE machine has (SQL DEV):
>WIN XP SP1 (Pentium 4), with 40 Gb HD (free 10 Gb), Memory 512 Mb
>SQL WITH SP3
>What can I do?
>Thanks a lot
>
>
>
>