varbinary(max) datatype invalid

Question

CREATE TABLE Uploads
(
    id          uniqueidentifier NOT NULL PRIMARY KEY,
    DI_Id       INT              NOT NULL,
    FileData    VARBINARY(Max)   NULL,
    sFileName   nvarchar(50)     NOT NULL,
    ContentType nvarchar(50)     NOT NULL
)

I tried to create a table as above.

It produces an error incorrect near varbinary(max).

If I give fixed column size like varbinary(100), then no error occurs.

How can I declare varbinary(max) in SQL Server 2005?

Answer 1

SQL Server 2005 does support VARBINARY(MAX).

Either you're executing this CREATE TABLE statement against a SQL Server 2000 machine, or your database is still in the compatibility level= 80 (SQL Server 2000).

Check your compatibility level with this query:

SELECT name, compatibility_level
FROM master.sys.databases
WHERE name = 'yourdatabase'

If you get an error running this query - you're running against SQL Server 2000 :-)

If your database is level 80, you need to upgrade it to at least level 90 (SQL Server 2005):

ALTER DATABASE YourDatabase SET COMPATIBILITY_LEVEL = 90