Changing between Identity type and Int type

What is the TSQL command to chanage a column of int type to identity type
and vice versa?
Thanks in advance!
KMYou can't add the IDENTITY property to an existing table - you need to
create a new table with the IDENTITY column. If you have existing data, you
can create it with a different name, INSERT the existing data, drop the
existing table, then rename the new table. Enterprise Manager will do this
for you if you add the property in the table designer.
HTH. Ryan
"krygim" <krygim@.hotmail.com> wrote in message
news:OccfMRAIGHA.2012@.TK2MSFTNGP14.phx.gbl...
> What is the TSQL command to chanage a column of int type to identity type
> and vice versa?
> Thanks in advance!
> KM
>|||To add on, if your table is having millions of data do go for Enterprise
Manager, you can mess up your life doing so. It will remain in a hanged
situation. So better do it manual ways like creating a new table and renamin
g
droping the original one.
Thanks,
Sree
"Ryan" wrote:

> You can't add the IDENTITY property to an existing table - you need to
> create a new table with the IDENTITY column. If you have existing data, yo
u
> can create it with a different name, INSERT the existing data, drop the
> existing table, then rename the new table. Enterprise Manager will do this
> for you if you add the property in the table designer.
> --
> HTH. Ryan
> "krygim" <krygim@.hotmail.com> wrote in message
> news:OccfMRAIGHA.2012@.TK2MSFTNGP14.phx.gbl...
>
>|||But when I insert the existing data into the new table with the identity
column, how can I make sure that the keys are not changed?
(I am thinking of existing data having integer keys and a lot of unused
numbers due to deletions.)
Ryan wrote:

> You can't add the IDENTITY property to an existing table - you need to
> create a new table with the IDENTITY column. If you have existing data, yo
u
> can create it with a different name, INSERT the existing data, drop the
> existing table, then rename the new table. Enterprise Manager will do this
> for you if you add the property in the table designer.
>|||> You can't add the IDENTITY property to an existing table -
No ,you CAN
create table #t
(
col1 char(1)
)
insert into #t values ('a')
insert into #t values ('b')
insert into #t values ('c')
alter table #t add col2 int identity(1,1)
select * from #t
Actually you cannot EDIT/ALTER an IDENTITY property
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:es%23Y6YAIGHA.2628@.TK2MSFTNGP15.phx.gbl...
> You can't add the IDENTITY property to an existing table - you need to
> create a new table with the IDENTITY column. If you have existing data,
> you
> can create it with a different name, INSERT the existing data, drop the
> existing table, then rename the new table. Enterprise Manager will do this
> for you if you add the property in the table designer.
> --
> HTH. Ryan
> "krygim" <krygim@.hotmail.com> wrote in message
> news:OccfMRAIGHA.2012@.TK2MSFTNGP14.phx.gbl...
>|||My understanding of the question was to add the identity property to an
existing INT column, admitidly my opening sentance should have read :-
You can't add the IDENTITY property to an existing column
rather than
You can't add the IDENTITY property to an existing table -
Apologies for any mis-understanding. Either way KM now has an answer to his
question.
HTH. Ryan
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eiljmwAIGHA.1388@.TK2MSFTNGP11.phx.gbl...
> No ,you CAN
> create table #t
> (
> col1 char(1)
> )
> insert into #t values ('a')
> insert into #t values ('b')
> insert into #t values ('c')
> alter table #t add col2 int identity(1,1)
> select * from #t
>
> Actually you cannot EDIT/ALTER an IDENTITY property
>
> "Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:es%23Y6YAIGHA.2628@.TK2MSFTNGP15.phx.gbl...
>|||Ryan,
In the Enterprise Manager, I can create an int column with non-contiguous
(and even duplicated) values in different records, e.g., 1, 31, 1, 7 in 4
records. (Please note that the values are not in ascending order and two of
them are duplicated.) Then later, I can change the column to an identity
column without any value change. Thus I am sure that rather than dropping
and recreating the column, the Enterprise Manager is doing it in another
way. Please correct me know if I am wrong.
KM
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:es%23Y6YAIGHA.2628@.TK2MSFTNGP15.phx.gbl...
> You can't add the IDENTITY property to an existing table - you need to
> create a new table with the IDENTITY column. If you have existing data,
you
> can create it with a different name, INSERT the existing data, drop the
> existing table, then rename the new table. Enterprise Manager will do this
> for you if you add the property in the table designer.
> --
> HTH. Ryan
> "krygim" <krygim@.hotmail.com> wrote in message
> news:OccfMRAIGHA.2012@.TK2MSFTNGP14.phx.gbl...
type
>|||Hi Ferdinand,
To your question:
You should Set identity_insert on on the new table with the identity
before you insert the data.
The Set will last for the session or until you set identity_insert off.
Example:
Create table #source (cola int, colb int)
insert into #source values (1,2)
insert into #source values (3,4)
Create table #dest (cola int identity, colb int)
set identity_insert #dest on
Insert into #dest (cola,colb)
Select cola, colb
From #source
Select *
from #dest
-- drop table #dest
-- drop table #source
"Ferdinand Zaubzer" wrote:

