Error when inserting large text into SQL Server

[dominicosavio]

I have this error when I try to insert a large, very large text into a column with Text datatype

Microsoft OLE DB Provider for ODBC Drivers (0x80040E57) [Microsoft][ODBC SQL Server Driver][SQL Server] String or binary data would be truncated.

Can someone help to correct this problem? If I change the datatype of this column from TEXT to NVARCHAR then I will get another error, suggesting I should use CONVERT function.

Thanks

[Roy Sinclair]

How much text are you inserting (ie. how many characters long is the string)?

[dominicosavio]

very long, about 3 or 4000 characters

[raf]

I don't see the problem.
here an overview of all datatypes. Varchar or text should do. (i presume)
http://databasejournal.com/features/mssql/article.php/1442341#part_3

[whammy]

varchar allows 8,000 characters from what I understand, so I would use that since it will also be faster than nvarchar. I would only use Text if you absolutely have to since that will perhaps cause you more problems (since you have to then extract the data using getchunk()).

Make sure that you have manually set the field length - perhaps that's the problem? By default varchar is set at 50 characters...

[Roy Sinclair]

The maximum size for a varchar depends on the version of SQL Server in use. The "text" type supports 2^31 characters so his few thousand shouldn't have been a problem.

[whammy]

No doubt there, Roy (although I have no idea what the differences in varchar length are between SQL Server versions - you seem to have a great deal of experience in this area!) - but I've never heard of a truncation error with a text field, usually that happens when someone sets another field type to a certain number of characters (say, a varchar of 50), and then they try to insert data larger than that. So I suspect that something else is the issue...