hi,
i have created this table in ms sql 2000

CREATE TABLE [Cmm_Category] (
[CatID] [int] IDENTITY (1, 1) PRIMARY KEY CLUSTERED NOT NULL ,
[CatName] [nvarchar] (50) NULL ,
[CatImage] [varchar] (50) NULL ,
[CatFolder] [nvarchar] (50) NULL ,
[Active] [bit] default (1) NULL
) ON [PRIMARY]
GO

but when i export it to my remote server the index of catid and identity field is removed and when i try to modify the table with the syntax below i get erro
Incorrect syntax near '('

alter TABLE [Cmm_Category] Modify (
[CatID] [int] IDENTITY (1, 1) PRIMARY KEY CLUSTERED NOT NULL ,
[CatName] [nvarchar] (50) NULL ,
[CatImage] [varchar] (50) NULL ,
[CatFolder] [nvarchar] (50) NULL ,
[Active] [bit] default (1) NULL
) ON [PRIMARY]
GO


is there any way to set the primary key and identity without recreaing the field?

When you say you "export it to my remote server", do you mean you are using "replication" to do the export? If so, you're out of luck... "replication" will not pass the IDENTITY property to the table on the remote server.

Pretty sure that SELECT/INTO won't do it across servers, either.

--Jeff Moden

no i use export wizard to export my tables to remote host but in this way my primary keys are dropped