Altering Identity Column

I have a field in my table that is an identity field.  I have to write script to set the identity property to 'NO'.

I tried this

Alter Table MyTable
      Alter Column MyColumn Int Not Null
Go

But the identity property remains 'Yes'.  How can I change it through tsql script?
LVL 6
billy21Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SashPCommented:
SQL Enterprise Manager removes it by dropping and re-creating the column.
0
billy21Author Commented:
>SQL Enterprise Manager removes it by dropping and re-creating the column.


Not an option.  This field is my primary key.  I need to protect the data.
0
SashPCommented:
To remove the identity from a table with just an identity field.

BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_Table1
    (
    id int NOT NULL
    )  ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.Table1)
     EXEC('INSERT INTO dbo.Tmp_Table1 (id)
        SELECT id FROM dbo.Table1 TABLOCKX')
GO
DROP TABLE dbo.Table1
GO
EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1', 'OBJECT'
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

SashPCommented:
I believe that this is the only way.  Enterprise manager should look after your data, relationships and constraints.

Try this

Create a new diagram.

Add your table.

Remove the identity property from your column (do not save).

Right click on the diagram desktop and click "Save Change Script"

Have a look at the approach the EM will use for removing the identity.

Sash

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Duane LawrenceCommented:
I am just guessing but, I think you may only be trying to suspend the identity for a short time.  If that is the case then the below is the answer.

http://www.experts-exchange.com/Databases/Q_21040806.html
set identity_insert Tbl_TrailerSCAC_Info Off
set identity_insert Tbl_TrailerSCAC_Info On

Duanelawrence
databasedoctor
0
billy21Author Commented:
Strange.

I did as you said and pulled the script from EM.  When I run it against the same database I get a comedy of errors.

Server: Msg 3728, Level 16, State 1, Line 12
'DF__SPService__DateO__24416500' is not a constraint.
Server: Msg 3727, Level 16, State 1, Line 12
Could not drop constraint. See previous errors.

(122 row(s) affected)

Caution: Changing any part of an object name could break scripts and stored procedures.
The OBJECT was renamed to 'SPServicePlacementHistory'.
Server: Msg 3902, Level 16, State 1, Line 1
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

The script is...

Server: Msg 3728, Level 16, State 1, Line 12
'DF__SPService__DateO__24416500' is not a constraint.
Server: Msg 3727, Level 16, State 1, Line 12
Could not drop constraint. See previous errors.

(122 row(s) affected)

Caution: Changing any part of an object name could break scripts and stored procedures.
The OBJECT was renamed to 'SPServicePlacementHistory'.
Server: Msg 3902, Level 16, State 1, Line 1
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.



THIS IS EXTREMELY MESSY

0
billy21Author Commented:
This is the script...

      BEGIN TRANSACTION
      SET QUOTED_IDENTIFIER ON
      SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
      SET ARITHABORT ON
      SET NUMERIC_ROUNDABORT OFF
      SET CONCAT_NULL_YIELDS_NULL ON
      SET ANSI_NULLS ON
      SET ANSI_PADDING ON
      SET ANSI_WARNINGS ON
      COMMIT
      BEGIN TRANSACTION
      ALTER TABLE dbo.SPServicePlacementHistory
            DROP CONSTRAINT DF__SPService__DateO__24416500
      GO
      CREATE TABLE dbo.Tmp_SPServicePlacementHistory
            (
            DateOfChange datetime NOT NULL,
            service_placement_id int NOT NULL,
            supplier_id int NOT NULL,
            supplier_client_no int NULL,
            recipient_client_no int NOT NULL,
            case_type int NOT NULL,
            case_sub_type int NULL,
            case_no int NOT NULL,
            ref_no int NOT NULL,
            purchasing_agency int NULL,
            purchasing_team int NULL,
            finance_client_category int NULL,
            supplier_type int NULL,
            service int NULL,
            care_type int NULL,
            special_scheme int NULL,
            room_type int NULL,
            status int NULL,
            status_date datetime NULL,
            planned_start_date datetime NULL,
            planned_end_Date datetime NULL,
            actual_start_date datetime NULL,
            actual_end_date datetime NULL,
            setupby_agency int NULL,
            setupby_team int NULL,
            setupby_spno int NULL,
            authorising_agency int NULL,
            authorising_team int NULL,
            authorising_spno int NULL,
            booking_cancelling_agency int NULL,
            booking_cancelling_team int NULL,
            booking_cancelling_spno int NULL,
            placement_cancelling_agency int NULL,
            placement_cancelling_team int NULL,
            placement_cancelling_spno int NULL,
            notes varchar(250) NULL,
            reason_ended int NULL,
            block_contract bit NULL,
            authorising_error varchar(250) NULL,
            declined_reason int NULL,
            sp_booked bit NULL,
            sp_authorised bit NULL,
            sp_cancelled bit NULL,
            StartReason int NULL,
            doh_code char(2) NULL,
            Urgent bit NULL,
            RespiteRules bit NOT NULL,
            MAXSOSValue money NOT NULL,
            sp_start_reason int NULL,
            DefaultServiceDetail int NOT NULL,
            distance_to_placement int NULL,
            FirstAdmission bit NULL,
            SourceOfAdmission int NULL,
            ServicePriority int NULL,
            TypeOfService int NULL,
            FirstTimeClient bit NULL,
            ReferredBefore bit NULL,
            ServiceSupport int NULL,
            Proximity int NULL,
            DischargedBy int NULL,
            DischargedTo int NULL,
            Carer int NULL,
            TransferToFrom int NULL,
            TransferTarget int NULL
            )  ON [PRIMARY]
      GO
      ALTER TABLE dbo.Tmp_SPServicePlacementHistory ADD CONSTRAINT
            DF__SPService__DateO__24416500 DEFAULT (getdate()) FOR DateOfChange
      GO
      IF EXISTS(SELECT * FROM dbo.SPServicePlacementHistory)
             EXEC('INSERT INTO dbo.Tmp_SPServicePlacementHistory (DateOfChange, service_placement_id, supplier_id, supplier_client_no, recipient_client_no, case_type, case_sub_type, case_no, ref_no, purchasing_agency, purchasing_team, finance_client_category, supplier_type, service, care_type, special_scheme, room_type, status, status_date, planned_start_date, planned_end_Date, actual_start_date, actual_end_date, setupby_agency, setupby_team, setupby_spno, authorising_agency, authorising_team, authorising_spno, booking_cancelling_agency, booking_cancelling_team, booking_cancelling_spno, placement_cancelling_agency, placement_cancelling_team, placement_cancelling_spno, notes, reason_ended, block_contract, authorising_error, declined_reason, sp_booked, sp_authorised, sp_cancelled, StartReason, doh_code, Urgent, RespiteRules, MAXSOSValue, sp_start_reason, DefaultServiceDetail, distance_to_placement, FirstAdmission, SourceOfAdmission, ServicePriority, TypeOfService, FirstTimeClient, ReferredBefore, ServiceSupport, Proximity, DischargedBy, DischargedTo, Carer, TransferToFrom, TransferTarget)
                  SELECT DateOfChange, service_placement_id, supplier_id, supplier_client_no, recipient_client_no, case_type, case_sub_type, case_no, ref_no, purchasing_agency, purchasing_team, finance_client_category, supplier_type, service, care_type, special_scheme, room_type, status, status_date, planned_start_date, planned_end_Date, actual_start_date, actual_end_date, setupby_agency, setupby_team, setupby_spno, authorising_agency, authorising_team, authorising_spno, booking_cancelling_agency, booking_cancelling_team, booking_cancelling_spno, placement_cancelling_agency, placement_cancelling_team, placement_cancelling_spno, notes, reason_ended, block_contract, authorising_error, declined_reason, sp_booked, sp_authorised, sp_cancelled, StartReason, doh_code, Urgent, RespiteRules, MAXSOSValue, sp_start_reason, DefaultServiceDetail, distance_to_placement, FirstAdmission, SourceOfAdmission, ServicePriority, TypeOfService, FirstTimeClient, ReferredBefore, ServiceSupport, Proximity, DischargedBy, DischargedTo, Carer, TransferToFrom, TransferTarget FROM dbo.SPServicePlacementHistory TABLOCKX')
      GO
      DROP TABLE dbo.SPServicePlacementHistory
      GO
      EXECUTE sp_rename N'dbo.Tmp_SPServicePlacementHistory', N'SPServicePlacementHistory', 'OBJECT'
      GO
      CREATE CLUSTERED INDEX CIX_SPServicePlacementHistory ON dbo.SPServicePlacementHistory
            (
            service_placement_id,
            DateOfChange
            ) WITH FILLFACTOR = 90 ON [PRIMARY]
      GO
      CREATE NONCLUSTERED INDEX NNIX_SPServicePlacementHistory_SupplierID ON dbo.SPServicePlacementHistory
            (
            supplier_id
            ) WITH FILLFACTOR = 90 ON [PRIMARY]
      GO
      CREATE NONCLUSTERED INDEX NNIX_SPServicePlacementHistory_Ref_NO ON dbo.SPServicePlacementHistory
            (
            ref_no
            ) WITH FILLFACTOR = 90 ON [PRIMARY]
      GO
      CREATE NONCLUSTERED INDEX NNIX_SPServicePlacementHistory_Recipient_Client_No_Supplier_Client_No ON dbo.SPServicePlacementHistory
            (
            recipient_client_no,
            supplier_client_no
            ) WITH FILLFACTOR = 90 ON [PRIMARY]
      GO
      ALTER TABLE dbo.SPServicePlacementHistory ADD CONSTRAINT
            PK_SPServicePlacementHistory PRIMARY KEY NONCLUSTERED
            (
            service_placement_id,
            DateOfChange
            ) WITH FILLFACTOR = 90 ON [PRIMARY]
      
      GO
      COMMIT
0
billy21Author Commented:
>duanelawrence

No.  Some idiot has gone to each of our client sites and actually altered my field making it an identity when it shouldn't be.  It's a history table so I need to store multiple records with the same id.
0
Duane LawrenceCommented:
Doh! I hate it when that happens.

The script above looks good to me, I just read it, did not run it.  Paste the script into query analyser, and just run a section at a time, solve one error then move to the next.

Duane
0
SashPCommented:
Have you tried removing the identity field using EM->"Design Table"?

I believe that "Design Table" uses a slightly different approach, however it still must execute the same  commands against the database.

I would be inclined to remove the constraints from the table, remove the constraint drop from the script, re-run the script.

Ignore the final caution "Caution: Changing any part of an object name could break scripts and stored procedures.
The OBJECT was renamed to 'SPServicePlacementHistory'." It always appears when using sp_rename



0
billy21Author Commented:
I just had a thought.  This script needs to drop some default constraints.  The default constraints are most probably named differently at every site.  That means I can't write a generic script to correct this.  It will have to be corrected manually at each site.
0
billy21Author Commented:
>Have you tried removing the identity field using EM->"Design Table"?


Yep that's where that bunch of script I posted came from.
0
SashPCommented:
select object_name(constid) from sysconstraints
where id = object_id('[Table Name]')
0
SashPCommented:
declare @constname varchar(255)
declare @tablename varchar(255)

declare @sql varchar(1000)

declare cur cursor for
select object_name(constid),object_name(id) from sysconstraints
where id = object_id('SPServicePlacementHistory')

open cur

fetch next from cur into @constname, @tablename
while @@fetch_status = 0
begin
   set @sql = 'ALTER TABLE [' + @tablename + '] DROP CONSTRAINT [' + @constname + ']'
   --select @sql
   exec (@sql)
   fetch next from cur into @constname, @tablename
end


close cur
deallocate cur
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.