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
Solved

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

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

791 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