> But when I insert the existing data into the new table with the identity
> column, how can I make sure that the keys are not changed?
> (I am thinking of existing data having integer keys and a lot of unused
> numbers due to deletions.)
>
> Ryan wrote:
>
>|||Run the SQL Profiler as the EM makes the change. You'll be amazed.
ML
http://milambda.blogspot.com/|||> Thus I am sure that rather than dropping
> and recreating the column, the Enterprise Manager is doing it in another
> way. Please correct me know if I am wrong.
>
Enterprise Manager create a new table, loads data from the old table and
them drops the old table to implement this change. You can verify this with
a Profiler trace or by clicking the 'save change script' button. The end
result is as if the identity property was simply added to the column but the
actual technique used is more involved.
Hope this helps.
Dan Guzman
SQL Server MVP
"Krygim" <krygim@.hotmail.com> wrote in message
news:OrPh5wBIGHA.2912@.tk2msftngp13.phx.gbl...
> Ryan,
> In the Enterprise Manager, I can create an int column with non-contiguous
> (and even duplicated) values in different records, e.g., 1, 31, 1, 7 in 4
> records. (Please note that the values are not in ascending order and two
> of
> them are duplicated.) Then later, I can change the column to an identity
> column without any value change. Thus I am sure that rather than dropping
> and recreating the column, the Enterprise Manager is doing it in another
> way. Please correct me know if I am wrong.
> KM
>
> "Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:es%23Y6YAIGHA.2628@.TK2MSFTNGP15.phx.gbl...
> you
> type
>

Changing a Columns Identity Properties

