Data trucated after Insert in a TExt type field

Question

Data trucated after Insert in a TExt type field

thriveni

Ten Centuries

Points: 1104
More actions
October 12, 2004 at 2:23 pm

#161653

I have a SQL server table that has a text(16) field which is updated with a huge amount of text (1000 - 9000 characters)which are actually the contents of an entire text file. However after the Insert , only 256 characters seem to show up. It seems as thought the remaining gets truncated . I am really confused why this is happening. I recall reading that the text field can hold more than 8000 characters
One point to note here would be that the data for the text field has special characters and quotes too.
Any help would be appreciated.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

john-132685 SSCertifiable Points: 6134 More actions · Answer 1

I think you're going to find that the data is truncated before the insert. You didn't mention how the insert is taking place. What is the syntax used? are you using BCP or is the client application doing it? Some client drivers might not support or understand these datatypes.

thriveni Ten Centuries Points: 1104 More actions · Answer 2

Actually this is being done from ACCESS2000. The variable just before the table update from ACCESS shows me the right text with all the characters.

I also just copied and pasted the insert statement(as formed in the ACCESS code) into the SQL query analyzer and executed that. It truncates at the same place.

Do I have to use BCP ? How would I do that ?

hoo-t SSChampion Points: 12713 More actions · Answer 3

How are you viewing the data? For example, Query Analyzer has a default maximum number of characters per column to display in the results pane. I'm not sure what the default is, mine is set to 512 characters. See tools, options, results.

Steve

thriveni Ten Centuries Points: 1104 More actions · Answer 4

That's it ! Thanku so much.

I had mine set to 256 character limit display.

john-132685 SSCertifiable Points: 6134 More actions · Answer 5

I'm not real familiar with Access, but my old fallback is to turn on the SQLProfiler and see for certain exactly what is being sent by the client. (in days gone by client drivers would truncate automatically to 255.)

Then you can also see that if the client driver is using a straight insert, that it is properly escaping the quotes...