VARCHAR(MAX) not allowed?

25 Aug 2006 6:04 PM

royend

Hi.
I am trying to add a column to my table which should be a big varchar,
therefor I tried this:
ALTER TABLE table_name ADD column_name VARCHAR(MAX)

The error I got was somewhat surprising:
Incorrect syntax near 'MAX'.

What do you think this is?
Every tip and advice are appreciated.
Thanks, royend

25 Aug 2006 6:12 PM

Andrew J. Kelly

Check the compatibility level for that db. If you upgraded from 2000 then it
will still be set to 80 and you need to change it to 90 for any of the new
features to be available.

--
Andrew J. Kelly SQL MVP

<roy***@gmail.com> wrote in message

Show quote

news:1156529081.093751.31610@74g2000cwt.googlegroups.com...
> Hi.
> I am trying to add a column to my table which should be a big varchar,
> therefor I tried this:
> ALTER TABLE table_name ADD column_name VARCHAR(MAX)
>
> The error I got was somewhat surprising:
> Incorrect syntax near 'MAX'.
>
> What do you think this is?
> Every tip and advice are appreciated.
> Thanks, royend
>

25 Aug 2006 6:18 PM

modhak

VARCHAR(MAX) is SQL Server 2005 feature

25 Aug 2006 6:31 PM

Aaron Bertrand [SQL Server MVP]

> VARCHAR(MAX) is SQL Server 2005 feature

Yes. But if your database is in compatibility mode, which means it needs to
work in SQL Server 2000, you can't use SQL Server 2005 features.

25 Aug 2006 6:19 PM

Aaron Bertrand [SQL Server MVP]

> I am trying to add a column to my table which should be a big varchar,
> therefor I tried this:
> ALTER TABLE table_name ADD column_name VARCHAR(MAX)
>
> The error I got was somewhat surprising:
> Incorrect syntax near 'MAX'.
>
> What do you think this is?

Either Andrew is right, and you have 2005 in 2000 compatibility, or are you
using SQL Server 2000 or earlier. VARCHAR(MAX) datatype is only supported
in SQL Server 2005.

25 Aug 2006 6:29 PM

royend

Thanks all.
I will contact our database administrator and check what version we are
using.
royend...

Aaron Bertrand [SQL Server MVP] skrev:
Show quote

> > I am trying to add a column to my table which should be a big varchar,
> > therefor I tried this:
> > ALTER TABLE table_name ADD column_name VARCHAR(MAX)
> >
> > The error I got was somewhat surprising:
> > Incorrect syntax near 'MAX'.
> >
> > What do you think this is?
>
> Either Andrew is right, and you have 2005 in 2000 compatibility, or are you
> using SQL Server 2000 or earlier. VARCHAR(MAX) datatype is only supported
> in SQL Server 2005.

25 Aug 2006 6:48 PM

Aaron Bertrand [SQL Server MVP]

You don't need your DBA for that.

SELECT @@VERSION;

DECLARE @dbName SYSNAME;

SET @dbName = DB_NAME();

EXEC sp_dbcmptlevel @dbName;

<roy***@gmail.com> wrote in message
Show quote

news:1156530598.637508.162360@i3g2000cwc.googlegroups.com...
> Thanks all.
> I will contact our database administrator and check what version we are
> using.
> royend...
>
>
> Aaron Bertrand [SQL Server MVP] skrev:
>> > I am trying to add a column to my table which should be a big varchar,
>> > therefor I tried this:
>> > ALTER TABLE table_name ADD column_name VARCHAR(MAX)
>> >
>> > The error I got was somewhat surprising:
>> > Incorrect syntax near 'MAX'.
>> >
>> > What do you think this is?
>>
>> Either Andrew is right, and you have 2005 in 2000 compatibility, or are
>> you
>> using SQL Server 2000 or earlier. VARCHAR(MAX) datatype is only
>> supported
>> in SQL Server 2005.
>