Remove Identity seed w/ T-SQL

Does anyone know how to remove the Identity attribute of a column using T-SQL. I have created the Identity attribute with the following. I know I can do it with a script created through Enterprise Manager (which is really long and hard to re-create with Dynamic SQL), but I am looking for a one line opposite to the following statement:

IF (SELECT COL_LENGTH('[dbo].tablename', 'UserID')) IS NULL
ALTER TABLE [dbo].tablename ADD UserID INT NOT NULL IDENTITY (1, 1)

something like:
ALTER TABLE [dbo].tablename ALTER COLUMN UserID"REMOVE IDENTITY"

Thanks,
JoshThere is not a simple way to accomplish this. You need basically to drop the column altogether and then add it back to the table. as a non-identity column.

So you could add a new non-identity column to the table (UserIDNEW), copy your UserID data into it, drop the UserID column, then rename your UserIDNEW column back to UserID.

Wish I had better news.

I looked upthis article to explain more concretely what I am talking about.

Terri|||Terri,
I too found the same article. I didn't think there was a simple way to do it, but I thought I would ask.

Thanks,
Josh

Remove identity property of a primary key

Hi,
I have a table with a column named ID as primary key, this column has
the identity property. This ID is referenced by some other tables as foreign
key.
Is there a way, I can use "alter table alter ID int not null...." TSQL
to remove this identity property?
Thanks!
WWW: http://hardywang.1accesshost.com
ICQ: 3359839
yours Hardy1. drop FK
2. sp_rename table with identity to some other name
3. create table new with the same name without identity
4. insert new select * from old
5. create FK
6 drop old
"Hardy Wang" <hardywang@.hotmail.com> wrote in message
news:O7AqRnXrFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a table with a column named ID as primary key, this column has
> the identity property. This ID is referenced by some other tables as
> foreign key.
> Is there a way, I can use "alter table alter ID int not null...." TSQL
> to remove this identity property?
> Thanks!
> --
> WWW: http://hardywang.1accesshost.com
> ICQ: 3359839
> yours Hardy
>|||The only way is dropping the column. Try using EM if you really want to do
this. In EM, before saving changes, press button "save change script", third
from left to right in the tool bar. You will see what really EM does in orde
r
to accomplish this tak.
Example: (from northwind.orders)
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
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.Orders
DROP CONSTRAINT FK_Orders_Shippers
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Orders
DROP CONSTRAINT FK_Orders_Employees
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Orders
DROP CONSTRAINT FK_Orders_Customers
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Orders
DROP CONSTRAINT DF_Orders_Freight
GO
CREATE TABLE dbo.Tmp_Orders
(
OrderID int NOT NULL,
CustomerID nchar(5) NULL,
EmployeeID int NULL,
OrderDate datetime NULL,
RequiredDate datetime NULL,
ShippedDate datetime NULL,
ShipVia int NULL,
Freight money NULL,
ShipName nvarchar(40) NULL,
ShipAddress nvarchar(60) NULL,
ShipCity nvarchar(15) NULL,
ShipRegion nvarchar(15) NULL,
ShipPostalCode nvarchar(10) NULL,
ShipCountry nvarchar(15) NULL
) ON [PRIMARY]
GO
DECLARE @.v sql_variant
SET @.v = N''
EXECUTE sp_addextendedproperty N'MS_Description', @.v, N'user', N'dbo',
N'table', N'Tmp_Orders', N'column', N'OrderID'
GO
ALTER TABLE dbo.Tmp_Orders ADD CONSTRAINT
DF_Orders_Freight DEFAULT (0) FOR Freight
GO
IF EXISTS(SELECT * FROM dbo.Orders)
EXEC('INSERT INTO dbo.Tmp_Orders (OrderID, CustomerID, EmployeeID,
OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName,
ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry)
SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate,
ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion,
ShipPostalCode, ShipCountry FROM dbo.Orders (HOLDLOCK TABLOCKX)')
GO
ALTER TABLE dbo.[Order Details]
DROP CONSTRAINT FK_Order_Details_Orders
GO
DROP TABLE dbo.Orders
GO
EXECUTE sp_rename N'dbo.Tmp_Orders', N'Orders', 'OBJECT'
GO
ALTER TABLE dbo.Orders ADD CONSTRAINT
PK_Orders PRIMARY KEY CLUSTERED
(
OrderID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX CustomerID ON dbo.Orders
(
CustomerID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX CustomersOrders ON dbo.Orders
(
CustomerID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX EmployeeID ON dbo.Orders
(
EmployeeID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX EmployeesOrders ON dbo.Orders
(
EmployeeID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX OrderDate ON dbo.Orders
(
OrderDate
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX ShippedDate ON dbo.Orders
(
ShippedDate
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX ShippersOrders ON dbo.Orders
(
ShipVia
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX ShipPostalCode ON dbo.Orders
(
ShipPostalCode
) ON [PRIMARY]
GO
ALTER TABLE dbo.Orders WITH NOCHECK ADD CONSTRAINT
FK_Orders_Customers FOREIGN KEY
(
CustomerID
) REFERENCES dbo.Customers
(
CustomerID
)
GO
ALTER TABLE dbo.Orders WITH NOCHECK ADD CONSTRAINT
FK_Orders_Employees FOREIGN KEY
(
EmployeeID
) REFERENCES dbo.Employees
(
EmployeeID
)
GO
ALTER TABLE dbo.Orders WITH NOCHECK ADD CONSTRAINT
FK_Orders_Shippers FOREIGN KEY
(
ShipVia
) REFERENCES dbo.Shippers
(
ShipperID
)
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.[Order Details] WITH NOCHECK ADD CONSTRAINT
FK_Order_Details_Orders FOREIGN KEY
(
OrderID
) REFERENCES dbo.Orders
(
OrderID
)
GO
COMMIT
AMB
"Hardy Wang" wrote:

> Hi,
> I have a table with a column named ID as primary key, this column has
> the identity property. This ID is referenced by some other tables as forei
gn
> key.
> Is there a way, I can use "alter table alter ID int not null...." TSQ
L
> to remove this identity property?
> Thanks!
> --
> WWW: http://hardywang.1accesshost.com
> ICQ: 3359839
> yours Hardy
>
>

remove IDENTITY in sql server

suppose my table is
create table TEST (
id NUMERIC(10,0) IDENTITY(1,1) NOT NULL)
I want to remove IDENTITY(1,1) from id without dropping tableHi ankur
I suspect you are planning on sorting out your own no-gaps identity field here. This is a very common request but rarely is a good choice. Before you carry on I would really recommend you just carry for a little longer in this thread:
http://www.dbforums.com/showthread.php?t=1620216
It could save you a heck of a lot of wasted time if it turns out this is not the best way to procede.|||I am toatally new to sql server

so

initailly our project manager has ordered me to put auto incrment

but

now

PM has said to remove auto increment

I am in bit trouble dont know to remove auto increment

so

I asked help from you

help me please|||Look up the ALTER TABLE command in BOL. You will want to pay attention to the DROP COLUMN option.|||beh.

just tell him you removed it, but don't. PM shouldn't be making such low level design decisions!|||why don't you want to drop the table?

create a new one, copy over the numbers (after all, the table has only one column), then drop the old one and rename the new one

something about this whole scenario does not smell right

the project manager is actually you, isn't she|||Think bigger picture

If you still need to retain the id, what is the PM's plan to manage new id's?

And do you have a natural key?|||table is too large but I have taken a small example

there large data in I want not to drop

if there is any query are way to remove IDENTITY(1,1) without effecting
data|||table is too large but I have taken a small example

there large data in I want not to drop

if there is any query are way to remove IDENTITY(1,1) without effecting
data

No, but what's big? ALTER does not allow you to drop an IDENTITY Property on a Column...

As a matter of fact, do you know how save a script in Enterprise Mangler?

go create a test table with an identity column

Then go into EM and remove the identity column and save it...it wil ask you if you want to save the script SQL Server used to make the change...you'll see what SQL Server has to do

Well I can tell you..it it will copy the data to another table where the column will be defined as int, drop the old table and rename the new table to the old table...but go look for yourself

Personally, I'd bdp the data out in native format, create a new table with the definition you want, bcp the data in, rename the old table to something else like table_old, the rename the new table to the old

Again, how many rows are we talking about

Oh...you can disable the identity column in your stored procedures...you are using sprocs as data access only right...and then do

SET IDENTITY_INSERT ON

But that's a hack|||And do you have a natural key?as if this would make a difference to the yes-identity-no-wait-a-sec-no-identity PM|||I want to remove IDENTITY(1,1) from id without dropping tableDropping the identity property can not be done directly, but you can acheive similar effects in more than one way. The problem is that there are many reasons for not wanting to drop the table, and without understanding exactly what you are trying to do or avoid I can't give you clear instructions.

The short answer is to simply recreate a working copy of the table without the identity property, drop the original table, then rename the working copy so it has the same name as the original. This process is easy, safe, and relatively foolproof.

There are a number of other solutions, but without understanding your requirements I have no idea which solution or solutions to suggest.

-PatP|||This may just be naivety on my part but why can't you add a new int column and copy the identity values into the new column, then drop the identity column?|||This may just be naivety on my part but why can't you add a new int column and copy the identity values into the new column, then drop the identity column?Not naive at all George - that's the way I would do it (though with decimal(10) so you can insert half a million records a day for 6 years). There is a downside to that though:
http://www.nigelrivett.net/SQLAdmin/AlterTableProblems.html|||Ahh I see!
Thanks Poots :)

I guess as long as you're aware of the potential problems you can use your best judgement per case.|||Yes I got my answer to remove IDENTITY from column

see attachment set IDENTITY to NO|||see attachment set IDENTITY to NOJust curioius, but do you have any clue what setting IDENTITY to "NO" on this screen will actually do? It will produce exactly the result you have requested, using exactly the method that you wanted to avoid.

As long as you are happy with what you've found, that's fine. It just isn't what you requested! ;)

-PatP|||Yes I got my answer to remove IDENTITY from column

see attachment set IDENTITY to NO

To quote a Guiness Stout Commercial...

"Brilliant!"|||go create a test table with an identity column

Then go into EM and remove the identity column and save it...it wil ask you if you want to save the script SQL Server used to make the change...you'll see what SQL Server has to do

That sounds oddly familiar

Remove Identity column constraint/mgmt

Hello...
We have some tables that had been using Identity columns as a Primary
Key...but we abandoned that approach a few weeks ago and adopted GUIDs
instead.
These tables are included in Publications that were originally on SQL
2000...but we upgraded to SQL 2005.
Is there a way I can remove the Identity constraint from the server
pub...does the Not for Replication handle this?
Or...would be better to Drop the column and reinitialize the subscribers?
thanks for any help
- will
You would be best to drop the identity column, NFR will not drop the
identity column but will not enforce the identity property if the insert is
caused by a replication process.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"dw" <dw@.discussions.microsoft.com> wrote in message
news:16F706A2-F1D3-4215-9A70-DDDD5575920E@.microsoft.com...
> Hello...
> We have some tables that had been using Identity columns as a Primary
> Key...but we abandoned that approach a few weeks ago and adopted GUIDs
> instead.
> These tables are included in Publications that were originally on SQL
> 2000...but we upgraded to SQL 2005.
> Is there a way I can remove the Identity constraint from the server
> pub...does the Not for Replication handle this?
> Or...would be better to Drop the column and reinitialize the subscribers?
> thanks for any help
> - will
|||Thanks for the help. I may test out going the NFR route...just so I don't
have to mess with the table schema too much. Will changing to NFR force a
Re-Init of for the subscribers?
"Hilary Cotter" wrote:

> You would be best to drop the identity column, NFR will not drop the
> identity column but will not enforce the identity property if the insert is
> caused by a replication process.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "dw" <dw@.discussions.microsoft.com> wrote in message
> news:16F706A2-F1D3-4215-9A70-DDDD5575920E@.microsoft.com...
>
>
|||This is something you do on the subscriber - so for transactional
replication it will have no impact on reinitialization. For merge and
updateable subscribers it will.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"dw" <dw@.discussions.microsoft.com> wrote in message
news:C109A5B7-07DE-4E85-88C5-6BA71CBCCEFD@.microsoft.com...[vbcol=seagreen]
> Thanks for the help. I may test out going the NFR route...just so I
> don't
> have to mess with the table schema too much. Will changing to NFR force a
> Re-Init of for the subscribers?
> "Hilary Cotter" wrote:
sql

Remove Identity

I have created an identity column in a table.Now i want to remove the
identity property from the column by using T-SQL script.
Can anyone tell How?
Thanking you!
ToeenHi.
You can use something as this:
Create tha table with Identity...
CREATE TABLE [City]
( [f_city_id] [int] IDENTITY (1, 1) NOT NULL ,
[f_city_desc] [varchar] (50) DEFAULT ('')
) ON [PRIMARY]
GO
And to Modify the column, erasing the property of the Identity.
Alter TABLE City
Alter column f_city_id [int] NOT NULL
Hermilson T.
****************************************
**********************
I have created an identity column in a table.Now i want to remove the
identity property from the column by using T-SQL script.
Can anyone tell How?
Thanking you!
Toeen|||> You can use something as this:
> Create tha table with Identity...
> CREATE TABLE [City]
> ( [f_city_id] [int] IDENTITY (1, 1) NOT NULL ,
> [f_city_desc] [varchar] (50) DEFAULT ('')
> ) ON [PRIMARY]
> GO
> And to Modify the column, erasing the property of the Identity.
> Alter TABLE City
> Alter column f_city_id [int] NOT NULL
You can't clear the IDENTITY property from the table column using the ALTER
TABLE statement. You have to re-create the database and move the data, for
example:
BEGIN TRANSACTION
CREATE TABLE dbo.TemporaryTable
(
id int NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.YourTable)
EXEC('INSERT INTO dbo.TemporaryTable(id)
SELECT id FROM dbo.YourTable TABLOCKX')
GO
DROP TABLE dbo.YourTable
GO
EXECUTE sp_rename N'dbo.TemporaryTable', N'YourTable', 'OBJECT'
GO
COMMIT
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Or you can use a select into statement
select * into newtable from sourcetablewithidentity
drop table sourcetablewithidentity
exec sp_rename dbo.TemporaryTable', 'YourTable'
Dandy Weyn, Belgium
MCSE, MCSA, MCDBA, MCT
http://www.dandyman.net
Check my SQL Server resource pages (currently under construction)
http://www.dandyman.net/sql
"Sebastian K. Zaklada" <szaklada-dont-like-spam@.skilledsoftware.com> wrote
in message news:usLELMK8DHA.1948@.TK2MSFTNGP12.phx.gbl...
> You can't clear the IDENTITY property from the table column using the
ALTER
> TABLE statement. You have to re-create the database and move the data, for
> example:
> BEGIN TRANSACTION
> CREATE TABLE dbo.TemporaryTable
> (
> id int NOT NULL
> ) ON [PRIMARY]
> GO
> IF EXISTS(SELECT * FROM dbo.YourTable)
> EXEC('INSERT INTO dbo.TemporaryTable(id)
> SELECT id FROM dbo.YourTable TABLOCKX')
> GO
> DROP TABLE dbo.YourTable
> GO
> EXECUTE sp_rename N'dbo.TemporaryTable', N'YourTable', 'OBJECT'
> GO
> COMMIT
> sincerely,
> --
> Sebastian K. Zaklada
> Skilled Software
> http://www.skilledsoftware.com
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>|||This is probably not what you want, but there is a chance it will help you.
If all you want to do is insert some values in the identity column yourself
directly, you may be able to get by by doing
set identity insert <tablename> on
Then you can insert your values. When you are done, set identity insert to
off.
Eric
"Toeen" <masha@.brain.net.pk> wrote in message
news:uMU5qUJ8DHA.2168@.TK2MSFTNGP12.phx.gbl...
> I have created an identity column in a table.Now i want to remove the
> identity property from the column by using T-SQL script.
> Can anyone tell How?
> Thanking you!
> Toeen
>|||> select * into newtable from sourcetablewithidentity
> drop table sourcetablewithidentity
SELECT INTO will retain the identity property under SQL 2000. However,
Toeen can specify a column list to either exclude the identity column or
CAST it to a regular numeric value like the example below.
SELECT
CAST(MyIdentityColumn AS int),
MyOtherData
INTO newtable
FROM sourcetablewithidentity
Hope this helps.
Dan Guzman
SQL Server MVP
"Dandy WEYN" <no_spam_info@.dandyman.net> wrote in message
news:402aaa65$0$13253$ba620e4c@.news.skynet.be...
> Or you can use a select into statement
>
> select * into newtable from sourcetablewithidentity
> drop table sourcetablewithidentity
> exec sp_rename dbo.TemporaryTable', 'YourTable'
> --
> Dandy Weyn, Belgium
> MCSE, MCSA, MCDBA, MCT
> http://www.dandyman.net
> Check my SQL Server resource pages (currently under construction)
> http://www.dandyman.net/sql
>
> "Sebastian K. Zaklada" <szaklada-dont-like-spam@.skilledsoftware.com> wrote
> in message news:usLELMK8DHA.1948@.TK2MSFTNGP12.phx.gbl...
> ALTER
for
> rights.
>

Remove Identity

I have created an identity column in a table.Now i want to remove the
identity property from the column by using T-SQL script.
Can anyone tell How?
Thanking you!
ToeenHi
You can use something as this
Create tha table with Identity...
CREATE TABLE [City]
( [f_city_id] [int] IDENTITY (1, 1) NOT NULL
[f_city_desc] [varchar] (50) DEFAULT (''
) ON [PRIMARY
G
And to Modify the column, erasing the property of the Identity.
Alter TABLE City
Alter column f_city_id [int] NOT NUL
Hermilson T
*************************************************************
I have created an identity column in a table.Now i want to remove th
identity property from the column by using T-SQL script
Can anyone tell How
Thanking you
Toee|||> You can use something as this:
> Create tha table with Identity...
> CREATE TABLE [City]
> ( [f_city_id] [int] IDENTITY (1, 1) NOT NULL ,
> [f_city_desc] [varchar] (50) DEFAULT ('')
> ) ON [PRIMARY]
> GO
> And to Modify the column, erasing the property of the Identity.
> Alter TABLE City
> Alter column f_city_id [int] NOT NULL
You can't clear the IDENTITY property from the table column using the ALTER
TABLE statement. You have to re-create the database and move the data, for
example:
BEGIN TRANSACTION
CREATE TABLE dbo.TemporaryTable
(
id int NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.YourTable)
EXEC('INSERT INTO dbo.TemporaryTable(id)
SELECT id FROM dbo.YourTable TABLOCKX')
GO
DROP TABLE dbo.YourTable
GO
EXECUTE sp_rename N'dbo.TemporaryTable', N'YourTable', 'OBJECT'
GO
COMMIT
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Or you can use a select into statement
select * into newtable from sourcetablewithidentity
drop table sourcetablewithidentity
exec sp_rename dbo.TemporaryTable', 'YourTable'
--
Dandy Weyn, Belgium
MCSE, MCSA, MCDBA, MCT
http://www.dandyman.net
Check my SQL Server resource pages (currently under construction)
http://www.dandyman.net/sql
"Sebastian K. Zaklada" <szaklada-dont-like-spam@.skilledsoftware.com> wrote
in message news:usLELMK8DHA.1948@.TK2MSFTNGP12.phx.gbl...
> > You can use something as this:
> >
> > Create tha table with Identity...
> >
> > CREATE TABLE [City]
> > ( [f_city_id] [int] IDENTITY (1, 1) NOT NULL ,
> > [f_city_desc] [varchar] (50) DEFAULT ('')
> > ) ON [PRIMARY]
> > GO
> >
> > And to Modify the column, erasing the property of the Identity.
> >
> > Alter TABLE City
> > Alter column f_city_id [int] NOT NULL
> You can't clear the IDENTITY property from the table column using the
ALTER
> TABLE statement. You have to re-create the database and move the data, for
> example:
> BEGIN TRANSACTION
> CREATE TABLE dbo.TemporaryTable
> (
> id int NOT NULL
> ) ON [PRIMARY]
> GO
> IF EXISTS(SELECT * FROM dbo.YourTable)
> EXEC('INSERT INTO dbo.TemporaryTable(id)
> SELECT id FROM dbo.YourTable TABLOCKX')
> GO
> DROP TABLE dbo.YourTable
> GO
> EXECUTE sp_rename N'dbo.TemporaryTable', N'YourTable', 'OBJECT'
> GO
> COMMIT
> sincerely,
> --
> Sebastian K. Zaklada
> Skilled Software
> http://www.skilledsoftware.com
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>|||This is probably not what you want, but there is a chance it will help you.
If all you want to do is insert some values in the identity column yourself
directly, you may be able to get by by doing
set identity insert <tablename> on
Then you can insert your values. When you are done, set identity insert to
off.
Eric
"Toeen" <masha@.brain.net.pk> wrote in message
news:uMU5qUJ8DHA.2168@.TK2MSFTNGP12.phx.gbl...
> I have created an identity column in a table.Now i want to remove the
> identity property from the column by using T-SQL script.
> Can anyone tell How?
> Thanking you!
> Toeen
>|||> select * into newtable from sourcetablewithidentity
> drop table sourcetablewithidentity
SELECT INTO will retain the identity property under SQL 2000. However,
Toeen can specify a column list to either exclude the identity column or
CAST it to a regular numeric value like the example below.
SELECT
CAST(MyIdentityColumn AS int),
MyOtherData
INTO newtable
FROM sourcetablewithidentity
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dandy WEYN" <no_spam_info@.dandyman.net> wrote in message
news:402aaa65$0$13253$ba620e4c@.news.skynet.be...
> Or you can use a select into statement
>
> select * into newtable from sourcetablewithidentity
> drop table sourcetablewithidentity
> exec sp_rename dbo.TemporaryTable', 'YourTable'
> --
> Dandy Weyn, Belgium
> MCSE, MCSA, MCDBA, MCT
> http://www.dandyman.net
> Check my SQL Server resource pages (currently under construction)
> http://www.dandyman.net/sql
>
> "Sebastian K. Zaklada" <szaklada-dont-like-spam@.skilledsoftware.com> wrote
> in message news:usLELMK8DHA.1948@.TK2MSFTNGP12.phx.gbl...
> > > You can use something as this:
> > >
> > > Create tha table with Identity...
> > >
> > > CREATE TABLE [City]
> > > ( [f_city_id] [int] IDENTITY (1, 1) NOT NULL ,
> > > [f_city_desc] [varchar] (50) DEFAULT ('')
> > > ) ON [PRIMARY]
> > > GO
> > >
> > > And to Modify the column, erasing the property of the Identity.
> > >
> > > Alter TABLE City
> > > Alter column f_city_id [int] NOT NULL
> >
> > You can't clear the IDENTITY property from the table column using the
> ALTER
> > TABLE statement. You have to re-create the database and move the data,
for
> > example:
> >
> > BEGIN TRANSACTION
> > CREATE TABLE dbo.TemporaryTable
> > (
> > id int NOT NULL
> > ) ON [PRIMARY]
> > GO
> > IF EXISTS(SELECT * FROM dbo.YourTable)
> > EXEC('INSERT INTO dbo.TemporaryTable(id)
> > SELECT id FROM dbo.YourTable TABLOCKX')
> > GO
> > DROP TABLE dbo.YourTable
> > GO
> > EXECUTE sp_rename N'dbo.TemporaryTable', N'YourTable', 'OBJECT'
> > GO
> > COMMIT
> >
> > sincerely,
> > --
> > Sebastian K. Zaklada
> > Skilled Software
> > http://www.skilledsoftware.com
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
>

Remove identity

Hi all,

Is there a way to remove the identity from a column
by using alter table?

thanx

Hi,

identity is no property, you have to create another column, copya the values to the new column and drop the old one.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Hi,

In the SQL Server Management Studio Express, when I right click in table, and I choice the modify option, bellow appear the column properties where I set the Identity Specification to No or Yes.

This option disables/enable the identity?

But is there a way to disable the identity via command line?

thanx|||

Hi,
"disabling" the property does the things behind the scenes I mentioned in my earlier post. In SQL 2k (I didn't check that in SQL2k5 right now) it did a creation of a new table, copy the data to the new table, and renamed the table vice versa to the new / old name.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Thank u very much for your help

Remove a constraint

I executed the following sp several times

ALTER PROCEDURE [dbo].[sp_GetItemsByCategoryOnPage]

AS

BEGIN

CREATE TABLE #TemporaryItems

(

ID int IDENTITY PRIMARY KEY,

...

[auto] [bit] NULL CONSTRAINT [DF_CostupdateItems_auto] DEFAULT ((1)),

...

)

...

END

Suddlenly I got a message:

The constraint [DF_CostupdateItems_auto] already exists. How can I remove it from DB?

Hi there,

You can use such a statement :

alter table YourTableNameWithThisConstraint drop constraint [DF_CostupdateItems_auto]

The constraints store in each Table's structures not in global.

__
May the God blessings be.

LotraSoft Ltd.

|||You will get the error message if multiple connections call the SP simultaneously. Even though the temporary table name is uniquefied automatically by SQL Server since you have a named constraint for the default you can't only execute the SP serially. So either remove the constraint name from the column definition so that SQL Server can automatically generate a unique name or don't call the SP concurrently.

Remote Connection Issue

Hello, I have a leb setup to install and test Microsoft Identity Integration Feature Pack. I have two servers running in two different forests and SQL running on one of the servers. I must connect to the SQL server from the othere machine but when I try connecting I get an error message that says "The service account cannot access SQL server. Ensure that the server is accessible, the service account is not a local account being used with a remote SQL server, and that the account doesn't already have a SQL login."

Any ideas what i could be doing wrong?

Hi,

Which Account is used to start the server is it domain account with proper privilege ?

refer the links http://support.microsoft.com/kb/555585 and http://msdn2.microsoft.com/en-us/library/ms143691.aspx for workaround.

Hemantgiri S. Goswami