MS SQL: syntax error in create table sql

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?

Code: ( sql )

USE [MyProducts]GO/****** Object: Table [dbo].[Category] Script Date: 10/12/2007 11:22:52 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[Category]( [CategoryID] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NOT NULL, [CategoryNumber] [int] NOT NULL, [ModifiedDate] [datetime] NOT NULL, CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED ( [CategoryID] ASC ) WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY], CONSTRAINT [IX_UniqueCategoryNumber] UNIQUE NONCLUSTERED ( [CategoryNumber] ASC ) WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]) ON [PRIMARY] GOSET ANSI_PADDING OFF

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.|||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.

Thanks.|||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.

MS Sql replication - Primary keys problem

Hi.
I am trying to replicate an MS SQL server but when I do that all my primary keys on the tables on subscriber server are gone and the datebase is unusefull.
Can anyone help me in that situation?

Thanks in advance.

Quote:

Originally Posted by Yordan Yordanov

Hi.
I am trying to replicate an MS SQL server but when I do that all my primary keys on the tables on subscriber server are gone and the datebase is unusefull.
Can anyone help me in that situation?

Thanks in advance.

Set "Copy unique key constraints" property of the articles on the publisher to True if you're on SQL Server 2005.

MS SQL query, whats the default order the rows returned are sorted by?

i have a table and a column called req_id, i have it set as the primary key.. so if i just do SELECT * FROM table, shouldnt the rows returned be sorted by the order that the rows were inserted?

this database was improted from an access database.. when i did that in access it would return the rows in sorted order by the order the row was inserted.. but now in MS SQL, its not sorted in that order.. i can't really tell what type of order it's inIf you want an order, specify the order with the ORDER BY clause. If you are willing to take whatever order the optimizer decides on, omit the ORDER BY clause.

-PatP|||hmm this is weird.. in the access database if i select it, they're returned in the order the rows were inserted.. but after importing that database into ms sql, and selecting that table, the order isn't the same row i got when i ran the query in the access db|||Jet, the default database engine used by MS-Access is rather "simple-minded" when it comes to query optimization. MS-SQL has a much more powerful optimizer, which is a two-edged sword... The MS-SQL optimizer is able to easily process queries that Jet would never complete, but it does that processing in a very different way. As an interesting side effect, it also means that unless you specify an order in your query, there is no guarantee that running the exact same query on the same box will ever return the rows in the same order, even though it often will return them in a consistant order.

-PatP|||Ahhh ok i see what you're saying.

In my query, i had a left join statement in there.. i took that out and used a subquery instead of the join and it returns the rows fine now in the order they were inserted. Looks like the join caused the problem.|||No, the JOIN did not cause the problem. The absence of an ORDER BY statement caused the problem.|||The problem is actually a lot simpler than "the join caused the problem". If you want an order, specify it with the ORDER BY clause. If you don't care about an order and are willing to accept the order determined by the optimizer at the moment, omit the ORDER BY clause.

-PatP

MS SQL 2005 identity lost new database

Hello

I am creating a TABLE

CREATE TABLE [dbo].[TbTest](
[id_TbTest] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED ,
[Title] [varchar](100) NULL,
) ON [PRIMARY]

when looking at the Column Properties in MS Server Management Studio I get :
Identity Specification = Yes
(Is Identity) = Yes
Increment = 1
Seed = 1

now if I want to make a copy of that database, importing datas and tables

I get :
Identity Specification = No
(Is Identity) = No

How can I avoid this problem ? my application is not working anymore

ALTER TABLE [dbo].[TbTest] WITH NOCHECK ADD
CONSTRAINT [PK_TbTest] PRIMARY KEY CLUSTERED
(
[id_TbTest]
) ON [PRIMARY]

is not solving that problem

thank youit sounds like you copied your data using SSIS or DTS or whatever you want to call it. This will copy your data but not your schema. You likely lost all of your indexes and constraints as well. What you want to do if you want to copy all of your data and your schema, is to take a backup of the database you want to copy and restore that database to a new location. If it is a new server, you will have to remap you logins as well.|||With SQL Server 2000 Enterprise Manager you could quickly and easily copy both data and schema between servers. With 2005, you can't. This is Microsoft's idea of progress...

A work-around is to generate a script for you schema and execute it on the target server, and then you SSIS to transfer just the data.|||thank you

what is the code line for ALTER COLUMN to create IDENTITY (1,1)
I must do it now with code ?|||there is not one.

I am a little concerned from what you said in first and last post that you are going to mess up all of your primary key and foreign key relationships.

however if you must you need to create a new table and in your create table statement you must define an identity column. copy the data from your current table to the new one. drop the old table, and rename the new one.|||in that way maybe ?:

ALTER TABLE [dbo].[TbTest] ADD
[id1_TbTest] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED;
ALTER TABLE [dbo].[TbTest] DROP COLUMN [id_TbTest];
EXEC sp_rename '[TbTest].[id1_TbTest]', 'id_TbTest', 'COLUMN';

thank you