Hi,
How do I change the identity properties of an existing column using
trans-sql?
Thanks
----
--
----
--Razak wrote:
> Hi,
> How do I change the identity properties of an existing column using
> trans-sql?
> Thanks
> ----
--
> ----[/
color]
What do you mean exactly? The seed? See DBCC CHECKIDENT.
David Gugick
Imceda Software
www.imceda.com|||I meant changing the property of the column to become an IDENTITY column.
This is because the column used to be an identity column, but someone has
messed up with the db and all the identity columns inside all tables has
lost their identity property. The columns are still there. Because of this,
every "INSERT ..." sql scripts to add new records generate error.
Therefore, I need to set back the IDENTITY prop for the id columns in every
table, but I need to do it using T-SQL since the db is in remote sql server.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:ea8pUeiGFHA.1500@.TK2MSFTNGP09.phx.gbl...
> Razak wrote:
> What do you mean exactly? The seed? See DBCC CHECKIDENT.
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Razak wrote:
> I meant changing the property of the column to become an IDENTITY
> column. This is because the column used to be an identity column, but
> someone has messed up with the db and all the identity columns inside
> all tables has lost their identity property. The columns are still
> there. Because of this, every "INSERT ..." sql scripts to add new
> records generate error.
> Therefore, I need to set back the IDENTITY prop for the id columns in
> every table, but I need to do it using T-SQL since the db is in
> remote sql server.
There is no supported SQL to add an identity attribute to an existing
column. Creating a new table and inserting the data between them is the
easiest way (in most cases).
You could use SQL EM, which will try and automate most of the this for
you, probably by creating a new table and then dropping the old one.
See this article for more information:
http://www.windowsitpro.com/Article...2080/22080.html
David Gugick
Imceda Software
www.imceda.com|||Thanks for your reply.. It seems like I will have to there and fix it
locally on the sever.
Thanks again
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:Oj8pn3jGFHA.2616@.tk2msftngp13.phx.gbl...
> Razak wrote:
> There is no supported SQL to add an identity attribute to an existing
> column. Creating a new table and inserting the data between them is the
> easiest way (in most cases).
> You could use SQL EM, which will try and automate most of the this for
> you, probably by creating a new table and then dropping the old one.
> See this article for more information:
> http://www.windowsitpro.com/Article...2080/22080.html
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Razak wrote:
> Thanks for your reply.. It seems like I will have to there and fix it
> locally on the sever.
You don't need to be on the server to fix it, unless I'm
misunderstanding what you're saying. You can connect through any query
tool and execute a custom script to make the change or possibly use SQL
EM (remotely is needed) to make the change. I would encourage you to
test all changes on a dev server first.
David Gugick
Imceda Software
www.imceda.com|||Creating a new copy of the table will break its relationship with other
tables since I need to maintain the values inside the supposedly id column.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OasMiRoGFHA.544@.TK2MSFTNGP12.phx.gbl...
> Razak wrote:
> You don't need to be on the server to fix it, unless I'm misunderstanding
> what you're saying. You can connect through any query tool and execute a
> custom script to make the change or possibly use SQL EM (remotely is
> needed) to make the change. I would encourage you to test all changes on a
> dev server first.
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Razak wrote:
> Creating a new copy of the table will break its relationship with
> other tables since I need to maintain the values inside the
> supposedly id column.
But that's the only solution to your problem AFAIK. If you try and make
the change using SQL EM on a dev server, you can run Profiler at the
same time and capture all the SQL SQLEM is uing to make the change. It
may shed some light on how to script this out yourself. I think the
article I referenced goes into this quite a bit.
David Gugick
Imceda Software
www.imceda.com

Changing a column to an Identity column. Please Help...

I have a situation where we converted over a large database. The database
had one table that used autonumber(Access) that we need to preserve the IDs
upon conversion for a table that refereneces the field. So during
conversion I turned off the identity and it brought over the ID column with
the correct values...but now I find that I can't turn the Identity back on
for that column!!!!!
I am in big trouble here...is there some way to take an existing int, no
null column into an identity column'
Please help.
Thanks,
Ron"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:%23cWhTLZBGHA.3580@.TK2MSFTNGP11.phx.gbl...
> I have a situation where we converted over a large database. The database
> had one table that used autonumber(Access) that we need to preserve the
> IDs upon conversion for a table that refereneces the field. So during
> conversion I turned off the identity and it brought over the ID column
> with the correct values...but now I find that I can't turn the Identity
> back on for that column!!!!!
> I am in big trouble here...is there some way to take an existing int, no
> null column into an identity column'
> Please help.
> Thanks,
> Ron
First of all, you didn't need to turn it off, you could have used SET
IDENTITY_INSERT.
How did you turn it off? With Enterprise Manager?
You should be able to modify the column to Identity with EM.|||Hi
Did you use "SET IDENTITY_INSERT ON" then if you are still using the same
session then you can use SET IDENTITY_INSERT OFF, other/new sessions will no
t
be affected.
If you removed the IDENTITY property, then you will have to create a new
column with the identity property and move the data into it using the above
commands to allow insertion.
John
"RSH" wrote:

