Solved

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

Posted on 2007-11-29
8
5,593 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
  • 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 142

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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20373851
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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 142

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

758 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now