BULK INSERT to Text field

[sunaj]

I’m bulk loading files into a table which, among other fields, has a text field (the data to be stored here can contain more than 8000 characters). When I try to use BULK INSERT to load this data I get an error message if the data that is going into the field is more than ~5000 characters long. If I cut down the size of the data which is going into the text field, the bulk insert works fine.
Any Ideas?

My T-SQL
[tt]
BULK INSERT tblCTD FROM 'D:\janus\HydChem\tblCTD.csv' WITH (FIELDTERMINATOR = '!', ROWTERMINATOR = '&', TABLOCK)
[/tt]

The Error message
[tt]
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.

Connection Broken
[/tt]

Thx in advance


Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[collierd]

Is it SP2?

 

[sunaj]

It's .760 = service pack 3
Do you think that should make a difference?
-----------------------------------------------------
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
-----------------------------------------------------

Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[collierd]

That should be ok - just a thought
I had a similar problem some time back and it needed SP2 to fix

 

[sunaj]

Anybody else?

Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[Glasgow]

I noticed this post the other day but, sadly, I had nothing to contribute. Today I have hit a very similar problem following a database restore (which still seems to work however). The text is:

[Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionRead (WrapperRead()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Processed 1 pages for database 'ST', file 'STLog' on file 1.

Connection Broken

Did you get anywhere with finding a resolution?

 

[sunaj]

Yes and no. I found that the BULK INSERT (and bcp) has an build in limitation of the field size of 8000 chars - so there is little point of using it to insert into text fields. I changed my program to use an INSERT statement through a ADO.net SQLCommand - which work, but much slower than the BULK INSERT....
Good luck

Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[Glasgow]

OK thanks for response.

I'm a big fan of BULK INSERT so I do sympathise with your own plight.