> I have a situation where we converted over a large database. The database
> had one table that used autonumber(Access) that we need to preserve the ID
s
> upon conversion for a table that refereneces the field. So during
> conversion I turned off the identity and it brought over the ID column wit
h
> the correct values...but now I find that I can't turn the Identity back on
> for that column!!!!!
> I am in big trouble here...is there some way to take an existing int, no
> null column into an identity column'
> Please help.
> Thanks,
> Ron
>
>|||I tried this but I still get the ERROR:
Msg 8102, Level 16, State 1, Line 2
Cannot update identity column 'ID'.
SET IDENTITY_INSERT [TESTACH].[dbo].[DirectDeposit] ON
GO
UPDATE [TESTACH].[dbo].[DirectDeposit] SET ID = 73986 WHERE DirectDepositID
= 73986
GO
What am I doing wrong'
THANKS!
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:uHwK7ZZBGHA.2840@.TK2MSFTNGP12.phx.gbl...
> "RSH" <way_beyond_oops@.yahoo.com> wrote in message
> news:%23cWhTLZBGHA.3580@.TK2MSFTNGP11.phx.gbl...
> First of all, you didn't need to turn it off, you could have used SET
> IDENTITY_INSERT.
> How did you turn it off? With Enterprise Manager?
> You should be able to modify the column to Identity with EM.
>sql

Changing a column to an Identity column. Please Help...

RSH wrote:
> I have a situation where we converted over a large database. The database
> had one table that used autonumber(Access) that we need to preserve the ID
s
> upon conversion for a table that refereneces the field. So during
> conversion I turned off the identity and it brought over the ID column wit
h
> the correct values...but now I find that I can't turn the Identity back on
> for that column!!!!!
> I am in big trouble here...is there some way to take an existing int, no
> null column into an identity column'
> Please help.
> Thanks,
> Ron
You can't change this property for an existing column and the IDENTITY
value can only be set on INSERT. What this means is that you'll have to
create a new table and INSERT the existing data into it using the SET
IDENTITY_INSERT option. You can then drop the old table and rename the
new one.
If you modify the table in Enterprise Manager (SQL Server 2000) or
Management Studio (2005) then it will make the change for you or will
generate a script to do it.
David Portas
SQL Server MVP
--Okay next question...is there a simple query that will allow me to do
something like...
INSERT * FROM ACHMaster INTO ACH
?
I need someway to perform the insert from one table to the other.
Thanks for all of your help!!!!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1135105250.447899.52020@.g43g2000cwa.googlegroups.com...
> RSH wrote:
> You can't change this property for an existing column and the IDENTITY
> value can only be set on INSERT. What this means is that you'll have to
> create a new table and INSERT the existing data into it using the SET
> IDENTITY_INSERT option. You can then drop the old table and rename the
> new one.
> If you modify the table in Enterprise Manager (SQL Server 2000) or
> Management Studio (2005) then it will make the change for you or will
> generate a script to do it.
> --
> David Portas
> SQL Server MVP
> --
>|||INSERT ACH SELECT * FROM ACHMaster
Note: SELECT * shouldn't be used for production queries. For a one-time
fix, however, it should work, provided the tables were created with the
columns in the same order. It is always best to spell out the column list,
both for the INSERT and the SELECT.
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:etKU9oZBGHA.2476@.TK2MSFTNGP10.phx.gbl...
> Okay next question...is there a simple query that will allow me to do
> something like...
> INSERT * FROM ACHMaster INTO ACH
> ?
> I need someway to perform the insert from one table to the other.
> Thanks for all of your help!!!!
>
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1135105250.447899.52020@.g43g2000cwa.googlegroups.com...
>|||THANKS ALOT!!!!!!
--Ron
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:uybyzPbBGHA.3292@.TK2MSFTNGP09.phx.gbl...
> INSERT ACH SELECT * FROM ACHMaster
> Note: SELECT * shouldn't be used for production queries. For a one-time
> fix, however, it should work, provided the tables were created with the
> columns in the same order. It is always best to spell out the column
> list, both for the INSERT and the SELECT.
>
> "RSH" <way_beyond_oops@.yahoo.com> wrote in message
> news:etKU9oZBGHA.2476@.TK2MSFTNGP10.phx.gbl...
>

change value in a identity column

How can I change the value in a identity column? I cannot use update to change its value.
Thank youIdentity columns can not be updated but you could get around this by doing the following:

--Allows you to enter your own value into the Identity column in
--your table
SET IDENTITY_INSERT [table name] ON
INSERT [table name] ([enter full column list here])
SELECT [New ID], [all other columns]
FROM [table name]
WHERE ID = [Old ID]
SET IDENTITY_INSERT [table name] OFF

DELETE
FROM [table name]
WHERE ID = [Old ID]

