varchar only up to 256 characters?

[David Cho]

When I run the code below, SELECT LEN(@mytext) prints 300 as expected.
Yet SELECT @mytext only prints the first 256 characters.

Isn't varchar supposed to have up to 8000? Is there something from the
discrete math class I am forgetting here? Sorry, that was over 10 years
ago.

DECLARE @mytext varchar(500)

SELECT @mytext = '1111111111'
select @mytext = @mytext + '2222222222'
select @mytext = @mytext + '3333333333'
select @mytext = @mytext + '4444444444'
select @mytext = @mytext + '5555555555'
select @mytext = @mytext + '6666666666'
select @mytext = @mytext + '7777777777'
select @mytext = @mytext + '8888888888'
select @mytext = @mytext + '9999999999'
select @mytext = @mytext + '0000000000'

select @mytext = @mytext + '1111111111'
select @mytext = @mytext + '2222222222'
select @mytext = @mytext + '3333333333'
select @mytext = @mytext + '4444444444'
select @mytext = @mytext + '5555555555'
select @mytext = @mytext + '6666666666'
select @mytext = @mytext + '7777777777'
select @mytext = @mytext + '8888888888'
select @mytext = @mytext + '9999999999'
select @mytext = @mytext + '0000000000'

select @mytext = @mytext + '1111111111'
select @mytext = @mytext + '2222222222'
select @mytext = @mytext + '3333333333'
select @mytext = @mytext + '444444444'
select @mytext = @mytext + '5555555555'
select @mytext = @mytext + '6666666666'
select @mytext = @mytext + '7777777777'
select @mytext = @mytext + '8888888888'
select @mytext = @mytext + '9999999999'
select @mytext = @mytext + '0000000000'

SELECT LEN(@mytext)
SELECT @mytext
*** Sent via Developersdex http://www.developersdex.com ***

[Simon Hayes]

I'm guessing you're using Query Analyzer? It only displays 256
characters per column by default - you can change this in Tools -
Options - Results (the maximum value is 8192)

And check out the REPLICATE() function in Books Online:

set @mytext = replicate('x', 300)

Simon

[David Cho]

Thank you. With my varchar, I am doing more than just displaying it
using QueryAnalyzer. The string is being sent via email, but in the
email, it is cut off at 256.

*** Sent via Developersdex http://www.developersdex.com ***

[Simon Hayes]

In that case, you'll have to give more details of how you're sending
the email (xp_sendmail, xp_smtp_sendmail, an external script or tool
etc.), and how you pass @mytext to the email program. Is it a stored
procedure parameter, is it INSERTed into a table and then picked up by
the email program etc.?

It seems likely that somewhere a variable, parameter or table column
has been declared as varchar(256) instead of something larger, but
without more information it's hard to say.

Simon

[David Portas]

How are you generating the email? I'm guessing that somewhere you are
passing the string as a parameter that's declared as VARCHAR/CHAR
(256). The @message param of xp_sendmail supports up to 8000
characters.

--
David Portas
SQL Server MVP
--