Solved

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

Posted on 2007-11-29
8
5,608 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

710 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