You might also want to put all this inside a transaction to ensure everything happens as you expect it too.|||Im just qurious..
Why do you want to change it?|||because i want to remove some old data in a identity enabled table, and i want to change the remind data's identity value back to starting from 1 to ...|||don't do it

see Gaps in autonumber sequences (http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid576584_tax285649,00.html)
(registration may be required, but it's free)

just leave the gaps alone|||what he said.

for whatever reason you think you need them sequential you need to rethink.

If you'd like, post your reasons to this list and folks can help you find better ways to do what you are after....

Originally posted by r937
don't do it

....

just leave the gaps alone|||I have a table which keeps users' saved exams, and it increases fast. I need to remove anything which are 3 months old with a schedule job which will run once a week. Exam ID is the identity field. I dont want to let the Exam ID grow to too big value. So after I removed the old exams, I want to reset the exams ID in that table back to starting with 1.
And that is my case. Thanks for all suggestions.|||Originally posted by cobraeyez
I have a table which keeps users' saved exams, and it increases fast. I need to remove anything which are 3 months old with a schedule job which will run once a week. Exam ID is the identity field. I dont want to let the Exam ID grow to too big value. So after I removed the old exams, I want to reset the exams ID in that table back to starting with 1.
And that is my case. Thanks for all suggestions.

How many exams are taken a day? Consider BOL: an integer can have a value upto 2,147,483,647, a bigint upto 9,223,372,036,854,775,807. Is it really necessary to reset the identity field?|||Originally posted by cobraeyez
I dont want to let the Exam ID grow to too big value.
define "too big"

as Kaiowas suggested, it will be a long time before you run out of numbers

for example, if you add 1,000 new exams every day, guess how long you can keep adding without having to worry about it?

2,147,483,647 / 1000 = 2,147,487 days = 5879.5 years

again, please define "too big" and why it's too big

;)|||Just thought of another reason NOT to do this:
- The GAP indicates there used to be more exams;
- What would you do should you have have to readback from an archive?|||Why do you want to remove more then 3 months?

Why don't you want a large exam id? Is this something you show to the users?

I'd argue that users are used to large numbers they need to write down. I'd also argue that if Fred took a test 9 months ago, and he wanted information about it, you should be able to find it.

Big is a relative term. If you get more then 10,000,000 rows you need to look at your indexes again. Do you give 10,000,000 exams in a 3 month period?

Phone numbers are this long. People can remember them if they need to.

Originally posted by cobraeyez
I have a table which keeps users' saved exams, and it increases fast. I need to remove anything which are 3 months old with a schedule job which will run once a week. Exam ID is the identity field. I dont want to let the Exam ID grow to too big value. So after I removed the old exams, I want to reset the exams ID in that table back to starting with 1.
And that is my case. Thanks for all suggestions.|||well, it seems I really don't need to. The original reason was I was using integer type in VB to retrieve the exam ID. Now I changed it to Long. It should be ok.

Thank for all your help

Change TinyInt identity into SmallInt

