Import Fails if varchar/nvarchar set to MAX length
I have a SQL 2005 table with the following that I am trying to import in to my DB project::
CREATE TABLE [dbo].[Test_XML]
(
[id] [uniqueidentifier] NULL,
[name] [nvarchar] (50) NULL,
[description] [nvarchar] (MAX) NULL,
[testxml] [xml] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
I get the following error:
Error 1 Unexpected token was encountered.
If I change the [description] [nvarchar] (MAX) NULL, to [description] [nvarchar] (200) NULL,
Then the error will go away and everything will be fine.
Does Team Edtion for DB Devs not support using MAX to define the size? Or is this just a SQL 2005 issues and will be fixed?
Thanks
this happens when executing the CREATE TABLE statement or when importing into the table?
if it happens when importing then try executing this statement first:
EXEC sp_tableoption '[dbo].[Test_XML]', 'large value types out of row', 0
if that doesn't work, what does this yield:
declare
@name sysname select
@name = DB_NAME() exec
sp_dbcmptlevel @name does it say: The current compatibility level is 90.
I noticed it when I tried to import an existing DB into a new project. But it also fails if you modify an existing table and change the size from [description] [varchar] (200) NULL, to [description] [varchar] (MAX) NULL. and save it. I changed all the ones that were set to MAX to 200 for now and eveything works. Seems like there is a problem with using MAX.
When I try:
EXEC sp_tableoption '[dbo].[Test_XML]', 'large value types out of row', 0
I get the same results "Unexpected toekn was encountered:
When I tried:
declare @name sysname
select @name = DB_NAME()
exec sp_dbcmptlevel @name
I do get "The current compatibility level is 90"
