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