Hi,
I have a table BehaviorProp with an TinyInt identity BP_ID, I need to
change the column to a SmallInt.
I tried this:
drop index behaviorprop.PK_BehaviorProp
alter table behaviorprop alter column BP_ID smallint
but I get
An explicit DROP INDEX is not allowed on index
'behaviorprop.PK_BehaviorProp'. It is being used for PRIMARY KEY
constraint enforcement.
How can I remove the primary key constraint and add it again after the
change?
Thanks in advance,
Stijn Verrept.You need to drop the constraint instead...
begin tran
alter table behaviorprop drop constraint PK_Behaviourprop
alter table behaviorprop alter column BP_ID smallint
alter table behaviourprop add constraint PK_behaviourprop primary key (
bp_id )
commit tran
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Stijn Verrept" <TURN_moc.tfosyrtne@.njits_AROUND> wrote in message
news:UcSdnQiSTJvOC_TZRVnytw@.scarlet.biz...
> Hi,
> I have a table BehaviorProp with an TinyInt identity BP_ID, I need to
> change the column to a SmallInt.
> I tried this:
> drop index behaviorprop.PK_BehaviorProp
> alter table behaviorprop alter column BP_ID smallint
> but I get
> An explicit DROP INDEX is not allowed on index
> 'behaviorprop.PK_BehaviorProp'. It is being used for PRIMARY KEY
> constraint enforcement.
> How can I remove the primary key constraint and add it again after the
> change?
> --
> Thanks in advance,
> Stijn Verrept.|||Hi
create table test (c1 tinyint identity(1,1) not null)
insert into test default values
alter table test alter column c1 int
"Stijn Verrept" <TURN_moc.tfosyrtne@.njits_AROUND> wrote in message
news:UcSdnQiSTJvOC_TZRVnytw@.scarlet.biz...
> Hi,
> I have a table BehaviorProp with an TinyInt identity BP_ID, I need to
> change the column to a SmallInt.
> I tried this:
> drop index behaviorprop.PK_BehaviorProp
> alter table behaviorprop alter column BP_ID smallint
> but I get
> An explicit DROP INDEX is not allowed on index
> 'behaviorprop.PK_BehaviorProp'. It is being used for PRIMARY KEY
> constraint enforcement.
> How can I remove the primary key constraint and add it again after the
> change?
> --
> Thanks in advance,
> Stijn Verrept.|||Tony Rogerson wrote:

> You need to drop the constraint instead...
> begin tran
> alter table behaviorprop drop constraint PK_Behaviourprop
> alter table behaviorprop alter column BP_ID smallint
> alter table behaviourprop add constraint PK_behaviourprop primary key
> ( bp_id )
> commit tran
Thanks for your reply, but then I get:
'PK_Behaviourprop' is not a constraint.
and when I look in enterprise manager at constraints there aren't any
there, only the primary key (in the indexes tab).
Kind regards,
Stijn Verrept.|||Oh, I did not care about PRIMARY KEY constraint , see Tony's answer
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ucjy$rMeGHA.4304@.TK2MSFTNGP05.phx.gbl...
> Hi
> create table test (c1 tinyint identity(1,1) not null)
> insert into test default values
> alter table test alter column c1 int
>
> "Stijn Verrept" <TURN_moc.tfosyrtne@.njits_AROUND> wrote in message
> news:UcSdnQiSTJvOC_TZRVnytw@.scarlet.biz...
>|||Need your version, use PRINT @.@.VERSION, the CREATE TABLE script including
indexes and any constraints.
And, the exact error message (cut and paste).
many thanks
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Stijn Verrept" <TURN_moc.tfosyrtne@.njits_AROUND> wrote in message
news:SI-dnbH15pMVBvTZRVnyvQ@.scarlet.biz...
> Tony Rogerson wrote:
>
> Thanks for your reply, but then I get:
> 'PK_Behaviourprop' is not a constraint.
> and when I look in enterprise manager at constraints there aren't any
> there, only the primary key (in the indexes tab).
> --
> Kind regards,
> Stijn Verrept.|||Tony Rogerson wrote:

