By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,508 Members | 1,915 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,508 IT Pros & Developers. It's quick & easy.

MS SQL: syntax error in create table sql

Expert 5K+
P: 7,872
Using Management Studio (for MS SQL server 8) I had created a table, given a primary key and created a unique key.
I then had the management studio "script 'create' to file" and it saved an SQL statement (below).
I then dropped/deleted the table and tried to execute the creation statement.
It says "Incorrect syntax near '('. " and references the block connected with the "WITH" statement. (Actually both of them cause the error and removing them gets rid of it.
Is there a way to keep the information contained in the WITH statement in my table creation SQL? Is it even required info?

Expand|Select|Wrap|Line Numbers
  1. USE [MyProducts]
  2. GO
  3. /****** Object:  Table [dbo].[Category]    Script Date: 10/12/2007 11:22:52 ******/
  5. GO
  7. GO
  9. GO
  10. CREATE TABLE [dbo].[Category]
  11. (
  12.     [CategoryID] [int] IDENTITY(1,1) NOT NULL,
  13.     [Name] [varchar](50) NOT NULL,
  14.     [CategoryNumber] [int] NOT NULL,
  15.     [ModifiedDate] [datetime] NOT NULL,
  17.     (
  18.         [CategoryID] ASC
  19.     )
  20.     WITH 
  21.     (
  22.         PAD_INDEX  = OFF, 
  24.         IGNORE_DUP_KEY = OFF, 
  25.         ALLOW_ROW_LOCKS  = ON, 
  26.         ALLOW_PAGE_LOCKS  = ON
  27.     ) ON [PRIMARY],
  28.     CONSTRAINT [IX_UniqueCategoryNumber] UNIQUE NONCLUSTERED 
  29.     (
  30.         [CategoryNumber] ASC
  31.     )
  32.     WITH 
  33.     (
  34.         PAD_INDEX  = OFF, 
  36.         IGNORE_DUP_KEY = OFF, 
  37.         ALLOW_ROW_LOCKS  = ON, 
  38.         ALLOW_PAGE_LOCKS  = ON
  39.     ) ON [PRIMARY]
  40. ) ON [PRIMARY]
  42. GO
Oct 19 '07 #1
Share this Question
Share on Google+
3 Replies

Expert 100+
P: 1,017
Usually you never use ‘With’ statement while creating a table.

Remove this statement and create table without it then go to table definition and see it should be picked by default.

Good Luck.
Oct 19 '07 #2

Expert 5K+
P: 7,872
it "appeared" to create correctly without the WITH statements.
I was just bothered by the management studio auto-createing the statement then telling my there were syntax errors. The msdn help on the T-SQL for it shows WITH statements in the smae style.
All well, I'll just remove them.

Oct 19 '07 #3

Expert 100+
P: 1,017
You can change some settings but not all.
Most of them are server level settings and can not be changed and just read only.

For example you see ON [PRIMARY] and it allows you to put data on one device and indexes on different devices or even spread tables among several devices but server should see your other devices first before you are trying to create something on them. So it is available but for people who know what they doing…
You are doing grate so good luck.
Oct 19 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.