Solved

Msg 170, Level 15, State 1, Line 4 Incorrect syntax near max on CREATE in SQLCMD

Posted on 2007-11-29
8
5,601 Views
Last Modified: 2008-02-01
Hi everyone, i am trying to run the CREATE proceedure on SQLCMD and i have come accross a snagg.

The code is below.
-------
USE [MyDB]
GO
/****** Object:  Table [dbo].[MyTable]    Script Date: 11/29/2007 13:25:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MyTable](
      [Column1] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Column2] [int] IDENTITY(1,1) NOT NULL,
      [Column3] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
---------

It all is working apart from when i get to
[Column3] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

Anywhere i use[nvarchar](max) it keeps coming up with the above error, regardless of what table i am trying to crate.

Any help would be very much appriated.
Thank you

Baker
0
Comment
Question by:Bakersville
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 27

Expert Comment

by:MikeToole
ID: 20373720
Remove the [] from around the data types (also you don't need them round simple column names):

E.g.
  Column3 nvarchar(max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 20373789
is your target database eventually in compatibility mode 80 (means sql 2000)?
if yes, change it to 90 (sql 2005), and your code should run fine.
0
 
LVL 27

Expert Comment

by:MikeToole
ID: 20373809
Ignore my previous post, your create statement worked for me as is.
0
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20373851
0
 
LVL 27

Expert Comment

by:MikeToole
ID: 20373886
angelIII,
I tried a switch to compatibility = 80 and it still worked.
Now I'm puzzled.
Mike
0
 
LVL 1

Author Comment

by:Bakersville
ID: 20373909
Thank you both for your help.

I changed the compatibility to 90 and it works great.

Thanks for your quick responses, means i can get the site live tonight now.

Baker
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20373956
>angelIII,
>I tried a switch to compatibility = 80 and it still worked.
>Now I'm puzzled.
>Mike
well, you might have take the wrong database/server ....
0
 
LVL 27

Expert Comment

by:MikeToole
ID: 20374655
angelIII,
<well, you might have take the wrong database/server .... >
No, I checked that out. It complained about the COLLATE when I rolled compatibility back to 70, but let the (Max) through.
But at least the problem got fixed :-)
Mike
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question