Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
| Search | Today's Posts | Mark Forums Read
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old February 20th, 2007, 06:59 AM
Authorized User
 
Join Date: Jun 2003
Location: Indore, M.P., India.
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to sandeep
Default dropping the identity property of column

Hi All,

I need to write a SQL script to drop the identity property of the column without dropping the column itself. In SQL Server 2000 this can be achieved as

sp_configure 'allow update', 1
GO
reconfigure with override
GO
update syscolumns set colstat = colstat - 1 /*turn off bit 1 which indicates it's an identity column */
where id = object_id('test')
and name = 'col1'
go
exec sp_configure 'allow update', 0
go
reconfigure with override
go

In SQL Server 2005 this script fails as it does not allow direct updation to system tables. Does anybody know of any workaround for this?

Regards,
Sandeep Saran
  #2 (permalink)  
Old February 22nd, 2007, 03:02 AM
Authorized User
 
Join Date: Sep 2004
Location: Edegem, Antwerpen, Belgium.
Posts: 40
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Why not using an alter table?

alter table TestTable
alter column id int

http://geertverhoeven.blogspot.com
  #3 (permalink)  
Old February 26th, 2007, 02:37 AM
Authorized User
 
Join Date: Jun 2003
Location: Indore, M.P., India.
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to sandeep
Default

That would not drop the identity property of the column.
  #4 (permalink)  
Old March 2nd, 2007, 01:57 PM
Friend of Wrox
 
Join Date: Aug 2004
Location: Orange County, CA, USA.
Posts: 385
Thanks: 0
Thanked 0 Times in 0 Posts
Default

two questions for you....

Why would you want to change the system tables?
Why would the manufacturer of the database want to allow you to modify the system tables?





Similar Threads
Thread Thread Starter Forum Replies Last Post
Removing off the Identity Property happygv SQL Server 2000 17 September 10th, 2007 07:38 AM
Identity Property issue Navy1991 SQL Server 2000 2 January 6th, 2006 05:27 AM
What is @@identity property? subhashbankey SQL Server 2000 2 December 22nd, 2005 09:01 PM
Identity column jbenson001 SQL Server 2000 5 April 12th, 2005 02:01 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.