VARCHAR(MAX) limited to 1000 Chars?

[ben1234zz]

Hi

We have a VARCHAR(MAX) variable updating data in a VARCHAR(MAX) field but it appears to truncate the data at 1000 chars with no error.

The data being loaded is HTML if that makes any difference.

I am stumpted, I have checked the coding over and over but cannot see any errors.

Any assistance would be excellent, there is too much code to post but if this is a known mistake/problem?

Thanks
B

 

[gmmastros]

I don't know of any known issues with varchar(max) truncating at 1000 characters.

How do you know that it is truncating? If you're looking at the data in a SQL Server Management Studio window (grid view), then it will be truncated when it displays the data.

Click tools->options
expand Query Results -> SQL Server -> Results to grid
There the a "Maximum Characters Retrieved" section. What does it show for Non XML Data?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[genomon]

varchar [ ( n | max ) ]
Variable-length, non-Unicode character data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of data entered + 2 bytes. The data entered can be 0 characters in length. The ISO synonyms for varchar are char varying or character varying.

That being said, the data must be getting truncated somewhere else (possibly from assigning a different variable somewhere, or part of the proc that loads the data?). How about a look at the code?


Cogito eggo sum – I think, therefore I am a waffle.

 

[ben1234zz]

Thanks for your post.

When you said 'somewhere else' it prompted me to think triggers! I stupidly created a VARCHAR(1000) variable in the trigger!

Thank you so much.

B

 

[gmmastros]

Uh oh. Red Flag Alert [small](no, not the Double D variety)[/small]!

I may be worried over nothing, but.... whenever I hear "variable" and "trigger" used in the same sentence, my looping/cursor/broken code spidey sense gets alerted to a potential problem.

EVERY TIME you write a trigger, you MUST write it in such a way that it can handle multiple rows being inserted simultaneously. If you don't write it this way, you open yourself up to potential data issues.

Now, I'm not saying your trigger code is bad. How could I, I have no idea what it looks like. However, I strongly encourage you to double check this trigger and even run some tests to make sure it can handle multiple row updates, inserts and/or deletes. If you're not sure, feel free to post the trigger code here and we'll take a look.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ben1234zz]

Hi

Thank you for your post.

I have fallen foul of writing a trigger for a single row update and having it fail on multiple rows. Its a good one to be cautious of!

Fortunately this trigger is good for multiple record transactions.

Thank you again.

B