Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2005
    Posts
    266

    Unanswered: 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 you

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    it 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.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Dec 2005
    Posts
    266
    thank you

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

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    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.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Dec 2005
    Posts
    266
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •