Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Altering Identity Column

Posted on 2004-11-09
16
Medium Priority
?
3,084 Views
Last Modified: 2011-09-20
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?
0
Comment
Question by:billy21
  • 7
  • 6
  • 2
  • +1
16 Comments
 
LVL 8

Expert Comment

by:SashP
ID: 12533073
SQL Enterprise Manager removes it by dropping and re-creating the column.
0
 
LVL 6

Author Comment

by:billy21
ID: 12533086
>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
 
LVL 8

Expert Comment

by:SashP
ID: 12533107
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 8

Accepted Solution

by:
SashP earned 1600 total points
ID: 12533142
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
 
LVL 14

Assisted Solution

by:Thandava Vallepalli
Thandava Vallepalli earned 400 total points
ID: 12533168
0
 
LVL 6

Expert Comment

by:Duane Lawrence
ID: 12533184
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
 
LVL 6

Author Comment

by:billy21
ID: 12533528
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
 
LVL 6

Author Comment

by:billy21
ID: 12533532
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
 
LVL 6

Author Comment

by:billy21
ID: 12533552
>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
 
LVL 6

Expert Comment

by:Duane Lawrence
ID: 12533705
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
 
LVL 8

Expert Comment

by:SashP
ID: 12533713
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
 
LVL 6

Author Comment

by:billy21
ID: 12533732
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
 
LVL 6

Author Comment

by:billy21
ID: 12533743
>Have you tried removing the identity field using EM->"Design Table"?


Yep that's where that bunch of script I posted came from.
0
 
LVL 8

Expert Comment

by:SashP
ID: 12533790
0
 
LVL 8

Expert Comment

by:SashP
ID: 12533820
select object_name(constid) from sysconstraints
where id = object_id('[Table Name]')
0
 
LVL 8

Expert Comment

by:SashP
ID: 12534052
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question