Max Length of "Text" and "Varchar"

I'm trying to save a memo to a database..
Right now it's declared as "text"
what's the max size of that?2,147,483,647 bytes

MAX doesn't apply for concatenated variables?

If I load up a local variable defined as varchar(MAX) with a 10,000-character continuous string, i.e. '123456789.....100000' the variable retains the full value as expected. However, if I break that string up into '123456789....5000' + '5001...10000', the variable is truncated at 8000 characters.

Is there a way around this?

Thanks.

This has to do with type conversion. SQL Server does not do an auto coversion from a not max varchar to a max varchar. The key is to cast your values to a varchar(max) type. For example:

select len (replicate ('*',10000))

--

8000

select len (replicate (cast('*' as varchar(max)),10000))

--

10000

sql

Max Data Type in SQL2000

Anything I can use in SQL2000 in place of varchar(max) (only in SQL2005) with a 2 GB-per-instance capacity

DECLARE jkcursor CURSOR

READ_ONLY

FOR SELECT top 100 episodeid,episodedate,journalentry

FROM ccnidcdw.[Pre-AuthThin].dbo.tbljournal

WHERE journalentry like '%CoCustServ%'

DECLARE @.episodeid varchar(15), @.episodedate datetime, @.journal varchar(2gb)

OPEN jkcursor

FETCH NEXT FROM jkcursor INTO @.episodeid,@.episodedate,@.journal

WHILE (@.@.fetch_status = 0)

BEGIN

print @.journal

FETCH NEXT FROM jkcursor INTO @.episodeid,@.episodedate,@.journal

END

CLOSE jkcursor

DEALLOCATE jkcursor

thanks

Text/NText and Image comes with restrictions. Check the link below.

http://msdn2.microsoft.com/en-us/library/aa276838(SQL.80).aspx

|||

anything I can do with varchar ?

|||

Nope. Varchar(max) & NVarchar(max) are introduced very first in SQL Server 2005. You have to use Text/NText. But with lot of restrictions.

You don't have other options in SQL Server 2000.

|||

The limit for Varchar is 8000 while Nvarchar is 4000 and there is no Varchar/Nvarchar(max) in 2000 so you have to use Text/NText but you cannot do comparison and other operation with Text/NText. The restrictions are covered in the link I posted.

|||thanks for you help

Max characters over column?

I am trying to bring down my column sizes in SQL Server 2000. Original
creater made them all varchar(1000) and that's way to much. A lot of
these have static lengths and some have a range. What I'm trying to do
is to pick a column, say last_name, and find out the maximum amount of
characters used for the existing data and then adjust accordingly. So
say of all the last names in the last_name column, it came out to 75
characters, I would make the column 100 characters. Just an example.
Any way to parse through a column in a query and find out the maximum
character length used for any given value?
Thanks.
JRSelect Max( Len( MyColumn ) )
From MyTable
How's that?
Colin.
"JR" <jriker1@.yahoo.com> wrote in message
news:1142894694.958758.314000@.i39g2000cwa.googlegroups.com...
>I am trying to bring down my column sizes in SQL Server 2000. Original
> creater made them all varchar(1000) and that's way to much. A lot of
> these have static lengths and some have a range. What I'm trying to do
> is to pick a column, say last_name, and find out the maximum amount of
> characters used for the existing data and then adjust accordingly. So
> say of all the last names in the last_name column, it came out to 75
> characters, I would make the column 100 characters. Just an example.
> Any way to parse through a column in a query and find out the maximum
> character length used for any given value?
> Thanks.
> JR
>