> Need your version, use PRINT @.@.VERSION, the CREATE TABLE script
> including indexes and any constraints.
> And, the exact error message (cut and paste).
> many thanks
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005
23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer
Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
Error message:
Server: Msg 3728, Level 16, State 1, Line 2
'PK_Behaviourprop' is not a constraint.
Server: Msg 3727, Level 16, State 1, Line 2
Could not drop constraint. See previous errors.
The create table script is at: http://www.entrysoft.com/script.sql it
will create temptest database and will display the error when you try
to run
begin tran
alter table behaviorprop drop constraint PK_Behaviourprop
alter table behaviorprop alter column BP_ID smallint
alter table behaviourprop add constraint PK_behaviourprop primary key (
bp_id )
commit tran
It must be something simple.
Thanks for taking the time!
Kind regards,
Stijn Verrept.|||Not really answering the question but this script will work.... Good (new)
sql 2005 management studio and the create change script option on modify
table ;)
/* To prevent any potential data loss issues, you should review this script
in detail before running it outside the context of the database designer.*/
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
GO
ALTER TABLE dbo.BehaviorProp
DROP CONSTRAINT FK_BehaviorProp_Users
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.BehaviorProp
DROP CONSTRAINT FK_BehaviorProp_Seniors
GO
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_BehaviorProp
(
BP_ID smallint NOT NULL IDENTITY (1, 1),
BP_SNID int NOT NULL,
BP_Stamp smalldatetime NOT NULL,
BP_Score1 tinyint NOT NULL,
BP_Score2 tinyint NOT NULL,
BP_Score3 tinyint NOT NULL,
BP_Score4 tinyint NOT NULL,
BP_Score5 tinyint NOT NULL,
BP_Score6 tinyint NOT NULL,
BP_Desc1 varchar(200) COLLATE Latin1_General_CI_AS NULL,
BP_Desc2 varchar(200) COLLATE Latin1_General_CI_AS NULL,
BP_Desc3 varchar(200) COLLATE Latin1_General_CI_AS NULL,
BP_Desc4 varchar(200) COLLATE Latin1_General_CI_AS NULL,
BP_Desc5 varchar(200) COLLATE Latin1_General_CI_AS NULL,
BP_Desc6 varchar(200) COLLATE Latin1_General_CI_AS NULL,
BP_USID smallint NOT NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_BehaviorProp ON
GO
IF EXISTS(SELECT * FROM dbo.BehaviorProp)
EXEC('INSERT INTO dbo.Tmp_BehaviorProp (BP_ID, BP_SNID, BP_Stamp,
BP_Score1, BP_Score2, BP_Score3, BP_Score4, BP_Score5, BP_Score6, BP_Desc1,
BP_Desc2, BP_Desc3, BP_Desc4, BP_Desc5, BP_Desc6, BP_USID)
SELECT CONVERT(smallint, BP_ID), BP_SNID, BP_Stamp, BP_Score1, BP_Score2,
BP_Score3, BP_Score4, BP_Score5, BP_Score6, BP_Desc1, BP_Desc2, BP_Desc3,
BP_Desc4, BP_Desc5, BP_Desc6, BP_USID FROM dbo.BehaviorProp WITH (HOLDLOCK
TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_BehaviorProp OFF
GO
DROP TABLE dbo.BehaviorProp
GO
EXECUTE sp_rename N'dbo.Tmp_BehaviorProp', N'BehaviorProp', 'OBJECT'
GO
ALTER TABLE dbo.BehaviorProp ADD CONSTRAINT
PK_BehaviorProp PRIMARY KEY CLUSTERED
(
BP_ID
) ON [PRIMARY]
GO
ALTER TABLE dbo.BehaviorProp WITH NOCHECK ADD CONSTRAINT
FK_BehaviorProp_Seniors FOREIGN KEY
(
BP_SNID
) REFERENCES dbo.Seniors
(
SN_ID
) ON DELETE CASCADE
GO
ALTER TABLE dbo.BehaviorProp WITH NOCHECK ADD CONSTRAINT
FK_BehaviorProp_Users FOREIGN KEY
(
BP_USID
) REFERENCES dbo.Users
(
US_ID
) ON DELETE CASCADE
GO
COMMIT
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Stijn Verrept" <TURN_moc.tfosyrtne@.njits_AROUND> wrote in message
news:Ke-dnSqDzbrrO_TZRVny2w@.scarlet.biz...
> Tony Rogerson wrote:
>
> Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005
> 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer
> Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
> Error message:
> Server: Msg 3728, Level 16, State 1, Line 2
> 'PK_Behaviourprop' is not a constraint.
> Server: Msg 3727, Level 16, State 1, Line 2
> Could not drop constraint. See previous errors.
> The create table script is at: http://www.entrysoft.com/script.sql it
> will create temptest database and will display the error when you try
> to run
> begin tran
> alter table behaviorprop drop constraint PK_Behaviourprop
> alter table behaviorprop alter column BP_ID smallint
> alter table behaviourprop add constraint PK_behaviourprop primary key (
> bp_id )
> commit tran
>
> It must be something simple.
> Thanks for taking the time!
>
> --
> Kind regards,
> Stijn Verrept.|||Tony Rogerson wrote:

> Not really answering the question but this script will work.... Good
> (new) sql 2005 management studio and the create change script option
> on modify table ;)
This works indeed! Thanks!
Kind regards,
Stijn Verrept.